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;
}
?>