LabVIEW and SQL – Part 1

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/

xampp

phpMyAdmin

It’s not perfectly user-friendly, but there are a million tutorials around here.

Create a database „labview“ and a new table „testcase““

anlegen

Add a new table

tabelleerstellen

With the following structure

tabelleübersicht

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)

odbcad32

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.

obdcad2

Using LabSQL

Download LabSQL and copy it into this folder

C:\Program Files\National Instruments\LabVIEW 2009\user.lib

oder similar, depending on your version.

LabSQL-VIs

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!

Connection

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.

Command

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

Recordset

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: