Ok, this is the third part of our discussion of how to effectively utilize databases in a LabVIEW-based application. To recap, In Part 1 we have covered the basic justifications for utilizing databases, and checked-out some drivers that implement basic database connectivity. Part 2 expanded on that information by going into the (very basic) basics of how to design and build a database. I also provided some links for further reading, and a LabVIEW tool for executing an SQL script to turn it into an actual database. What you are looking at now is Part 3 and it will present code that implements the LabVIEW side of the data management capability we discussed earlier. If you haven’t read the other two sections, I would recommend that you do so before continuing — don’t worry, we’ll wait for you.
Those of you who did read the previous portions, feel free to talk among yourselves until the others get back.
Everybody back? Good! Let’s continue.
Example Code
The database we have implemented so far covers three basic areas in the Testbed application. Something that I didn’t mention before is that these areas were not picked at random, or arbitrarily. Rather, if you look at them you see that each one presents an example of one kind of database operation while presenting useful concepts that you will want to know about in the future.
- Processes to Launch: This section demonstrates data that has an inherent structure as embodied in its one foreign key relationship.
- Event Recording: Here we considered a table to which applications will eventually write. It also shows a little more structure in that it relates to two different header tables: one that identifies the application generating the event and one that identifies the type of event that is being recorded.
- Default Sample Period: Although much of the data in a system will be structured, there is still a place for simple setting such as you might store in an INI file. This last example showed such a situation.
Carrying forward with this idea of demonstrating a variety of concepts, as we go through the code that implements the LabVIEW side of the connection, I will point out a few different techniques that you will find useful. The thing to remember is that there are engineering decisions that you have to make and no one technique or approach will serve in every possible situation.
Reading Processes to Launch
The new VI that performs this operation is still named Configuration Management.lvlib:Get Processes to Launch.vi
and has the same basic structure as the INI file version (which I renamed), but the configuration file IO logic is replaced with the database IO drivers I presented earlier.
Although the structure is basically the same as before, there are a few changes due to the improved structure that the database provides. First, there is a new input (Launch Condition
) that is tied internally to a database search term. Second, the output data structure is modified to utilize the enumeration for the launch mode, replacing the boolean value used before.
In terms of the query code itself, the large SQL statement in the string constant is for the most part pretty standard code. The statement specifies what columns we want (label, item_path, launch_mode
), the table they are in (launch_item
) and the WHERE
clause provides the search terms that define the output dataset we want. Likewise, note that although I never read the launch_order
value, I use it to sort the order of the results. If you have data that needs to be in a specific order this is an important point. Unless you explicitly tell the DBMS how to order the results, the sequence of records is totally undefined. The only real complication is contained in the WHERE
clause.
You will recall from our discussion of normalization that the two primary search terms we will be using are stored as ID numbers that reference a pair of header tables. These header tables contain the human-readable labels that we want to use for our searches. This code demonstrates one approach to resolving the ID references through the use of subqueries. A subquery is (as its name suggests) a small query that occurs inside the main query. They are typically employed to lookup data that the calling application doesn’t directly know. In this example, we know that the application name and launch condition, but we don’t know what ID numbers are associated with those inputs. The subqueries look up those values for us so the main query can use them to get the data we want.
The advantage of subqueries is that they allow you to specify what you want in the terms that are meaningful to the calling application. The disadvantage is that they can complicate SQL code and the resulting code can be more DBMS-specific. In addition, with some DBMS (like for example, Jet) there can be a significant performance hit involved in subqueries. You’ll note that the example code mitigates this potential issue by buffering the search results, thus ensuring that the “hit” will only occur once.
Saving Errors to the Database
This operation is performed by a new version of Startup Processes.lvlib:Store Error.vi
. As with the original version, the input error cluster provides the information for the error record, and the output error cluster is the result of the record insert.
This code shows an alternative to using subqueries to obtain the ID references. The two subVIs look-up the required IDs in the database the first time they are called and buffers the results for later reuse. The two routines are very similar, so here is what the application ID VI looks like:
The advantage of this approach is that the required SQL is much simpler and very standard. The only real “disadvantage” is that you have to create these buffers — which really isn’t very much of a disadvantage. I really like this technique for situations where there are common IDs that are used over and over again. Unfortunately, this sort of modularization isn’t always possible, in most real-world applications you will need to know both techniques.
Read Default Sample Period
You will recall that this is new functionality so there is no “old code” to compare it to. Here is one option for what Configuration Management.lvlib:Get Default Sample Period.vi
could look like.
The code here is very similar to that for reading the processes to launch, the main difference being that two of the search terms are in essence hardcoded. Current, there is only one parameter being stored in this table, but that situation could change at any time. While replicating this VI for each additional parameter would work, the result could be a lot of duplicated code. Consequently, I typically prefer to start with a lower-level VI that is something like this…
…and then make the VIs like Configuration Management.lvlib:Get Default Sample Period.vi
simple wrappers for the buffer.
Hopefully by now you are beginning to see a bit of a pattern here. Every type of access doesn’t require a unique technique. In fact, there are really very few unique use cases — and frankly we just covered most of them. The real key is to get comfortable with these basic methods so you can customize them to address your specific requirements.
Moving On…
So we now have our database, and the VIs for accessing it. How do we roll this into out application? One way is to simply replace the old INI file versions of the VIs with the new database-oriented ones — which is what I have done for now. I say. “…for now…” because there is a better solution, but that will have to wait for another day. Right now, the solution we have is serviceable so I want to get back to a couple more important topics. Here’s the updated application:
http://svn.notatamelion.com/blogProject/testbed application/Tags/Release 7
Until next time…
Mike…