Salesforce PHP Oracle Database Replication Tool

mike · #1 08-06-2007, 03:34 PM

Here is my latest release that will help developers and organizations tremendously. This new script takes parameters from a web form and will create your Oracle tables/objects via the API. All you have to supply is the following:

You're Salesforce Username
You're Salesforce Password
Table Prefix (e.g. sforce_ ) not required
Oracle Database Name
Oracle User Name
Oracle Password

David Clairborne recently released a tool that will create the schema that a user could copy and paste from a web browser for MySQL and I sent him the Oracle script that performs the same process. This new script will not only create that same file, but it will login to your Oracle database and create the tables for you. This new function ads the ability to create your tables on the fly, without having to try and do it by each individual salesforce object.

This script was developed against a new developers account and it works without any hiccups. It has also been tested against a real live org, a sandbox, and a modified developers account. I do not see why it would not work against anyone's or any company's live instance.

If you plan on using this script to test it out, there are a few lines of code that need to be checked so they match your paths of the following scripts from the PHP toolkit:

Check line numbers 19 and 20 and make sure you correct the paths to the following:

PHP Code:


			
require_once ('./includes/soapclient/SforcePartnerClient.php');
require_once ('./includes/soapclient/SforceHeaderOptions.php');

Check line number 37 and make sure you correct the path to the following:

PHP Code:


			
$wsdl = './includes/soapclient/partner.wsdl.xml';

If you forget to modify these lines, the script will fail. This takes into account that the table names and field names in Oracle cannot exceed 30 characters, so you will need to check your table names once the script is complete. An example of this is the Oracle reserved word, Number. There are field names which are in salesforce that have this same exact name. Here is what I did to check this and change the name to Number__c:

PHP Code:


			
$field->name = substr($field->name, 0, 30);
if ($field->name == "Number")
{
       $field->name = "Number__c";
}
else
{
      $field->name = $field->name;
}

If you wish to obtain the SQL file that is also created to this, it will be located in the same folder as this script runs in and is called:

Code:

salesforce_oracle.sql

If you wish to only have the SQL file generated then uncomment the exit() statement on line number 99.

This script uses ADOdb to connect to Oracle; you MUST be using this database class in order for this script to work, if you do not, THE SCRIPT WILL FAIL.

You can download the ADOdb package from the main menu on my home page. This package was developed to work with:

Apache
Oracle 10g
PHP 5.*

If you have any questions please feel free to use the contact form on this site and I will do my best to get back to you ASAP!

I am working on improving this system with enhancements that will even perform the database replication with a nice UI that users will be able to actually pick the tables and fields that they wish to backup. I am also going to release this same script for MySQL in the next couple of days

Thanks,
Mike

mike · #2 08-09-2007, 10:27 AM

Updated the Oracle database replication tool, Minor changes and Oracle Connection check at start of script

~Mike

#3 09-18-2007, 11:19 PM

I am looking to do some automated importation of data and was curious if you have found a way to do this with PHP?

mike · #4 09-19-2007, 08:52 AM

Quote:

Originally Posted by Unregistered

I am looking to do some automated importation of data and was curious if you have found a way to do this with PHP?

When you say some sort of automated process, do you mean like replicate your data from salesforce to a local database (like oracle)?

If that is what you mean, yes I have created scripts which can run on cron (automated) and download an entire object and its data into a local database. I have done this for both Oracle and MySQL

Can you be more specific on what you are trying to accomplish. Also a name would be nice to know