Announcement

Collapse
No announcement yet.

How work with two different database/connections using INSERT

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How work with two different database/connections using INSERT

    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

    Comment


    • #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();
      }

      Comment

      Working...
      X