On-The-Fly Database logging with TS3.0 and MySQL

[bruno_p]

I try to use MySQL4.0.18 with Teststand 3.0 (and the MySQL ODBC v3.51 driver).

I can log my results when i use a copy of the generic recordset schema : I replaced the auto-incremented id by a GUID (wich is a string of 38 chars in the MySQL database).

But this configuration does not work with the On-The-Fly option : Teststand creates an empty record in the table "uut_result" to get the guid reference. Then it uses this guid with the table "step_result". At the end of the execution it should update the previous record, but it creates a new entry in "uut_result" to insert the data.

I try to adapt the generic insert schema but it does not work : The SQL statement to insert the data doesn't recognize the string fields (t
he simple quotes ( ' ' ) are missing).

Can anyone help me ?

[Scott_Richardson]

Bruno -
In the past when I did some MySQL testing I found that I had better results when using the "unofficial" OLE-DB Provider that was available from MySQL. See http://solutions.mysql.com/other/related-sites/?item=142

I am aware of the following issues with the ODBC driver from MySQL and TestStand:

1) When you use the MySQL ODBC driver and the operating system specifies a comma character as the decimal point character, the ODBC driver can return the following error when logging results:

[MySQL][ODBC 3.51 Driver][mysqld-nt] Column count doesn't match
value count at row 1

This error occurs because the ODBC driver internally converts a floating point value to a string value the system local and MySQL interprets the comma decimal point character in the SQL syntax as a multi-value list character separator. To fix this, configure the MySQL data source in the ODBC Administrator and enable the option "Don't Use Set Locale" located in the Miscellaneous Options section.

2) The MySQL ODBC driver returns the following error while logging results to database when you specify column data type as "String":

