How to write a 2D array to a database using database connetivity toolkit?

[Slopoke03]

Hey Gang.

I am having trouble writing records efficiently to my database. I have a 2D array of elements that I am writing to my database. Currently, I am writting one row at a time using a FOR loop and "DB Tools Insert Data.vi". Unfortunately, this process is very time consuming. When I try writing in the entire 2D array (as opposed to 1 row at a time) I get the error:

"Number of query values and destination fields are not the same..." 

Is there any way to write a 2D array to a database in one shot?? (and take up the same number of records as if I wrote each row individually)

Any ideas would be much appreciated.

Using LV 2010.

Cheers

[JMotaB]

I had the same question a while back and didn't find a way to do it other than the method you are using. I think one question for you is, do you have your table already defined and you only need to update the values or do you need to create or add to a table ?

[Slopoke03]

Nope, everything is already defined. I am just looking for a way to write the data in bulk as idividually writting a thousand records is fairly time consuming.

[JMotaB]

I was thinking you may be able to use the "DB Tools Update Data.vi" but I remember now and I think it works like the "DB Tools Insert Data.vi" so you would have to do the same

[NitzZ]

Hi,

I am not sure what exactly you guys are missin but you shouldn't get such errors. Well, the DB toolkit takes columns rather than rows when passing a 2d array. So a miss match may occur in the number of rows being passed and the rows in the table if you are considerin it as rows and passing the values. It would be easy for me to explain if you could post your code.

Regards,

Nitzz

(Give Kudos to good Answers, Mark it as a Solution if your porblem is Solved;)) 

[Slopoke03]

Maybe Ive explained the issue incorrectly. Please see the attached snapshot. The top portion of the code is what I currently have. It works, but it writes one database record at a time and the loop runs ~1000 times. (How I am generating the data to write is not particularly important but how I am writing to the database is currently my issue)

The second loop is what I would like to get working as the database write function will only run 4 times (as determined by the number of elements in the variable "Multiple Tables".

Thanks to all for the input so far.

[Chris V]

Hi Slopoke03,

Unfortunately, there is no way to insert mutliple rows with that function.

There are two different options I can think of right now:

1) If you right-click on the For Loops you are using, you can select Configure Iteration Parallelism . . .  This will allow LabVIEW to create multiple instances of that loop and run multiple iterations at the same time.  However, I'm not sure if this will have a sizeable effect since I'm fairly certain the database commands will block each other.  You will also no longer have control over the order in which the rows will be inserted.

2) You could compile everything into a large SQL statement.  This could get very messy, but it would then insert all of the rows into the database in one command.

Have a great day,

Chris V