How To Log Using Db.execute

[gillecaluimi]

Just getting started with daqfactory and need to update remote mysql table with logging data.  I've seen a couple of posts regarding doing direct db sql statementws (db.execute) but I can't seem to locate a how to section in the daqfactory help section

[AzeoTech]

The section you want is section 9.4 and 9.5 in the user's guide, mostly 9.5.  db.execute() is pretty straight forward.  Assuming you have the database open with open() or openEx() and a handle, you simply pass that handle along with a SQL string that you want executed.  You can send any valid SQL string you want.  With execute(), however, you won't get a response, so for SELECT, you almost always want to use Query(), or better yet, use QueryToClass().  In fact, I never use Query() any more and when doing database work pretty much always just use these 4 functions:

 

open() or openEx():  to open the database

execute() : to run all SQL commands except SELECT

queryToClass(): to run SELECT commands

close(): to close the database

[AzeoTech]

I should add: there is one case where you actually can't use QueryToClass() and have to use Query() and the other associated functions.  QueryToClass() has to know what the size of the result set is in order to work.  To do this in a way that works on all databases, it wraps the SQL function COUNT() around your SELECT statement.  So, if you had SELECT * from myTable;, QueryToClass() would first execute: SELECT COUNT(*) from myTable; then it would execute the statement you specified.  There are two cases where you might not want this:

 

1) doing COUNT first puts too much of a load on your database

2) adding COUNT() around your SELECT makes it invalid because you have a complex SELECT statement.

 

Query() passes your SELECT right through so won't do this, but you lose performance having to scan through your result set in script.  If your result set is only one record, no problem, but the performance difference between Query() and QueryToClass() when its 10,000 records is several orders of magnitude, and the difference increases exponentially with the size of the result set.

[gillecaluimi]

Thanks, but I'm having trouble opening the db.  I've got 64bit OBDC mysql dsn setup (MySQL ODBC 5.2 Unicode Driver), which can successfully connect to the remote server mysql db.  The User DSN is called testDSN which connects to db=testDB using credentials user=testUser, pwd=testPWD

created sequence updateWeather:

 

local testDB

testDB= db.Open("testDSN","testUser","testPWD")

 

running the sequence:

O1002 Unable to open ODBC database: updateWeather Line 2 - Uncaught error in sequence updateWeather

 

is there a problem using the 64bit OBDC drivers with daqfactory?

[AzeoTech]

Yes there is.  You can't use 64 bit ODBC drivers with 32 bit applications.  This is a Windows thing.  We ran into the same issue when we upgraded our server to 64 bit.  We used to have some stuff in Access (yes, I know, but it was setup a long time ago...) and there is only a 32 bit Access ODBC driver available.  So, we had to migrate the database to a better one that had a 32 bit ODBC driver.

 

So, you need to use a 32 bit version of your ODBC driver.  Windows makes this a pain actually, because there are TWO ODBC control panels, one for 64 bit drivers, and one for 32 bit.  When you go to control panel on a 64 bit OS, you get the 64 bit version and none of the data sources there will work with 32 bit apps, including DAQFactory.  

 

Per the Windows knowledgebase:

 

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

 

Notice how the 64 bit version is in the folder with "32" in it, while the 32 bit version is in the one with "64" in it!

 

The knowledgebase also says that the 64 bit version of the control panel will display 32 bit user DSN's but not 32 bit system DSN's.  I don't know about that.  I just always use the 32 bit version.

[gillecaluimi]

OK, baby steps...was able to open the database but this fails

 

local weatherDB

weatherDB = db.Open("testDSN")

db.Execute(weatherDB,"INSERT INTO test(teststring,date) VALUES ("hello",NOW())")

 

I've tested this sql statement in a different sql manager...and it works.

I get error

 

C1000 Channel or function not found: updateWeather Line 3 - Uncaught error in sequence updateWeather

[AzeoTech]

That's because you have " inside of ".  To DAQFactory (and most any other programming language), its syntactically incorrect.  Its looking for a variable called hello, and truthfully, even if that existed it would fail because there are missing operators.

 

Anyhow, if you want to use " in a static string, use ' to specify the string:

 

'INSERT INTO test(teststring, date) VALUES "hello", NOW())'

[gillecaluimi]

Hmmmm......when you enter the code, the automatic hint that pops up for db.Execute has "UPDATE t SET ..." in the example 

but changing it made it work...thanks

[AzeoTech]

Yes, it does.  Because its valid to use ".  You just can't put " inside of " because DAQFactory thinks the " is a closing quote, not an actual character.  This is the same with almost every programming language, and the ones that are different (like C) use escape characters.  The automatic hint doesn't have an internal quote.  In fact, the automatic hint isn't even a valid SQL string, its just a placeholder. 

 

So this is valid:

 

db.execute("SELECT * from abc")

 

while this is not:

 

db.execute("SELECT "table".* from abc")

 

because DAQFactory parses it as three separate items:

 

"SELECT "

table

".* from abc"

 

The first and last items are static strings.  The middle, table, is assumed to be a channel or variable.  But as I said, even if table was valid variable, you are still missing operators.  You can concatenate strings using +:

 

"SELECT " + table + ".* from abc"

 

and then if table is valid, say with the string "myTable" (without the quotes) in it the result would be:

 

SELECT myTable.* from abc

[gillecaluimi]

what object type is the second argument of db.Execute(arg1,arg2)....string?

[AzeoTech]

String.  Its the SQL string you want to execute.  However, you can construct strings in DAQFactory from variables using the + concatenation operator.

[apmm80]

Hi. My name is Andrés and I have a problem with the db.execute() comand.

I use the ODBC 32bits in WIN 7 proffesinonal.

I send this sequence:

     private dbase
     dbase = db.Open("DeAva32b","EXPCV","GAX")
     db.Execute(dbase,'INSERT INTO LIF/ELF (Fecha,El1,El2,Den,Temp) VALUES ("04/10/13","11","27","18","17.51")')

     db.Close(dbase)

The comand "db.Open" seems to work properly but the line DB.execute()  develop an error

The error code is "Unable to execute: HY010: PruebaBBDD Line 3 - Uncaught error in sequence PruebaBBDD"

I don´t know the meaning of this error.

Also I try changing " " and ' ' but I can´t fix this error

Can you help me?

Thank´s very much

 

Also I try changing " " and ' ' but I can´t fix this error

Can you help me

Thank´s very much
 

[AzeoTech]

There is most likely an error in your SQL script.  I'm guessing you don't want quotes around the numbers.  Usually a good starting point is to type your queries directly into the database using whatever tool they provide (for example the MySQL prompt) and make sure the syntax is ok.