Populating an empty cluster in an object on the fly

[tbob]

Here is what I would like to do.  I want to create a generic vi to handle database functions.  I want it to be able to work with a variety of databases, different number of fields (columns) and each field can be either a string or a numeric.  For example DB1 can contain a table with Name, Address, Phone (all strings).  DB2 can contain a table with Name (str), Age (numeric), Country (str).  I want to use a generic object to hold one recordset, and build an array of objects to hold all the data.

I created an object with an empty cluster.  I can read the database and get all the column names and data types.  How can I build the object so that it has the proper element type with its label being the column name.  For instance, when I specify DB1, I want my object to be a cluster of 3 string elements, labeled Name, Address, Phone.  If I specify DB2, I want my object to be a cluster of Name (a string), Age (a numeric), and Country (a string). 

The database can contain any number of fields of either string or numeric types (I will treat dates as strings).  So one database might contain 6 fields and another might contain 12 fields.  I would like one generic set of vi's to be able to work with any database I choose without having to hard code the object cluster to match the table columns.  Is this possible?

Thanx in advance to whoever can help.

[smercurio_fc]

You can use scripting to create the cluster(s). You can't do this at runtime, however.

How about just using variants?

[tst]

If you just want the GUI part, you can do something like what Mike has in this post. You'll probably have to adapt it, but I believe it has all the code you need.

Another alternative is to use a .NET control such as a DataTable, but I'm not sure if you can populate the types dynamically. In any case, if you need the actual data type in your code, you have no choice other than to create some explicit code. LVOOP can help you there, because you can create the basic code in the parent class and then just create a new child class for each new table or DB, but whether this is practical depends on your setup.

[tbob]

Tst's solution sounds good, but too complicated.  I avoid overly complicated code as much as I can. 

I've decided to keep things simple while also allowing for future expansion and changes.  Instead of trying to match the actual data type in the database, I'm going to convert all datatypes to strings after reading the database.  Then I can use just a 1D array for the column names and a 2D array for data in my object cluster.  That way I can adapt to any database, no matter how many columns, and no matter what data types.  

Of course I will have to convert numeric strings to numerics for numerical manipulations (the only thing I'm doing is to compare a measurement with upper and lower limits).  Then I have to write back new data to the database.  I'll have to convert strings back to the original data types.  But I can use the information from the DB Tools List Column vi.  It lists the name and datatype of each column.

The Variant idea from smercurio was valuable.  It made me think that if I have to convert to variant and back, I might as well just use strings since most of the database fields will be strings anyway.

Thanx to all who tried to help.

Message Edited by tbob on 04-08-2010 09:55 AM