Month: January 2015

Turning a VI On and Off in Software

Posted on by mlportersr

Something you hear about a lot on the LabVIEW users forum are people who want to stop a VI and then restart it. They will often say things like,

“I have a VI that acquires some data and displays it to the user just the way I want. Now I want to run it several times, but change the test setup between runs – and my customer wants it all done automatically. I have code that can do the test setup, but now I need to run my acquisition and display VI and I can’t figure out how to programmatically click the ‘Abort’ and ‘Run’ buttons.”

Clearly, they can’t actually click the button, but if you consider the situation for a moment you realize that they don’t actually need to start and stop any VIs to obtain the desired behavior. All you really need is a way to put VIs into a mode where they don’t do anything until you are ready for them to continue. It’s, likewise, not unusual to hear from people who are trying to sort out how to add interactivity to a state-machine the normally runs without human intervention. Of course this is why the whole question is interesting: We all run into situations where we want the code to stop and wait until something happens.

Obviously, if you put the problem that way its easy to see that we have just described an event-driven application – which we have just spent several weeks building.

A Little Review

However, to fully realize our goal of implementing “stop-start” behavior, we need to give further consideration to the timeout events we have been using. If a VI is truly going to be dormant when we “turn it off” and then go back to normal when “turned on” we need to first have the code that defines the VI’s “normal” operation (whether it be sequential logic, a state-machine, or something in between) located in the timeout case. Then second, we need to be able to disable and then re-enable the timeout event.

Already in the testbed code we have seen that you can use a negative timeout to turn off the timeout event – as we do in some of the logic for the error checking initialization code. The thing is, this technique has a lot more to offer than as simply a way to trap startup errors. As an example of what else it can do, we are going to expand our testbed application such that instead of simply reading data from one process, it will be capable of drawing data from three different processes. One will return random numbers, one a sine wave and one a ramp signal. To select the input to read, we’ll have a pop-up menu on the display process’s front panel. Although this would seem to be a pretty significant change, you’ll see that it’s really not.

Modifying The DAQ Process

The first thing to note when considering the changes we will need to make, is that the basic infrastructure for this new capability already exists. We already have a structure where we are dynamically changing the timeout event’s timeout.

  1. When it the acquisition process starts, the timeout is set to zero.
  2. If initialization is successful, the timeout is set to 1 so acquisition is started.
  3. Each time a new datapoint is acquired and sent to the GUI, the timeout is set to the value read from a FGV.

All we have to do is expand on this existing functionality, and the first step in that process is to change the behavior in Item 2 above. Currently, the code wants to start acquiring data as soon as it can, so when the error check finishes, the timeout is set to 1. We want to change that behavior because we don’t want the process to start acquiring data until it’s turned on. To implement that change requires a single key-stroke to change the “1” to a “-1”.

Now that the code is set to wait until it is enabled, we need a way to turn it on – and by extension turn off all the processes that are not being enabled. Hopefully, you are thinking ahead and already see where I am going with this: another UDE. Let’s call it something logical like Change Source, and make the data it carries a string named Source Name. Normally, you might think that this would be a place to use something like an enumeration, and normally you would be right. However, there are exceptions to all rules, and as you will see this is an interesting exception.

With the new UDE created, here is its event handler…

the Change Source event handler

…and as you can see it couldn’t be simpler. We compare the Source Name value to the contents of a string control called My Name and if they match, the timeout is set to 1, otherwise it’s set to -1. Because all three data sources will incorporate this same logic, the effect is that only one of the three – the one whose name matches the Source Name value – will be enabled.

But where does My Name come from? I added this control to the front panel and connected it to a terminal on the connector pane. We will need to modify the startup code to populate this just before running the process VI – and that’s our next stop.

Oh, one more thing: Now that we have the acquisition process modifications done,(yes, that is all there is to it…) I made two copies of it changed the data generation code in the copies to produce a ramp and a sine wave, and added the new acquisition VIs to the project.

Passing a Startup Value

To this point, the process VIs haven’t needed any information when they were launched, but now the acquisition processes at least need to know in essence “who they are”. Here is the modified testbed.vi code:

modified launcher for passing identification string to plugin

The VI reference to the Open VI Reference node now shows the added string input terminal, which also appears on the Start Asynchronous Call node. To that new input, I have wired the Label value from the launch process data. Remember that, it will become important in just a moment.

