Logging to a MySQL database

[vasilli]

I have searched this topic and haven't found any clear answers.

Context:

I'm running TestStand 3.5, MySQL 4.1, WinXP. My task is to log some measurements from an existing TS script into a MySQL database (that I have already created). There are about 4-5 different record types that I need to add throughout the execution.

I have figured out how to create a schema for my DB and I can log simple results. The problem is I have NO IDEA how it actually writes to the database. I've looked through the sequence files and the closest thing I found is the LogToDatabase callback. But even that function is not very straightforward. I don't see where it takes the results and which table it directs them to. I would like to understand how TS gets my results, uses the schema, and puts it all together to write to the DB. Is the whole schema activated/used for EACH write operation? How does it know which table and which record to write to?

Ideally I need to write my UUT info to one table then 4 different types of measurements to 4 different tables (~200 of each measurement type)... all at various times throughout the execution. I don't understand how to control the schema to do this!

I've noticed a step type "Open SQL statement" and "Data operation". Can I use this functionality INSTEAD of the schemas? (and how?) Or WITH the schema? (and how?)

Any clarification that I could get about the DB logging process would be greately appreciated.

tia

Vasilli Ryjikh

vasilli@hotmail.com

[Patrick_P.]

Hey Vasilli,

In TestStand if you are wanting to log results, using schemas is the recommended way to do it.  The Database steps do exist, but those are usually used when you want to retrieve data from a database, and they are a little more difficult to use than schemas.  The way a schema works is it determines whether or not properties should be logged to the database and then in the LogToDatabase Callback you mentioned it creates a Logging property with all the values that might need to be logged.  This is then passed to an activex control that does all the logging for us.  Probably the best way to view this is to actually set a breakpoint and step into the LogToDatabase Callback which will actually call Database.seq.  From here you can see this Logging property being generated.

The general steps you would go through to create a schema to log values to a database would be to create a copy of the schema and then modify it for your specific database.  Within the database options there are two main tabs you will be making changes to.  The first one to configure is the Statements tab.  This tab specifies which table you want to store your data to.  There are three ways to tell TestStand to log data to the database.  You can specify to log data for specific step types, specific properties that exist, or if a certain precondition is met.  All three of these are configured from the statements tab.

The next tab you will have to configure is the Columns/Parameters tab.  This determines which columns you want your data to be stored within the database.  Again it has the three methods to tell TestStand to log your data or not:  specific step types, expected properties, and preconditions. 

All this aside, what I would recommend to do is create a copy of the MySQL insert schema, and add 4 statements for each of your types of measurements.  The easiest way to write these to the database would probably be to create a custom step type that stores those measurements into a property.  You could then just set up a statement that would write to the database for each of those step types.  Then you would just log the property that you created.

Message Edited by Patrick P. on 02-23-2006 12:16 PM

[vasilli]

Thank you for an elaborate answer, Patrick. I understand (and have done) most of what you have mentioned. Could you please elaborate on this part?

---------

The easiest way to write these to the database would probably be to create a custom step type that stores those measurements into a property.  You could then just set up a statement that would write to the database for each of those step types.  Then you would just log the property that you created.

---------

I'd like to know how to create this custom step type, how I would get my results into the 'properties' and how to associate this new step type w/ the schema.

Once again, thank you.

[Patrick_P.]

Hey Vasilli,

To create a custom step type, you need to open the type palette and select MyTypes.ini from the ring control.  Then you can right click and select to insert a step type.  Once you have added this to your mytypes.ini and saved it, you should be able to go back to the database options and go to either the statements tab or the columns/parameters tab and select your new type as the types to log.  What this will do is for every step of this type; data will be logged to the database.  In your case, you could just create four different step types for each measurement.  Then in your statements tab, you would have four different entries there each representing the different tables you want to write to in your database.

In addition to creating a custom step type, you will probably want to add a field to store your measurements in.  This new field should be added under the Results container.  The reason for this is that all fields underneath the Results container are passed as parameters to the LogToDataBase callback, and more specifically they are added to the logging property I mentioned before.  Once you have added the property underneath the Results container, you will need to go back to the columns/parameters tab and add your field into the expression text box so that it is logged to the database.  For example, if you added an array called MyArray underneath the results container, you would put Logging.StepResult.MyArray as the expression on the columns/parameters tab.  Hope this helps out!
 

Message Edited by Patrick P. on 02-23-2006 05:06 PM

[vasilli]

Thank you, Patrick. This most definetely makes things much more clear. I will try this.

[vasilli]

Patrick,

I have tried your suggested method. I have created a custom step type and inserted it into my sequence. I have associated the step type with a statement in the schema (apply to: Step Result, types to log: Step_LogUUT).

Running this new custom stop type does NOT write to the database. I have tried on-the-fly and regular database logging options. This step does absolutely nothing. I must be doing something wrong. What I'm trying to do is to get some information from the user (in the form of a dialog box) and write that info immediately into that database. I would really appreciate further assistance.

[Patrick_P.]

Hey Vasilli,

Did you make sure to also have an entry in the columns/parameter tab for where you log your data?  You need to make sure that the property you want to log has an entry in the columns/parameters tab, and you have put the property you want to Log in the expression of that column.  As I listed before you need to find the Logging property which corresponds to the property you want to log and enter that as the expression. 

One thing you can try to see if the logging is working correctly is set the types to log on the Step_Result Statement in the Statements tab.  This should then log Step_Result data just for your specific step type.