Image data to SQL database

[Corey.Rotunno]

Hello all,

---

SUMMARY OF QUESTION: How can I force a binary string to be logged into PROP_BINARY instead of PROP_RESULT? If I can't, how can I approximate that behavior?

---

  I am investigating ways to log image data to my SQL database. So far things I have done:

1) In LabVIEW - Read jpg file and convert pixmap into image. Stored image in custom SQL table as "Image". Then, again using LV, read binary data from SQL and  displayed as the image. THIS WORKS.

2) In TestStand, logged image data as "binary string" to SQL database. This did not work as TestStand wants to store that directly in the "DATA" column of PROP_RESULT which is varchar(255) which means I only get the first 255 characters.

3) In TestStand created a datatype for NI_Pixmap and then tried to log additional result in that format. This sort of works, but TestStand logs each element of the pixmap datatype as a separate row in PROP_RESULT, with the bulk of the image data stored in PROP_BINARY, which is where I would want it.

So my question is....does anyone have any ideas how I might be able to force the actual imagedata to be stored in a single row of PROP_BINARY directly? Logging the pixmap and re-building the datatype requires many steps. It would be nice to log the image data directly as one BLOB. It seems that if I could get TestStand to put it in PROP_BINARY, DATA instead of PROP_RESULT, DATA that would solve my issue. Does anyone know a way to force a binary string to be stored in the BINARY table vs. the RESULT table?

Thanks,

[Corey.Rotunno]

I want to add a follow up:

I was able to accomplish what I had to do by logging the 24bit unflattened pixmap (2D Array of U32) to the database. By default TestStand saves this to the DATA column of PROP_BINARY which is great. What is not so great is that TestStand saves it as a 2D array of 64 bit numbers. In doing so, it takes twice as much memory as required. Is there a way to get TestStand to actually record it as 32bit numbers? I would rather not do something weird like joining two 32bit numbers.

Thanks,

[saki.BOMB]

Hi Corey,

I found this forum post and one of the reply was to write a LabVIEW code to save the image to the database and then call that VI using TestStand.

How to save binary array result to database

Cheers,

[Corey.Rotunno]

Saki,

  Thanks for your reply. I have implemented the suggestion of using a VI to write the image. However, this is not ideal because I do not know of any good ways of tying the database entry to a certain test execution.  Do you have any suggestions?

FYI - I have successfully used TestStand to write the image, however it is very space inefficient. To do this I wrote the unflattened pixmap (2D array of I32) to the database. However, teststand converts this to an array of 64bit numbers. Being the image is really 24bit/pixel, I am wasting 40bits for every 64bits written to the database.

[saki.BOMB]

Hi Corey,

This might not be the most simple solution, but you can create your own Database Schema and customize it so the Data is only 24bit...

Cheers,

[Scott_Richardson · Accepted solution]

Corey -

The PROP_BINARY statement in the default TestStand schema does not specify a format, so a numeric array will be written out as R8 (64-bit double precision value).

One option is to:

• Duplicate the PROP_BINARY statement, name it PROP_IMAGE, and place it before the PROP_BINARY statement in the schema.
• You will need to configure the PROP_IMAGE statement to identify the image array property in some way. One easy way is to define a custom TestStand data type, "ImageData", that you use for properties that contain the images that you want to log in results. By doing this, you can configure the "Types to Log" setting for the statement to "ImageData", so the PROP_IMAGE will only process result properties of type "ImageData".
• You can do the following to configure the PROP_IMAGE statement to log the data as a 32-bit array:
  • Set the "Format" expression for DATA column to "I4", so that the logger will write the array as 32-bit integers.
  • Set the "Value to Log" expression for DATA_FORMAT column to "I4" so that you log the format of the data in the DATA column.
• Note that the traversing options for the statement already enable "Skip Remaining Property Result Statements for this Property", so the PROP_BINARY statement that is after PROP_IMAGE will be skipped.

[Corey.Rotunno]

Saki,

  I am, or was at least trying to write it to the DATA column of the PROP_BINARY table. That would have been the most convenient place for me to put it to keep it associated with a particular execution. I have all but given up though with that route. I am going down a road similar to what you mention in your post now. I created a table just for image data. However I do not know how to edit teststand for it to be able to include another type of result in the database. Because of this I am passing the image data to a LabVIEW VI with teststand and logging to my custom table in LabVIEW. Its not the cleanest solution but there are always other things to tackle.

Thanks for your reply,

[Corey.Rotunno]

Scott!,

  Thank you that is exactly what I needed! I already went ahead and created a PROP_IMAGE table before I saw your post, but I was missing how to get TestStand to include it in the results for a particular execution. I resorted to a pretty dirty work-around by passing the image data to LabVIEW and let LabVIEW populate that table. The down-side with doing this is that the table entries were not explicitly tied to a particular STEP_RESULT entry. I did the best I could by logging UUT.PartNumber, UUT.SerialNumber, LoginName, and startdatetime.

 I am fortunate to be off until Tuesday, so I will try your solution out when I return.

Thanks again!,

[Scott_Richardson]

Just to clarify that the new PROP_IMAGE statement suggestion above is intended to write the data to the PROP_BINARY table and not to a separate table.

[Corey.Rotunno]

Thanks for the clarification. I am implementing your suggestion now, seems to work great. I think I might modify it a little to populate a new table meant for image data exclusively. That way I can include a few different columns to help describe the image format.

Thanks again,