Exporting from SAGE to MYSQL in PHP

Recently, I had to create a script that exported records from a SAGE database to MySQL.

It wasn’t exactly easy finding the correct connection string… and this obviously varies between different SAGE versions… but I thought I’d post the functions I created… which extract the data… and then create the SQL statements…

You would then use mysqli in order to execute the whole batch of statements.

  1.  
  2. // SAGE FIELDS
  3. $sagefields_array = array("WEB_PUBLISH","WEB_SPECIAL","STOCK_CAT","STOCK_CODE","WEB_DESCRIPTION","WEB_DETAILS","WEB_CATEGORY_1","WEB_CATEGORY_2","WEB_CATEGORY_3","UNIT_OF_SALE","SALES_PRICE","UNIT_WEIGHT","WEB_IMAGE_FILE","SUPPLIER_PART_NUMBER","DESCRIPTION");
  4.  
  5. $sage_result_size = 0;
  6.  
  7. function extract_from_sage() {
  8.         Global $sagefields_array;
  9.         Global $sage_result_size;
  10.  
  11.         $sage_result = array();
  12.  
  13.         $sagefields = implode(",",$sagefields_array);
  14.  
  15.         $conn=odbc_connect(‘DSN=SageLine50v12;Driver={Sage Line 50v12};uid=manager;pwd=;’,”,”);
  16.         if (!$conn) { exit("Connection Failed: " . $conn); }
  17.  
  18.         $sql="SELECT ".$sagefields." FROM STOCK";
  19.         $rs=odbc_exec($conn,$sql);
  20.         if (!$rs) { exit("Error in SQL"); }
  21.  
  22.         while ( $sage_array_row = odbc_fetch_array($rs) ) {
  23.                 array_push($sage_result,$sage_array_row);
  24.         }
  25.         odbc_close($conn);
  26.  
  27.         $sage_result_size = sizeof($sage_result);
  28.  
  29.         return $sage_result;
  30. }       
  31.  
  32. function create_sql($sage_result) {
  33.         $sql = "";
  34.         foreach($sage_result as $k => $v) {
  35.                 foreach($v as $key => $value) {
  36.                         $v[$key] = addslashes($value);
  37.                 }
  38.                 $sagevalues = "#Q#".implode("#Q#,#Q#",$v)."#Q#";
  39.                 $sagevalues = str_replace("n","",$sagevalues);
  40.                 $sagevalues = str_replace("r","",$sagevalues);
  41.  
  42.                 $sql.= "REPLACE INTO products values(".$sagevalues.");";
  43.         }
  44.  
  45.         return $sql;
  46. }

Please note that not all functionality is included in this code.

Due to the remote mysql server not accepting remote connection in my case… I had to use post, in order to execute the commands… but you could of course execute the sql directly.

You can obviously change the type of sql statement to your liking… if you don’t want to use replace into.

Leave a Reply