Originally Posted by nrwingate

By the way, Nice Picture on force.com.....

what link did you see that at? Or was that in the email

Developers: Build Custom Applications with Force.com - salesforce.com

oh shoot what a terrible picture, I look like a fricken elephant !!

Hey Mike......

I am pretty sure I have something out of whack with regards to the php/mysql connection..... here is the evidence....

Fatal error: Call to undefined function mysql_connect() in C:\PHPIntegration\xampp\htdocs\mysql_check0.php on line 3

I get this when I run the following ..... (from the php.net site)

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?>

Thoughts on where that problem might exist...?

Thanks,

Yeah that is an issue with your installation!

Sorry man I do not know that much about things like that

Hi,

I followed all the instructions, but i get these error, when i try to run this script againts my sandbox or prod.

Undefined property: stdClass::$types in D:\xampp\htdocs\salesforce_mysql_build.php on line 68

Any suggestions?

I also tried your Online Tool ,but get the message
Warning: fopen(prod.sql) [function.fopen]: failed to open stream: Permission denied in /home/mike/public_html/mysqlbuild/index.php on line 26

and
Warning: fwrite(): supplied argument is not a valid stream resource in /home/mike/public_html/mysqlbuild/index.php on line 67

Originally Posted by Jumpaloo

Hi,

I followed all the instructions, but i get these error, when i try to run this script againts my sandbox or prod.

Undefined property: stdClass::$types in D:\xampp\htdocs\salesforce_mysql_build.php on line 68

Any suggestions?

I also tried your Online Tool ,but get the message
Warning: fopen(prod.sql) [function.fopen]: failed to open stream: Permission denied in /home/mike/public_html/mysqlbuild/index.php on line 26

and
Warning: fwrite(): supplied argument is not a valid stream resource in /home/mike/public_html/mysqlbuild/index.php on line 67

Sorry about the permissions issue. when I uploaded the build tool to my new site, I forgot to set the permissions on the folder.

I just did that and just tested it and it worked. Please try it again and as for the error in the download it is probably because that was written against the old toolkit, 11. I will update it against the new tool kit

let me know if it works for you, Mike

Not sure if people will find this script useful, but its a slight variation of the main Salesforce to mysql replication script.

It essentially loops through every object in salesforce(Except those specified in its ignore section) and sync's it to a mysql database. You must have a database created via Mike's Salesforce schema replicator. Certain objects such as history cannot be sync'd so you may have to tweak the object ignore list a bit.

You must preload the database(using the dataloader) with data up till todays date(or wtvr date you specify in the time-stamp). You must then create a file called mirror.info and save a time-stamp in the following format: 2010-01-21T15:05:55Z

The script will then pull every record modified in Salesforce after that time stamp and sync it with your mysql database. The time-stamp will be updated at the completion of the script.

Sorry if its a bit messy in terms of its screen output but it functions quite well and we have a Cron job setup to sync every half hour.

One major flaw, it does not detect deleted records. Not really sure how that can be accomplished so any suggestions would be much appreciated!

Hope this helps someone!

PHP Code:

<?php

error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
define("SOAP_CLIENT_BASEDIR", "includes/soapclient");
require_once (SOAP_CLIENT_BASEDIR.'/SforcePartnerClient.php');

$USERNAME = "YOURLOGIN";

$PASSWORD = "YOURPASSWORD";

$client = new SforcePartnerClient();
$client->createConnection(SOAP_CLIENT_BASEDIR.'/partner.wsdl.xml');
$myloginresult = $client->login($USERNAME, $PASSWORD);

$conn = mysql_connect("URL", "LOGIN", "PASS") or die(mysql_error());
mysql_select_db('SFmirror') or die(mysql_error());

mysql_set_charset('utf8', $conn);

//Start log file;
if(!$logfile = fopen("log.txt",'a+'))
{
       echo "Cannot open file ($filename)";
           exit;
}
//Get lastdatemodified from "mirror.info"
if(!$file = fopen("mirror.info",'r+'))
{
       echo "Cannot open file ($filename)";
           exit;
}

fwrite($logfile,"Starting Sync...\n");


$lastdatemodified = fgets($file);

$lastdatemodified = trim($lastdatemodified);

echo $lastdatemodified;

