How to store array of 'set of coordinates' into database table

[enthusiast4]

I am struggling to store an array of (x,y) coordinates of a particle/blob in a database by creating a table such that:

Column: represents a particle/blob in AVI

Row: represents a frame number of AVI

Each cell of table: represents a set of (X,Y) coordinates of all the pixels available in a partcular frame.

I have created a table with a single column which represents a single particle/blob of a AVI and rows corresponding to a the number of frame numbers. The LabVIEW doesn't display the array on the table (Array gets converted into Binary datatype which is non displayable), so I even tried to convert the Array(Array in front panel) into string by using Flatten To String.VI. But, even after converting the array into string, still the table doesn't display the 'set of coordinates' as string.

Can someone give me insight into how i can store such 'set of coordinates' in a single cell of database table? Also, how can I extract  a particular row (corresponding to a frame number) from a table since a a row is simply named as 'VALUE' in a table (Check Capture image)

[aputman]

Where are you trying to display the array and what do you mean "Binary datatype which is non displayable"?  I don't see the control that you show in capture.png anywhere in your VI file.  I suspect that you are trying to query this data from your database (although I don't see that code) but you are displaying it as a variant array, rather than a string array.  There is a function in the database toolkit called Database Variant to Data.  Give this a string array constant and it will convert the data for you.

You can also use "array to spreadsheet string" to store the array into the database as a string and then use "spreadsheet string to array" to convert it back to an array when retrieving the data.

[enthusiast4]

I want to display the array/ set of coordinates because  I want to extract the 'set of coordinates' for a particular row at the end. The datatype of the column value is 'Long binary dataype' and this shows as array(non displayable) in database_readtable.VI (attached below). I have already tried using Variant To data.VI by coonecting the string array constant, but it throws an error saying  'datatype incompatible' . Also, I have tried using Variant to Flattened String.VI but then it shows blank(nothing) in the column of table.

I will try with "array to spreadsheet string" and will check if it works.

[aputman]

If you are going to use Array to Spreadsheet String, you'll want to change the column type in the dB to a string format.  Depending on the dB engine, there are several.  char, varchar, text, blob, etc.  Pick one that has the capacity to store what you need.  

[Yamaeda]

In your read you try to convert all data to strings, that won't work for a Long binary data (and it's understandable the Variant field doesn't know how to present it). I'd try to convert the data to an Array of clusters with String and Numeric array. If the binary is hard to handle, just use a Varchar field and use the Array to spreadsheet as mentioned.

/Y

[enthusiast4]

---

@aputman wrote:

If you are going to use Array to Spreadsheet String, you'll want to change the column type in the dB to a string format.  Depending on the dB engine, there are several.  char, varchar, text, blob, etc.  Pick one that has the capacity to store what you need.  

---

I have tried using Array to Spreadsheet String.VI by changing the column type in dB to string format. It Displays perfectly the 'Array of coordinates' as string on the front panel of VI as ' spreedsheet string'. But,  when I check in my database , it still shows as 'Long binary data' and doesn't display anything on the table .

How can I use varchar,char,text,blob in Database Connectivity Toolset? Does it allow?

PS: I have attached the updated images.

[aputman]

Why do you convert the spreadsheet string to an array before you write it to the dB?  Store it as a string and when you retrieve it from the dB, use the Spreadsheet string to Array VI to convert it back.  

You mention viewing the data in Access.  Do you have a good reason for needing to do this?  The purpose of a database is to store the data.  The front end application deals with displaying it to the user in a meaningful way.  

[enthusiast4]

---

@aputman wrote:

Why do you convert the spreadsheet string to an array before you write it to the dB?  Store it as a string and when you retrieve it from the dB, use the Spreadsheet string to Array VI to convert it back.  

You mention viewing the data in Access.  Do you have a good reason for needing to do this?  The purpose of a database is to store the data.  The front end application deals with displaying it to the user in a meaningful way.  

---

I converted Spreadsheet string to Array before writing it to DB because the 'columns' of DB Tools Insert Data.VI is not allowing to have string as datatype. Instead, it has a default type of 1 D string array. Anyways, I will try to find out something till I find a good result.

I was simply checking the data in Access, no specific reason.

[aputman]

This is from the help file:

DB Tools Insert Data VI

data specifies the data you want to insert in the database. If data is a cluster, this VI inserts each item in the cluster to each element in the columns input. The item order of the cluster determines the order how this VI inserts the items to the table. For example, this VI inserts the 0^th item to the 0^th element of the columns input. If the columns input is empty, this VI inserts the 0^th item of the cluster to the first column in table.

If data is not a cluster, this VI inserts the data to the column specified by the columns input.

If you only have one column in the dB, you don't need to wire anything to the columns input and the string can be wired direct to the data input.  If you write to multiple columns, the data should be built into a cluster, not an array.

[enthusiast4]

I tried to convert my 'Array of coordinates' into spreadsheet string and pass it as string datatype to the database. But, when i try to read the table, it shows blank value for the string column (named as 'particle') and only shows value for the column 'frame' of datatype 'long'.

The table gets created but it is throwing me the following error:

Error -2147217913 occurred at NI_Database_API.lvlib:Cmd Execute.vi->extractBLOB_from_AVI.vi

Possible reason(s):
ADO Error: 0x80040E07
Exception occured in Microsoft JET Database Engine: Data type mismatch in criteria expression. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->extractBLOB_from_AVI.vi

PS: Below is my updated VI, Image: Shows how the table gets created