Cluster size programmatically for Insert Data function

[alex b.]

Hi,

The problem is that we are trying to build an application as generic as possible. We have to write some data to an Access database. The number of columns is subject to change from a system to another (between 20 and 100).

The function "Insert data" in the database connectivity requires some kind of cluster. After each part is processed by our system, I want to write the data (which is an array) to the database.  I know I can use the bloc "Array to Cluster" and "hardcode" the cluster size. It will work for a specific application. But then, when I have another application with a different number of columns (different data size) in my database, I have to change this value in the bloc "Array to Cluster" and build another application? This is very inconvenient.

So I want to know if there is any way to transform my data before I input it in the "Insert Data" function to make my application generic. I have been trying a lot of things, and it appears that this function really needs a structure with a fixed size.

Thanks for your help

alexandre boyer

[tst]

There is a limit to how much you can (and should) make an application generic and you should probably rethink your design. In general, you should have no interaction with the tables in your database. Instead, you should have VIs which will be assigned specific functionalities and will hide the structure of the actual saved data.

For example, suppose your application performs a test and then saves the results. These tests are defined in your DB. In this case, you could create a VI called "Save test data". That VI would accept a data structure (let's say a typedef cluster or an array) representing the test data and would then internally be responsible for saving that data. If your DB is properly designed, it is possible that the VI will need to go to several tables in order to process the data, but the point is that the VI will handle the saving of the data without any need for the application to know how it's doing it.

You create functions like that for everything you want to do. That way you sever the link between the application and the DB. Once you do this, of course, you have no need to have a general conversion from an array to a cluster.

I suggest you try searching the forum for a document Mike Porter posted about working with DBs. It might help you.

[Logan_K]

I saw your post, and I remember struggling with the exact same limitation of the database connectivity toolset about... 3 or 4 years ago? I went back to my code and my solution to the problem back then was to hard-code a case structure with different possible cluster sizes, and then force an upper limit on the number of columns in my table. This was not the ideal way to solve this problem, but it worked at the time.

Since then, I have had much more practice with databases, and I realized that the structure of my database was ultimately what restricted me, not the restriction of using a cluster instead of an array or variant. So, you need to ask yourself what your purpose for this database is. If it is just to log data to a table, then I would maybe consider using a spreadsheet instead of a database because spreadsheets are way easier to work with. You can always import spreadsheet data into databases later on if you need to.

Otherwise, you need to take a long look at the structure of your tables. I have found that utilizing multiple tables and using a unique ID for each entry is a convenient way to link data between those multiple tables. For example, table 1 can be used to store a unique ID for each set of data that you plan to store in the database. Then, in table 2, you can have a single column for all of your data and another column for the Data ID which corresponds to that data. Then, if you want to pull out all of the data with a particular ID, you just find the ID in table 1 and then select all elements from table 2 which have that corresponding ID. You could also have a column in table 2 for a description of the data that is stored in that row. This structure has fixed table sizes, and is flexible enough to accomodate multiple datapoints for a particular entry.

Anyways, I hope this helps!
Logan

[tst]

I just remembered a thread you might wish to check out. If you really want to, you can probably use Kevin's method, but I would advise against it. Instead, as metioned, you should probably either use another tool or design your DB differently.

[alex b.]

Hi,

Thank you all for your responses. I am considering a spreadsheet to save the data.

The point is that we are implementing our product on different machines with different sizes. The actual client is using an Access database for a long time with a debatable structure. He wanted us to incorporate our data in it using the same kind of structure he already has, but I don’t want to uses cases, just in case he comes with another machine with another size. And I certainly don’t want to compile many different applications doing the same thing.

Anyway, the client agreed with the spreadsheet, so everything is nice.

I really appreciated your help.

Thanks

Alex boyer