+ Start a Discussion

help with sf data query and mapping to sql db?

Hi i have object in salesforce that contains a city called Unterföhring it contains some weird character now when i do a sql on local db to find this city which it does contain Unterföhring i keep getting a no match.


$querySF = "SELECT Id, Name, Building_Name__c, Street__c, Building_Number__c, Postcode__c, City__c, Country__c, Building_Type__c, Building_Status__c, Building_Entry__c, Fibre_Access__c, POP_Type__c, Wayleave_Status__c ";
$querySF .= "FROM Location__c WHERE Active__c = true AND EXT_ID__c = null";

$resLocation = $sforce -> do_query($querySF, 2000);

foreach $location ( @{$resLocation} ) {

    $add = 1;
    $locationId = $location -> {'Id'}['Id'];

#print $locationId." ".$location -> {'Name'}." ".$location -> {'City__c'}."\n";

    # get tt city id
    $queryTT = "SELECT * FROM h_city WHERE name = '".$location -> {'City__c'}."'";
    $st = $db -> prepare($queryTT);
    $st -> execute;
    $row = $st -> fetchrow_hashref;
#    print $row -> {'h_city_id'}."\n";
    $cityID = $row -> {'h_city_id'};

    if ($row -> {'h_city_id'} < 1) {
        $add = 0;


I am assuming that this is the problem   $queryTT = "SELECT * FROM h_city WHERE name = '".$location -> {'City__c'}."'"; it cannot match the name?

This is not my code and i am not a perl expert?


Seems to me most likely that your local database does not support Unicode characters, and so either the query is confusing it or the word Unterföhring cannot exist in it and so it finds nothing.  The query in Salesforce.com should work fine since Salesforce.com is inherently Unicode.


i think the table is latin based, should i convert the city__c to latin and then pass that to db sql query?


Maybe try pig latin, like nterföhringuay.


Just kidding.  If you can, you should try to make it so that your local table stores Unicode.  Whatever's in that table now for the U with the umlaut is probably some nonsense character which is going to make it hard for you to query on.