How do I perform a conditional insert into the TestStand database?

[DRTurner]

Hi everyone!

I have modified the TestStand SQL database to log my specific results after each execution.

I use the generic TestStand NI_DatabaseLogger.seq to log the results to the database. The specific step is the Active X call of ITSDBLog.LogResults.

I am using the Database Options window (Configure->Result Processing) to configure the Statements that write to the schema.

For example, to write to the Test_Release table I use the Command Text:

"INSERT INTO Test_Release (FirmwareRelease,MainFW,CommsFW,UltrasonicsFW,LCDDisplayFW,FairFW) VALUES (?,?,?,?,?,?)"

These values are subsequently defined in the Columns/Parameters tab.

The Test_Release table values generally do not get updated after each execution – including the Primary Key of FirmwareRelease. I want this information to stay the same, and subsequent tables to simply reference the Primary Key that already exists.

The first run is fine, and everything logs correctly. However, when I run my TestStand script for a second time, I get an error because duplicate values would be created as all fields already exist in this table. I don’t want to create a new row, I just want child tables to point to the row that already exists.

Using the default TestStand log to database procedure and the Database Options window and commands, is there a way to have a conditional insert into the database only if all fields in a row don’t already exist?

I’ve tried writing SQL commands like the following, but get errors because of the question marks:

"INSERT INTO Test_Release (FirmwareRelease,MainFW,CommsFW,UltrasonicsFW,LCDDisplayFW,FairFW) SELECT (?,?,?,?,?,?) FROM dual WHERE NOT EXISTS (SELECT * FROM Test_Release WHERE Test_Release.FirmwareRelease = ?)"

Or will I have to write some specific SQL commands and update NI_DatabaseLogger.seq? (Not sure how to do this!)

Any help on conditional inserts into the TestStand database would be greatly appreciated

Regards,

David

[mfaltin]

Hi DRTurner,

Could you give us a bit more info about the Database itself.

How is it structured? 

What exactly are you trying to get out of it?

This will help us to find a way to get this working through TestStand.

-Mitchell Faltin | NI