if (isset($client))
            {
                
                
                    // Retrieve all the objects in the organization
               $result = $client->describeGlobal();
               
                // Cycle through each object
               foreach ($result->types as $objectType)
               {
                       echo "()";
                       echo $objectType;
                       echo "()";
                       $q = "Select ";
                       $resultObject = $client->describeSObject($objectType);
                          
                    // Cycle through each field
                  foreach ($resultObject->fields as $field)
                    {
                        if ($field->name == "Binary")
                        {
                              $field->name = "Binary__c";
                        }
                        
                        $q = $q . $field->name . ","; 
                            
                    }
                   
                    $q = substr($q, 0, strlen($q) - 1);
                    $q = $q . " FROM ";
                      $q = $q . $objectType;
                      
                      $q = $q . " where LastModifiedDate > ". $lastdatemodified;                                                        
                      
                      //ObjecType exceptions to Ignore. 
                      if(strcmp($objectType,"AccountHistory") == 0 
                          ||  strcmp($objectType,"AccountShare")== 0
                          ||  strcmp($objectType,"ActivityHistory") == 0 
                         ||  strcmp($objectType,"AsyncApexJob")== 0 
                         ||  strcmp($objectType,"Case__c")== 0 
                        ||  strcmp($objectType,"CaseHistory")== 0 
                        ||  strcmp($objectType,"CaseSolution")== 0 
                        ||  strcmp($objectType,"ContactHistory")== 0 
                        ||  strcmp($objectType,"ContractHistory")== 0  
                        ||  strcmp($objectType,"DocumentAttachmentMap")== 0 
                        ||  strcmp($objectType,"EmailStatus")== 0 
                        ||  strcmp($objectType,"EmailTemplate")== 0 
                        ||  strcmp($objectType,"Event")== 0
                        ||  strcmp($objectType,"FiscalYearSettings")== 0 
                        ||  strcmp($objectType,"GroupMember")== 0 
                        ||  strcmp($objectType,"LeadHistory")== 0 
                        ||  strcmp($objectType,"LeadHistory")== 0 
                        ||  strcmp($objectType,"Name")== 0 
                        ||  strcmp($objectType,"NoteAndAttachment")== 0 
                        ||  strcmp($objectType,"OpenActivity")== 0 
                        ||  strcmp($objectType,"OpportunityFieldHistory")== 0 
                        ||  strcmp($objectType,"OpportunityHistory")== 0 
                        ||  strcmp($objectType,"Period")== 0 
                        ||  strcmp($objectType,"ProcessInstanceHistory")== 0 
                        ||  strcmp($objectType,"ProcessInstanceStep")== 0 
                        ||  strcmp($objectType,"ProcessInstanceWorkitem")== 0  
                        ||  strcmp($objectType,"QueueSobject")== 0 
                        ||  strcmp($objectType,"Scontrol")== 0 
                        ||  strcmp($objectType,"SolutionHistory")== 0 
                        ||  strcmp($objectType,"UserLicense")== 0 
                        ||  strcmp($objectType,"UserPreference")== 0 
                        ||  strcmp($objectType,"UserRole")== 0 
                        ||  strcmp($objectType,"Vote")== 0 
                        )
                      {
                  

                      }

                      else
                      {
                           
                          $field_list = array();
                          //build an array of field names which correspond to the current objectType;
                          $field_list = buildflist($field_list,$client,$objectType);
                        $queryOptions = new QueryOptions(500);
                          $response = $client->query(($q), $queryOptions);
                          
                          if ($response->size > 0)
                          {
                          
                              mysql__update($response,$objectType,$field_list,$conn);
                              
                              while (!$response->done)
                                {
                                    echo "<pre>";
                                    echo "batch";
                                    echo "</pre>";
                                    set_time_limit(100);
                                 $response = $client->queryMore($response->queryLocator);
                                    mysql__update($response,$objectType,$field_list,$conn);
                                  }
                          
                          }
                      }
                  
                   }
                   
             }
else
    {
        echo "Error establishing mysql connection on attempted table synch. Exiting...";
        exit;
    }

echo "test2";

include 'include/closedb.php';

    update_timestamp($file,$logfile);
    fwrite($logfile,"done");
    fclose($logfile);
    fclose($file);

