Hello,
This Project has 2 connection defined. 1 is MySQL and the other MSAccess. Both work fine in that I can access data from each.
I’m working on 1 app that should read rows from the MSAccess database and insert them into the MySQL database.
I have created a “blank” app and in SC has chosen the MSAcess connection on SQL button.
It creates the RS from the MSAccess database fine, but when I try to create a new connection to MySQL and import the records
in the RS, I get an error :
Errorodbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains the following unknown field name: ‘job_num’. Make sure you have typed the name correctly, and try the operation again., SQL state S0022 in SQLExecDirect
So it is not changing the connection to the MYSql. User and password are good. Any ideas on what I’m doing wrong would be appreciated…thanks
Below is the code for “On Execute”.
$check_sql = “SELECT
a.OrderNo as ON1,
a.JobNo as JN1,
a.QtyOrdered as Q1,
a.DueDate as DD1,
b.CustCode as CC1”
. " FROM OrderDet a LEFT Join Orders b on a.OrderNo = b.OrderNo"
. " WHERE a.Status=‘Open’";
sc_select(rs, $check_sql);
if (false == {rs})
{
sc_error_message(‘Error while accessing database.’);
}
else
{
/****************** Works OK until Here **************************/
$arr_conn = array();
$arr_conn[‘drive’] = “mysqlt”;
$arr_conn[‘server’] = “localhost”;
$arr_conn[‘user’] = “root”;
$arr_conn[‘password’] = “MyGoodPassword”;
$arr_conn[‘database’] = “MyDatabase”;
$arr_conn[‘persistent’] = “Y”;
$arr_conn[‘encoding’] = “utf8”;
sc_connection_new(“mysql_orders”, $arr_conn);
while(!$rs->EOF)
{
{OrderNo} = $rs->fields[0];
{JobNo} = $rs->fields[1];
{QtyOrdered} = $rs->fields[2];
{DueDate} = $rs->fields[3];
{CustCode} = $rs->fields[4];
echo "{OrderNo} {JobNo} {QtyOrdered} {DueDate} {CustCode}" ;
$insert_table = ‘orders’;
$insert_fields = array(
‘job_num’ => “’{JobNo}’”,
‘quantity’ => “’{QtyOrdered}’”,
);
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;
sc_exec_sql($insert_sql);
$rs->MoveNext();
}
$rs->Close();
}