LV Database Connectivity - Insert Into DB From 2D Array

[hopelesslyLost]

[LV2106]

Hello,

I am inserting multiple records into my DB during testing. I am attempting to create/find a process that can insert an array of strings (2D) into their respective positions in my DB, regardless of the length of the array. For instance:

ID      | aaa

field1 | val 1

field2 | val 2

...

fieldN | val N

I intend to insert into table X at fields ID, 1, 2, and N with their respective values of aaa, val 1, val 2, and val N. This would allow me to reuse the VI by wiring in the table string and arrays. The problem that I am running into is caused by the expected number of arguments. I can only insert information currently of static sizes, not of sizes determine at run time.

I can array to cluster (manually determined size), but it will fail when I need to insert more values than the selected size. I have not been able to find any solution to this problem after hours and hours of looking through forums, and I am not sure if it is even possible?

Potential routes:

1. I could create a vi that takes an input integer and determine which case of the array to cluster to use and manually create cases for up to some number of clusters that until I don't expect anymore, but that seems like a poor brute force approach.
2. Turn the array into a flattened cluster somehow? and pass that into the database insert?
3. Attempt with an array of variants, and then variant into one large variant? This works with mixed results... The data is no longer the string value and seems to become a reference? I am not 100% sure. i.e. string test -> ????[] ?? ????
4. Use the array to dynamically create the SQL string that can be executed on the DB.

I would appreciate any help with this problem. I would like to make this approach as streamline as possible so that future alterations are not incredibly difficult to view and understand, but at this point, I might have to give up on that.

Or am I going about this the wrong way.

[Mark_Yedinak]

First, when working with databases I prefer to work through stored procedures. I am not a fan of raw SQL in the code. Stored procedures allow you to make changes to the DB schema without necessarily requiring the code to change.

No, onto your specific issue. It is a little difficult to understand your actual DB structure with your example. Does your table have Columns ID, Field1, Field2, ... FieldN? Are you asking to be able to insert data and only fill in specific columns leaving the others with their default value? If you don't have stored procedures, then I would construct the SQL insert/update statement at run time. I generally do not use any other VI from the DB connectivity toolkit other than Open, Close, Execute and Fetch.

[hopelesslyLost]

Mark,

Thank you for your reply. I am not familiar with stored procedures, but I will look into them. I will do my best to better explain my desired functionality.

I have 3 main tables that I am recording data in. For this explanation, I will call them table_units, table_tests, and table_test_measurements. In each of these tables, I have some number of fields (11, 6, and 5 respectively). I want to create a VI which can correctly Insert the into a given table with the following inputs: table_name as a string, and record information as a 2D string array consisting of the data's field and value being inserted. All columns are being written with each insert procedure.

The issue occurs at the Insert VI from the connectivity toolkit. The Insert VI wants to accept a cluster that has been cast to variant into its data to my knowledge. I have not been able to get the insert VI to play nicely with any other data. This is an issue because the array to cluster's size cannot be set during run time. I must set the array to cluster size during development. Any mismatch in the size and the data will cause errors. This prevents me from using a single VI to insert to any table given the table name and 2D string array of data, as the cluster size is different for each table. How can I create a VI that takes these inputs an inserts into table_units, table_tests, and table_test_measurements as long as the 2D string array is correct? The VI below is for reference. My goal is to place everything that is not the table name or the data array into its own VI and wire those two as inputs.

If my technical desires seem obtuse please let me know. I will look into the stored procedures more to see if they would be a fit for my desired functionality, but I would also appreciate it if you explained more about how you typically interact with databases from LabVIEW. This is my first time trying using LabVIEW for connecting with databases, and almost my first time ever using LabVIEW lol.

 

[hopelesslyLost]

Follow up - Potential approach 1:

  

I create a VI seen in the first screenshot which accepts a 2d string array. It works by checking the size of the data column and using the array to cluster VI with the size being manually set equal to the case value of the data array size. I do no particularly like this method, but it will work up to the max cluster size of 255. I stopped early because I do not expect my tables to hold even close to the number of fields it can handle.

I would still like to find a more elegant solution to do this, but it does work...

[TBurgess15]

I'd go with option #4. Programmatically build your SQL statement and use the DB Execute VI to run it. See W3 for example insert statements if you aren't familiar (https://www.w3schools.com/sql/sql_insert.asp) This way you will avoid cluster size errors associated with the DB Insert VI. One final thing to consider is how the database is storing your values, so you may need to concatenate ' ' around varchars/strings, etc.

Something along the lines of this code snippet: