mysql scripts with Salesforce.

ncee · #1 12-06-2007, 12:30 PM

I'm trying to build a mysql script to find duplicate records in salesforce. This is what I tried:

PHP Code:


			
$query = "SELECT Id, Name, Email, count(Email) from contact 
                    group by Email 
                    having count(Email) > 1 
                    order by Email;";

and I got this error:

Fatal error: Uncaught SoapFault exception: [ns1:MALFORMED_QUERY] MALFORMED_QUERY: SELECT Id, Name, Email, count(Email) from contact ^ ERROR at Row:1:Column:25 unexpected token: count in C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php:457 Stack trace: #0 [internal function]: SoapClient->__call('query', Array) #1 C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php(457): SoapClient->query(Array) #2 C:\xampp\htdocs\xampp\Salesforce\duplicate.php(23) : SforceBaseClient->query('SELECT Id, Name...') #3 {main} thrown in C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php on line 457

Am I writing the script wrong or does salesforce not like the word "count"?

mike · #2 12-06-2007, 01:05 PM

Quote:

Originally Posted by ncee

I'm trying to build a mysql script to find duplicate records in salesforce. This is what I tried:

PHP Code:


			
$query = "SELECT Id, Name, Email, count(Email) from contact 
                    group by Email 
                    having count(Email) > 1 
                    order by Email;";

and I got this error:

Fatal error: Uncaught SoapFault exception: [ns1:MALFORMED_QUERY] MALFORMED_QUERY: SELECT Id, Name, Email, count(Email) from contact ^ ERROR at Row:1:Column:25 unexpected token: count in C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php:457 Stack trace: #0 [internal function]: SoapClient->__call('query', Array) #1 C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php(457): SoapClient->query(Array) #2 C:\xampp\htdocs\xampp\Salesforce\duplicate.php(23) : SforceBaseClient->query('SELECT Id, Name...') #3 {main} thrown in C:\xampp\htdocs\xampp\Salesforce\includes\soapclie nt\SforceBaseClient.php on line 457

Am I writing the script wrong or does salesforce not like the word "count"?

Have you tried your query in the sforce explorer? You can test your queries there and if you can get them to work, then use them in a php script

One thing that you also may try to do is to create local copies of your salesforce contact table and then run the query locally, then use that data to find the duplicates. Once you have the duplicates, you can use the delete() command and run a php script to delete those records

ncee · #3 12-06-2007, 02:42 PM

I downloaded sforce explorer (very nice tool) SourceForge.net: Downloading ... . I tried using putting the script

Code:

SELECT Id, Name, Email, count(Email) from contact 
                    group by Email 
                    having count(Email) > 1 
                    order by Email

into the sforce explorer, but it gave me an error at the first "count" say "unexpected token: count."

I don't want to create a copy of the database and run the query locally. I'm guessing that salesforce doesn't like count.

mike · #4 12-06-2007, 03:49 PM

The Salesforce SQL, also known as SOQL, is limited in its query statements when trying to perform group by, limit, order by and other standard SQL statements. This is the reason that we download our Org's data to a local DB and then we can perform all the queries needed to perform such actions that you are trying to do

Anyway thanks for posting on my site and please do not hesitate to ask any kind of question that you have.

Best of luck

~Mike

#5 12-12-2007, 03:30 AM

The SOQL does not allow the count fonctionnality. Even if the order by and limit is allowed !!!
But once u've got your records array you can check his length to have the total of records !