Creating database

[PeggyC]

I am reading the following tutorial document

Creating a TestStand Database Schema from Scratch 

http://zone.ni.com/devzone/cda/tut/p/id/6484#toc4

And, in creating the database, it says to use the Database Viewer under Tools>>Database Viewer.  I want to know if there is a programmatical way that I can create the database dynamically?  say, after each test execution?

Thanks.

Peggy

[Dennis_Knutson]

Of course there is but what is the point of creating a new database each time? Why would you want your test results in separate databases? That sort of goes against the whole concept of a database.

[PeggyC]

Can you tell me how?

One idea go behind is because I am using SQL 2008 Express, which it says it has limit to 2GB of database size.  For one, I don't know how to check the database size and don't know how big of my next entry would be, so I figure it is easier to have separate database per execution.  In this way, I know for sure, SQL 2008 Express is able to handle.  Second, the target system has no separate database server.  SQL Express is running on the same machine (of at most 2GB of RAM) with other applications in parallel.  So, if each each database stores only one execution result, the size will be smaller, and when updating the database, it will be less memory intensive.

[Dennis_Knutson]

It's 4GB. You will probably not reach that limit for quite a long while. I don't have it installed here but checking the size is pretty simple and should be explained in the help. The size of the database should not matter much when writing to the database if you have designed the schema correctly. The database is not stored in memory after all.

Are you talking about creating a completely new database or new tables? For new tables, you simply use the build in database write function to do the CREATE TABLE command.

[PeggyC]

Thanks.

I don't know where I got that 2GB from.  But 4GB is great.

I am thinking to create a brand new database. and then populate with the tables. 

[Dennis_Knutson]

Creating a whole new database is different and I don't know how you would go about that. It is not supported by SQL statements so you would probably need to use ActiveX.

Before you take this path, determine how much a single run of the sequence requires. Determine how many times/day/week/month the sequence will be run. From that, you should be able to determine how much time you have before you need to archive old data. Consider also how much you really need to store. The defaults schemas from NI may include tables or columns that you don't care about.

[PeggyC]

Okay.  Thanks.

For creating the database, I am looking at the "Test UUTs" entry point sequence in the default sequence process model file.  I am thinking I can create a subsequence step (Create New Database) right before the call of "New UUT for Database Logging" test step. 

In that "Create New Database" subsequence, I use the function "Open Database", "Close Database", and "Open SQL Statement" to write the CREATE TABLE statement?!

Does that sound right?!

I am using SQL 2008 Express.  Do you know where I can find information on using TestStand to call SQL Server Management Studio functions?  ie. create new database.

Thanks.

[PeggyC]

You mentioned schema, and this is really confusing to me.  I am browsing this discussion forum, and I found the following discussion

"How do I select a database schema to use for a sequence file?"

http://forums.ni.com/ni/board/message?board.id=330&message.id=21712&requireLogin=False

What is DatabaseOptions?  And where I can find more information on it?

For my application, there will be supporting multiple different tests for different UUTs.  So, the idea is that the system will have at least two different databases.  One for system, the other one for each individual UUTs.  For the execution history, they will be recorded into system database (could I use schema to record the information?), and the other is simply recording execution results.  Originally, I wanted to have separate database for each execution, because it just makes management easier.  But, I am new to both TestStand and also this database server.  I am kind of running around, not sure exactly how to implement it.

[Dennis_Knutson]

I'm just getting more confused. Typically, you have a single database with multiple tables. The schema is the database design - what tables there are, what columns are in each, the data types of the columns, the linking of the tables.

The default schemas have a 'top-level' table called UUT_Results (overall result/date_time,erial number, etc.). I'm guessing this would be similar to your system table/database. A common query might be for yield information. You could query this table to check how many units passed and how many units failed during a specific time span. The standard schema adds a STEP_RESULTS table linked to the UUT_RESULTS. Another common query would be to find out which tests failed the most times. Because the tables are linked, you could do a query where you ask for which steps failed for specific units and for some date/time range. Both of these queries are typical of what a quality department might use for process improvement. Additional standard tables would include measurement values so that you could tabulate all measurements for a specific test for a specific UUT. This could allow you to look at the actual results and compare to the limits. Are the measurements stable, centered in the limits window, etc.

The links or relationships between tables in a single database make for a powerful tool and eases the writing of standard queries. You may have completely different plans for the data and I have no idea what kind of process control you want to implement. Discussing this with the intended end users and an experienced data base administrator is really what is going to drive the design of your database.

[PeggyC]

I have the table designed.  The problem is I am more use to use standard SQL read/write statement to log the data.  For example, if I am writing the result log, I will code the following (assume on-the-fly recording)

1. when test execution starts, I open the database.  Initialize the result variable to initial value

         (database.open)

2. prompt user for UUT information.  Save that to the UUT Information variable.

          (<update execution history statement>.update)

          (<update execution history statement>!<table field> = <field value>)

          (<update execution history statement>.done)

3. start test execution

4. for each test step executed

5.    save the result into result variable

6.    write result variable into the database by commit statement the update

          (<update result statement>.update)

          (<update result statement>!<table field> = <field value>)

          (<update result statement>.done)

7. execute next test step (go to step 4)

8. test execution finished

9. write test execution result into database

          (<update uut statement>.update)

          (<update uut statement>!<table field> = <field value>)

          (<update uut statement>.done)

10. execution done

         (database.close)

For each of the <xxx statement>, I assume would be corresponding to the statement for each of the schema.  But, it is unclear to me how I use TestStand to achieve the same thing.