Database

[SKant]

Hi 

I am working in a LabVIEW and SQL server 2008 project and stuck in reading the array data from SQL server.

Actually my requirement is simple. I have to read the data from the SQL table and table contain three column as attached image

Data column contain the array value. There is no limit of no of row (array value) in Data Column of  SQL table .Data available in SQL server shown in attached image.

I have attached the VI which I am using for writing and reading from  SQL table.

I have to fixed this bug ASAP .Please help me to read the column array value.

Thanks 

[mikeporter]

Have you tried something like:

SELECT data_id, product_sn, data FROM dummy;

You might have problems with the third column name. "data" might be reserved word. But even if not, it isn't very descriptive, so you should change it.

Mike ..

[SKant]

Actually I need data from column data only so I tried below query:

SELECT DATA FROM DUMMY;

Using belo query also I am getting same results:

SELECT data_id, product_sn, data FROM dummy

I have attached the code snap for your reference.

Thanks

[SKant]

I am not facing any problem using third column name "Data". 

[Dennis_Knutson]

You are getting only a single 1D array because that is the data type you have requested. Change from the 1D dbl to a 2D dbl.

[SKant]

Whe I select 2 d array  I am getting the following error " LabVIEW: The data type of the variant is not compatible with the data type wired to the type input."

[kingehp]

---

@srikant_kumar wrote:

Whe I select 2 d array  I am getting the following error " LabVIEW: The data type of the variant is not compatible with the data type wired to the type input."

---

My guess is that you have each column defined with a different Data Type in your SQL Database. Here's a list of SQL Data Types.

Try to change all columns to Text (string) data and then try reading them in LabVIEW. You can create a string constant (to define your data type) and wire it to database varient to data function.

Something like this:

[PhillipBrooks]

Several years ago, I wrote a VI that uses scripting to create a LabVIEW cluster by reading the structure of a database table.

Use the created typdef with Database Variant to Data to give yourself an 1D array of records with the closest datatypes supported (string, numeric, datetime...)

https://decibel.ni.com/content/docs/DOC-19512

[ZX81]

This thread has been quite for several weeks.  Does that mean it's solved?

It looks like an important detail is that the 'Data' column in the .jpg included in the original post always has "<binary data>".  The best the ODBC module can do is put it in a byte stream, and call it a string.

Looking at your 'Write array data into SQL...', I see that you are writing a 2D array of DBL.  So, you need to 'Unflatten From String' on it to convert it from the string to the DBL array.  You'll need to wire an empty 2D-DBL into the type input for Unflatten.

Also, if you look at the INSERT in your Write....vi, you will see a red dot for type-conversion.  You will almost always get that on INSERT, but it's a clue to look at.  In this case, everything is converted to variant.  But in the module that connects it to the DB, it converts LabView-strings into DB-strings with no problem; same with most numbers or dates.  But there is no conversion from an array to a single DB data type, so it converts it to a string (I suspect using 'flatten to string').

Ideally, you should never write an array into a column of a table, or you should explicitly flatten the string yourself before writing it.  A databsae designer would probably insist that you make your table much longer, with (for example) StepData_ID, Product_SN, row, col, and value, then enter 12 rows in the table for a 3x4 array, each with the same StepData_ID and Product_SN, but different row, col, value.  Or, you could make the database designer cringe, and make 12 columns: one for each element in the array.

PhillipBrooks:  that's a cool tool...  I've given up using INSERT without naming all the columns because I'm tired of changing a table & breaking all my code.  But this looks like a great alternative.  The example you gave is a great example of how to convert the variant from SELECT into LabView data, too.