How can I use a mySQL database schema with numeric auto increment primary key instead of GUID?

[Brosig]

Hello!

I'm using the TestStand "MySQL Insert (NI)" database schema with GUID as primary key. So everything works fine.

But I prever using numeric values as primary key, because the database is in conjunction with another database which uses numeric values as primary key.

Is this possible?

Has anyone an idea how I can modify the "Generic Recordset (NI)" for use with MySQL?

Thanks!

Configuration:

• Microsoft Windows XP
• TestStand 3.1
• MySQL 4.1.12a
• MySQL ODBC 3.51 Driver

Brosig

[Scott_Richardson]

Brosig -
The limitation that I found when designing the MySQL schema was that in order to maintain constraints between tables the TestStand database logging feature needs to be able to get back the primary key(auto incremented number) for a record when created in order to use it as a foreign key in a second record in a different table. For example after creating and committing a record into the TestUUTs table, when immediately reading the primary key field, the value is not available so I cannot use it in the StepResult table.

I thought that I would just try it again, but now I am hitting issues with writing data to VARCHAR fields.  The ODBC driver just does not behave as nicely for recordsets as it does for insert statements.

[Brosig]

Hello Scott!

Thanks for your research.

So I feel confident that it is not possible.

I will take the standard mySQL schema.

Regards

Brosig

[Scott_Richardson]

If I get a chance in the future, I will have to try to debug the MySQL ODBC driver to try to get some insight as to why it does not return the key and why it is failing with the VARCHAR.  Until then, we are limited to the INSERT schema.

[vasilli]

Hi,

I'm having a similar problem and I was wondering if anybody found a solution.

I am using TestStand 3.51 w/ MySQL 4.1 and ODBC 3.51. I have created my own schema (based on the MySQL NI schema). I want to use autogenerated integers as my primary key. It seems to work on the first run, but the second insertion of a record produces the same primary key (integer) as the first. Insertion cannot work b/c the primary key is the same.

If I restart TestStand I can insert one more record. Two in a row won't work.

Any idea why this is happening? How can I get a unique number every time?

Much thanks!

Vasilli

[adamr399]

Hi Scott,

Is it possible to make use of LAST_INSERT_ID() as a workaround for returning the primary key id into the subsequent table that is queried?

Thanks,
Adam

[Scott_Richardson]

Adam -
The TestStand Database Logging feature does not allow you to run a separate SQL command after executing the command for a statement(table), so I do not think that you can use an auto incrementing column for the tables. There is just no way to get it back in a generic way. One option that I tried is something similar to the Oracle schema where you call a store procedure to return a sequence ID for each record that you want to add.

So you would have to create the following sequence table in MySQL:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

Then create a stored procedure as shown below that will increment the sequence value and return it in a recordset:

CREATE PROCEDURE `getseqid`()
BEGIN
        UPDATE sequence SET id=LAST_INSERT_ID(id+1);
        SELECT LAST_INSERT_ID();
END

Then update the MySQL tables to use INT primary and foreign key values, so the TestStand MySQL SQL file to create all tables would have text like this:

CREATE TABLE UUT_RESULT
(
 ID    INT  PRIMARY KEY,
...
)
~
CREATE TABLE STEP_RESULT
(
 ID    INT  PRIMARY KEY,
 UUT_RESULT   INT  NOT NULL,
...
)
~
...

Then update the schema primary and foreign key columns in the TestStand Database Options dialog box to be INT to match the table. For the primary key columns, you will have to set the Primary Key Type to "Get Value from Recordset" and set the Primary Key Command Text to "call getseqid()". This will call the stored procedure to determine the next value to use as the ID value.

Hope this helps...

[adamr399]

Hi Scott,

Yes this does sound like it will work - though I have not yet tried it out.  My only concern here is the length of the signed int data structure is 32768 (I think) in MySQL - which would severley limit the table size - and therefore we'd prefer to use an unsigned bigint.  Would there be any problems with teststand handling this?

My only concern against using the autogenerated GUID (aside from the aesthetic look) is that since it is generated by Teststand I am unsure if there is *any* possibility of it being duplicated in the same database table - that is to ask does Teststand somehow query the database table to check if the value already exists prior to insertion or will the whole process croak when the MySQL database refuses the insertion because the primary key must be unique?


Thanks,
Adam
 

[Scott_Richardson]

Adam -
My choice of INT when I tested it was the wrong datatype if it defaults to a 2 byte number. We get and store the primary key value as a variant value, so whatever we are given from the database, we will use it as the foreign key later. The variant type support 4 byte and even 8 byte integers. Minimally the MySQL ODBC driver will support 4 byte which is 2G values, which should be sufficient.

For GUIDs, we rely on Microsoft functions to generate the unique GUID and we do not ask the database if the GUID already exists. My understanding is that the function will not generate a duplicate value across systems. If the logging feature attempts to log a record with a key that already exists, typically the database will fail the SQL statements being executed. In the case of the default process models, they will raise the error to the sequence and the execution will terminate. I would not expect this to happen.

[adamr399]

Hi Scott,

This answers all of my questions - I will fly with your suggested approach of using a stored procedure and change the data structure to unsigned bigint.

I have seen some posts hinting that the GUID might get duplicated when multiple sequences with the same name are used.  This might be applicable in our setup since we have more than one (physical) teststand running the same test program set and accessing the same tables residing on a single database server.

I appreciate all the help/support you have provided 🙂

Thanks,
Adam