Insert Multi Row Oracle result set into MYSQL using PHP

Question

A little background. I have an Oracle database that I am trying to query and then insert into a local MYSQL database so that I can generate canned reports. I have been trying to figure out this insert into Mysql for a while now. I have the Oracle portion running correctly but when I try to insert I have been getting a syntax error in mysql.

The result set comes back with 8 rows the first of which is the Key in MYSQL. I would really like to convert this insert query I built into a insert on duplicate key update statement but am lost on how I would do this properly. Any help you guys can provide would be appreciated.

$db1 = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HOST)(PORT = 1521))(CONNECT_DATA=(SERVICE_NAME=Service)))';

$c1 = oci_connect("Userid", "Pass", $db1);   

$sql = oci_parse($c1, "select statement") ;

oci_execute($sql);

$i = 0;

   while ($row = oci_fetch_array($sql)){
   $i++;
    $k = $row[0];
    $dte = $row[1];
    $cus = $row[2];
    $odr = $row[3];
    $lin = $row[4];
    $cas = $row[5];
    $lpo = $row[6];
    $cpl = $row[7];
    $cpo = $row[8];
   };

$db_user = "userid";

$db_pass = "Pass";

$db = new PDO('mysql:host=host; dbname=databasename', $db_user, $db_pass);

$stmt = $db->prepare("INSERT INTO `cuspi` (k, dte, cus, odr, lin, casa, lpo, cpl, cpo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

$recordcount = count($k);
 for ($i = 0; $i < $recordcount; $i++) {
  $records[] = [
  $k[$i],
  $dte[$i],
  $cus[$i],
  $odr[$i],
  $lin[$i],
  $casa[$i],
  $lpo[$i],
  $cpl[$i],
  $cpo[$i],
 ];
 }


foreach ($records as $record) {
$stmt->execute($record);
}
?>

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lin, casa, lpo, cpl, cpo) VALUES ('0', '0', 'N', '1', '6', '6', '6', '1', '6'' at line 1 ) array(1) { [0]=> array(9) { [0]=> string(1) "0" [1]=> string(1) "0" [2]=> string(1) "N" [3]=> string(1) "1" [4]=> string(1) "6" [5]=> string(1) "6" [6]=> string(1) "6" [7]=> string(1) "1" [8]=> string(1) "6" } } — Darren Tracy
– Darren Tracy, Commented Sep 18, 2017 at 15:08

Darren Tracy · Accepted Answer · 2017-09-20 15:08:04Z

1

I was able to figure out the Answer. I was missing the grave accent around the column references for the insert.

Original

$stmt = $db->prepare("INSERT INTO `cuspi` (k, dte, cus, odr, lin, casa, lpo, cpl, cpo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

Fixed

$stmt = $db->prepare("INSERT INTO `cuspi` (`k`, `dte`, `cus`, `odr`, `lin`, `casa`, `lpo`, `cpl`, `cpo`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

answered Sep 20, 2017 at 15:08

Darren Tracy

371 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Multi Row Oracle result set into MYSQL using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related