One item while browsing my own site that noticed is that some of the tutorials or examples are really not all that clear and for that I apologize.
A developer new to Salesforce wanted me to help them out with a replication script that would download/replicate data to Salesforce. I agreed to help him out and I wanted to share this with everyone to give them an idea how really simple it is to create this.
So I am going to release an entire script that will work with any object in Salesforce, just simply by changing the query to Salesforce and the table you want to update in MySQL.
Step 1:
Go to My MySQL Schema generator and create a your own organization's MySQL schema. This covers objects from A to Z, including custom objects.
- Once you fill out all the pertinent information, it will log in to Salesforce
- Create your schema and generate a download
- All data and log in information is not stored and the file is deleted once the download is complete.
or simply create your own MySQL schema from scratch.
Here is the schema from my schema generator for the Product2 object from my development instance:
Code:
CREATE TABLE sforce_product2(
Id VARCHAR(18),
Name VARCHAR(255),
ProductCode VARCHAR(255),
Description TEXT,
IsActive VARCHAR(5),
CreatedDate DATETIME,
CreatedById VARCHAR(18),
LastModifiedDate DATETIME,
LastModifiedById VARCHAR(18),
SystemModstamp DATETIME,
Family VARCHAR(40),
IsDeleted VARCHAR(5),
PRICE_PER_1K__c INT(0),
PRICE_PER_5K__c INT(0)
);
I wrote a script that creates bogus records in Salesforce, 156,000 of them. to test the MySQL replication
STEP 2:
Here is the entire script that will replicate any object to MySQL just by simply changing a couple of lines and the SOQL query to salesforce
PHP Code:
<?php
ini_set("soap.wsdl_cache_enabled","0");
require_once ('./includes/soapclient/SforcePartnerClient.php');
/*****************************************************************************
* This script uses ADODb, you must have ADOdb installed or in a common *
* folder to run this script. The best way to do this is to go to *
* http://adodb.sourceforge.net/ and download the latest version and install *
* it in your include_path in the php.ini file. *
******************************************************************************/
require_once ('adodb.inc.php');
$db = NewADOConnection('mysql');
//MySQL database connection information
$db->Connect("localhost","mike","mas4155","salesforce");
//Truncate current database to establish a refresh
$sql = "TRUNCATE TABLE sforce_product2";
if ($db->Execute($sql)) echo "<strong>Produc2 Table Truncated</strong><br />";
//Salesforce Connection information
$wsdl = './includes/soapclient/partner.wsdl.xml';
$userName = "username";
$password = "password";
//setup connection
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName,$password);
//SOQL query for Object in Salesforce
$soql = "Select Id, Name, ProductCode, Description, IsActive, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, Family, IsDeleted, PRICE_PER_1K__c, PRICE_PER_5K__c FROM Product2 ";
//Processes the query to get account information from Salesforce
$records = get_records($client,$soql,$db);
if ($records === false)
{
//mail("mike.simonds@maxim-ic.com","PHP Product2 Script Error","There has been an error in the product2 replication script\nPlease Check Script");
}
echo '<p>There are currently '.$records.' products:</p>';
$today = date("F j, Y, g:i a");
$end = 'Product2 Import script ended at '.$today.' and inserted '.$records.' into the database';
function get_records($connection,$query,$db)
{
//Set this to the number of records to process per batch
//200 is the minimum
$queryOptions = new QueryOptions(1000);
$connection->setQueryOptions($queryOptions);
$response = $connection->query(($query),$queryOptions);
//for debugging
//echo '<pre>' . print_r($response,true) . '</pre>';
//exit;
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<br />";
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;
}
}
return $count_records;
}
/*Function to store records into
* database in chunks of the $queryOptions = new QueryOptions(200);*/
function store_in_db($records,$db)
{
$record_count = count($records);
//echo $record_count;
$rows_loaded = 0;
for ($i = 0; $i < $record_count; $i++) //foreach ($records as $r)
{
$r = new SObject($records[$i]);
$pass_this['id'] = $r->Id;
foreach ($r->fields as $key => $value)
{
$pass_this[$key] = addslashes($r->fields->$key);
}
$fields = implode(",",array_keys($pass_this));
$values = implode("','",array_values($pass_this));
$query = "INSERT INTO sforce_product2 (".$fields.") VALUES ('".$values."')";
//executes and loads data coming from salesforce into table
if ($db->Execute($query))
{
$rows_loaded++;
}
else
{
echo $db->ErrorMsg()."<br />";
return false;
}
//exit;
}
return $rows_loaded;
}
?>
Right now it is set to replicate the product2 table in Salesforce to MySQL, but let's say I wanted to replicate and it is named export_product2.php.
So let's say you wanted to replicate another object from Salesforce.
So create your query! I do this by logging into the Salesforce Data loader and selecting all the fields
Let's use the account object:
so find
PHP Code:
$sql = "TRUNCATE TABLE sforce_product2";
and change it to:
PHP Code:
$sql = "TRUNCATE TABLE sforce_account";
this will wipe out all your data in your MySQL sforce_account table each time you run the replicate script, so new/fresh data is inserted
so find the variable $soql in the script and change it from
PHP Code:
$soql = "Select Id, Name, ProductCode, Description, IsActive, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, Family, IsDeleted, PRICE_PER_1K__c, PRICE_PER_5K__c FROM Product2 ";
TO
PHP Code:
$soql = "Select Id, IsDeleted, MasterRecordId, Name, Type, ParentId, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, ShippingCountry, Phone, Fax, AccountNumber, Website, Sic, Industry, AnnualRevenue, NumberOfEmployees, Ownership, TickerSymbol, Description, Rating, Site, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, Account_Description__c, Area__c, End_Equipment__c, FY_CY_Auto_Info__c, FY_CY_Automotive__c, FY_CY_Communications__c, FY_CY_HSSP__c, SA_ID__c, FY_CY_Info_Comm__c, FY_CY_ISP__c, FY_CY_Micros__c, FY_CY_Mixed_Signal_1__c, FY_CY_Mixed_Signal_2__c, FY_CY_MM__c, FY_CY_Notebook__c, FY_CY_Optocon__c, FY_CY_SPC__c, FY_CY_SPM__c, FY_CY_SSIP__c, FY_CY_Thermal_Battery__c, FY_CY_Total_Design__c, FY_CY_Total_Purchasing__c, FY_CY_Wireless__c, Inactive__c, maxim_id__c, Region__c, Status__c, WebID__c FROM Account";
then find (around line 140):
PHP Code:
$query = "INSERT INTO sforce_product2 (".$fields.") VALUES ('".$values."')";
and change the table to sforce_account:
PHP Code:
$query = "INSERT INTO sforce_account (".$fields.") VALUES ('".$values."')";
rename the script to export_account.php and you have a replication script that you can run on CRON to replicate your account object and BAM! Here is the replication script for the account object:
PHP Code:
<?php
ini_set("soap.wsdl_cache_enabled","0");
require_once ('./includes/soapclient/SforcePartnerClient.php');
/*****************************************************************************
* This script uses ADODb, you must have ADOdb installed or in a common *
* folder to run this script. The best way to do this is to go to *
* http://adodb.sourceforge.net/ and download the latest version and install *
* it in your include_path in the php.ini file. *
******************************************************************************/
require_once ('adodb.inc.php');
$db = NewADOConnection('mysql');
//MySQL database connection information
$db->Connect("localhost","mike","mas4155","salesforce");
//Truncate current database to establish a refresh
$sql = "TRUNCATE TABLE sforce_account";
if ($db->Execute($sql)) echo "<strong>Account Table Truncated</strong><br />";
//Salesforce Connection information
$wsdl = './includes/soapclient/partner.wsdl.xml';
$userName = "username";
$password = "password";
//setup connection
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName,$password);
//SOQL query for Object in Salesforce
$soql = "Select Id, IsDeleted, MasterRecordId, Name, Type, ParentId, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, ShippingStreet, ShippingCity, ShippingState, ShippingPostalCode, ShippingCountry, Phone, Fax, AccountNumber, Website, Sic, Industry, AnnualRevenue, NumberOfEmployees, Ownership, TickerSymbol, Description, Rating, Site, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, Account_Description__c, Area__c, End_Equipment__c, FY_CY_Auto_Info__c, FY_CY_Automotive__c, FY_CY_Communications__c, FY_CY_HSSP__c, SA_ID__c, FY_CY_Info_Comm__c, FY_CY_ISP__c, FY_CY_Micros__c, FY_CY_Mixed_Signal_1__c, FY_CY_Mixed_Signal_2__c, FY_CY_MM__c, FY_CY_Notebook__c, FY_CY_Optocon__c, FY_CY_SPC__c, FY_CY_SPM__c, FY_CY_SSIP__c, FY_CY_Thermal_Battery__c, FY_CY_Total_Design__c, FY_CY_Total_Purchasing__c, FY_CY_Wireless__c, Inactive__c, maxim_id__c, Region__c, Status__c, WebID__c FROM Account";
//Processes the query to get account information from Salesforce
$records = get_records($client,$soql,$db);
if ($records === false)
{
//mail("mike.simonds@maxim-ic.com","PHP Product2 Script Error","There has been an error in the product2 replication script\nPlease Check Script");
}
echo '<p>There are currently '.$records.' products:</p>';
$today = date("F j, Y, g:i a");
$end = 'Product2 Import script ended at '.$today.' and inserted '.$records.' into the database';
function get_records($connection,$query,$db)
{
//Set this to the number of records to process per batch
//200 is the minimum
$queryOptions = new QueryOptions(1000);
$connection->setQueryOptions($queryOptions);
$response = $connection->query(($query),$queryOptions);
//for debugging
//echo '<pre>' . print_r($response,true) . '</pre>';
//exit;
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<br />";
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;
}
}
return $count_records;
}
/*Function to store records into
* database in chunks of the $queryOptions = new QueryOptions(200);*/
function store_in_db($records,$db)
{
$record_count = count($records);
//echo $record_count;
$rows_loaded = 0;
for ($i = 0; $i < $record_count; $i++) //foreach ($records as $r)
{
$r = new SObject($records[$i]);
$pass_this['id'] = $r->Id;
foreach ($r->fields as $key => $value)
{
$pass_this[$key] = addslashes($r->fields->$key);
}
$fields = implode(",",array_keys($pass_this));
$values = implode("','",array_values($pass_this));
$query = "INSERT INTO sforce_account (".$fields.") VALUES ('".$values."')";
//executes and loads data coming from salesforce into table
if ($db->Execute($query))
{
$rows_loaded++;
}
else
{
echo $db->ErrorMsg()."<br />";
return false;
}
//exit;
}
return $rows_loaded;
}
?>
This script processes records in batch and that number is set with this line of code:
PHP Code:
$queryOptions = new QueryOptions(1000);
You can set that anywhere from 200 to 2000 (I think that 2000 is the max, but don't quote me on that)
I hope this helps someone, let me know if anyone has any questions
~Mike
LinkBack URL
About LinkBacks
Reply With Quote
Originally Posted by vadugam
. But I am not sure that each user of my application will have API enabled.
Bookmarks