Using parameterized statements - VALUES(?,?,?)

[vasilli]

What are the rules for using a parameterized INSERT statement such as:

INSERT INTO table (col1, col2, col3) VALUES(?,?,?);

I'm having problems using this w/ an auto-incremented ID field. I have created a new MySQL schema based on the following table:

CREATE TABLE UUT (
    ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    SERIAL_NUMBER VARCHAR(21) NOT NULL,
    PRIMARY KEY (ID),
    KEY IDX_UUT1(ID)
);

In DataBase options I have created a statement UUT with the following command:

"INSERT INTO UUT (ID, SERIAL_NUMBER) VALUES (?,?)"

The problem is the first parameter, which an auto-incremented integer. TestStand gets some kind of a 6 digit number from MySQL and inserts that as the ID. This only works once as the second INSERT returns the same number. I know that the problem is the parameterized list. The question marks represent parameters, as far as I understand, which are evaluated for each column.

Ideally I would like to just skip inserting the ID (as it's going to be inserted automatically by the DB anyway). So, this statement WORKS:

"INSERT INTO UUT (SERIAL_NUMBER) VALUES (123)"

But as soon as I used the question mark...

"INSERT INTO UUT (SERIAL_NUMBER) VALUES (?)"

... it DOESN'T work. It seems that the number of question marks has to be exactly the same as the number of columns/parameters.

How can I avoid using the first parameter at all? Any other solution for my auto-increment problem? What is the full syntax for these unfriendly question-mark operators?

[Adam B.]

Hi Vasilli,

Although most of the information in this thread is from 2.0, there is a little tidbit that discusses why you can't use ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT line to create the ID tag:

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 can see this behavior is you look at the default MySQL database logging.  You can see the SQL statement for creating the default tables in C:\Program Files\National Instruments\TestStand 3.5\Components\NI\Models\TestStandModels\Database.  Since TestStand is taking care of the auto-incrementing, the field itself does not have to be auto-incrementing, it just needs to be a primary key.  If you get to this point, you should be able to utilize the MySQL Insert Schema to guide you in your development.  However, please let me know if you have additional questions.  Thanks and have a good one.