Database Connectivity Toolkit Multi Row Insert

[lisam]

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

[MikaelH]

I would use the low level SQL syntax to do this...if the database supports it.

INSERT INTO test1 (Column1,Column2) VALUES(1,2),(3,4),(5,6);

I've stopped using NI's DB toolkit, instead I use the LabSQL ADO drivers wrapped in my own driver.
This is much faster.

I've attached my version

DB_Export.zip

[Tim_S]

16 hours ago, MikaelH said:

I've stopped using NI's DB toolkit, instead I use the LabSQL ADO drivers wrapped in my own driver.

This is much faster.

Do you have anything indicating how much of a speed difference there is?

[MikaelH]

Sorry,  I don't remember how much faster it was. 

[lisam]

Hi Mikael, thank your suggestion and help!

I managed to connect to my db using open and close connection invoke nodes and execute a query using ADO execute. Thanks very much indeed. My only question: do you know if ADO compatible with Linux Real-Time cRIO?

Thanks for your time, it is much appreciated.

Lisa

 

 

Edited November 1, 2016 by lisam

[MikaelH]

It probably won't work on Linux. :-(

[lisam]

damn, that seemed like a great method. Do you know of any method that will work on linux?

I've been following a tutorial for LabVIEW to PostgreSQL for Linux RT using the system exec VI, (https://decibel.ni.com/content/docs/DOC-30308) which works but it opens and closes the connection every time data is inserted (unless I string all the values together first - which isn't very efficient). Another suggestion is using the Call Library Function Node - but I can't find any examples with a PostgreSQL API.

 

[drjdpowell]

I’ve been working on a wrapper for libpq.dll, which theoretically could be made to work on Linux by just using a libpq.so file, but I have only tried Windows so far.

[Rolf Kalbermatter]

On 1-11-2016 at 2:01 PM, lisam said:

Hi Mikael, thank your suggestion and help!

I managed to connect to my db using open and close connection invoke nodes and execute a query using ADO execute. Thanks very much indeed. My only question: do you know if ADO compatible with Linux Real-Time cRIO?

Thanks for your time, it is much appreciated.

Lisa

Of course not! ADO stands for ActivX Database Objects and ActiveX is a Windows only technology.

Depending of the actual database server you want to access there are several possibilities but not all of them are readily doable in LabVIEW for Linux.

If your database driver is implementing the whole communication directly in the LabVIEW VI level such as the MySQL driver here which access the MySQL server directly through TCP/IP communication, then you are fine.

Accessing the unixODBC driver is another possibility which keeps the LabVIEW part independent of the actual database driver implementation. This project does provide such a LabVIEW library however, it is not always easy to get a working ODBC driver for a specific database server. Microsoft officially supports Linux clients with their latest SQL server but I have not tried that at all, and if you talk about the NI Linux realtime targets an additional problem is the architecture (ARM based for the low cost targets and x64 based for the high end targets) and the fact that NI Linux RT isn't a normal standard Linux system but in several aspects a slimmed down Linux kernel that some precompiled binaries may not work on, and to expect Microsoft to give you the source code of their SQL server libraries to compile your own binaries for a specific target is of course pretty hopeless.

Edited November 13, 2016 by rolfk