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

giovannino · May 7, 2020, 7:59pm

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

daveprue · May 7, 2020, 8:46pm

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

giovannino · May 7, 2020, 8:46pm

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,Region,PostalCode,Country,Zone,Phone,Fax,Mobile,Email,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=Region,PostalCode=PostalCode,Country=Country,Zone=Zone,Phone=Phone,Fax=Fax,Mobile=Mobile,Email=Email,SalesmanID=SalesmanID,Id_anag = Id_anag";

sc_exec_sql($sql_insert_into);

daveprue · May 7, 2020, 8:46pm

[QUOTE=giovannino;34695]

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,Region,PostalCode,Country,Zone,Phone,Fax,Mobile,Email,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=Region,PostalCode=PostalCode,Country=Country,Zone=Zone,Phone=Phone,Fax=Fax,Mobile=Mobile,Email=Email,SalesmanID=SalesmanID,Id_anag = Id_anag";

sc_exec_sql($sql_insert_into);[/QUOTE]

Something like this:



$sql_customers="[INDENT]SELECT [/INDENT]
[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]
[INDENT] FROM
[/INDENT]
[INDENT=2]Q_Customers
[/INDENT]
[INDENT] ORDER BY[/INDENT]
[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 ="[INDENT]INSERT INTO  
[/INDENT]
[INDENT=2]eos_b2bc.customers  (
[/INDENT]
[INDENT=3]CustomerID,
CompanyName,
ContactName,
Address,
City[/INDENT]
[INDENT=2])
[/INDENT]
[INDENT]VALUES (".
[/INDENT]
[INDENT=2]. "'" . $rs[0][0] . "',"
. "'" . $rs[0][1] . "',"
. "'" . $rs[0][2] . "',"
. "'" . $rs[0][3] . "',"
. "'" . $rs[0][4][/INDENT]
[INDENT]. ")[/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

giovannino · May 7, 2020, 8:46pm

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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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] . “’,”
.")";

daveprue · May 7, 2020, 8:46pm

ok another example:


$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

giovannino · May 7, 2020, 9:11pm

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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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

daveprue · May 7, 2020, 8:46pm

[QUOTE=giovannino;34751]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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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[/QUOTE]

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():


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

if ($rs === false) {[INDENT]exit;    // If we arrive here it means the SQL query was badly written
[/INDENT]
}
elseif (count($rs) == 0) {[INDENT]exit;   // If we arrive here it means that no records matched our query
[/INDENT]
 }

In addition, between the two “foreach()” statements, add this line:


$thevalues = "";  // This ensures that the string is initialized, and cleared each time thru.

Dave

giovannino · May 7, 2020, 8:46pm

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 ???

daveprue · May 7, 2020, 8:46pm

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

giovannino · May 7, 2020, 8:46pm

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

daveprue · May 7, 2020, 8:46pm

Turn on debugging in the application and you will see the SQL that gets generated

giovannino · May 7, 2020, 8:46pm

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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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 !!!

daveprue · May 7, 2020, 8:46pm

[QUOTE=giovannino;34765]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,Region,PostalCode,Country,Zona,Phone,Fax,Mobile,Email,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 !!![/QUOTE]

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] . “’”;

giovannino · May 7, 2020, 8:47pm

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’)

robydago · May 7, 2020, 8:47pm

I think you have to reset you var:

foreach($rs as $rec) {
$thevalues = “”;

// For Each Field in that record…

foreach ($rec as $field) {

giovannino · May 7, 2020, 8:47pm

Thanks Roby, I will check today…

daveprue · May 7, 2020, 8:47pm

[QUOTE=daveprue;34754]
In addition, between the two “foreach()” statements, add this line:


$thevalues = "";  // This ensures that the string is initialized, and cleared each time thru.

Dave[/QUOTE]

as I said before

giovannino · May 7, 2020, 8:47pm

Thanks Dave !!! It was the fixing