function mysql__update($response,$objectType,$field_list,$conn)
{
    if ($objectType == 'Case')
                          {
                      $objectType = 'Case__c';
                          }
                      elseif ($objectType == 'Group')
                          {
                      $objectType = 'Group__c';
                          }
                      else
                          {
                          $objectType = $objectType;
                          }
    
    
    
    foreach($response->records as $record)
    {
        $push_list = array();
        
        $query = "SELECT Count(*) FROM " . $objectType . " WHERE ID = \"" . $record->Id . "\"";
    
        $cur_id = $record->Id;
    
        array_push($push_list,$cur_id);
    
        $result = mysql_query($query);
        if (!$result) {
                    die('Invalid query: ' . mysql_error());
                    }
                    
        $match = mysql_fetch_assoc($result);
        
        foreach($record->fields as $field)
        {
            array_push($push_list,$field);
            //$push_list = $push_list . $field . ",";

        }
        
        //Remove the last ',' from push_list

    
    
         //If for some reason $push_list and $field_list do not have the same amount of elements, then 
        //something is amiss. Time to exit...
        if(count($push_list) != count($field_list))
        {
            echo "Push_list != Field_List Exiting......";
            exit;
        } 
        
        //If match is 1, then the record exists and we are going to do an update
        if($match['Count(*)'] == 1)
        {
            $updateq = "UPDATE " . $objectType . " SET "; 
            
            for($i=0;$i<count($push_list);$i++)
            {
                if($push_list[$i] != "")
                {
                $updateq = $updateq . $field_list[$i] . ' = "' . addslashes($push_list[$i]) . '",';
            
                }
            }
            $updateq = substr($updateq, 0,-1);
            
            $updateq = $updateq . ' WHERE Id = "' . $cur_id . '"';
            
            //echo "<br />";
            //echo $updateq;
        
            $result = mysql_query($updateq);
            if (!$result)
                     {
                    die('Invalid query: ' . mysql_error());
                    }    
            
        }
        
        //If match is 0, then the record does not exist and we are going to do an insert
        else
        {
            $insertq = "INSERT INTO " . $objectType . "(";
            for($i=0;$i<count($push_list);$i++)
            {
                //Add field lists
                if($push_list[$i] != "")
                {            
                    $insertq = $insertq . $field_list[$i] . ",";
                }
            }
            
            $insertq = substr($insertq, 0, -1);
            $insertq = $insertq . ") VALUES (";
            
            for($i=0;$i<count($push_list);$i++)
            {
                //Add field lists
                if($push_list[$i] != "")
                {    
                        $insertq = $insertq . '"' . addslashes($push_list[$i]) . '",';
                }
            }

            $insertq = substr($insertq, 0, -1);
            $insertq = $insertq . ")";
            
                $result = mysql_query($insertq);
        
                if (!$result)
                     {
                    die('Invalid query: ' . mysql_error());
                    }    
            
            //echo $insertq;
        }
        //echo "<br />";

        //echo "<br />";
    }
    
    echo $objectType;    
    echo "<br />";
    echo "done.";
}



function buildflist($field_list,$client,$objectType)
{
    
     // Retrieve the object definition
    $resultObject = $client->describeSObject($objectType);
              
     // Cycle through each field
      foreach ($resultObject->fields as $field)
       {
           $temp = $field->name;
        array_push($field_list,$temp);
    }
    return $field_list;
}




function update_timestamp($file,$logfile)
{
    echo "updating timestamp....";
    $time = getdate();
    $month;
    $year;
    $day;
    $hour;
    $min;
    $second;

     if(strlen($time['mon'])==1)
         {
             $month = '0' . $time['mon'];    
         }
     else
         {
             $month = $time['mon'];
         }
     
     if(strlen($time['mday']) == 1)
         {
             $day = '0' . $time['mday'];
         }
     else 
         {
             $day = $time['mday'];
          }
    if(strlen($time['hours']) == 1)
        {
            $hour = '0' . ($time['hours']-1);
        }
    else 
        {
            $hour = $time['hours'];
        }    
    if(strlen($time['minutes']) == 1)
        {
            $min = '0' . $time['minutes'];
        }    
    else 
        {
            $min = $time['minutes'];
        }
    if(strlen($time['seconds']) == 1)
        {
            $second = '0' . $time['seconds'];
        }
    else
        {
            $second = $time['seconds'];
        }
        
    $new_timestamp = $time['year']."-".$month."-".$day."T".$hour.":".$min.":".$second."Z";
    
   rewind($file);
   fwrite($file,$new_timestamp);
   fwrite($logfile,$new_timestamp);
}

?>

Thanks for that!! I am going to clean up your post a little so the code is viewer friendly

Hey can't really edit the message. Can you replace the echo timestamp in the last part with
rewind($file);
fwrite($file,$new_timestamp);
fwrite($logfile,$new_timestamp);

Thanks for cleaning it up, at work and didnt have a chance to touch it up.