Benefits of SOAP Web Service over ADO?

[WizRider]

Hi all.
Forgive this newb question (and also forgive me if its been asked before but I couldn't find an answer using Search):

I'm looking at integrating to the SugarCRM system from a custom written app. I've managed to prove the connectivity to the web services provided using .NET and an appropriate .WSDL file.

So far so good. However, beyond a few methods that I've used to prove the concept so far ("get_available_modules") I am yet to start to get very deep in the functions provided. From what I have seen though, in order to get access to, let's say, a set of meetings, I need to use a "get_entry_list" command along with the module name, query string, order by clause etc. etc.

Now, it seems to me that, and please correct me if I am wrong here, but I can't easily perform a join at this time using this syntax. Is this wrong? Can I put join clauses in the query string somehow? What format do they follow?

If I can't do more complex data retrievals using joins etc. then what is the benefit of using the Web Services over just connecting via ADO/ODBC and performing straightforward SQL statements on the relevant tables using joins etc. to get the data just as I want it in a recordset?

Am I missing something? I was hoping the Web Service methods would supply me with some higher level methods (such as Get_Meeting, Get_Contact, etc. etc.) rather that with some low level inline SQL style queries).

Please don't mistake this for bashing - I'm a newb to SugarCRM and am probably missing something - hence the reason for this post.

Anyone care to offer an opinion?
Cheers...

[monkey]

The benefit to using the SOAP interface is that if you ever decide to set values in the database you don't have to figure out how to create UUID's which are used for the ID values of all the objects.

Also when using the SOAP interface getting mettings etc isn't too difficult. What kind of joins are you looking to do? You should have access to most of the fields you would want to query on when getting the value.

I also started using the SOAP interface recently and it took about a day of playing around and testing out various queries for the data I wanted. Since there isn't much in the way of documentation you really do have to play around a bit. If you have an idea of what kind of query you are trying to do I (or someone else) may be able to help you craft the query string that you would need to use.

[julian]

Hello WizRider,

Currently, the SOAP interface's joins are limited to a hardcoded list (mostly Leads, Bugs, and Cases). In 4.5, we plan to release a more generic API that will allow you to pull records related between any two modules. This function is the meat of that feature:

 // Retrieves array of ids for records of $get_module linked to $from_module by $get_id
// Example: to retrieve list of Contacts associated to Account X: $return = get_linked_records("Contacts", "Accounts", "X");
function get_linked_records($get_module, $from_module, $get_id) {
    global $beanList, $beanFiles;
    $error = new SoapError();
 
    // check to make sure both modules exist
    if (empty($beanList[$get_module]) || empty($beanList[$from_module])) {
        $error->set_error("no_module");
        return $error->get_soap_array();
    }
 
    if( ! is_array($get_id)) {
        $idList = array($get_id);
    } else {
        $idList = $get_id;
    }
    
    // instantiate and retrieve $from_module
    $from_class = $beanList[$from_module];
    require_once($beanFiles[$from_class]);
 
    $returnList = array();
    
    foreach($idList as $get_id) {
        $from_mod = new $from_class();
        $from_mod->disable_row_level_security = TRUE;
    
        $from_mod->retrieve($get_id);
    
        // loop through link fields of $from_module, checking for a link to $get_module
        foreach ($from_mod->get_linked_fields() as $linked_field) {
            $from_mod->load_relationship($linked_field['name']);
            $field = $linked_field['name'];
    
            if (empty($from_mod->$field)) {
                continue;
            }
    
            // link between $from_module and $get_module was found
            // retrieve and return list of linked records
            if ($from_mod->$field->getRelatedModuleName() == $get_module) {
                $id_arr = $from_mod->$field->get();
                // This kludge is because I couldn't get php to merge the array for me
                foreach($id_arr as $oneID) 
                    $returnList[] = $oneID;
            }
        }
    }
        
    if(is_array($returnList) && $returnList != "") {
        return $returnList;
    }
    // no link between $get_module and $from_module found
    $error->set_error('no_relationship_support');
    return $error->get_soap_array();
} 
  

You can customize the portal_get_entry_list() function in ./soap/SoapPortalUsers.php to use this instead of get_related_list() to pull related records. In 4.5, this should be part of the base code.

[thierry.beeckmans]

You can customize the portal_get_entry_list() function in ./soap/SoapPortalUsers.php to use this instead of get_related_list() to pull related records. In 4.5, this should be part of the base code.

I've noticed that I had an older 4.5 version which didn't include those changes.
I thought to replace only those SOAP files but now I can't pull a thing out of it.

     $result = $proxy->get_relationships(
        $sess,
        'Accounts',
        'b22b0137-00e1-f5c6-3f64-460bc6ae3506',
        'Contacts',
        '',
        false
    );
    echo '<pre>';
    print_r($result);
    echo '</pre>'; 
  

Array
(
    [ids] => Array
        (
        )

    [error] => Array
        (
            [number] => 0
            [name] => No Error
            [description] => No Error
        )

)

Using the SOAP functions delivered with the older 4.5.1 I received the following output

Array
(
    [ids] => Array
        (
            [0] => Array
                (
                    [id] => b50f6f78-7e9e-e211-470c-45f694aefbcc
                    [date_modified] => 2007-04-25 14:15:56
                    [deleted] => 0
                )

            [1] => Array
                (
                    [id] => ebfaf5e4-2fac-feba-8f45-460cf3d9a37e
                    [date_modified] => 2007-06-21 09:35:57
                    [deleted] => 0
                )

        )

    [error] => Array
        (
            [number] => 0
            [name] => No Error
            [description] => No Error
        )

)

Is this because other changes to the sugarbean has been done, or do I pass wrong variables?