PHP & Salesforce - Full Data Extract Process Tutorial

mike · #1 06-18-2007, 02:33 PM

I have successfully created an entire process that connects to Salesforce via the API using PHP to download each table that we backup in our database (Oracle 10g).

This process connects to the API via PHP's built in SOAP using the partner.wsdl file and then using the ADOdb database abstraction php class to insert the records via a PL/SQL stored procedure.

This new process replaces what I call the legacy system we had in place to download our marked tables using the DataLoader and storing the data into .CSV files. Then using SQL *Loader to truncate the tables and load the data fresh every time the process runs. This whole process could take up to 1 hour to download and load larger objects such as:

Product2 - which has 130,000 plus records
Opportunity - which has 60+ columns/fields and 64,000 records
and others

The PL/SQL Procedure is relatively easy to run if you want to test it out for yourself. If you choose just the ID and Description fields to test on the Opportunity Object, you will need to do some manipulation because Salesforce set the size of the Description field to 32000 bytes, which can only be defined in Oracle as a CLOB:

Code:

CREATE OR REPLACE PROCEDURE sforce_opp_insert_test (
   pm_id            IN  VARCHAR2,
   pm_description   IN  CLOB DEFAULT EMPTY_CLOB()
)
IS
BEGIN
      INSERT INTO php_opportunity
                  (ID, description)

           VALUES (pm_id, pm_description);
COMMIT;
END sforce_opp_insert_test;

/

If you are using ADOdb as your abstraction layer to connect to Oracle, you will need to utlize the OCI_B_CLOB so PHP knows how to deal with the CLOB when inserting the CLOB field into the database:

PHP Code:


			
$conn->InParameter($stmt, $description,'pm_description', -1, OCI_B_CLOB);

This is actually a <a href="http://bugs.php.net/?id=41711">bug</a> in PHP but there is a way around it. If any of the CLOB fields are NULL, meaning if any of the description fields do not have any data, then you have to populate the CLOB with some default text. Here is an example of what I did:

PHP Code:


			
if (strlen($r->fields->Description) == 0)
{
  $description = "No Description Posted";
}
else
{
  $description =  $r->fields->Description;
}

This checks to see if the string length is Zero and if it is, it populates a simple > "No Description Posted", so the field is not null and your PHP script will not render a FATAL error (as I found out in testing)

I have tested this script and the real scripts that I have written and they work flawlessly. If anyone has any questions then feel free to contact me

Here is a full example of a script that will work:

PHP Code:


			
 ini_set("soap.wsdl_cache_enabled", "0");

require_once ('../includes/soapclient/SforcePartnerClient.php');
require_once ('../includes/soapclient/SforceHeaderOptions.php');
require_once ('adodb.inc.php');# load code common to ADOdb

$conn = ADONewConnection("oci8");
$_ret = $conn->Connect('', "database user", "password", "database");

//TRUNCATEs the table each time the script is ran from Salesforce
$sql = "TRUNCATE TABLE PHP_OPPORTUNITY";

if ($conn->Execute($sql))





// Salesforce Login
$wsdl = '../includes/soapclient/partner.wsdl.xml';
$userName = "me@mymail.com";
$password = "password";

$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName, $password);
$rows_loaded = 0;

//this processes the function get_accounts to perform your extract from your
//instance of salesforce
$accounts = get_accounts($client);

if ($accounts)
{
  echo 'There are currently ' . count($accounts) . ' opportunities:
';
  foreach ($accounts as $r)
  {
        $r = new SObject($r);

        //set up PL/SQL Statement
        $strsql = "begin sforce_opp_insert_test(";
        $strsql .= ":pm_id,";
        $strsql .= ":pm_description";
        $strsql .= "); end;";

        $id = $r->Id;

        if (strlen($r->fields->Description) == 0)
        {
          $description = "No Description Posted";
        }
        else
        {
          $description =  $r->fields->Description;
        }

        //Prepares PL/SQL Statement
        $stmt = $conn->Prepare($strsql);
        $conn->InParameter($stmt, $id, 'pm_id');
        $conn->InParameter($stmt, $description,'pm_description', -1, OCI_B_CLOB);

        $rconn = $conn->Execute($stmt);

        if (!$rconn) {
            echo $conn->ErrorMsg();
            echo '' . print_r($r->fields, true) . '';
            exit;
        }
  }
  echo $rows_loaded;
}



//This is the function that performs the extract from salesforce
//and loops through all your data until the last record is
//returned
function get_accounts($connection)
{

  $query = "Select Id, Description, Project_Comments__c FROM Opportunity";

  //lets set this to get 500 records each time
  $queryOptions = new QueryOptions(500);
  $response = $connection->query(($query), $queryOptions);

    // Checks response records
    if ($response->size > 0)
    {
        $accounts = $response->records;
            // Cycles through additional responses if the number of records
            // exceeds the batch size
        while (!$response->done)
        {
            set_time_limit(100);
            $response = $connection->queryMore($response->queryLocator);
            $accounts = array_merge($accounts, $response->records);
        }
    }

  return $accounts;
}

?>

mike · #2 06-25-2007, 01:59 PM

I have encountered some errors while doing this due to some tables/objects being so large that the php scripts are timing out and cannot finish their downloads from Salesforce.com. Each table download has its own script and, as I have stated before, some of data in these tables have large values.

An example of the problem lies in the Opportunity object, which has over 50,000 records, with 40 plus columns. You can imagine the amount of data that is being downloaded. If the scripts are not written properly, your script will timeout

I am going to continue to develop these scripts and see if I can get a clean run without a timeout over a weeks time. If the scripts can run for a week, multiple times a day (so the data in the locale Oracle database will remain fresh) and run without any such error or timeout that may occur

Mike

mike · #3 06-26-2007, 07:21 AM

Since I stated in an earlier post about the scripts that were not finishing, I did some small changes within the main function to increase the run time of the loop while retrieving data from Salesforce.com.

It has to do with increasing the time limit so the script will basically reset itself each time it retrieves 500 records. The built in function in php is:

Code:

set_time_limit(300);

Here is an entire, working, function that can be modified to download the opportunity table and then do with it what you wish:

PHP Code:


			
function get_accounts($connection)
{

  $query = "Select Id, AccountId, Name, Description, StageName, Amount, Probability, ExpectedRevenue, TotalOpportunityQuantity, CloseDate,
            Type, NextStep, LeadSource, IsClosed, IsWon, ForecastCategory, CampaignId, HasOpportunityLineItem, Pricebook2Id,
            OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp FROM Opportunity";
  //Description,
  $queryOptions = new QueryOptions(500);
  $response = $connection->query(($query), $queryOptions);

    // New code starts here
    if ($response->size > 0)
    {
        $accounts = $response->records;


            // Cycles through additional responses if the number of records
            // exceeds the batch size
        while (!$response->done)
        {
            set_time_limit(300);
            $response = $connection->queryMore($response->queryLocator);
            $accounts = array_merge($accounts, $response->records);

        }
    }

  return $accounts;
}

With the array merge and returning the $accounts, you can then use that array to store the data into Oralce, MySQL, or even write it to a CSV file if that is what you wish

mike · #4 07-10-2007, 12:42 PM

Just an update on this. I have successfully finished writing the Oracle DB replication scripts for the following Salesforce Objects / Tables:

Account
AccountShare
AccountTeamMember
Contact
Opportunity
OpportunityLineItem
OpportunityTeamMember
PriceBookEntry
Product2
User

Someone once told me that this could not be accomplished in PHP, that it would not work. Well it does and like a charm. If you wish to see some of the working examples, they are posted on this site or Contact me!