logging containers and arrays to database

[shaharl3]

Hi,

I am trying to log custom data types to the access database in order to programmatically retrieve back the data and use it. 

In oppose to the NI types of types string, boolean etc. which I can easily retrieve back from the database tables, I am not able to do the same with the 

custom data types since they are of types array of containers, array of strings and mainly other types of array. 

• How do I Iog a container to the database in a way that I am able to get each element of the container in a separate cell, so I could easily retrieve the data back?
• How do I log an array? From what I understand, TestStand stores arrays in the database after converting the data to a binary format. In this way I cannot retrieve the data back from the database and use it. When I tried to so, I could only view the arrays in the PROP_BINARY table, but not where each element appears in a separate cell. Therefore I could not retrieve the data. The only way in which I could log the elements of the array to database is by adding all the indexed elements of the array as additional results and then retreive the ir values from the PROP_RESULT table, under the DATA column (except the arrays of containers. I did not figure this out yet)
• Should I write a new schema? should I use a loop for the array? I have been working on this for weeks with very little success. 

any help would be much appreciated!

thank you in advance,

Shahar Levi

[Stephan_D]

Hey Shahar,

maybe we should first take a look at the abilities of a Microsoft Access Database or databases in general. It is easy to store a data type like a number, a string or a boolean in a database, because the database offers this data types by itself. You will find all supported data types for a Microsoft Access DataBase on the following website:

Microsoft Access Data Types
http://msdn.microsoft.com/en-us/library/windows/desktop/ms714540(v=vs.85).aspx

As you see, Access offers a variety of data types like BIT for a boolean value or LONG for a numeric value. A database doesn't offer the ability to store a container or a array of values by an integrated data type. If you want to save data like this, you have to save the data as BINARY information. If you use BINARY, it is not so easy to access a single value. If you want to get one peace of information, you have to read the whole binary information and extract only the desired information.

• How do I Iog a container to the database in a way that I am able to get each element of the container in a separate cell, so I could easily retrieve the data back?
  -> Save every element of the container as seperate database entry. E.g a Container like (UUT number, UUT Status, Limit) must be saved as 3 seperate database columns
• How do I log an array?
  -> Same like the first question. It is not possible to save an array of values in one field of a database, because a database doesn't provide an array data type. If you want to store an array of values, you can save every value a seperate field in the database.

Regards, Stephan

[Daniel-E]

Hey Shahar,

I've done some research into this, and I have a few ideas of how we can accomplish the things you've asked about. 

1) Logging Containers: To test this, I created a custom data type MyTestContainer, which is a container of a number and a string. My test sequence assigned values to the number and string in the container, and then logged this container to the database with an AdditionalResults step. In that step, I simply specified the result as "Locals.MyTestContainer". I then examined the database and found that the number and string were logged as separate records in my database, and I could easily pull out either of the container elements with a database operation. For example, I have a database operation which pulls MyTestContainer.MyNumber out, and displays it in a MessagePopup.

Is this similar to what you were trying to do? If you're seeing different behavior, would it be possible to see a screenshot of how the data is being logged to the database in your instance?

2) Logging Arrays: As you mentioned, TestStand's default behavior is to log arrays as binary data, which is viewable in the TestStand Database Viewer. Arrays will be logged this way by default, whether they are logged as a measurement from a code module or with the Additional Results step type.

You're correct that this cannot be pulled directly back into TestStand. This is because TestStand's database operation steps only cover simple data types such as numbers, strings and Boolean values, and cannot handle binary data. You could, however, write a code module (in LabVIEW, C, or a similar language) which would parse this binary data from the PROP_BINARY table and return it to TestStand.

While it is recommended to log arrays as a binary value in this way, if you do want to log each element of the array as a separate record, you can certainly do so. I wrote some test code which uses a For Loop to accomplish this. It iterates a set number of times according to the size of your array, and calls an Additional Result step to log each element of this array to the database. You can view the elements of the array in the PROP_RESULT table, and can pull out an individual element as desired. I'm going to attach that code to this message.

3) New Schema: At this point, I don't necessarily think a new schema is necessary--that would be dependent on your overall database logging needs and what types of results you want in each table. If you do want to change the way results are logged, creating a new schema would allow you to specify which types of results go to each table, which could make more sense for your system depending on what information you need to access later.

I hope this helps clear things up a bit, and if you do have any further questions about it, definitely let us know! 

[shaharl3]

Thank you Daniel,

I have managed to overcome the problem by using additional results and log each element in a seperate cell using for-loop, as you suggested. 

thanks again,

Shahar Levi

[Vekthoar]

Hi All,

Can anyone show me how can I retrieve the arrays from the PROP_BINARY table? I am using the database connectivity toolkit, and the database variant to data function does not seem to work for the Data field, altough there must be a reason that the array bound and type is also stored.

Thanks