To work with databases in LabVIEW, there are a number of solutions. The official way is to use the LabVIEW Database Connectivity Toolkit (http://sine.ni.com/nips/cds/view/p/lang/en/nid/209060 ) Just like every NI product, it’s not that cheap, but affordable for commercial use, and simply works. But today, I’d like to introduce you to one of the two bigger „free“ projects: LabSQL. http://jeffreytravis.com/lost/labsql.html
It was developed by Jeffrey Travis, who wrote LabVIEW for everyone. The tool is pretty old (version 6), but it still works nicely. And is part of my legacy work, so I’ve all kinds of neat wrappers and examples from all the years I’ve been using LabVIEW now.
LabSQL works with ADO-Objects. ADO (ActiveX Data Objects) are based on ActiveX and provide and interface for multiple kinds of databases (and similar things like CSV files). You’re required to have MDAC (Microsoft Data Access Class) (oder successors), which is pre-installed since Win2k (we’re not doing NT any more, right? It was hard enough to get Win7 established..)
Let’s assume we have a mySQL database. It’s pretty much the same for every kind of database that uses ODBC (Open Database Connectivity). ODBC was developed as an abstraction to interconnect different database types with different programming languages.
Creating a database
Easiest way to get a mySQL database is with XAMPP. http://www.apachefriends.org/en/xampp.html
XAMPP give you a mySQL server, a php server and some other useful stuff. It’s not ideal for production (safety, stuff like that), but it’s pretty much standard for web developers. It also gives you phpMyAdmin, which gives you a GUI for your database.
Start Apache and the MySQL server and got tohttp://127.0.0.1/phpmyadmin/
It’s not perfectly user-friendly, but there are a million tutorials around here.
Create a database „labview“ and a new table „testcase““
Add a new table
With the following structure
You also need to install the ODBC-Connector.http://www.mysql.com/downloads/connector/odbc/
This is the thing that allows OBDC apps to access mySQL.
Press Win+R, and run odbcad32.exe , where you need to create a DSN (Data Source Name)
For this, select the System-DSN tab, and add the mySQL OBDC driver. Create a DSN Name (myDB) and add your connection data. Test if you can access your database.
Download LabSQL and copy it into this folder
C:\Program Files\National Instruments\LabVIEW 2009\user.lib
oder similar, depending on your version.
I haven’t really found a good documentation for LabSQL, sou you need to play. The Vis for LabSQL should be in the „User Libraries“ . Inside them, there’s a folder called LabSQL ADO functions.
ADO has 3 kinds of objects: commands; connections, recordsets
First, create a connection object, open connection to the database. Then, create commands, and get recordsets back. Then close commands and your connection.
Please note that not all database systems allow all commands!
|ADO Connection Begin Transaction||Starts a transaction. Transactions are a number of commands, and if you start them, they will either finish successfully or don’t do anything at all.|
|ADO Connection Close||Closes a transaction|
|ADO Connection Commit Transaction||Saves chacnces and commits / finishes a transactions|
|ADO Connection Create||Creates a connection object. Use at the very beginning.|
|ADO Connection Destroy||Destroys a connection object. Use in the end.|
|ADO Connection Execute||Execute a command.|
|ADO Connection Open||Opens a connection. Requires the DSN name string.|
|ADO Connection Rollback Transaction||Aborts a transaction and rollbacks half-finished changes.|
|ADO Connection Set Mode||(see: http://www.devguru.com/technologies/ado/quickref/connection_mode.html )|
|ADO Connection Set Timeout||Sets timeout.|
|ADO Command Create||Create a command|
|ADO Command Destroy||Destroy a command|
|ADO Command Execute||Exectue|
|ADO Command Get Integer Parameter Value||I’m not going to explain self-explaining things. That’s stupid.|
|ADO Command Get Parameters|
|ADO Command Set Active Connection|
|ADO Command Set Command Text|
|ADO Command Set Parameter Value||sets paraemeters in stored procedures|
|ADO Command Set Stored Procedure Text||with Stored Procedures you can work with longer sequences (helps you fight against Injections! – not working in mySQL!)|
|ADO Command Set String Parameter Value|
|ADO Fields Get Count||gets number of colums|
|ADO Fields Item||give him an index, get a single column|
|ADO Item Value||read column of the thing that you accessed with Fields Item.|
|ADO Recordset BOF||BOF= Begin of File. Interesting if you work with move/cursors (pointers) that might theoretically access invalid recordsets.|
|ADO Recordset Cache Size|
|ADO Recordset Close|
|ADO Recordset Create|
|ADO Recordset Destroy|
|ADO Recordset EOF||see BOF.|
|ADO Recordset GetFields|
|ADO Recordset GetString|
|ADO Recordset Move||Hey, ho pointer arithmetic|
|ADO Recordset MoveFirst|
|ADO Recordset MovelAst|
|ADO Recordset MoveNext|
|ADO Recordset Open|
|ADO Recordset RecordCount|
These are not „real“ ADO commands, but specific SQL-VIs:
|ADO SQL Fetch Data||Gets data and puts it into a record set. (Don’t use this).|
|ADO SQL Fetch (GetString)||Gets Data and puts it into a record set. Creates an array. (Don’t do this.)|
|ADO SQL Execute||Yes, baby.
Executes all kinds of SQL-commands and returns a 2d-array.
to be continued