The only other modification required is to add a VI, after the launch loop, that broadcasts a signal to the system telling it that the launch process is finished. Let’s take a quick look at why that signalling is needed and how it works.

Signalling Completion

During startup it can be critical for things to happen in the right order. Most of the time you can ensure proper sequencing by simply launching processes in the right order. Sometimes, however, conflicting requirements can leave you with no “right order”. As you might imagine, there are many possible scenarios requiring this sort of synchronization, and just as many potential solutions from which to choose. Moreover, as with Boolean logic, the “correct” solution will sometimes depend on how you are thinking about the problem.

In our testbed, we see a common situation: I like to have the display process launch very early in the startup process. In fact, the only thing that will typically launch before the GUI is the exception handler – which always comes first because the error handling has to be in place before you do anything. The GUI comes next because it is often the logical place to initialize common system resources that the acquisition processes will need when they start. The conflict lies in the need for the GUI to initialize its own front panel. Now that there are three acquisition processes running, the GUI had to select which one to display first. However, before one can be selected, the process VI has to be running and it won’t launch until after the GUI.

The solution to this conundrum is another type of “stop-start” situation. We need to let the GUI initialize everything except its front panel and then make it pause and wait until it receives a signal telling it that the launcher is done. Once that signal is received, the GUI can finish its initialization and commence normal operation.

To implement this functionality in a generic, reusable way, I have created two simple VIs that encapsulate a named LabVIEW notifier. Here is the routine that waits for the notification:

vi to wait on startup notification

The VI is contained in a library that creates a name space for the logic. The VI first acquires a reference to a notification, the name of which is derived from the name of the library containing it &ndash: in this case Launch Completed. It then waits for that notification to occur, but with a timeout specified. To make this code easier to use, I modified the API slightly to allow the developer to define the timeout in units of time. Finally, if the wait ends in a timeout, I generate an error to that effect.

The VI for sending the notification is equally simple:

vi to send the startup notification

Located in the same library as the wait VI, the sender also derives the notification name from the library name and then sends the notification. An important point to remember is that notifiers are often likened to queues because on the surface they seem very similar. However, in addition to the fact that a new notification will overwrite an old one, there is another big difference. If you have multiple listeners monitoring the same queue, LabVIEW channels the enqueued data to the various VIs in round-robin fashion. For example, say you have three VIs all pulling data from the same queue, any one VI will only see every third item enqueued. By contrast, a notifier works more like an event in that if you have three VIs waiting for notifications the same notifier, they will all see every notification.

Adding Control to the GUI

So to recap, we have modified our existing acquisition process, created two new ones using it as a template, and modified the startup logic to pass a new piece of data that the acquisition processes need. The only thing left now is to update the GUI.

Start on the front panel (always a good place to start), add a test ring control and label it Data Source. But a text ring in the current state isn’t very useful because it doesn’t contain anything. To fix that, we will (using a property node) insert into the control an array of strings that we want to appear as selections. And where do we get the array of strings? Here’s the code:

initializing the ring control

Using data from the same VI that the launcher used to obtain a list of startup processes, we generate an array of all the process labels, delete the first two elements (as discussed earlier, the exception handler and display processes) and write the remainder of the array to the ring control’s Strings[] property. The result is a popup menu containing the names of the acquisition data sources.

Next, we add an event case for a value change event on the ring control.

ring control Value Change event handler

The ring value is a numeric, so the event handler uses it to index an element from the array of process labels, and uses the resulting string to fire the Change Source event. This is why we can use a string as the event data: Both the string we are using the fire the event, and the strings we are using to tell the processes who they are, derive from the same source: the process labels. Consequently, it is impossible for them to not match.

Finally, we need to look at the GUI initialization logic.

wait for the notification, then finish GUI initialization

The first subVI called is the wait routine discussed above. When that notification is received, all the processes are loaded so the GUI can finish initializing itself by firing two value change events: one to set the sample period and a second to select the initial data source.

Summing Up

As usual, the completed code is below and when you run it you will now see that you can select between the three data sources we discussed. However, there is another lesson to be learned. I started this discussion saying that one might feel justified in saying that it would be a major undertaking to make such fundamental changes in the way an application operates. But we have just seen that it really is not. I can honestly say that describing what I did took much longer than making the modifications – and the reason is simple. I didn’t have to write very much code. Instead I was able to leverage features that were already built into the code, and reuse ideas that had already served me well in the past.

