How to add support for mySQL in TS?

[deshollinador]

Hello,

I am trying to send result data to a mySQL server and I am getting the following runtime error:

------------------
"An error occurred in the 'Put UUT Result Info' step of the 'Log UUT Results' sequence in 'Database.seq'.
Error executing substep 'Post'. Native error code -2147467259 0x80004005
Microsoft OLE DB Provider for ODBC Drivers:
[Microsoft][ODBC Driver Manager] Invalid string or buffer length
User-defined error code. Error Code: -10"
------------------

What do I have to modify to support this DBMS?

Following are the features of my development station:

TestStand 1.0.2
MS Windows 98
mySQL 3.23.38
mySQL ODBC driver 2.50.37.00

I set "Access" as the DBMS in TS because mySQL is not supported. I use Ac
cess because mySQL has a data modifier named "AUTO_INCREMENT" that, I guess, is similar to the "COUNTER" type in Access.

Thanks in advance for your help.

Sergio Cervantes

[KittF]

Someone reported the following information to me, but I have not tried it myself. I believe the cause of the problem was related to auto generated database IDs. I believe the MySQL inserts the autogenerated ID into the database at an atypical time relative to other DBMSs that auto generate IDs.

---------------------------------------------
I installed a Windows version of MySQL and attempted to use their ODBC driver or OLE-DB Provider with TestStand. I found the following:

1) The ODBC driver and OLE-DB Provider allowed me to log data to their database using TestStand when using INSERT type schema with GUID primary keys.
2) The ODBC driver and OLE-DB Provider returned odd errors if I attempted to use recordset type schema with AUTO_INCREMENT primary keys.
3) T
he ODBC driver would not allow me to view the table contents from within the TestStand database viewer application.
4) The OLE-DB provider did work and allowed me to view the table contents from within the TestStand database viewer application.

[Scott_Richardson]

Sergio -
When I played with MySQL and TestStand 2.0, I did not have much success with the MySQL ODBC driver and TestStand. Instead I used the MyOLEDB Provider v3.0 and had better luck.

Keep in mind that in TestStand 2.0, the database logging feature is fully customizable using a configuration dialog. TestStand 1.0.3 used a sequence and this turned out more work and slower. I would recommend either upgrading to TestStand 2.0 or at least looking at an evaluation version to review its database logging feature. I think that you will find it a lot nicer and customizable.

If I remember correctly when using TS 2.0 and its database logging feature, using a SELECT command and using a primary key of type INTEGER AUTO_INCREMENT, the field did not return a value immediately so setting up a relationship between the UUT table and the STEP table was not possible, even when specifying a server cursor.

Instead I used a VARCHAR primary key and used INSERT commands by specifying a GUID as the key value. Below is an old file that I found that I might have used.

You may find that you have to limit the type of data that you write out and create a simpler schema.

In TestStand 2.0, the DBMS string is arbitrary, the schema definition does not use this string value to make any decisions during logging.

Scott Richardson (NI)

// SQL commands to create the RESULT tables in an SQL Server database.
// Keys for each table are 32 character GUIDs

