MySQL syntax error with TestStand 4.0 database logging

[bpeters]

I had been using MS Access database to log results which worked fine but the access database is too limited. Switched to MySql. Database works fine and I can query it from Visual C++ and was able to create all my result tables fine from TestStand.

I get a syntax error when actually trying to log results and it has to do with either a missing quote or and extra quote, I can't quite tell. I thought I saw a post on this somewhere but can't find it now. Here is the complaint from TestStand:

An error occurred calling 'LogOneResult' in 'ITSDBLog' of 'zNI TestStand Database Logging'
An error occurred executing a statement.
Statement: UUT_RESULT.
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES' at line 1
Description: [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES' at line 1
Number: -2147467259
NativeError: 1064
SQLState: 37000
Reported by: Microsoft OLE DB Provider for ODBC Drivers
Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Number: -2147217887
NativeError: 0
SQLState: 37000
Reported by: Microsoft OLE DB Provider for ODBC Drivers

Source: TSDBLog

And here is all that appears in the MySql logfile:

INSERT INTO UUT_RESULT DEFAULT VALUES
070914

You can't see it here but the string is truncated after the word VALUES. The next line starts with the odd number.

Any ideas?

Thanks,

Bill Peters

BAE SYSTEMS

[TerryS]

Hi Bill,

One thing to check is if the MySQL database that you have setup for use with TestStand can allow null values or can allow default values.  Try running the SQL statement that is throwing the error outside of TestStand to see if you get the same error. 

Also, check the MySQL manual to see that it supports the DEFUALT VALUES syntax.

[bpeters]

Thanks,

Yes as it turned out I had a problem with my schema. MySql doesn't support default recordsets, it supports create/insert. There is a specific TestStand schema for that (although in 4.0 it's got some bugs).

After I got the schema straightened out everything is running dandy.

Thanks again,

Bill Peters

BAE SYSTEMS

[Scott_Richardson]

Bill -
When we added the MySQL support, I could not get MySQL to return the value of an auto incremented field reliably, so using the Insert method is suggested. Also, you stated "(although in 4.0 it's got some bugs)" in your posting. Could you be more specific on the issues that you found and what you changed?

[bpeters]

Sure. There were two bugs in the ini file that contains the default TS schemas (I think it was Default_Database_Options.ini or like that...).

Anyway the table description for STEP_NUMERICLIMIT had for it's command line INSERT INTO MEAS_NUMERICLIMIT. I guess TestStand looked at that and said hmm I better create that table too. Then later down the page when you try to create MEAS_NUMERICLIMIT it says the table already exists even though it is empty. The generate SQL button builds a bad file and then the database viewer creates a bad database. I would have to go into MySql and drop the database. I had to save the schema under a custom name, edit it read it back in, generate a new SQL file, but then it would still fail validation because the ini files don't get updated until you exit TestStand. So basically I did this process 3 times before I got it right.

It's wasn't a big deal but it's amazing how a little thing can cause a lot of work.

The second bug, I'm not certain was initially in the file or somehow got set on the fly. There was a random int statement at the very end of the SQL table description for UUT_RESULT. It was also in the schema INI file. Since the int had no name and occurred in a odd place, it created a syntax error in MySql. Same fix as above.

But like I said it's working great now. I like this approach because I serve it to the net with Apache and parse the database with simple PHP scripts.

Thanks,

Bill Peters

BAE SYSTEMS

[HeatherTcore]

Having similar problem, but using the Database "Data Operation" step type configured for a "set and put" data operation instead of schemas. Is there a way to have teststand use an "INSERT" my SQL command whenever attempting to do a "put"? It just seems redundent to have to build the complete INSERT SQL command when the Data Operation step type encapsulates the function we need so nicely.

BTW, I'm a bit surprised that executing a teststand database operation results in syntax errors, or as you mentioned odd behaviour for supported databases. Spent better part of the day trying to figure out what I was doing wrong :-).

[Scott_Richardson]

Heather -
The Database Operation step type does not know how to execute an SQL statement directly. Its purpose is to map values to columns for a SELECT statement or parameters for an INSERT statement or stored procedure. The Database Operation step can be executed multiple times for a single parameterized INSERT or stored procedure statement using the Set and Put operation. You may already know this, but if you want to execute a single SQL command once, you can use the Open Statement step by building the string SQL command as an expression, for example:

"INSERT INTO TABLE_NAME (COLNAME1, COLNAME2) VALUES(" + Str(Locals.Var1) ", " Locals.Str1 ")"

Hope this helps...

[HeatherTcore]

Thanks Bill, Very Helpful, but can you confirm the following:

a) When i select "Edit Data Operation" for a database Data operation, I should specify the Statement Handle associated with an INSERT SQL command.

b) The SQL Command specified in the Column/Parameters Values TAB is a SELECT command allowing Testand to load the column Names from the database.

What would the syntax be for the INSERT command, since the Column/Parameter Values specified must somehow get mapped into the COLUMN and VALUES string appended to the INSERT command?

I am currently building the insert string manually, as you suggested, and it works great. The benefit to using the Data Operation step type is that if It's easier to manage changes in the database table if I decide not to log certain measurements, or If I change a column name in my database. To do a proper job building the INSERT string, I would need to do what the Data Operation is doing already ... i.e. read the Schema from the table, and verify that the ColumnNames used in the insert command exist in the database, omitting those entries where no Column exists,

[Scott_Richardson]

Heather -
a) When you use an INSERT statement like the one I posted, the SQL command connects to the table, inserts the values specified in the no recordset or parameters are returned. so the Data Operation step cannot be used. The INSERT statement like this stands by itself.

b) If you use an INSERT statement like this:

"INSERT INTO TABLE_NAME (COLNAME1, COLNAME2) VALUES(?, ?)"

the statement handle contains a parameterized statement that you execute multiple times in a Data Operation step. The Data Operation step must use the "Execute" operation and it will define 2 columns, one for COLNAME1 and another for COLNAME2. I think my previous posted incorrectly mentioned a Set and Put operation. The data types must match the cooresponding fields.

[Olof]

Hi,

I have a similar problem.

I´m trying to use "On-The-Fly logging" but I get following error:

An error occurred calling 'LogOneResult' in 'ITSDBLog' of 'TestStand Database Logging'
An error occurred executing a statement.
Statement: TestResult.

Source: TSDBLog

-2147352567; User-defined error code.
Possible System Error: Exception occurred.

Step 'LogResult' of sequence 'ProcessModelPostResultListEntry' in 'SequentialModel_xxxx.Seq' 

I´m using TestStand 3.5 and a MySQL database.

If I don´t use "On-The-Fly logging" it works fine...

Regards Olof