fill tables of an mysql database via odbc connector

[janwolk]

dear cummunity,



while testing this script



function jobArrived( s : Switch, job : Job )

{



var ordernumber = job.getNameProper();



dbConn = new DataSource();



dbConn.connect("data-test", "root", "");



if ( dbConn.isConnected() )

{

var len = job.getNameProper().length;

// var campaign = job.getNameProper().left(len - 12);

var campaign = job.getNameProper().left(12);

var jobname = job.getNameProper();

var quantity = job.getNameProper().right(3);



s.log(1,"Database connection succes" + ordernumber + "ja praat" + campaign + " " + jobname + " " + quantity);



dbStat = new Statement(dbConn);

dbStat.execute("rsp_insert_records '" + campaign + "'" + "," + "'" + jobname + "'" + "," + quantity);

dbConn.disconnect();

job.sendToSingle(job.getPath());

}

else

{



s.log(1,"Database connection error");

}

job.sendToSingle(job.getPath());

}



i Need help Debugging Switch, the odbc connector and the mysql database. If i feed a sample flow, i get the error "Database Connection error", and I do not know where to start searching. AFAIK the Connection via ODBC and mysql is working, so what values would i feed this Statement with?



dbConn.connect("data-test", "root", "");



data-test ist the odbc dsn, root would be the Password of the mysql database?



Tnak you in advance, Jan





----





ok, you can debug the odbc Driver in Microsoft and log it to file: here you'll find:



SwitchScriptExe 1bf4-1534 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)

HDBC 0x03529810

HWND 0x00000000

WCHAR * 0x71CB8B34 [ -3] "****** 0"

SWORD -3

WCHAR * 0x71CB8B34

SWORD -3

SWORD * 0x00000000

UWORD 0



DIAG [IM014] [Microsoft][ODBC Driver Manager] Der angegebene DSN weist eine nicht übereinstimmende Architektur von Treiber und Anwendung auf. (0)



So, that means you'll have to install the 32 bit Version of the mysql odbc driver

[dkelly]

Switch is still a 32-bit application so you need the 32-bit ODBC driver. connect() returns false if the connection failed and a message is logged about the error. Unfortunately the API doesn't give the script access to the error code or message....

[janwolk]

yeah, thanks, that's it, here http://www.milanm.com/?p=556 you'll find how to handle 32/64 bit...

[janwolk]

Are there some mySQL nerds in here? I added some to the code like the following snippet:



I want to update the row jobteile within the table stpauli and need to save the value for idjob, but I do not know how to do this an to set another variable wit this saved variable... Any hints? Thank you in advance!!



function jobArrived( s : Switch, job : Job )

{



// var ordernumber = job.getNameProper();

// var ordernumber = [Metadata.Text:Path="/JDFAdapter/Auftrag/@Auftragsnummer",Dataset="Jdf",Model=JDF];

var ordernumber = s.getPropertyValue("Ordernumber");

var customer = s.getPropertyValue("Kunde");

var desc = s.getPropertyValue("Jobbeschreibung");



dbConn = new DataSource();





dbConn.connect("BEO","root","(work)");



if ( dbConn.isConnected() )

{

var len = job.getNameProper().length;

// var campaign = job.getNameProper().left(len - 12);

var campaign = job.getNameProper().left(12);

var jobname = job.getNameProper();

var quantity = job.getNameProper().right(3);



s.log(1,"Database connection succes" + ordernumber);



dbStat = new Statement(dbConn);

dbStat.execute("INSERT INTO `jobs`(`jobnr`, `kundenname`, `beschreibung`) VALUES ('" + ordernumber + "', '" + customer + "', '" + desc + "')");

dbStat.execute("INSERT INTO `jobteile` (`idjob`) SELECT id FROM `jobs` WHERE jobnr like '%" + ordernumber + "%'");

// dbStat.execute("INSERT INTO `jobteile` (`datum`) SELECT SUBSTRING(timestamp,1,10) FROM jobs WHERE jobnr like '%" + ordernumber + "%'");

dbStat.execute("UPDATE `jobteile` SET `idbereich` = `1` WHERE `jobteile`.`id` = '%" + ordernumber + "%'");

dbConn.disconnect();

job.sendToSingle(job.getPath());

}

else

{



s.log(1,"Database connection error");

}

job.sendToSingle(job.getPath());









}

[freddyp]

You want to update something in the table stpauli, but you do not use that table in your SQL statements. I also do not understand what value you want to put where and without seeing the database, that is difficult.



Anyway, I would recommend that you create a stored procedure in the database. In MySQL Workbench you find the stored procedures under the name of the database where the tables are too. Right-click and "Create stored procedure ...".



Your stored procedure would look something like this (not tested of course):



CREATE PROCEDURE `UpdateJob`(IN JobNumber VARCHAR(6), CustomerName VARCHAR(255), Description VARCHAR(255))



BEGIN



INSERT INTO `jobs`(`jobnr`, `kundenname`, `beschreibung`) VALUES (JobNumber, CustomerName, Description);

INSERT INTO `jobteile` (`idjob`) SELECT id FROM `jobs` WHERE jobnr like JobNumber;

UPDATE `jobteile` SET `idbereich` = `1` WHERE `jobteile`.`id` = JobNumber;



END



The stored procedure has three advantages:

- you can loop, use conditions, create temporary variables, perform calculations, ...

- you can easily test and debug the stored procedure inside Workbench

- to use it in Switch all you have to do is:

dbStat.execute("CALL UpdateJob(" + ordernumber + "," + customer + "," + desc + ")");



Something else: you do a sendToSingle regardless of whether the SQL statement worked or not. Change the outgoing connection definition of your script to Traffic-Light and use



job.sendToData(1,job.getPath()); //when the statement was successful and

job.sendToData(3,job.getPath()); //when there was an error



Then at least you can react (eg with a mail) when there was a problem updating the database.



Freddy

[janwolk]

Thank you again for this tip, I'll do so as far as the mysql.proc table will work again in our database...



Thank you so much that far! regards, Jan



freddyp wrote: You want to update something in the table stpauli, but you do not use that table in your SQL statements. I also do not understand what value you want to put where and without seeing the database, that is difficult.



Anyway, I would recommend that you create a stored procedure in the database. In MySQL Workbench you find the stored procedures under the name of the database where the tables are too. Right-click and "Create stored procedure ...".



Your stored procedure would look something like this (not tested of course):



CREATE PROCEDURE `UpdateJob`(IN JobNumber VARCHAR(6), CustomerName VARCHAR(255), Description VARCHAR(255))



BEGIN



INSERT INTO `jobs`(`jobnr`, `kundenname`, `beschreibung`) VALUES (JobNumber, CustomerName, Description);

INSERT INTO `jobteile` (`idjob`) SELECT id FROM `jobs` WHERE jobnr like JobNumber;

UPDATE `jobteile` SET `idbereich` = `1` WHERE `jobteile`.`id` = JobNumber;



END



The stored procedure has three advantages:

- you can loop, use conditions, create temporary variables, perform calculations, ...

- you can easily test and debug the stored procedure inside Workbench

- to use it in Switch all you have to do is:

dbStat.execute("CALL UpdateJob(" + ordernumber + "," + customer + "," + desc + ")");



Something else: you do a sendToSingle regardless of whether the SQL statement worked or not. Change the outgoing connection definition of your script to Traffic-Light and use



job.sendToData(1,job.getPath()); //when the statement was successful and

job.sendToData(3,job.getPath()); //when there was an error



Then at least you can react (eg with a mail) when there was a problem updating the database.



Freddy