CREATE TABLE UUT_RESULT
(
ID VARCHAR (38) PRIMARY KEY,
STATION_ID CHAR (255),
BATCH_SERIAL_NUMBER CHAR (255),
TEST_SOCKET_INDEX INT,
UUT_SERIAL_NUMBER CHAR (255),
USER_LOGIN_NAME CHAR (255),
START_DATE_TIME DATETIME,
EXECUTION_TIME FLOAT,
UUT_STATUS CHAR (255),
UUT_ERROR_CODE INT,
UUT_ERROR_MESSAGE CHAR (255)
)
~
CREATE TABLE STEP_RESULT
(
ID VARCHAR (38) PRIMARY KEY,
UUT_RESULT VARCHAR (38) NOT NULL,
STEP_PARENT VARCHAR (38) ,
STEP_NAME CHAR (255),
STEP_TYPE CHAR (255),
STATUS CHAR (255),
REPORT_TEXT CHAR (255),
ERROR_CODE INT,
ERROR_MESSAGE CHAR (255),
MODULE_TIME FLOAT,
TOTAL_TIME FLOAT,
NUM_LOOPS INT,
NUM_PASSED INT,
NUM_FAILED INT,
ENDING_LOOP_INDEX INT,
LOOP_INDEX INT,
INTERACTIVE_EXENUM INT,
STEP_GROUP CHAR (30),
STEP_INDEX INT,
ORDER_NUMBER INT,
CONSTRAINT UUT_RESULT_FK FOREIGN KEY (UUT_RESULT) REFERENCES UUT_RESULT (ID)
)
~
CREATE TABLE STEP_PASSFAIL
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
PASS_FAIL TINYINT,
CONSTRAINT STEP_PASSFAIL_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE STEP_STRINGVALUE
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
COMP_OPERATOR CHAR (30),
STRING_LIMIT CHAR (255),
STRING_VALUE CHAR (255),
CONSTRAINT STEP_STRINGVALUE_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE STEP_MSGPOPUP
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
BUTTON_PRESSED INT,
RESPONSE CHAR (255),
CONSTRAINT STEP_MSGPOPUP_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE STEP_CALLEXE
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
EXIT_CODE INT,
CONSTRAINT STEP_CALLEXE_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE STEP_SEQCALL
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
SEQUENCE_NAME CHAR (255),
SEQUENCE_FILE_PATH CHAR (255),
CONSTRAINT STEP_SEQCALL_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE STEP_PROPERTYLOADER
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
NUM_PROP_READ INT,
NUM_PROP_APPLIED INT,
CONSTRAINT STEP_PROPERTYLOADER_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE MEAS_NUMERICLIMIT
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
NAME CHAR (255),
COMP_OPERATOR CHAR (30),
HIGH_LIMIT FLOAT,
LOW_LIMIT FLOAT,
UNITS CHAR (255),
DATA FLOAT,
STATUS CHAR (255),
CONSTRAINT MEAS_NUMERICLIMIT_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE MEAS_IVI_SINGLEPOINT
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
TYPE INT,
CHANNEL CHAR (255),
DATA FLOAT,
CONSTRAINT MEAS_IVI_SINGLEPOINT_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE MEAS_IVI_WAVE
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
TYPE INT,
CHANNEL CHAR (255),
INITIALX FLOAT,
DELTAX FLOAT,
DATA LONGBLOB,
CONSTRAINT MEAS_IVI_WAVE_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~
CREATE TABLE MEAS_IVI_WAVEPAIR
(
ID VARCHAR (38) PRIMARY KEY,
STEP_RESULT VARCHAR (38) NOT NULL,
TYPE INT,
CHANNEL CHAR (255),
INITIALX FLOAT,
DELTAX FLOAT,
DATA0 LONGBLOB,
DATA1 LONGBLOB,
CONSTRAINT MEAS_IVI_WAVEPAIR_FK FOREIGN KEY (STEP_RESULT) REFERENCES STEP_RESULT (ID)
)
~

[deshollinador]

Thanks, Nemo & Scott for your quick replies.

Sorry, I have some other questions:

What is a GUID?

Where can I get the myOLEDB provider (v3.0 or +)?

As a way to solve the problem I tried to recompile the TSDBComponents.prj (that comes with TS) to see how data is sent to the server. I could not do it because two files are missing: cvi_db.h and cvi_db.lib. Where could I get them?

Is there any other way to "spy" the SQL query that is sent from TS to the DBMS?

Thanks,
Sergio Cervantes

[Scott_Richardson]

The files cvi_db.h and cvi_db.lib come with the CVI SQL Toolkit from NI. I believe the Toolkit is on the TestStand CD.

Under Downloads/Tools for Win32 section of their web site, you can find the OLEDB Provider,
http://www.mysql.com/downloads/os-win32.html

I am not aware of what type of debugging tools MySQL or OLE-DB has.

Scott Richardson (NI)

[Calvin Broadhurst]

Scott,
Thanks very much for posting the sql needed to create the tables for TestStand in mySQL... that has solved one of my problems very nicely.
However, see you mention that you have had some success using the MyOleDB provider... can you please enlighten me a little as to how you managed to use this.
I downloaded and installed MyOleDb 3.0 but after setting up the data link if I try to view data it pops up a dialog to configure the data link, Clicking on Ok results in an error "Unspecified Error(-2147467259)"
The connection string expression I am using is
"Provider=MySqlProv;Data Source=testdb;Password=test;User ID=test_user;Location=ws1730"

Thanks
Calvin

[Adam B.]

Calvin,

As you can see the previous posts on this thread are from a while ago and pertain to a different version of TestStand. You should be able to go to the Data Link tab, select MySQL as the Database Management System and then build the Data Link String using the Build button, selecting the driver, and specifying the rest of the information. In addition, you should already have MySQL statements to create the correct tables for the MySQL schema. They are located at C:\Program Files\National Instruments\TestStand 3.1\Components\NI\Models\TestStandModels\Database. This should give you a little better direction, but if you have any other questions, please post back and we'll make sure you get all the answers you need. Have a good one.

Adam B.
Applications Engineer
National Instruments