The sort of maintainability that allows our code to be nimble and not brittle should be a goal of all that you do, but it takes practice. Practice, and an understanding that learning never stops because there is always more to learn about the craft that we practice.

http://svn.notatamelion.com/blogProject/testbed application/Tags/Release 8
http://svn.notatamelion.com/blogProject/toolbox/Tags/Release 4

Next time, we are going to take this line of work a step further and consider a more complex case. Sometimes you don’t want the data sources to stop acquiring data when they are “inactive”. Rather you want them to continue monitoring their respective inputs even when they are running in the background. But, how do you then manage the data transfer to the GUI? Well, perhaps the best solution for effectively displaying process data on the GUI is to never pass process data to the GUI. Sound confusing?

Until Next Time…

Mike…

One quick “PS”. Now that I am getting a good number of these posts done, I have been thinking I would like to have them available in languages other than English. I’m not exactly sure how it would work yet, but if you would like to help with the effort, please contact me.

Talking to a Database from LabVIEW

Posted on by mlportersr

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.

Read Processes to Launch

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.

Store Error to Database

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:

Get Appl_ID

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.

Read Default Sample Period

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…

Read Misc Setup Values

…and then make the VIs like Configuration Management.lvlib:Get Default Sample Period.vi simple wrappers for the buffer.

Read Default Sample Period - Improved

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…

Building a Testbed Database

Posted on by mlportersr

As we continue our exploration of using databases to store data in your applications, the next thing we need to do is consider what the basic structure off a database should look like and how to construct a simple one that would be suitable for our Testbed application. Considering what we have assembled so far, we can immediately see two existing areas where a database would be useful:

  • Lookup for processes to launch: Currently this information is being stored in the INI file.
  • Recording errors that occur: Currently errors are being logged to a test file that is stored in the same directory as the application.

In both cases, moving this data into a database will offer a number of benefits in terms of configurability and reliability. But in addition to these two existing opportunities, there is a third value to implement that is new.

  • The default sample period: In the existing code, this value is defined by the default value of a control on the front panel of the Display Data process. This technique works, and in many case is a perfectly fine solution, but there are also cases where your customers will want to be able to change a value without you needing to modify the code. So we’ll make that change too.

As we go through the following discussion, an important point to remember is that project development is — in addition to everything else — a process of ongoing refinement. As you work through a project you are always going to be learning more about what it is that you are trying to accomplish and you need to be willing to incorporate those “lessons-learned” in your code. Of course this point assumes that your code is modularized such that this sort of “mid-course correction” can be implemented without doing violence to what you already have in place.

In order to break this work up into bite-sized pieces, this post will address the structure of the database itself. The VIs needed to implement this configurability will be addressed in the next installment.

Why be Normal(ized)?

According to Wikipedia, normalization (in the database sense) is “…the process of organizing the fields and tables of a relational database to minimize redundancy.” Basically, this means that a given piece of information, like a person’s name, should only be stored in one place in the database. Normalization is important because it is one of the major ways that databases ensure consistency in the data they contain. For example, if an operator’s name is only stored in one place, you don’t have to worry after the fact if “Robert Porter” and “Robert Portor” are really the same person.

Beyond this basic explanation, there is a lot of detail that we don’t need to discuss right now. In fact, there are whole books written on the topic of normalization. For your further reading, I have included links to two very good ones are at the end on the post. Our goal right now is to simply cover the basics so as you can understand what a real database developer puts together for you. Moreover, if the need should ever arise for you to create a database for yourself, this information certainly won’t make you an expert, but it will help you avoid a few major pitfalls.

The first step in creating a database is called Data Modelling. Data modelling is a process of looking at your data in order to understand its inherent structure. Ideally, data modelling should be something that you do early in your design process, regardless of whether you are going to be using a database. If you think about it, this point is particularly true when doing LabVIEW development. After all, LabVIEW’s core paradigm is one of dataflow, so how can you expect to properly define an application’s dataflows if you don’t understand the data’s inherent structure? Naturally therefore, when starting work on this testbed, I did some basic data modelling. Let’s go over what I discovered.

Supporting Many Applications

When studying the data that an application needs to handle, a good place to start is with a list of the data items that the code will need to complete the intended tasks. Some of this information will be common to all the tasks the code will be supporting, and some will be unique. At first glance, it might not seem like there is any information that the various functions have in common, but if we think ahead a bit, we see that there is one. To this point, we have been talking about the program that we are creating, as if it is the only one we are ever going to build and install on a user’s computer. But is this really a reasonable assumption?

