Database Connectivity Toolkit (multi row insert)

[lisam88]

Hi all. I recently got the Database Connectivity Toolkit (DBCT) on LabVIEW 2016 and I'm trying to insert multiple rows into a database.

Using a sine signal input I attempted to insert 50 rows in one iteration, but every method I tried returned 50 values in one row. Using the end of line constant and array to spreadsheet string, returned what looked like 50 rows, but when I queried "SELECT COUNT(*) FROM.." the no of rows was equal to the number of iterations.

On another forum post, someone suggested it is not possible to insert multiple rows using the DBCT. Can anyone confirm this?

I have written some code which allows me to insert 3 rows in one iteration, under a single connection. However, this method is very tedious and wouldn't work for large amounts of data.

Can anyone think of a better way to do this?

Thanks in advance,

Lisa

[aputman · Accepted solution]

Build your own query and use the execute query VI. 

[aputman]

Also, you misunderstand what "1 row" means on your returned results.  That doesn't mean there were 1081 entries in one row.  Your query was to return the count of rows in the table.  The count is the one result.  That doesn't mean there is only one row. 

You should consider some Labview and SQL tutorials before getting too far into this.  A couple of things to note:

• The sequence structure is not needed.  Think dataflow.  If you don't know what that means, there is a lot of information about it.  Labview is built around dataflow.
• Don't convert an array to a cluster in order to index the data with an unbundle node.  Use the index array VI or use a for loop to autoindex the data.
• It doesn't make sense to generate a sine wave and then only save 3 points from the wave.  I'm not sure of your intent here.  And it certainly doesn't make sense to write each point along the sine wave to its own row within the database. 

[Sam_Sharp]

On another forum post, someone suggested it is not possible to insert multiple rows using the DBCT. Can anyone confirm this?

I can confirm that it definitely is possible. The way I did it was to build a parametric query (using ? as placeholders) where I repeat the VALUES part multiple times (depending on SQL server - e.g. "INSERT INTO t1 (name, value, timestamp) VALUES (?,?,?),(?,?,?)" ) and then assign the parameters in a for loop (as it's better than just writing the values in the query as it stops you from accidentally sending a string that could wipe your DB!) and then finally execute as a single query.

[lisam88]

hi aputman,

Thanks for your useful tips and suggestions.

The row count I understand. In this case the loop did 360 iterations and three values were inserted in each iteration which gives a total number of 1081 rows. Which is what I was looking for. Previously I created a model with a sine wave generating 50 samples per iteration. I did this example with the system exec.vi and 50 samples with 14 iterations then returned 700 rows. Using the connectivity toolkit a 50 sample sine wave only returned 879 rows in 879 iterations - so 50 values are on the same row.

In the above example, I only plotted 3 points from the wave in attempting to insert multiple rows. As the only method I could come up with was inserting the values into separate insert functions, I only did three to see if it worked. But of course this is not a very effective method. This is just a basic model to see how I can generate data and insert them into a postgreSQL database. In the application it will be used for, it will require each piece of data to be inserted in different rows and columns. This is why I'm trying to avoid having multiple data points on the same row. 

Thanks again.

Lisa

[lisam88]

Hi Sam, thanks for your comment. I had a look at the LabVIEW Database Insert.vi example. I attempted the example to insert data into my database. But I get the error:

"Error 91 ocurred at NI Database_API.lvlib:Cmd Set Parameter Value (C).vi-> Database Insert.vi

Possible reason(s):

ADO Error: 0x0000005B

Variant To Data in NI_Database_API.lvlib:CMd Set Parameter Value (C).vi->Database Insert.vi"
Can this also work with a sine wave input?

[lisam88]

Just tried the query you suggested and it works perfectly. Thank you!

[Emidpont]

Hey,

I've tried your solution for my case but I'm getting a syntax error and I can figure out why.

Do you have any ideas about this ?

Thank you in advance;

[aputman]

I can't read the error message but I'm fairly certain that it has to do with putting single quotes around the float value.  This is only necessary for strings and datecodes.

[Emidpont]

Hello,

Thank's for your reactivity.

I removed them but I'm getting the same error.