Issue with get_entry_list soap call with SQL Server 2005

[mickeyhaynes]

I am using Sugar Open Source Version 4.5.1b (Build 1246) with PHP on SQL Server 2005. The client application is written in C# using Visual Studio 2005.

The issue arises when executing a subquery through the get_entry_list web service. The underlying SQL that is generated by the web service is not what is expected and creates a SQL exception.

When calling the get_entry_list service with the following parameters:

module_name = "Opportunities"
query = "opportunities.id in (select opportunity_id id from opportunities_contacts where contact_id = '<<contactId>>)'"
order_by = ""
offset = 0
select_fields = {"id", "name"}
max_results = 100
deleted = 0


A SQL Exception is generated. Using SQL Trace to inspect the SQL created by the web service shows the following:

SELECT count(*) c FROM opportunities LEFT JOIN users
ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities
ON opportunities.id=accounts_opportunities.opportunit y_id
LEFT JOIN accounts
ON accounts_opportunities.account_id=accounts.id
where (opportunities.id in (SELECT count(*) c FROM opportunities_contacts where contact_id = '<<contactid>>'))
AND (accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0)
AND (accounts.deleted is null OR accounts.deleted=0)
AND opportunities.deleted=0

The key issue is in the line:
where (opportunities.id in (SELECT count(*) c FROM opportunities_contacts where contact_id = '<<contactid>>'))

The correct SQL should be:
where (opportunities.id in (SELECT opportunity_id id FROM opportunities_contacts where contact_id = '<<contactid>>'))

The web service is apparantly changing my subquery to try to get a Count, but this creates a SQL exception.

Please advise. Regards....

[julian]

Hello mickeyhaynes,

It looks like the culprit is this line in ./data/SugarBean.php:

         $pattern = '/SELECT(.*?)(\s){1}FROM(\s){1}/is';  // ignores the case
        $replacement = 'SELECT count(*) c FROM ';
        $modified_select_query = preg_replace($pattern, $replacement, $query); 
  

I see why this method exists (to create COUNT() statements from generic queries), but it should not be applied to the query you pass in the SOAP call. For now, you have three options:

1) change your SOAP application so it does not rely on a subquery in the WHERE clause (these will probably not be supported in the revamped SOAP API for Sugar 5.0)
2) change the Sugar code (in SugarBean.php) to add an exception if the query is coming from SOAP
3) modify your subquery to try and sidestep the preg_replace() call

This is something I'd like to fix in the product as well, but it'll require re-working a few parts of the API. It'd be a good idea to file a bug for this on http://bugs.sugarcrm.com/ -- you can reference this forum thread when posting the bug.

Thanks!

[mickeyhaynes]

Thank you, Julian, for your prompt response. I have posted a bug #12878 for this issue.

Unfortunately, in my case I cannot change the PHP code on the server, so I will need to come up with a client-side solution. Do you have an alternative suggestion to get opportunities for a contact without using the get_entry_list subquery method?

Thanks again...

[julian]

Hey mickeyhaynes,

Definitely. Take a look at the get_relationships() SOAP method. Prior to 4.5.1c, many relationships were not available through this method, but that's since been fixed. You'll want to make a call similar to this:

 $output = $client->get_relationships($session_id, "Contacts", "<your Contact GUID>", "Opportunities", "", 0); 
  

[mickeyhaynes]

That worked perfectly, thanks Julian! I suppose there are quite a few knowledge base articles and code samples out there that need to be updated. Better documentation on the soap.php would also be great!

Thanks again.

[rayner.jrp]

How can i get an unlimited number of elements with get_entry_list method througth soap api of sugar?