Sql logging

[Andreschrosa]

Hey folks, I'm starting out in DAQFactory programming now and having trouble with something pretty simple: using ODBC sql server I create a data source and modify quickmod pro to log data into that sql database, but it returns this error message:

C1011 Could not create table for logging. Msg: incorrect syntax near ')'.

I'm using MS sql server 2005 express.

I created a new logging called logSql, edited all sequences where logme is called to work with logSql.

logSql's configuration is the following:

ODBC database:

Data source: Logging

Table name: Logging

on details tab:

fixed interval

interval 1

snapshot

time sig figs 12

[AzeoTech]

First, make sure you let DAQFactory create the table. It expects certain field names. If you want to create your own table and do more advanced stuff you are going to have to use the lower level db. functions to query the database directly. Its really typical DAQFactory. The high level stuff like Logging Sets gives you the ability to do generic logging, but if you need to do something outside the norm, you have to drop into script and do it there.

That said, the most likely problem is that SQL is actually not completely standardized. Different database types use slightly different dialects. I forgot what dialect we default to, but its definitely not MSSQL (I think its actually MySQL). Fortunately you can easily change the dialect DAQFactory uses. To the right of the Data Source entry in the logging set is a button that says SQL. Clicking on that pops up a window where you can specify what SQL name should be used for the two commands used and the three data types. Type in what MSSQL uses and it should work better for you. Note that its saved with the logging set, so if you create several logging sets, you'll need to specify the dialect in each one.

[Andreschrosa]

That must be it, changed it, still not working, but the error msg now reads: invalid syntax near Logging.

I will switch to mysql then and see if it works, as long as it uses sql it's ok for our application.

Thanks for the help

[Andreschrosa]

Yep, it worked fine with mysql, thanks again!

[Andreschrosa]

I decided to give a try to firebird interbase sql, it works fine with QueryToClass function, but some configuration is needed for the sql logging set to work right.

Firebird ODBC datasource give options to activate quoted identifiers, sensitive identifiers and auto quoted identifiers. I have tried many combinations of then, but none seens to work.

Any help with this is appreciated.

[dchappel]

Dumb question....

To date, we have been using Access as our database to log data.

Is there a free (open source) SQL database that is a good match with DF?

Thanks for any advice the forum members might have...

[AzeoTech]

Well my first choice, open source or not, is MySQL. Its much better than Access, and unless you are doing a really big system, better than all the other alternatives that I know of. For that matter, it may be just as good for big systems, but I doubt anybody is doing anything that big (think the backend of google, or similar).

[dchappel]

OK...

Have figured out MYSQL, added ODBC link, etc. Thanks Guru.

Table gets added to my database with Channel names I have selected. However, data is not getting inserted. Just have nulls and empty sets when trying to see data.

No errors in command / alert.

I am using the Test device and channel is updating (can see values changing in table view).

Using 1 sec fixed interval.

What am I missing?

[AzeoTech]

I think with MySQL you have to change the sql commands for the data types. The default in DAQFactory is to work with Access since that is the most common for some reason. Next to data source in the logging set is a button named "SQL" that when you press pops up a window allowing you to specify the SQL terms to use. The default is DOUBLE and TEXT. You'll have to look in the MySQL manual for what to use instead. I don't know off the top of my head. Once you change it, you'll need to delete the table and let DAQFactory recreate it.