+ Reply to Thread
Results 1 to 5 of 5

Thread: mysql scripts with Salesforce.

  1. #1
    ncee is offline Junior Member
    Join Date
    Oct 2007
    Posts
    14

    Unhappy mysql scripts with Salesforce.

    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"?

  2. #2
    mike's Avatar
    mike is offline Administrator
    Join Date
    May 2007
    Location
    Wylie, Texas
    Posts
    557
    Blog Entries
    15
    Quote Originally Posted by ncee View Post
    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

  3. #3
    ncee is offline Junior Member
    Join Date
    Oct 2007
    Posts
    14
    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.

  4. #4
    mike's Avatar
    mike is offline Administrator
    Join Date
    May 2007
    Location
    Wylie, Texas
    Posts
    557
    Blog Entries
    15
    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. #5
    Unregistered Guest

    Hi

    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 !

+ Reply to Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

SEO by vBSEO 3.5.1