How can I convert array of database variant to cluster?

[Brian_Beal]

When I use the Database Connectivity Toolkit to read a recordset from a database, I just use a SQL query.  This returns an array of variant data.  I have always just indexed this array in a sequence structure that has the database variant conversion function in it and build a cluster from that.  There has got to be an easier way, but I can't find it.  Of course, my recordset has various data types in it.  Any ideas?

[Joachim082]

Hi Brian,

You have to know the datatypes of the columns in the sql-table. You can get the details of every table using the "DB Tools List Columns.vi" but be aware of the different datatypes in the database and in LabVIEW.

For me the "Database Variant to Data.vi" works very fine - maybe the type-definition of every column is a lttile bit circuitous but I decide the datatypes in LabVIEW and not the descriptive ADO infos.

HTH

Kind Regards

--
Joachim

[Brian_Beal]

Yes, I already know the datatypes.  The question is how is easily convert the array of variants to a cluster.  I already have the cluster defined with the proper datatypes in the correct order.  If I have an array with 30 elements, it is rather tedious to do the programming to convert them one at a time.

[Joachim082]

do you mean with 30 elements columns or rows?

[Brian_Beal]

Sorry.  I mean 30 columns.  I want to convert a 1-D array, which represents 1 record in the database.

Thanks

[Joachim082]

sorry, therefore I've no solution. In my current application the "Database Variant to Data.vi" works very fine (I have at maximum 10 columns)

Maybe a workarround would be the "DB Tools Fetch Recordset Data.vi" combined with the "DB Tools Fetch Element Data". The second one has the column index and also the datatype as an input.
The problem would be the different datatypes in the array.

[tst]

You could try modifying the OpenG Variant Configuration VIs to work with extracting data from tables or queries, but I would advise against it, as that would require your cluster elements to have the same name as your columns and would create a strong coupling between your application and the database.

Personally, I just use the method you described - I have my typedef'd data structures (which are only relevant within my application) and when I have to put data from the DB in one of them, there is a subVI which will get the data from the DB and put it into the cluster one element at a time (although even with large clusters, I try to avoid the sequence structures). You can organize your queries to have fields with the same data type near each other and then convert entire subsets of the resulting array into that datatype and use the Index Array primitive to get out all the adjacent ones without using too much screen space.

[Brian_Beal]

That method is better than what I am using, but it still seems crude.  I can write my cluster natively to the table, but I can't read it.  Maybe NI will work on that.

Thanks!

[tst]

Again, you can try a combination of the OpenG VIs and the DB toolkit parser and it will give you generic code, but I think that working directly with the structure of the table is a bad idea.

[Ben]

Here is the  seed of an idea I had been nocking around.

Compose an SQL query based on the cluster elements. I chose to use the caption of the cluster elements so that the unbundles have the names I want but the caption can be set to match the collumn names from the table. (If you really want to get fancy, update the captions of the cluster of the typedef before the application opens)

Do the query and get your array of variants. They should be returned in the order of the query (which we composed based on the cluster order)

Pass the array of variants into another foor loop that uses the Class ID to do the variant conversion.

Come to think of it....

Just wiring the variants to the geneic value refs of the cluster may be good enough.?????

In theory if "cluster" is a type def, new fileds can be added to tthe typedef and it should get updated from the DB.

Maybe turn this into a template?

Your thoughts?

Ben

Message Edited by Ben on 01-03-2007 09:02 PM

Message Edited by Ben on 01-03-2007 09:04 PM

Message Edited by SarahM on 01-08-2007 12:24 PM