Thread: synchronizing salesforce and a mysql database

There is a feature in APEX that can call a web service. I have not used it yet, just played around with it but have not tried to call. I am going to be doing this in the next few weeks to see if we can actually insert records into Oracle and MySQL.

I have however provided some replication examples from Salesforce to MySQL in the past on this site and will post another one. This one exports from the user object in Salesforce to a table called sforce_user in MySQL.

PHP Code:

<?php

ini_set("soap.wsdl_cache_enabled", "0");
error_reporting(E_ALL);
include_once ('/opt/lampp/htdocs/adodb/adodb.inc.php');
require_once ('/opt/lampp/htdocs/includes/sfdc_new.inc');

$db = ADONewConnection("mysql");


//MySQL database connection information
$db->Connect("localhost", "mike", "mas4155", "salesforce");
$db->debug = true;

//Truncate current database to establish a refresh
$table = "sforce_user";
$sql = "TRUNCATE TABLE $table";
$db->Execute($sql);
if ($db)
{
    echo "table truncated\n";
}
else
{
    echo "Failed";
    var_dump($db->ErrorMsg());
    exit();
}

$soql = "Select Id, Username, LastName, FirstName, Name, CompanyName, Division, Department, Title, Street, City, State, PostalCode, Country, Email, 
Phone, Fax, MobilePhone, Alias, IsActive, TimeZoneSidKey, UserRoleId, LocaleSidKey, ReceivesInfoEmails, ReceivesAdminInfoEmails, EmailEncodingKey, 
ProfileId, LanguageLocaleKey, EmployeeNumber, LastLoginDate, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, 
OfflineTrialExpirationDate, OfflinePdaTrialExpirationDate, UserPermissionsMarketingUser, UserPermissionsOfflineUser, UserPermissionsAvantgoUser, 
ForecastEnabled FROM User";

//Processes the query to get account information from Salesforce
$records = get_records($client, $soql, $db);

exit;

function get_records($connection, $query, $db)
{

    //Set this to the number of records to process per batch
    //200 is the minimum
    $queryOptions = new QueryOptions(2000);
    $connection->setQueryOptions($queryOptions);

    $response = $connection->query(($query), $queryOptions);
    //echo '<pre>' . print_r($response, true) . '</pre>';

    //for debugging


    if ($response->size > 0)
    {
        $records = $response->records;

        // Cycles through additional responses if the number of records// exceeds the batch size
        $count_records = 0;
        while (!$response->done)
        {
            $records = $response->records;
            set_time_limit(100);
            ini_set("memory_limit", "512M");


            //Process curent $records
            $current_count = store_in_db($records, $db);

            if ($current_count === false)
            {
                return false;
            }
            else
            {
                $count_records += $current_count;
            }
            echo "processed " . $count_records . " records\n";
            flush();

            $response = $connection->queryMore($response->queryLocator, $queryOptions);


        }
        set_time_limit(100);
        $records = $response->records;
        //Process curent $records

        //store the last set of records into the database
        $current_count = store_in_db($records, $db);

        if ($current_count === false)
        {
            return false;
        }
        else
        {
            $count_records += $current_count;
        }
    }
    echo "processed " . $count_records . " records\n";
    return $count_records;
}


function store_in_db($records, $db)
{
    global $table;
    $record_count = count($records);
    $rows_loaded = 0;

    foreach ($records as $r)
    {
        $pass_this['id'] = $r->Id;
        foreach ($r->fields as $key => $value)
        {
            //$pass_this[$key] = addslashes($r->fields->$key);
            $pass_this[$key] = $r->fields->$key;
        }

        $insertSQL = $db->AutoExecute($table, $pass_this, 'INSERT');

        $rows_loaded++;

    }
    return $rows_loaded;
}

?>

You have to use the ADOdb php database abstraction layer class (you can download that from my main menu, it's free.

then perform the query that you want to do and make sure that the fields match your tables.

I just wrote the script above. I also used the MySQL schema generation tool from my site (off my main menu also) and created a schema based off of my production organization.

PHP Code:

include_once ('/opt/lampp/htdocs/adodb/adodb.inc.php');
require_once ('/opt/lampp/htdocs/includes/sfdc_new.inc'); 


These two lines need to be altered and the second one, sfdc_new.inc is a simple class that contains all your login and client setup and stores the value in a variable called $client. Here is that small script:

PHP Code:

<?php


require_once ('/opt/lampp/htdocs/includes/soapclient_new/SforcePartnerClient.php');
require_once ('/opt/lampp/htdocs/includes/soapclient_new/SforceHeaderOptions.php');
// Login to salesforce.com

$login = "user@email.com";
$password = "password";
$key = 'Your Key'


$password = $password.$key;

$wsdl = "/opt/lampp/htdocs/includes/soapclient_new/partner.wsdl.xml";

$client = new SforcePartnerClient();
$client->createConnection($wsdl);

try
{
    $loginResult = $client->login($login, $password);
   
    if ($loginResult->passwordExpired)
    {
        $client = null;
    }
}
catch (exception $e)
{
    
    $client = null;
    echo '<pre>'.print_r($e,true).'</pre>';
}
?>

You can actually use that for a centralized location for all your php scripts in case you have to change your password or key!


There are other methods and if you want to explore that, we can. You can take a look at the API docs for getUpdated and getDeleted?

I have an example for that with Oracle, but that can be easily changed


Hope this helps

~Mike

Cool this looks really useful,

So once I have this script or a similar kind up and running is there a way to get it to update the mysql tables automatically everytime a change is made on salesforce

Thank you

No, not currently! There is no way that you can kick this off to match your data in Salesforce. I run these scripts nightly on a CRON job to keep the data up to date as much as possible

Again though, there is a way to call web service with APEX. So you could write a trigger to call a class or a web service and then that could possibly run the script but I am looking into that this week and next. Once the trigger runs like after update, then that could send the data to your database

~Mike

This is a good tutorial that might help (cheenath.com)

That is a really good tutorial man!!! Thanks for posting that. I will try that out and see if I can do some more things with that. This maybe the start of something good with PHP, if it can handle it

~Mike

@ t14 -
\

I setup that tutorial and it works great, but I cannot figure out, or if it is even possible, to send the data from the APEX class to the endpoint. In the tutorial it sends out a request and then reads the entire end point file and gets the body of the script. Now I find a way to send the ID of the account that was just created from the trigger.

If you could send out the ID of the account (or whatever object that you have the data on) then you could possibly do something with the script.

It does send everything via GET so it is possible. I will keep playing around with this and see if there is a way to send out data from Salesforce.

So if that becomes possible, it would be possible to enter that new account into a local database such as Oracle or MySQL. That would be outstanding

There could be more to this and I will keep looking into it

~Mike

I actually made some more progress on this and can receive the ID and NAME via post. I will share that tomorrow morning man!! I do really appreciate you sharing that tutorial with me

~Mike