In a way, this is sort of like talking about a computer that will only have one program installed on it. In addition, we have talked before about the advantages of seeing an “application” as the aggregation of the independent behaviors of many different processes that operate more or less independent of one another. It seems to me unlikely that the assumption we have been making will continue to hold up in the future.

In the world governed by INI files, this reality is addressed by simply having a separate INI file for each program. Databases accomplish the same task by adding a field that identifies which application is associated with (or in database-speak, related to) each data record. But how should these relationships be recorded in the database? Well, we could just include a field in each table called something like application_name, but this solution has two problems. First, remembering what we said about normalization, this approach would produce a lot of redundant data and a lot of opportunities for things to be misspelled, or mislabeled. Second, since we would be using that value to qualify the queries we perform in order to extract just the data related to one particular application, those string fields will end up being search terms and indexing or searching strings is inherently very inefficient.

Both problems are solved by creating a separate table that uniquely identifies each application. Primarily, it would hold the application name, but could contain other information such as version number, who wrote it, and when it was modified. In this table, each application will have one record and each record has a unique number associated with it called the Primary Key. Any other table that needs to relate to its data to a specific application can simply include a field that holds this number — and numbers are very fast to search and index. Here is the SQL code needed to create this type of table, sometimes called a header table:

CREATE TABLE appl (
    id       AUTOINCREMENT PRIMARY KEY,
    label    TEXT(40) WITH COMPRESSION,
    ver_nbr  TEXT(10) WITH COMPRESSION,
    dev_name TEXT(50) WITH COMPRESSION
  )
;

The first line contains the command (CREATE TABLE) and specifies the new table’s name (appl). Everything between the parentheses is a comma-delimited list of the columns that the table will contain. Each column definition consists of a column name and a definition of the column’s contents. Note that column names can contain spaces, but it complicates the syntax, so I don’t use them.

The first column is named id and the column definition presents the Jet DBMS syntax for identifying a column that you want to be an automatically generated integer primary key. Other DBMS also have mechanisms for accomplishing the same thing, but the syntax varies. The next three columns are defined as variable-length text fields of differing sizes. Note that defined in this way, the indicated count specifies the maximum number of characters that the field can contain, but the DBMS only stores the actual number of characters you put in it. Finally, the WITH COMPRESSION keywords are needed because a few versions ago, Jet was converted to storing UNICODE characters, which are 2-bytes long. The effect of this change is that ASCII strings started taking up twice as much memory as was needed. The WITH COMPRESSION keywords tell Jet to store the strings as 1-byte values.

One more point. The last field (dev_name) is intended to contain the name of the developer that last modified the application. But, wouldn’t what we said earlier about normalization apply to this information as well? Yes it would. To be formally correct, there should be another table of developers to which this table should relate. However, I put this in here to highlight the important point that in database design — as with LabVIEW programming — there trade-offs and so we need to ask ourselves whether the added normalization provides sufficient benefit to justify the added complication of another table. In this case, the answer I came up with was, “No”.

Processes to Launch

When we consider the information needed to launch the startup processes with the desired user feedback, we find that there are three things we need — plus of course a reference to the application identified in the table we just created. This is what we have.

  1. appl_id: This is the numeric reference to the table identifying the applications.
  2. label: This is the string that will be displayed to the user while the process is being launched. Before it was derived from the name of the VI. This approach is more flexible.
  3. item_path: As when this data was stored in the INI file, this string is a relative path to the VI to be launched.
  4. item_mode: This string indicates the mode used to launch the process VI. It is associated with an enumeration in LabVIEW that (for now at least) simply indicates whether the VI is reentrant or non-reentrant.
  5. launch_order: This is an integer column we will use to sort the query results so the process VIs are launched in the right order.

So with this information in hand we are ready to define our next table, right? Well, not quite. We need to consider another assumption that we have made. To this point, the code we have created only uses VIs that are dynamically loaded in one place: at program startup. Will this assumption always be true? Simple logic would say that dynamic loading is a very powerful technique, so we will very likely be wanting to use it in the future. So let’s create another header table that will store a string indicating the condition where the VI will be launched. Right now the table will only have a single value in it, “Startup”.

