How work with two different database/connections using INSERT

Developers Programming
#1

Hi all,

I got a mssql db table (dbo.VALORIGPS) as origin of data and a mysql as destination table (gps_data).
If I use sc_exec_sql ($insert_gps_data, “conn_mssql_log”); then I got the error about gps_data that is a mySQL table
mssql_query(): message: Invalid object name ‘gps_data’. (severity 16) becouse it’s using the conn_mssql_log that is done for mssql.

How have I to operate in this cases ??

$insert_gps_data =
“INSERT INTO gps_data (idGPS, Palmare, Operatore, DATA, ORA, LATIT, LONGI, ALTIT, KMH)
SELECT idGPS, Palmare, Operatore, DATA, ORA, LATIT, LONGI, ALTIT, KMH
FROM dbo.VALORIGPS”;

sc_exec_sql ($insert_gps_data, “conn_mssql_log”);

#2

Hi,
you can’t use two different databases/connections in one statement.
You have to select (sc_select/sc_lookup) your data and prepare it for insertion (array()) using the first connection
and insert it into the other database using the second connection.

jsb

#3

Yes, thanks Jsb.
This is what I did and it seems to work.

$select_sqlsrv="SELECT idGPS,Palmare, Operatore, DATA, ORA,
LEFT(LATIT ,2) + ‘.’ + SUBSTRING(LATIT ,3,2) + SUBSTRING(LATIT ,6,4) AS LATIT,
SUBSTRING(LONGI ,2,2) + ‘.’ + SUBSTRING(LONGI ,4,2) + SUBSTRING(LONGI ,7,4) AS LONGI,
ALTIT, KMH FROM dbo.VALORIGPS WHERE DATA BETWEEN ‘{rif_data_travel}’ AND ‘{rif_data_travel1}’ ";
sc_select(my_data, $select_sqlsrv, “conn_mssql_log”);

if ({my_data} === false)
{
echo "Access error. Message ";
}
else
{
while (!$my_data->EOF)
{
{idGPS} = $my_data->fields[0];
{Palmare} = $my_data->fields[1];
{Operatore} = $my_data->fields[2];
{DATA} = $my_data->fields[3];
{ORA} = $my_data->fields[4];
{LATIT} = $my_data->fields[5];
{LONGI} = $my_data->fields[6];
{ALTIT} = $my_data->fields[7];
{KMH} = $my_data->fields[8];

$sql_insert = “INSERT INTO gps_data (idGPS, Palmare, Operatore, DATA, ORA, LATIT, LONGI, ALTIT, KMH)
VALUES ({idGPS},’{Palmare}’,’{Operatore}’,’{DATA}’,’{ORA}’,’{LATIT}’,’{LONGI}’,’{ALTIT}’, {KMH})
ON DUPLICATE KEY UPDATE Operatore = ‘{Operatore}’ , DATA = ‘{DATA}’, ORA = ‘{ORA}’,
LATIT = ‘{LATIT}’, LONGI = ‘{LONGI}’, ALTIT = ‘{ALTIT}’, KMH = {KMH}”;
sc_exec_sql($sql_insert);

echo {idGPS}." - “. {Palmare}.” - “. {Operatore}.” - “. {DATA}.” - “. {ORA} .” - “.{LATIT}.” - “. {LONGI} .” - “. {ALTIT} .” - “. {KMH} .”<br/>";
$my_data->MoveNext();
}
$my_data->Close();
}