Using data from MySQL via ODBC

[matb]

Hi all,



I'm having troubles trying to use data from MySql database into Powerswitch 8.

I had been able to establish a good connection ("Database connection succes") to my MySql database [cfr also Zegwaard 2008-06-13 07:18:00 ]

But I'm not able to use data from database!

I always get from the captured log (s.log(1,dbStat))

[object Database8::Statement]

... not so enought useful for me!



here the script



function jobArrived( s : Switch, job : Job )

{

dbConn = new DataSource();

dbConn.connect("mysql","root","mysql");

if ( dbConn.isConnected() )

{

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

}

else

{

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

}

dbStat = new Statement(dbConn);

dbStat.execute("SELECT * FROM prima WHERE uno = '11'");

s.log(1,dbStat);



var name = dbStat;

var theName = job.getName();

var newname = name + " - " + theName;

dbConn.disconnect();

job.sendToSingle( job.getPath(),newname );

}



So while my input file is "night_test_15.pdf" my output always appear as "Statement] - night_test_158.pdf".





Can anybody help me

ie where can I find scripting examples regarding managing data from database.





many thanks to all

mario



(Original post by Mario, G. Canale & C. S.p.A.)

[matb]

Hi Mario,



to access the result of a database query you have to use the suitable methods of the Statement class. Your code line

var name = dbStat;

simply assignes the Statement object itself to the variable name. What you actually want is to read a column value of a record/row found by the query.

A suitable code could look like that:



if( dbStat.isRowAvailable() ) {

dbStat.fetchRow();

name = dbStat.getColumnValue( "" );

}



Regards



Robert



(Original post by Robert, Impressed GmbH)

[foxpalace]

Hi Robert,



there is now getColumnValue in PowerSwitch 11 and the Docu isn't much helpful.

Can you tell us, how to get a result from a query (select bla,bla1,bla2 from table where bla = 'bla';) so that I can read the result with s.log?



Gruß

Michael

[dkelly]

I'm not aware of a getColumnValue() function. Switch has 4 different ways to get a value from a Statement select: getNumber(), getDate(), getString() and getBinary(). You can call getColumnDataType() to determine the native type or just call getString() if you don't really care.



Here's an example of retrieving results from SQL query via ODBC



var datasource = new DataSource();

datasource.useConnection("myDSN");

if (datasource.isConnected()) {

var stmt = new Statement(datasource);

stmt.execute("SELECT some,fields FROM table WHERE someField='0';");

if (stmt.isSuccess()) {

while (stmt.isRowAvailable()) {

stmt.fetchRow();

for (var c=0; c<stmt.getNumColumns(); c++) {

var name = stmt.getColumnName(c);

var value = stmt.getString(c);

}

}

}

}





Dwight Kelly

Apago, Inc.

dkelly@apago.com

[foxpalace]

Hi dkelly,



thank you - I get an result with getString, but only the first digit:



maybe the result is: 2012

i get only: 2



Can you help?

[dkelly]

Make sure your ODBC DSN encodings are UTF8 (query) and UTF32 (results)