Turning all this discussion into SQL commands to create the needed tables, this code creates the new header table:

CREATE TABLE launch_cond (
    id                AUTOINCREMENT PRIMARY KEY,
    launch_condition  TEXT(128) WITH COMPRESSION
  )
;

…then this code inserts the one record we need now…

INSERT INTO launch_cond (launch_condition) VALUES ('Startup');

…and finally we can create the table for holding the launch items:

CREATE TABLE launch_item (
    id              AUTOINCREMENT PRIMARY KEY,
    appl_id         INTEGER NOT NULL,
    launch_cond_id  INTEGER NOT NULL,
    label           TEXT(40) WITH COMPRESSION,
    item_path       TEXT(128) WITH COMPRESSION,
    launch_mode     TEXT(40) WITH COMPRESSION,
    launch_order    INTEGER,
    CONSTRAINT launch_cond_FK FOREIGN KEY (launch_cond_id) REFERENCES launch_cond(id),
    CONSTRAINT launch_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

This table contains all the same stuff we saw in the other table creation commands, but with a couple additions. The last two items in the column list aren’t columns. Rather, they create the relational links between this table and the two header tables by defining a pair of foreign key constraints. The name of the constraints are important because they will be used in error messages associated with the constraint. The constraint definitions themselves are straight-forward, specifying a column that references a specified column in a specified table.

Storing Errors

The foregoing case demonstrates how to deal with tables that store configuration data. Now we turn our attention to a table that the application writes to while running. Specifically, we will look at the table for storing errors that occur during program execution. Here are the pieces of information we know we will need going in:

  1. appl_id: This is the numeric reference to the table identifying the applications. In this case it is the application that generated the associated event.
  2. evt_dttm: This field holds timestamp values indicating when the events occurred.
  3. evt_type_id: In addition to knowing when an event occurred, you also need to capture what type of event it was. Starting off we can imagine three types of events (errors, warnings and notes) but there could be more, so we’ll store the type in another header table and reference it here.
  4. evt_code: This integer column hold the error code from the LabVIEW error cluster.
  5. evt_source: Another text column, this field holds the Source string from the LabVIEW error cluster.

First comes the new header table and its three records. Note that these event types correlate to the three values of a LabVIEW enumeration (Event Types.ctl).

CREATE TABLE event_type (
    id     AUTOINCREMENT PRIMARY KEY,
    label  TEXT(40) WITH COMPRESSION
  )
;

INSERT INTO event_type (label) VALUES ('Error');
INSERT INTO event_type (label) VALUES ('Warning');
INSERT INTO event_type (label) VALUES ('Note');

And then the table for storing the events…

CREATE TABLE event (
    id             AUTOINCREMENT PRIMARY KEY,
    appl_id        INTEGER NOT NULL,
    event_type_id  INTEGER NOT NULL,
    evt_dttm       DATETIME DEFAULT now() NOT NULL,
    evt_code       INTEGER,
    evt_source     MEMO WITH COMPRESSION,
    CONSTRAINT event_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id),
    CONSTRAINT event_event_type_FK FOREIGN KEY (event_type_id) REFERENCES event_type(id)
  )
;

The first new idea on display here is in the definition of the evt_dttm field. In addition to stating the data type (DATETIME), it also specifies that the field cannot be empty (NOT NULL) and tells Jet what value to use if an insert statement does not contain a time value: now(). This built-in Jet constant returns the current date and time. You also see that I introduced a new datatype MEMO to hold the textual description of the error. Given that it uses the same WITH COMPRESSION keywords as we use with the TEXT, you might assume that it is another way of storing textual data — and you’d be right. The difference between the two is that while both are variable length fields, a TEXT field can only hold a maximum of 255 characters. By contrast a MEMO field can (in theory at least) hold strings as long as 2.14-Gbytes.

The Default Sample Period

Finally there are always simple, unstructured setup values, like the default sample period, so let’s set up a table for them too. Note that I picked column names that reflect the simple organization of an INI file with sections, keys and values.

