Thread: New PHP Salesforce Database Replication Script

For the past couple of years I have helped some people with Salesforce, mostly with local replication to both Oracle and MySQl. One thing that I have always tried to accomplish is the ability to increase the functionality of my script so they run better and more efficient.

Most developers that I have talked to know that I use and prefer ADOdb, a open source database abstraction layer (which you can download from my main menu). Well the other day I was digging around the ADOdb site and came across a method inside the documentation that I never new existed and let me tell you it is OUTSTANDING.

the method is called AutoExecute and what it can do for your scripts and the ability and flexibility it gives PHP Salesforce Developers is just amazing. What it can do for you is give you the ability to create replication scripts on the fly for any object, either custom or standard and all you really have to do is change three items in the script, then rename it and run it.

How I get my schema's generated is no secret, I use the mysqlbuilder here at my site, it works flawlessly. It is located at Salesforce MySQL Schema Build Tool

I generate my queries using the Salesforce Data loader located at Apex Data Loader - developer.force.com

I simply select all the fields and then copy and paste them into my IDE application

I will be using the opportunity line item object in this example

So in this script the first thing to change is the table name in the truncate step so you can clean out the table prior to loading data.

PHP Code:

require_once ('/opt/lampp/adodb/adodb.inc.php');

$db = NewADOConnection('mysql');
//MySQL database connection information
$db->Connect("localhost", "mike", "mike", "salesforce");


//Truncate current database to establish a refresh
$sql = "TRUNCATE TABLE sforce_opportunitylineitem";
$db->Execute($sql); 


then I copy in the SOQL that I copied from the data loader;

PHP Code:

$soql = "Select Id, OpportunityId, SortOrder, PricebookEntryId, Quantity,
 TotalPrice, UnitPrice, ListPrice, ServiceDate, Description, CreatedDate, 

CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, 
IsDeleted, Description_c__c, Probability_Percent__c, Socket_Label__c, 
Quote_No__c, 

Price_Type__c, PO_No__c, Probability__c, BU_Allocation__c, Note__c, 
Sampled__c, EV_Kit_Provided__c, Dedicated_FAE__c, Fit__c, maximId__c, 

Competitors__c, Avg_Annual_Volume__c, Potential__c, Max_Potential__c, 

Max_Real__c, Part_Outcome__c, LineItemID__c, Allocation__c, 

Allocation_Percent__c, DWin_Link__c, Socket_Freeze_Date__c, 

No_fit_Lost_Socket_Reason__c, Socket_Strategy_Required__c, PS3_Link__c, TPD__c, 
ADV__c, LTR__c, MP_End_Date__c FROM OpportunityLineItem"; 


Custom fields and all

then here is where the new method takes over, AutoExecute. in the function that are in all of my examples here for replication scripts called store_in_db

PHP Code:

function store_in_db($records, $db)
{
    $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);
            if ($value == "")
            {
                unset($pass_this[$key]);
            }

        }

        $result = $db->AutoExecute('sforce_opportunitylineitem', $pass_this, 'INSERT');
        //$result = $db->AutoExecute('SFORCE_OPPORTUNITYLINEITEM', $pass_this, 'UPDATE', $id);

        if ($result === false)
        {
            echo 'Error updating: ' . $db->ErrorMsg();
            $db->debug = true;

        }
        else
        {
            echo "record inserted\n";
        }
        $rows_loaded++;
    }
    return $rows_loaded;
} 


if you pay close attention to the line:

PHP Code:

$result = $db->AutoExecute('sforce_opportunitylineitem', $pass_this, 'INSERT'); 


This is were the function does everything for you. All you do is pass the parameters of the table name, the array of data, and what you are trying to accomplish. It handles all the data conversations, money conversations, all data conversions for you and inserts the data into the database. I have not had one error at all and it is great.

So you are asking how can this work on the fly, so all you really have to do to create another object replication is change the table name in 2 places and the SOQL out of your data loader and bam, you're done.

I will attach a full example for the opportunity line item, please use it as you need and I hope you have the same success that I have had

Thanks!!


~Mike