SQL Database and LabVIEW EXE

[battler.]

Hello,

We have created a series of large data files in Excel.  We are retrieving data from these spreadsheets.

Considering moving to SQL database or equivalent but no experience in this field.  Searching for the best long-term technical solution to managing large sets of lookup data with LabVIEW EXE.

Ideas, guidance, assistance would be appreciated.

Thanks.

[drjdpowell]

I use SQLite, which is a library for datafiles with full-featured SQL.

[battler.]

---

@drjdpowell wrote:

I use SQLite, which is a library for datafiles with full-featured SQL.

---

Thanks for the info.  Questions:

- Is it easy to learn?

- How do you call it from LabVIEW?  Examples?

- How do you ship it with LabVIEW EXE?  Is it possible?

- Which database file formats does it use/create?

[drjdpowell]

Here's a video intro I did:

https://youtu.be/0UiI2R-pzxQ

Library is at http://sine.ni.com/nips/cds/view/p/lang/en/nid/212894

[daveTW]

Hello Battler,

I used for some projects MySQL and for others SQLite. So I can only tell for these two database engines on Windows platform.

MySQL:

• I used the Database Connectivity Toolkit, shipped with LabView. For basic tasks there are special VIs, like UPDATE, INSERT, SELECT. On the other hand there is the possibilty to build an SQL- String to use more sophisticated features like JOIN and ORDER BY or built-in functions like MAX() or LIMIT().
• It is a very fast engine and it is possible to add large datasets quite fast
• You can easily connect to a DB that is located on another machine in the network
• You have to install the DB engine and an ADO driver to connect to MySQL. So if you deploy your LabVIEW program, the customer has to do this too.

SQLite:

• I used the Toolkit provided by drjdpowell, found here: https://lavag.org/files/file/212-sqlite-library/
• The INSERT- performance of the DB- engine is not very good (I don't remember exactly but something around 500 recordsets / s), but SELECT is fast.
• I mostly used the Exec- function with an SQL- String, this works well.
• For me one main advantage is the "serverless" use, that means, you connect to a single DB- file (this represents the Schema in MySQL) which contains all tables in that DB. This makes it very easy to backup and move the DB to different machines.
• There is no need to install the DB engine, it is shipped as DLLs with your exe, so you need only one installer.

These are my two cents...

[niNickC]

I have written LabVIEW apps with SQLServer and MySQL. Both of these provide free versions to run.

In one case the database runs on the deployment machine, the other on a remote server.

In both cases, I used the LabVIEW connectivity toolkit. This uses the native OLEDB driver for SQLServer and an ODBC driver for MySQL. These acronyms get super confusing. The manual of the DB connectivity toolkit does a decent job at explaining.

Probably going to move away from ODBC to some native TCP/IP solution (so I can run on Real-time).

https://forums.ni.com/t5/Example-Code/Native-LabVIEW-TCP-IP-Connector-for-mySQL-Database/ta-p/349660...

Worth noting that Excel provides built-in support for SQL Server and you can get a plug-in for MySQL.

SQLite is nifty too but not so suitable for a server-side database with multiple concurrent remote users.

[FireFist-Redhawk]

---

@battler. wrote:

Hello,

We have created a series of large data files in Excel.  We are retrieving data from these spreadsheets.

Considering moving to SQL database or equivalent but no experience in this field.  Searching for the best long-term technical solution to managing large sets of lookup data with LabVIEW EXE.

Ideas, guidance, assistance would be appreciated.

Thanks.

---

I just want to point out that if you are using 64 bit version of LV, be prepared to do some configuring if you want to use the Database Connectivity Toolkit.

I'll refer you to this forum post where I detailed what I am talking about. If you are only using 32 bit, you can disregard though.

[mslhutchinson]

Hi Dave, 

Just wondering if whether using that toolkit you mentioned required you to use the SQLite ODBC driver - I'm using SQLite with the database connectivity toolkit and in that case I need to use the ODBC data sources interface to create a DSN. If its possible to avoid this I would be interested in knowing how! 

Thanks,

Matt

[daveTW]

Hi Matt,

there is no need for the ODBC driver. Just install the package via JKI VIPM, the needed DLLs are included and you are ready to go. And if You build an executeable, LabVIEW will copy the needed DLL as needed (I'm talking about Windows- System). I'm currently working with Ver. 1.3.1, which should be from around 2014/15. But I assume, the basic principles didn't change.

[pjroland1121]

As advice for future security of your SQL database contents, I recommend looking into "stored procedures" when you get the time.  By retaining the insert/update/select commands within a stored procedure you gain the following.

The procedure is called via the Exec function and you just feed in the parameters you need.

1. It's a lot easier to ensure complex datasets get recorded properly when multiple SQL tables are involved.

2. Any future edits to your LabVIEW code can't damage the database (like someone accidentally editing the commands)

3. Logic can be added to handle data differently depending on certain datavalues (like writing extra data to another table if a boolean is TRUE for example)

4. Error checking can be added into the stored procedure (like ensuring correct data ranges)

5. Custom error codes can be returned from the stored procedure to your EXE.