CREATE TABLE misc_setting (
    id         AUTOINCREMENT PRIMARY KEY,
    appl_id    INTEGER NOT NULL,
    p_section  TEXT(50),
    p_key      TEXT(50) WITH COMPRESSION,
    p_value    TEXT(255) WITH COMPRESSION,
    CONSTRAINT miscsettings_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Data Acquisition','Sample Period','1000'
     FROM appl
    WHERE label = 'Testbed'
;

The syntax used for inserting the record we need is a bit different because it has to look up the value that will go into the appl_id field using something called a subquery. The syntax for the Jet implementation of this type of operation is rather obscure and is in no way compliant with the SQL standard, but this is what it looks like. Unfortunately, it is not the only place where Jet runs counter to the standard. For example, getting the event table to automatically insert the event timestamp value using by using a default value of now() sidesteps the problem of formatting time values, which is also very non-standard.

A Few More Things

So there we have it: the structure for our simple database — or as much of it as we need right now. But you may be wondering what you are supposed to do with all that SQL code? I’m glad you asked. I have created a simple utility that executes the code to create the database, and you can find it (and the complete SQL source code file) here:

http://svn.notatamelion.com/blogProject/local database builder/Tags/Release 1

When you run the utility, it builds the new database file in the directory where the source code file is located. While I’m linking to things, it can be helpful sometimes to simply open a database file and look at its contents. If you have Access installed on your computer you can look at the resulting database file immediately, otherwise I can recommend a small, lightweight utility called Database .Net. It doesn’t require an installer, supports a bunch of different DBMS, and does not impact a computer’s registry so can run from anywhere. I like to keep it on a USB thumb-drive in case I need to take a quick look at the contents of a database.

Finally, here are a couple more links for books that I recommend for learning more about the issues we rushed through in this post. I really like The Practical SQL Handbook because it covers all the important points in a readable, entertaining way. A dry college text, this is not. Designing Quality Databases With IDEF1X Information Models, on the other hand, is a college text that goes into a tremendous amount of detail on the topic of data modelling and a common technique for documenting what you find — which of course makes it very through. This book is not a quick read, but it is worth the effort.

All we have left to cover now is the LabVIEW side of the job, which we’ll get into with the next post.

Until next time …

Mike…

Managing Data — the Easy Way

Posted on by mlportersr

As I work on this blog there are times when I have to put certain topics on hold because the infrastructure doesn’t yet exist to allow me to effectively cover the topic. Well, this is one of those times. What I want to do is start getting into some topics like advanced user interfaces. However, these capabilities presume the existence of a data management capability that our testbed does not yet possess. To begin filling in that blank, the next few posts are going to start looking at techniques for utilizing databases as a data storage mechanism for LabVIEW-based applications.

But why databases? Wouldn’t it be easier to store all our configuration or test data in text files? When considering the storage of any sort of data the first thought for many developers is often to just, “…throw it in a text file…”. Therefore, this question needs to be the first thing that we discuss.

The Ubiquitous INI File

The INI file, as a method for storing configuration data, is a standard that has been around for a long time. Its main advantage is that it is a simple human-readable format that anyone with a text editor can manipulate. However, INI files also have a significant downside, and its number one problem is security. Simply put, INI files are inherently insecure because they use a simple human-readable format that anyone with a text editor can manipulate. Anybody who knows how to use Notepad can get in and play around with your program’s configuration, so unless you are very careful in terms of data validation, this openness can become an open door to program instability and failure.

A second issue is that while INI files can be subdivided into sections, and each section can contain key name and value pairs; beyond that simplistic formatting they are largely unstructured. This limitation shouldn’t be a surprise, after all the format was developed at a time when programs were very simple and had equally simple configuration requirements. However, with many modern applications one of the big challenges that you have to face when using INI files is that it is difficult, if not impossible, to store configuration data that exhibits internal relationships.

For example, say you have 2 or 3 basic types of widgets that you’re testing and each type of widget comes in 2 or 3 variants or models. In this scenario, some of the test parameters will be common for all widgets of a specific type while others are specific to a particular model. If you want to capture this data in a text file, you have two basic options. First you can put everything in one big table — and so run the risk of errors resulting from redundant data. Second, you can store it in a more normalized form and try to maintain the relationships manually — which is a pain in the neck, and just as error prone.

Text Data Files?

OK, so text files aren’t the best for storing configuration data. What about test data? Unfortunately, you have all the same problems — in spades — plus a few new ones: For example, consider Application Dependency.

Application dependency means that the data format or structure is specific to the program that created the file. Basically, the limitation is that outside programs have to know the structure of your file ahead of time or it won’t be able to effectively read your data. Depending upon the complexity of your application, the information required to read the data may expose more about the inner workings of your code than you really feel comfortable publishing.

Another problem is numeric precision. You can’t save numbers in a text file, just string representations of the numbers. Consequently, numeric precision is limited to whatever the program used when the file was created. So if you think that all you need when saving the data is 3 decimal places, and then find out later that you really need 4, you’re pretty much hosed since there is no way to recreate the precision that was thrown way when the data was saved.

Finally, data in a text file usually has no, or at least inadequate, context. Everybody worries about the accuracy of their test results, but context is just as important. Context is the information that tells you how to interpret the data you have before you. Context includes things like who ran a test, when it was run, and how the test was configured. Context also tells you things like what unit of measure to use in reading numeric data, or when the instruments were calibrated.

The Case for a Database

Due to all the foregoing issues, my clear preference is to use databases to both manage configuration data and store test results. However some LabVIEW developers refuse to consider databases in a misguided effort to avoid complication. Out of a dearth of real information they raise objections unencumbered by facts.

My position is that when you take into consideration the total problem of managing data, databases are actually the easiest solution. Of course that doesn’t mean that there won’t be things for you to learn. The truth is that there will be things to learn regardless of the way you approach data management. My point is that with databases there is less for you to learn due to the outside resources that you can leverage along the way. For one simple (but huge) example, consider that you could figure out on your own the correct ways to archive and backup all your test data — or you could put the data into a database and let the corporate IT folks, who do this sort of thing for a living, handle your data as well.

So let’s get started with a few basic concepts.

What is a DBMS?

One common point of confusion is the term DBMS, which stands for DataBase Management System. A DBMS is software that provides a standardized interface for creating, maintaining and using databases. In a sense, the relationship between a DBMS and a database is exactly the same as the relationship between a word-processor, like Microsoft Word, and a letter home to your Mom. Just as a word-processor is a program for creating textual documents, so a DBMS can be seen as a program for creating databases. One big difference though, is that while word-processors are programs that you have to explicitly start before you can use them, a DBMS will typically run in the background as a Windows service. For example, if you are reading this post from a Windows-based computer, there is at least one DBMS (called Jet) running on your computer right now.

What is a database?

A database is a structured collection of data. In that definition, one word is particularly important: “structured”. One of the central insights that lead to the development of databases was that data has structure. Early on, people recognized that some pieces of information are logically related to other pieces and that these relationships are just as important as the data itself. The relationships in the data are how we store the data context we discussed earlier.

By the way, as an aside, people who talk about an “Access Database” are wrong on two counts since Access is neither a database or a DBMS. Rather is it an application development environment for creating applications that access databases. By default, Access utilizes the Jet DBMS that is built into Widows, but it can access most others as well.

How do you communicate with a database?

It wasn’t that long ago that communicating with a database from LabVIEW was painful. The first product I ever sold was an add-on for the early Windows version of LabVIEW that was built around a code interface node that I had to write using the Watcom C compiler. Actually, looking back on it, “painful” is an understatement…

In any case, things are now dramatically different. On the database side the creation of standards such as ODBC and later ADO (also called OLE DB) provided standardized cross-vendor interfaces. For their part, National Instruments began providing ways of accessing those interfaces from within LabVIEW (starting with Version 5). Today accessing databases through ActiveX or .net interfaces is a breeze. To demonstrate this point, I’ll present a package of drivers that I have developed and posted on the user forum several years ago.

Getting Connected…

The VIs we will now discuss are the core routines that you will need to interact with any DBMS that supports an ADO interface — which is basically all of them. The only common DBMS that doesn’t support ADO is SQLite. Instead, it has its own DLL that you have to access directly. Still, if you want a very lightweight database engine that will run on nearly anything (including some real-time hosts) it is a good choice and there are driver packages available through the forum.

Getting back to our standard interface, the following six routines provide all the support most applications will ever need. One thing to notice is that with the exception of one subVI that requires added code to work around a bug in certain version of SQL Server, most of the code is very simple, which is as it should be.

To start that exploration, we’ll look at the routine that is the logical starting place for any database interaction — and incidentally is the only routine that cares what database you are using.

ADO Database Drivers.lvlib:Acquire Connection.vi

The start of any interaction with a database, naturally involves establishing a connection to the DBMS that is managing it, and then identifying which specific database you want to use. This VI calls the ADO method that performs that operation.

Open Connection.vi

You’ll notice that the ActiveX method only has one required input: the Connection String. This string is a semicolon-delimited list of input parameters. Although the exact parameters that are required depends on the DBMS you are accessing, there is one required parameter, Provider. It tells the ADO functionality what driver to use to access the DBMS. This is what a typical connection string would look like for connecting to a so-called Access database.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=;Password=;

Note that in this case the Data Source consists of a path to a specific file. For another DBMS this same parameter might point to an IP address, a logical name the driver defines or the name of a Windows service. But what if you don’t know what connection string to use? Well you can ask the DBMS vendor — or you can check: www.ConnectionStrings.com. Yes, there is an entire website dedicated to listing connection strings, and it covers nearly every ADO-compatible DBMS on the planet.

ADO Database Drivers.lvlib:Execute SQL Command.vi

OK, you are connected to your DBMS and you have a reference to your database. The next question is what do you want to do now? One answer to that question would be to send a command to the DBMS telling it to do something like create a new table in which you can store data, or add a new record to an existing table. This VI meets that need by sending to the DBMS command strings consisting of statements written in a language called SQL.

Execute SQL Command.vi

The core of this routine is the connection Execute method. Because this method could be used to execute a command that returns data, the 0x80 input optimizes operation by telling the method to not expect or handle any returned data. The method, instead, returns a count of the number of rows that the last command impacted.

ADO Database Drivers.lvlib:Create and Read Recordset.vi

While sending commands is important, the most common thing to do with a database connection is to use it to read data from the database. To optimize operation when you have multiple users concurrently accessing the same database, ADO creates something called a recordset. A recordset is a memory-resident copy of the requested data that you can access as needed without requiring further interaction with the database itself.

Create and Read Recordset

The three subVIs shown create a recordset using a source string consisting of an SQL query, reads the recordset’s contents, and then closes the recordset to free its memory. For details of how these subVIs work, checkout the comments on their block diagrams.

ADO Database Drivers.lvlib:Release Connection.vi

Once you are finished using a connection, you need to tell ADO that you are done with it.

Close Connection.vi

Note that although the method called is named Close, it doesn’t really close anything. Thanks to built-in functionality called connection pooling the connection isn’t really closed, rather Window just marks the connection as not being used and puts it into a pool of available connections. Consequently, the next time there is a request for a connection to the same database, Windows doesn’t have to go to the trouble of opening a new connection, it can just pull from the pool a reference to a connection that isn’t currently in use.

ADO Database Drivers.lvlib:Start Transaction.vi

A topic that database folks justifiably spend a lot of time talking about is data integrity. One way that a DBMS supports data integrity is by ensuring that all operations are “atomic”, i.e. indivisible. In simple terms, this means that for a given operation either all the changes to the database are successful, or none of them are. This constraint is easy to enforce when inserting or modifying a single record, but what about the (common) case where a single logical update entails adding or modifying several individual records?

To handle this situation, DBMS allows you to define a transaction that turns several database operations into a single atomic operation. This VI marks the start of a transaction.

Start Transaction.vi

To mark the other end of the transaction, you have to perform either a Commit (make all the changes permanent) or a Rollback (undo all the changes since the transaction’s start).

ADO Database Drivers.lvlib:Rollback Transaction on Error.vi

This VI combines the Commit and Rollback operations into a single routine. But how does it know which to do? Simple, it looks at the error cluster. If there is no error it commits the transaction…

Commit Transaction on no Error

…but if there is an error it will rollback the transaction.

Rollback Transaction on Error

What’s Next

Clearly the foregoing six VIs are not a complete implementation of all that is useful in the ADO interface — for instance, they totally ignore “BLOBs”. However I have found that they address 95% of what I do on a daily basis, and here is a link to the new version of the toolbox that includes the drivers.

Toolbox Release 3

But let’s be honest, having an appropriate set of drivers is in some ways the easy part of the job. You still need a database to access and you need to know at least a bit about the SQL language. This is where what I said earlier I said about leveraging the time and talents of other people come into play. Your corporate or institutional IT will often assist you in setting up a database — many times, in fact, they will insist on doing it for you. Likewise, with an interface that is based on command strings written in SQL, you will find a wealth of talent willing to lend a hand, perhaps in-house but certainly online. Still, having said all that, it’s good for you to understand at least a bit about how these other aspects operate. Therefore, next time we’ll create a small local database (using Jet) and start implementing the data management for our testbed application.

Until next time…

Mike…