[MySQL][ODBC 3.51 Driver][mysqld-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 'Callback,'SequenceCall',
'Passed',NULL,0,NULL,5.2452199999999997

Internally, the database logging feature passes data to the databases as a BSTR. The MySQL ODBC driver fails to encapsulate string BSTR values in quotes when constructing SQL statements from parameterized data, i.e INSERT INTO TABLE(columns) VALUES(?'s). To workaround this problem, values must either not have spaces or the database logging feature needs to specify string columns as "VARCHAR" instead of "BSTR". You should see BSTR vs VARCHAR support in a future release of TestStand. In the mean time the database logging code can be changed and recompiled. The change is simple in

/Components/NI/Models/TestStandModels/Database/DBLog/Columns.cpp in the function CDbColumn::Initialize, the lines

case tsDBOptionsColumnType_Bstr:
m_AdoType = adBStr;
break;

can be changed to:

case tsDBOptionsColumnType_Bstr:
m_AdoType = adVarChar;
break;

See attached DLL as a workaround to this behavior in the ODBC driver.

3) The MySQL ODBC driver returns the following error while logging results to database when you specify column data type as "GUID":

[MySQL][ODBC 3.51 Driver][mysqld-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 '!G��k ��r�,'COMPUTERNAME',
NULL,-1,NULL,'usename','20040505153955',

To workaround this limitation, specify string columns as "String (VARCHAR)" instead of "GUID".

Hope this helps...
Scott Richardson (NI)

[bruno_p]

Thank you for your help !

The second part of your answer is the solution of my problem.

And thank you for the file attached: I tried to compile the DBLog project but there were too many errors...

Bruno

[Calvin Broadhurst]

Scott,
The modified DBLog.dll solved my problem also when using mysql ODBC, however latest TestStand 3.1f1 patch which fixes some of the problems with database logging does not include this modification.
I do not have access to a development environment to re-compile the latest DBLog.dll with this modification made to it. Is there any chance that an updated version could be posted here?
It would be really nice if NI could perhaps include this as a configurable option in the teststand logging configuration as MySQL is becomming more and more popular.

Cheers...
...Calvin

[Scott_Richardson]

Calvin -
The fixes in the 3.0 DLL that I attached to this posting should be in TestStand 3.1. TestStand 3.1 released in July of 2004.

[bruno_p]

Hi,

I use TestStand 3.1f1 now.
The MySQL dbms and a "MySQL Insert" schema are available from the TestStand database options. And the dll included in TestStand works without any patch.
But be careful about the type of the strings if you use a custom database schema (You should use VarChar).
You could try the default MySQL schema from NI and execute any example sequence to be sure that you can log your results to a MySQL database.
It is now easy to use MySQL with TestStand.

Thank you NI to have included MySQL support in TestStand.

Message Edité par bruno_p le 05-02-2005 04:33 PM

[Calvin Broadhurst]

Scott,
Thanks, I discovered after a little more investigation that the problem was that I was doing something silly which was causing things to fall over.

I do have one more question about on the fly database logging with TestStand 3.1f1

My question is:
Is there any way that I can store (to say "StationGlobals.LastDBGUID") the GUID which the teststand database logging module generates for a step result?
I have had a look at the code for this module and see that it could, theoretically, be possible to modify it to do this, however I get a whole stream of errors when I try to compile this code (without making any changes)

Why would I want to do this?
We are using a custom database schema which is quite different to the one supplied with TestStand. The reason for this is the type of testing we are doing is functional and performance testing during the development phase of our products, in this phase we are usually only testing one or two UUT but we have numerous test suites which can be made up of several hundred test cases. Both the test cases themselves and which ones are included in a test suite can change often so we needed extra tables to keep track of this. Also our developers wanted to have the ability to record conditions (temperature, supply voltage etc) under which certain measurements were made, with these conditions linked to the measurement record itself. We are using GUID's as the keys for the database entries.
Implementing the test suite/test case side of things and having the test cases linked to test suites has not been too difficult. It is also straight forward to make a measurement and link it to the appropriate test case... however, the problem I have is that when I use the TestStand database logging module to generate the GUID to use as the key for the measurement record I can not then store that GUID anywhere to use as the foreign key in a later step (the recording of a measurement condition). For recording the start/end and status of test suites and test cases I am using custom step types which have a DLL call that generates the GUID and stores it in station globals as the current testsuite/testcase and this is then used as the GUID for the database entries.
I hope that makes some sort of sense... unfortunately I do not see a way to attach images to posts in here otherwise I would post a picture of the database schema we are trying to use. Below I have tried to re-create it in text.

UUT Condition
|* |*
1 * |1 1 * |1
TestSuite --- TestCase --- TestMeasurement
|1 |1
|* |*
Instrument Limits

(The Instrument and UUT tables are used to provide tracability and reproducability for the test results)

Thanks,
Calvin

[Calvin Broadhurst]

oops...
looks like the text 'picture' of the database schema failed...
will try attaching it as a jpg

[Caroline Bright]

Hi Calvin,

Unfortunately there is no real way to get the GUID value. What I would suggest though is to create your own GUID. You can then change the primary key to "Use Expression". So you can create your own GUID and increment it as needed, store it in a station global, and then use that station gloabl as the expression for the primary key.

I hope this helps.

Thanks,
Caroline
National Instruments

[Calvin Broadhurst]

>Unfortunately there is no real way to get the GUID value.
>What I would suggest though is to create your own GUID.
>You can then change the primary key to "Use Expression".

Yes, for the GUID's for the test suite and test execution tables I am using a custom step type which makes a DLL call to create the GUID and store it to SG's. The database logging statements for those steps are then configured to use an expression which inserts these values from SG's into the primary/foreign keys. Problem is that I can not do this for a normal measurement step because the TestStand database logging module does not allow for calling other code...
The only two ways I can see to do this would be:
1/ To modify EVERY step type to include a DLL call pre-step which, while it would work, would complicate things and negate the relative simplicity and flexability of TestStand which is why me like TestStand so much.
2/ Adding another step to specifically generate a GUID before any measurements i.e.
...
Create GUID
Numeric Limit Test
Create GUID
Pass/Fail Test
...

What I would really like to be able to do is have the database logging module (DBLog.DLL) store the GUID which it generates (or at least return it so the sequence which calls it (Database.seq) can be modified to either store or discard this value). I have looked at the code and seen that this is theoretically possible to do with the addition of only a few lines of code however I can not get the code for this module to compile (without making any mods to it)

Any help would be very much appreciated.

Calvin

>So you can create your own GUID and increment it as needed,
>store it in a station global, and then use that station
>gloabl as the expression for the primary key.

Increment it??? unfortunately (or fortunately depending on how you look at it) GUID's are not quite that simple. It would be very dangerous to generate a GUID and then just increment it, a new GUID needs to be generated each time