Thread: Null values inserting into database

Hi Mike!

Thanks for an awesome tool.

After a bunch of false starts with copying a table from our sf instance to mysql on website local server I have finally managed to get data into a table! Albit NULL values! But at least its better than nothing!!

It's creating the exact number of records in the mysql table as there are records in SF.

Can anyone help?

PHP Code:

<?php
ini_set("soap.wsdl_cache_enabled","0");


require_once ('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/adodb.inc.php'); 

$db = NewADOConnection('mysql');


//MySQL database connection information
$db->Connect("localhost","username","password","database");

//Truncate current database to establish a refresh
$sql = "TRUNCATE TABLE sforce_property__c";
if ($db->Execute($sql)) echo "<strong>Property Table Truncated</strong><br />";

//Salesforce Connection information
$wsdl = 'soapclient/partner.wsdl.xml';
$userName = "##########";
$password = "##########";


//setup connection
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName,$password);

//SOQL query for Object in Salesforce
$soql = "Select Id, OwnerId, IsDeleted, Name, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, Unit_lot_number__c, Includes_Furniture__c, Purchase_Price__c, Status__c, Development_Project__c, Number_of_bedrooms__c, Number_of_bathrooms__c, Number_of_car_park_spaces__c, Deposit__c, Anticipated_Rent__c, Council_Rates__c, Body_Corporate_Fees__c, Commission_at_unconditional__c, Commission_at_settlement__c, Type__c, Gross_Rental_Yield__c, Level__c, Internal_Living_Area__c, External_Living_Area__c, Storage__c, study__c, Media_Room__c, Block_Size__c, Building__c, Floor_Plan__c, Curtains_and_Blinds__c, Window_Screens__c FROM property__c";


//Processes the query to get account information from Salesforce
$records = get_records($client,$soql,$db);

if ($records === false)
{
    mail("matt@investmentmentor.com.au","PHP Property Script  Error","There has been an error in the property replication script\nPlease Check Script");
    echo '<p>There was an error:</p>';   
}
echo '<p>There are currently '.$records.' products:</p>';

$today = date("F j, Y, g:i a");
$end = 'Property 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 property__c (".$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;


}

?>

Lol! I forgot to add the error message.

It loads up 851 errors on the page, this is the last one:

Warning: Invalid argument supplied for foreach() in /home/investme/public_html/phptoolkit/sync.php on line 132
There are currently 851 products:

msweet
Sorry for the delay, I have been out of town on vacation for the past 10 days or so.

Did you do a print_r on your variable $records in function get_records() by chance to see if you are actually getting data back from Salesforce?

check that and then we can work from there

If that doesn't work, do a print_r on $pass_this in the next function store_in_db

~Mike

Oh wow Mike that worked!

I simply uncommented the

PHP Code:

echo '<pre>' . print_r($response,true) . '</pre>'; 


Line of code and viola, the datablock appeared on the page.

All 851 records are present and accounted for with data.

Maybe the insert code isn't working correctly?

It's creating the 851 records in my mysql db, but only NULL val's are being inserted.

regards,

Matt

Also Mike, I'm using version 4.98 of ADODB, should I update to the latest version?

Hi again Mike,

I just discovered that I don't have a primary index key defined in the table property__c

Do I need to have one defined for line 134 to work?

PHP Code:

foreach ($r->fields as $key => $value) 


Thanks!

Matt

what version of the toolkit are you using, I think that is the issue

Originally Posted by msweet

Also Mike, I'm using version 4.98 of ADODB, should I update to the latest version?

No that version is fine, see my other post

Hi Mike,

I just updated to Photoolkit v13 and I'm still getting that error:

Warning: Invalid argument supplied for foreach() in /home/investme/public_html/phptoolkit/sync.php on line 134

Warning: Invalid argument supplied for foreach() in /home/investme/public_html/phptoolkit/sync.php on line 134
There are currently 851 products:


And 851 records are being created, but each column has null values.


regards,


Matt

Somewhere around line 132 add this, right before the foreach:

PHP Code:

echo '<pre>' . print_r($pass_this,true) . '</pre>'; 


and paste the results here


~Mike