TestStand database schema, Command Text

[Rafi2003]

Hi,
I made a copy of Generic Insert schema and began to modify it.

I deleted 2 fields from the UUT_RESULT table, and also from the table in my database. When I check the 'Validate' I get an error "Number fo Columns in the SQL text for statement UUT_RESULT does not match number of columns/parameters defined in schema".

I modified the text in the 'Command Text' to reflect the change that I had made, but it is recreated either when executing 'Validate' or when exit and re-entering the database configuration.

1) How to modify the schema to remove the fields?
2) Why is this schema uses INSERT where the 'Generic Recordset' uses SELECT?

3) The schemas provided by NI can not be modified. ONly their copy can. Also, the original sc
hemas do not have any text in the Commnad Text. Can you explain that please?

Thanks
Rafi

[Scott_Richardson]

Rafi -
1) The SQL statement must match the number of columns specified. If you delete a column from the Columns tab, you must alter the SQL statement by deleting the corresponding field name from the text and you must remove the "?" from the VALUES() section of the text.

2) Most SQL databases support both SELECT and INSERT statements. A SELECT statement opens the table(s) and allows the client to fetch and create new records. This is a lower performance method to access a database. An INSERT statement is only allowed to create new records and typically has better performance. TestStand offers both ways as an example to end-users.

3) We attempt to protect the NI schemas so that when a new release comes out and NI updates the schema, TestStand does
not overwrite any edits that you could have made to an NI schema.

Regarding the text not showing up for the Command Text control when dimmed, TestStand 3.0 had this behavior. TestStand 3.1 does not exhibit this behavior. For TestStand 3.0, all you have do it either copy the schema and look at the editable copy or temporarily check the Allow Editing of Schema on the Schemas tab and you can look at an NI schema.

Scott Richardson (NI)

[Rafi2003]

Hello Scott,

Thanks for your attention.

After deleting the column from the Column tab, I did altered the SQL statement in the 'Command Text'.

I did exactly that, before I issued my original queston. As I said before it didn't work. The original text returned!!

I followed you instruction to delete the '?' from the VALUES() and still it makes no difference!!!
I tried deleting only 2 '?' as the number of fields I'm deleting. I tried to delete all of them. I even deleted the entire VALUES(...) section. Still everything comes back to its original form.

By the Way--what is the purpose of the '?' in this statement?

I don't know it it is important but I'll describe how I built the new tables in my data base:
1) I copied an old data base and de
leted all the tables.
2) I used the Database Viewer, called the new database and then executed NI SQL called: 'Access Create Generic Insert Result Tables.sql'
3) In TestSTand Database Options I copied NI Generic Insert schema and renamed it. I modified the DataLink to look at the new table (it was working before with another tables) and then began to modify the UUT_RESULT table by deleting two fields.

Any suggestions?

Thanks
Rafi
TS 3.0+CVI7.0

[AllenP]

Hi Rafi,

The ? is a placeholder that TestStand fills in later. A typical INSERT statement in SQL would normally look like "INSERT INTO UUT_RESULT (COL_A, COL_B, COL_C) VALUES ('a', 1, 'abc')". TestStand looks for and replaces those question marks with the appropriate data values. The number of question marks must correspond to the number of columns specified in the first part of the statement.

It is really strange that your data is getting overwritten even after you make the change. The machine I am working on now only has TestStand 3.1, but I am going to attempt to reproduce this as well. The one thing you will have to do that I don't think you are doing is modifying the actual tables in the database. If you are removing two columns from th
e INSERT statement, be sure to delete those same two columns in the database. You can do this through the Database View. It may give problems if you are not inserting enough tables. In fact, since TestStand should know about this data link, it may be reloading the column names here. I will have to confirm on another PC though.

Allen P.
National Instruments

[Scott_Richardson]

Rafi -
I had forgotten about a TestStand 3.0 bug in the expression control used on the Database dialog where it fails to commit the value when edited on the dialog displayed by the f(x) button. You can either directly edit the text in the edit box on the dialog or after using the f(x), make a small change in the control after returning from the f(x) dialog. This problem occurs on the Database logging dialog box and the IVI step types dialog box because there are written with VB6. This problem does not occur in TestStand 3.1.

The ? character represents a parameter for "parameterized SQL statements". The values that the columns/parameters tab defines correspond to each ? character in the SQL command.

Another issue with TestStand 3.
0 is the following:
Why Do I Get Database Logging Errors While Using Parallel or Batch Model in TestStand 3.0?

Scott Richardson (NI)

[Rafi2003]

Hello Scott and thanks again,

I have just repeated the whole sequence.
--I created new database - no errors
--I deleted fews tabels (all the IVI.. from schema and database) - no errors
--I deleted 2 fields from table UUT_RESULT, modified the SQL statement, deleted them from the database - Got the same error/behaviour. Somehow, TS overwrite the fields of the original table.

I'm looking forward to hear from you

Thanks
Rafi

[Rafi2003]

Hello Scott,

BINGO!!

Thank you very much. I can now get going on the important stuff...

Thanks
Rafi