Announcement

Collapse
No announcement yet.

How can I use insert into reading data from MsAccess db for inserting into mySQL ?

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

  • How can I use insert into reading data from MsAccess db for inserting into mySQL ?

    Hi all,
    sorry for stupid question but on Monday morning... all it's possible. ;-)

    I got a MsAccess out of my server environment where I have to read data on a daily basis.
    I created an ODBC driver and so I can read data from there.

    Now I'm on a Blank application for update a mySQL Table reading data from that Ms Access DB.
    On a blank application I got a connection to choose and I choosed that one to connect to MsAccess.

    I'm using the INSERT INTO mySQL table ... select (MsAccess data)
    ON DUPLICATE KEY UPDATE SET ...... ;

    It's not possible becouse mySQL table is connected to another connection.

    How can I solve my double connection problem ?
    Many thanks

  • #2
    Giovannino,

    Firstly, you can have as many connections as you like. In production mode, click "new connection".

    When you do an SQL query, for instance sc_lookup (Dataset, "SQL Command", "Connection"), the last parameter is which connection to use.

    Hope this helps,

    Dave
    Dave Prue
    Code Whisperer
    Lahar International Corp
    www.lahar.net

    Comment


    • #3
      Hi Dave , thanks so much

      Based on your suggestion I set main connection of Blank application to myQSL and I did the query to Ms Access defining the connection as following:
      I can't use the MsAccess query into the INSERT INTO becouse I can't define the connection there.
      So first I did the MsAccess query obtainign an array of all values.

      $sql_customers="SELECT Q_Customers.CodAnag AS CustomerID, \"Q_Customers.Ragione sociale\" AS CompanyName, Q_Customers.NomeContatto AS ContactName, Q_Customers.Indirizzo AS Address, LocalitÓ AS City, Q_Customers.Provincia AS Region, Q_Customers.CAP AS PostalCode, Q_Customers.Nazione AS Country, Q_Customers.Zona AS Zone, Q_Customers.NumeroTelefono AS Phone, Q_Customers.NumeroFax AS Fax, Q_Customers.Cell AS Mobile, Q_Customers.Email, Q_Customers.Codag AS SaleseosID, \"Q_Customers.Id anag\" AS Id_anag
      FROM Q_Customers
      ORDER BY Q_Customers.CodAnag";

      sc_select(rs, $sql_customers, "conn_odbc");
      $rs_array={rs};
      echo $rs_array ;

      Now I got an array with all the MsAccess table values.


      My problem is that I don't know how to write the INSERT TO including the array just obtained and using the ON DUPLICATE sintax


      $sql_insert_into ="INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zone,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag )
      ?????

      the names of fiels of MsAccess and MySQL are the same....

      ON DUPLICATE KEY UPDATE SET
      CompanyName = CompanyName,ContactName = ContactName,Address=Address,City=City,Region=Regio n,PostalCode=PostalCode,Country=Country,Zone=Zone, Phone=Phone,Fax=Fax,Mobile=Mobile,Email=Email,Sale smanID=SalesmanID,Id_anag = Id_anag";

      sc_exec_sql($sql_insert_into);

      Comment


      • #4
        Originally posted by giovannino View Post

        My problem is that I don't know how to write the INSERT TO including the array just obtained and using the ON DUPLICATE sintax


        $sql_insert_into ="INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zone,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag )
        ?????

        the names of fiels of MsAccess and MySQL are the same....

        ON DUPLICATE KEY UPDATE SET
        CompanyName = CompanyName,ContactName = ContactName,Address=Address,City=City,Region=Regio n,PostalCode=PostalCode,Country=Country,Zone=Zone, Phone=Phone,Fax=Fax,Mobile=Mobile,Email=Email,Sale smanID=SalesmanID,Id_anag = Id_anag";

        sc_exec_sql($sql_insert_into);
        Something like this:

        Code:
        $sql_customers="
        SELECT
        [INDENT=2]Q_Customers.CodAnag AS CustomerID, \"Q_Customers.Ragione sociale\" AS CompanyName, Q_Customers.NomeContatto AS ContactName, Q_Customers.Indirizzo AS Address, LocalitÓ AS City, Q_Customers.Provincia AS Region, Q_Customers.CAP AS PostalCode, Q_Customers.Nazione AS Country, Q_Customers.Zona AS Zone, Q_Customers.NumeroTelefono AS Phone, Q_Customers.NumeroFax AS Fax, Q_Customers.Cell AS Mobile, Q_Customers.Email, Q_Customers.Codag AS SaleseosID, \"Q_Customers.Id anag\" AS Id_anag [/INDENT]
        FROM
        [INDENT=2]Q_Customers [/INDENT]
        ORDER BY
        [INDENT=2]Q_Customers.CodAnag [/INDENT]"; sc_lookup(rs, $sql_customers, "conn_odbc"); // You have the array populated at this point (but you ought to check for errors of course)... // Now we will write the other database... $sql_insert_into ="
        INSERT INTO
        [INDENT=2]eos_b2bc.customers ( [/INDENT][INDENT=3]CustomerID, CompanyName, ContactName, Address, City[/INDENT][INDENT=2]) [/INDENT]
        VALUES (".
        [INDENT=2]. "'" . $rs[0][0] . "'," . "'" . $rs[0][1] . "'," . "'" . $rs[0][2] . "'," . "'" . $rs[0][3] . "'," . "'" . $rs[0][4][/INDENT]
        . ")
        "; sc_exec_sql($sql_insert_into, 'other_connection');
        You can have as many connections as you like, you need to specify the connection in the sc_exec_sql() or sc_lookup() or sc_select() macros.

        Dave
        Last edited by daveprue; 03-24-2015, 01:57 AM.
        Dave Prue
        Code Whisperer
        Lahar International Corp
        www.lahar.net

        Comment


        • #5
          Great Dave !! Thanks. I will try asap

          Last problem I got is that Address field has sometime a "," before the Street number so it give me error. i.e. Street name , 10

          When I use VALUES is it possible to insert a REPLACE ? Is it the sintax like that ?? Thanks

          REPLACE ($rs[0][3], ',' , ' ')


          This is a part of my array
          CustomerID,CompanyName,ContactName,Address,City,Re gion,PostalCode,Country,Zona,Phone,Fax,Mobile,Emai l,SalesmanID,Id_anag C139,LAB. ANAL.MEDICHE FRANCO s.r.l.,,"Via Soncin, 38",PADOVA,PD,35122,IT,1,+39 049 8625966,0 049 8625967,,,AgGG,65 C084,SABER SRL,,"Via Bravi, 51",PONTE DI BRENTA,PD,35129,IT,1,+39 049 725511,,,,AgGG,68 C074,DIAGNOSTICA RIVIERA SRL,,"Via Bravi, 51",PONTE DI BRENTA,PD,35129,IT,1,+39 049 725511,,,,AgGG,69 C127,ISTITUTO FIGLIE S. CAMILLO,,"Via dell' Acqua Bullicante, 14",ROMA,RM,00177,IT,1,+39 0422 428200,,,,AgFB,71 C088,ISTITUTO DON CALABRIA,Bonfioli Arnaldo,"Via Don A. Sempreboni, 5",NEGRAR,VR,37024,IT,1,+39 045 6013111,0 045 6013603,,info@centrodoncalabria.it,AgGG,74


          I did the code as you suggested here down

          $sql_insert_into ="INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag )
          VALUES ("
          . "'" . $rs[0][0] . "',"
          . "'" . $rs[0][1] . "',"
          . "'" . $rs[0][2] . "',"
          . "'" . $rs[0][3] . "',"
          . "'" . $rs[0][4] . "',"
          . "'" . $rs[0][5] . "',"
          . "'" . $rs[0][6] . "',"
          . "'" . $rs[0][7] . "',"
          . "'" . $rs[0][8] . "',"
          . "'" . $rs[0][9] . "',"
          . "'" . $rs[0][10] . "',"
          . "'" . $rs[0][11] . "',"
          . "'" . $rs[0][12] . "',"
          . "'" . $rs[0][13] . "',"
          . "'" . $rs[0][14] . "',"
          .")";


          But it gives this Fatal error.

          (odbc): SELECT CustomerID,CompanyName,ContactName,Address,City,Re gion,PostalCode,Country,Zona,Phone,Fax,Mobile,Emai l,SalesmanID,Id_anag FROM Q1_Customers Fatal error: Cannot use object of type ADORecordSet_array as array in C:\Program Files (x86)\Scriptcase\v8_new\wwwroot\scriptcase\app\B2B _B2C\Import_Customers\index.php on line 1078

          The line 1078 is that one wiit <---

          $sql_insert_into ="INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag )
          VALUES ("
          . "'" . $this->rs[0][0] . "'," <---
          . "'" . $this->rs[0][1] . "',"
          . "'" . $this->rs[0][2] . "',"
          . "'" . $this->rs[0][3] . "',"
          . "'" . $this->rs[0][4] . "',"
          . "'" . $this->rs[0][5] . "',"
          . "'" . $this->rs[0][6] . "',"
          . "'" . $this->rs[0][7] . "',"
          . "'" . $this->rs[0][8] . "',"
          . "'" . $this->rs[0][9] . "',"
          . "'" . $this->rs[0][10] . "',"
          . "'" . $this->rs[0][11] . "',"
          . "'" . $this->rs[0][12] . "',"
          . "'" . $this->rs[0][13] . "',"
          . "'" . $this->rs[0][14] . "',"
          .")";
          Last edited by giovannino; 03-23-2015, 04:11 PM.

          Comment


          • #6
            ok another example:


            Code:
            $my_sql = "
                SELECT
                    name,
                    address
                FROM
                    accounts
            ";  
            
            sc_lookup(rs, $my_sql, "conn_one");
            
            
            // For Each Record Returned...
            
            foreach($rs as $rec) {
            
            // For Each Field in that record...
            
                foreach ($rec as $field) {
                    $thevalues .= "'" . $field . "',";
                }
                $thevalues = " VALUES (" . substr($thevalues, 0, -1) . ")";
            
                $my_other_sql = "
                    INSERT INTO  
                        our_customers  (
                            customer_name,
                            customer_addr
                        ) " . $thevalues;
            
                sc_exec_sql($my_other_sql, "conn_two");
            
            }
            If anything looks strange to you then let me know


            Dave
            Last edited by daveprue; 03-24-2015, 01:59 AM.
            Dave Prue
            Code Whisperer
            Lahar International Corp
            www.lahar.net

            Comment


            • #7
              Hi Dave , thanks so much again.
              I'm not a programmer and that's why I'm trying to use SC as framework but when the play becomes hard... I got serious limits.

              Based on your last suggestion here is what I did

              $sql_customers="SELECT CustomerID,CompanyName,ContactName,Address,City,Re gion,PostalCode,Country,Zona,Phone,Fax,Mobile,Emai l,SalesmanID,Id_anag FROM Q1_Customers";

              sc_lookup(rs, $sql_customers, "conn_odbc");

              foreach($rs as $rec)
              {
              foreach ($rec as $field)
              {
              $thevalues .= "'" . $field . "',";
              }
              $thevalues = "VALUES (" . substr($thevalues, 0, -1) . ")";

              $my_other_sql = "INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag ) " . $thevalues . " ON DUPLICATE KEY UPDATE SET
              CompanyName = CompanyName,ContactName = ContactName,Address = Address,City = City,Region = Region,PostalCode = PostalCode,Country = Country,Zona = Zona, Phone = Phone,Fax = Fax,Mobile = Mobile,Email = Email,SalesmanID = SalesmanID,Id_anag = Id_anag ";

              sc_exec_sql($my_other_sql, "conn_mysql");

              }


              It says that --> Undefined variable: thevalues
              I've tried to add $thevalues = ''; before sc_lookup but I don't know if it's correct

              For sure ON DUPLICATE KEY UPDATE SET has big mistakes . I don't know how insert the data apart from the CustomerID that is the key.
              The arrays sintax and use are not so easy to me... ;-(


              Attached the image of error
              Attached Files
              Last edited by giovannino; 03-24-2015, 05:58 AM.

              Comment


              • #8
                Originally posted by giovannino View Post
                Hi Dave , thanks so much again.
                I'm not a programmer and that's why I'm trying to use SC as framework but when the play becomes hard... I got serious limits.

                Based on your last suggestion here is what I did

                $sql_customers="SELECT CustomerID,CompanyName,ContactName,Address,City,Re gion,PostalCode,Country,Zona,Phone,Fax,Mobile,Emai l,SalesmanID,Id_anag FROM Q1_Customers";

                sc_lookup(rs, $sql_customers, "conn_odbc");

                foreach($rs as $rec)
                {
                foreach ($rec as $field)
                {
                $thevalues .= "'" . $field . "',";
                }
                $thevalues = "VALUES (" . substr($thevalues, 0, -1) . ")";

                $my_other_sql = "INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag ) " . $thevalues . " ON DUPLICATE KEY UPDATE SET
                CompanyName = CompanyName,ContactName = ContactName,Address = Address,City = City,Region = Region,PostalCode = PostalCode,Country = Country,Zona = Zona, Phone = Phone,Fax = Fax,Mobile = Mobile,Email = Email,SalesmanID = SalesmanID,Id_anag = Id_anag ";

                sc_exec_sql($my_other_sql, "conn_mysql");

                }


                It says that --> Undefined variable: thevalues
                I've tried to add $thevalues = ''; before sc_lookup but I don't know if it's correct

                For sure ON DUPLICATE KEY UPDATE SET has big mistakes . I don't know how insert the data apart from the CustomerID that is the key.
                The arrays sintax and use are not so easy to me... ;-(


                Attached the image of error
                Generally there are a couple of things that can cause problems, depending on your data.
                I made a (possibly) bad assumption that the first query would in fact return some records.
                To avoid errors when it does not, we need to add some code after the sc_lookup():

                Code:
                sc_lookup(rs, $sql_customers, "conn_odbc");
                
                if ($rs === false) {
                exit; // If we arrive here it means the SQL query was badly written
                } elseif (count($rs) == 0) {
                exit; // If we arrive here it means that no records matched our query
                }
                In addition, between the two "foreach()" statements, add this line:

                Code:
                $thevalues = "";  // This ensures that the string is initialized, and cleared each time thru.
                Dave
                Dave Prue
                Code Whisperer
                Lahar International Corp
                www.lahar.net

                Comment


                • #9
                  Ok, MsAccess tables have Always values inside but it is better to use the if code to control them as you suggest.

                  The real problem I have is how to write the right sintax of ON DUPLICATE KEY UPDATE SET paragraph based on array values ...selected above.

                  The two tables (MsAccess and mySQL) have the same name of columns but the values I've to insert I think are derived from arrays

                  CompanyName = CompanyName i.e. can't work . I think I've to use CompanyName = something connected to $thevalues ???

                  Comment


                  • #10
                    You need to google "var_dump" and play with that a bit, so that you can test and see the contents of variables, etc.

                    Also, you can turn on debugging in the left "Application->Settings" menu, and see all of the generated SQL, that should help you as well.

                    There are some great php and sql tutorials on w3schools.com that are very worthwhile the time spent.

                    Dave
                    Dave Prue
                    Code Whisperer
                    Lahar International Corp
                    www.lahar.net

                    Comment


                    • #11
                      OK, thanks, for var_dump I suppose that I need a different background first. I did a try but I got the lenght of variables....;-(


                      I think I have to use CompanyName = $field[1] or CompanyName = $field[0][1]

                      or better CompanyName = $rs[1] or CompanyName = $rs[0][1] ................. I don't know the sintax to use

                      Comment


                      • #12
                        Turn on debugging in the application and you will see the SQL that gets generated
                        Dave Prue
                        Code Whisperer
                        Lahar International Corp
                        www.lahar.net

                        Comment


                        • #13
                          Yes, I turn it On yet.

                          After all variables testing this is the only that show something....

                          $my_other_sql = "INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag ) " . $thevalues . " ON DUPLICATE KEY UPDATE SET
                          CompanyName = $rec[1],ContactName = $rec[2],Address = $rec[3],City = $rec[4],Region = $rec[5],PostalCode = $rec[6],Country = $rec[7],Zona = $rec[8], Phone = $rec[9],Fax = $rec[10],Mobile = $rec[11],Email = $rec[12],SalesmanID = $rec[13],Id_anag = $rec[14] ";

                          sc_exec_sql($my_other_sql, "conn_mysql");


                          On Address field I got a "," inside the value to separate the house # i.e. Street A. Volta, 51
                          Now it stop there with error.
                          Is there a way to jump that problem...
                          Then I think it will work !!!!

                          Comment


                          • #14
                            Originally posted by giovannino View Post
                            Yes, I turn it On yet.

                            After all variables testing this is the only that show something....

                            $my_other_sql = "INSERT INTO eos_b2bc.customers (CustomerID,CompanyName,ContactName,Address,City,R egion,PostalCode,Country,Zona,Phone,Fax,Mobile,Ema il,SalesmanID,Id_anag ) " . $thevalues . " ON DUPLICATE KEY UPDATE SET
                            CompanyName = $rec[1],ContactName = $rec[2],Address = $rec[3],City = $rec[4],Region = $rec[5],PostalCode = $rec[6],Country = $rec[7],Zona = $rec[8], Phone = $rec[9],Fax = $rec[10],Mobile = $rec[11],Email = $rec[12],SalesmanID = $rec[13],Id_anag = $rec[14] ";

                            sc_exec_sql($my_other_sql, "conn_mysql");


                            On Address field I got a "," inside the value to separate the house # i.e. Street A. Volta, 51
                            Now it stop there with error.
                            Is there a way to jump that problem...
                            Then I think it will work !!!!
                            Any time you are specifying a value for SQL, you can add single quotes to allow spaces or commas in the string:

                            Address = "'" . $rec[3] . "'";
                            Dave Prue
                            Code Whisperer
                            Lahar International Corp
                            www.lahar.net

                            Comment


                            • #15
                              Hi,
                              I'm doing testing about the for cycle . Mmmmhhh ... not good..

                              $thevalues = '';
                              sc_lookup(rs, $sql_customers, "conn_odbc");

                              foreach($rs as $rec) {

                              // For Each Field in that record...

                              foreach ($rec as $field) {
                              $thevalues .= "'" . $field . "',";
                              }
                              $thevalues = " VALUES (" . substr($thevalues, 0, -1) . ")";

                              echo $thevalues ."<br>";

                              }

                              Why I cannot have the fields and VALUES correctly like this


                              VALUES (aaaaaa, bbbbbb, cccccc , dddddddd) VALUES (eeeee, fffffff, gggggg , hhhhhhhh) VALUES (iiiiiii, lllllllllllllll, mmmmmm , nnnnnnnnn)


                              Here the output of above code


                              VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')
                              VALUES ( VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')'C002','BIOTESTS s.r.l.','D'ACUNTI','Via Crociata 2 Ang. S.Pio X')
                              VALUES ( VALUES ( VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')'C002','BIOTESTS s.r.l.','D'ACUNTI','Via Crociata 2 Ang. S.Pio X')'C003','U.L.S.S. 21 LEGNAGO','','Via Gianella 1')
                              VALUES ( VALUES ( VALUES ( VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')'C002','BIOTESTS s.r.l.','D'ACUNTI','Via Crociata 2 Ang. S.Pio X')'C003','U.L.S.S. 21 LEGNAGO','','Via Gianella 1')'C004','CASA DI CURA POLISPECIALISTICA PEDERZOLI S.p.A.','','Via Monte Baldo 24')
                              VALUES ( VALUES ( VALUES ( VALUES ( VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')'C002','BIOTESTS s.r.l.','D'ACUNTI','Via Crociata 2 Ang. S.Pio X')'C003','U.L.S.S. 21 LEGNAGO','','Via Gianella 1')'C004','CASA DI CURA POLISPECIALISTICA PEDERZOLI S.p.A.','','Via Monte Baldo 24')'C005','ANALISI MEDICHE PAVANELLO s.r.l.','','Via Bravi 51')
                              VALUES ( VALUES ( VALUES ( VALUES ( VALUES ( VALUES ('C001','CENTRO BIOMEDICO TERMALE SRL','','Via A. Volta 51')'C002','BIOTESTS s.r.l.','D'ACUNTI','Via Crociata 2 Ang. S.Pio X')'C003','U.L.S.S. 21 LEGNAGO','','Via Gianella 1')

                              Comment

                              Working...
                              X