Building a Web Backend in LabVIEW

When building a web-based application, one of the central goals is often to maximize return on investment (ROI) by creating an interface that is truly cross-platform. One of the consequences of this approach is that if you are using a LabVIEW-based program to do so-called “backend” tasks like data acquisition and hardware control, the interface to that program will likely be hidden from the user. You see the thing is, people like consistent user interfaces and while LabVIEW front panels can do a pretty good job as a conventional application, put that same front panel in a web page and it sticks out like the proverbial sore thumb.

Plus simply embedding a LabVIEW front panel in a web pages breaks one of the basic tenets of good web design by blurring the distinction between content and appearance. Moreover, it makes you — the LabVIEW developer — at least partially responsible for the appearance of a website. Trust me, that is a situation nobody likes. You are angered by what you see as a seemingly endless stream of requests for what to you are “pointless” interface tweaks, “So big deal! The control is 3 pixels too far to the left, what difference does that make?” And for their part, the full-time web developers are rankled by what they experience as a lack of control over an application for which they are ultimately responsible.

If you think about it, though, this situation is just another example of why basic computer science concepts like “low coupling” are, well, basic computer science concepts. Distinguishing between data collection and data presentation is just another form of modularity, and we all agree that modularity is A Very Good Thing, right?

It’s All About the (Data)Base

So if the concept that is really in play here is modularity, then our immediate concern needs to be the structure of the interface between that part of the system which is our backend responsibility, and that which is the “web guy’s” customer facing web-based GUI.

Recalling what we have learned about the overall structure of a web application, we know that the basic way most websites work is that PHP (or some other server-side programming tool) dynamically builds the web pages that people see based on data that they extract from a database. Consequently, as LabVIEW developer trying to make data available to the web, the basic question then is really, “How do I get my data into their database?”

The first option is to approach communications with the website’s database as we would with any other database. Although we have already talked at length about how to access databases in general, there is a problem. While most website admins are wonderful people who will share with you many, many things — direct access to their database is usually not one of those things.

The problem is security. Opening access to your LabVIEW program would create a security hole through which someone not as kind and wholesome as you, could wiggle. The solution is to let the web server mediate the data transfer — and provide the security. To explore this technique, we will consider two simple examples: one that inserts data and one that performs a query. In both cases, I will present a LabVIEW VI, and some simple PHP code that it will be accessing.

However, remember that the point of this lesson is the LabVIEW side of the operations. The PHP I present simply exists so you can get a general idea of how the overall process works. I make no claims that the PHP code bears any similarity to code running on your server, or even that it’s very good. In fact, the server you access might not even use PHP, substituting instead some other server-side tool such as Perl. But regardless of what happens on the other end of the wire, the LabVIEW code doesn’t need to change.

As a quick aside, if you don’t have a friendly HTTP server handy and you want to try some of this stuff out, there is a good option available. An organization called Apache Friends produces an all-in-one package that will install a serviceable server on just about any Windows, Linux or Apple computer you may have sitting around not being used. Note that you do not want to actually put a server that you create in this way on the internet. This package is intended for training and experimentation so they give short shrift to things like security.

The following two examples will be working with a simple database table with the following definition:

CREATE TABLE temp_reading (
   sample_dttm  TIMESTAMP PRIMARY KEY,
   temperature  FLOAT
   )
;

Note that although the table has two columns we will only be interacting directly with the temperature column. The sample_dttm column is configured in the DBMS such that if you don’t provide a value, the server will automatically generate one for the current time. I wanted to highlight this feature because the handling of date/time values is one of the most un-standardized parts of the SQL standard. So it will simplify your life considerably if you can get the DBMS to handle the inserting or updating of time data for you.

Writing to the Database

The first (and often the most common) thing a backend application has to do is insert data into the database, but to do that you need to be able to send data to the server along with the URL you are wanting to access. Now HTTP’s GET method which we played with a bit last week can pass parameters, but it has a couple big limitations: First, is the matter of security.

The way the GET method passes data is by appending it to the URL it is getting. For example, say you have a web page that is expecting two numeric parameters called “x” and “y”. The URL sent to the server would be in the form of:

http://www.mysite.html?x=3.14;y=1953

Consequently the data is visible to even the most casual observer. Ironically though, the biggest issue is not the values that are being sent. The biggest security hole is that it exposes the names of the variables that make the page work. If a person with nefarious intent know the names of the variables, they can potentially discover a lot about how the website works by simply passing different values and seeing how the page responds.

A second problem with the GET method’s way of passing data is that it limits the amount of data you can sent. According to the underlying internet standards, URLs are limited to a maximum of 2048 characters.

To deal with both of these issues, the HTTP protocol defines a method called POST that works much like GET, but without exposing the internal operation or limiting the size of the data it can pass. Here is an example of a simple LabVIEW VI for writing a temperature reading to the database.

Insert to database with PHP

This code is pretty similar to the other HTTP client functions that we have looked at before, but there are a few differences. To begin with, it is using the POST we just talked about. Next, it is formatting and passing a parameter to the method. Likewise, after the HTTP call it verifies that the text returned to it is the success message. But where do those values (the parameter name and the success message) come from? As it so happens I didn’t just pull them out of thin air.

Check out the URL the code is calling and you’ll note that it is not a web page, but PHP file. In fact, what we are doing is asking the server to run a short PHP program for us. And here is the program:

<?php
	if($dbc = mysqli_connect("localhost","myUserID","myPassword","myDatabase")) {
    	// Check connection
		if (mysqli_connect_errno()) {
			// There was a connection error, so bail with the error data
			echo "Failed to connect to MySQL: " . mysqli_connect_error();
		} else {
			// Create the command string...
			$insert = "INSERT INTO temp_reading (temperature) VALUES (" . $_POST["tempData"] . ")" ;
			
			// Execute the insert
			if(!mysqli_query($dbc,$insert)){
				// The insert generated an error so bail with the error data
				die('SQL Error: ' . mysqli_error($dbc));
			}
			
			// Close the connection and return the success message
			mysqli_close($dbc);
			echo "1 Record Added";
		}
	}
?>

Now the first thing that you need to know about PHP code is that whenever you see the keyword echo being used, some text is being sent back to the client that is calling the PHP. The first thing this little program does it try to connect to the database. If the connection fails the code echoes back to the clients a message indicating that fact, and telling it why the connection failed.

If the connection is successful, the code next assembles a generic SQL statement to insert a new temperature into the database. Note in particular the phrase $_POST["tempData"]. This statement tells the PHP engine to look through the data that the HTTP method provided and insert into the string it’s building the value associated with the parameter named tempData. This is where we get the name of the parameter we have to use to communicate the new temperature value. Finally, the code executes the insert that it assembled and echoes back to the client either an error, or a success message.

Although this code would work “as is” in many situations, it does have one very large problem. There is an extra communications layer in the process that can hide or create errors. Consider that a good response tells you that the process definitely worked. Likewise receiving one of the PHP generated errors tells you that the process definitely did not work. A network error, however, is ambiguous. You don’t know if the process failed because the transmission to the server was interrupted; or if the process worked but you don’t know about it because the response from the server to you got trashed.

PHP Mediated Database Reads

The other thing that backend applications have to do is fetch information (often configuration data) from the database. To demonstrate the process go back to the GET method:

Fetch from database with PHP

As with the first example, the target URL is for a small PHP program, but this time the goal is to read a time ordered list of all the saved temperatures and return them to the client. However, the data needs to be returned in such a way that it will be readable from any client — not just one written in LabVIEW. In the web development world the first and most common standard, is to return datasets like this in an XML string. This standard is very complete and rigorous. Unfortunately, it tends to produce strings that are very large compared to the data payload they are carrying.

Due to the size of XML output, and the complexity of parsing it, a new standard is gaining traction. Called JSON, for JavaScript Object Notation, this standard is really just the way that the JavaScript programming language defines complex data structures. It is also easy for human beings to read and understand, and unlike XML, features a terseness that makes it very easy to generate and parse.

Although, there are standard libraries for generating JSON, I have chosen in this PHP program to generate the string myself:

<?php
    if($dbc = mysqli_connect("localhost","myUserID","myPassword","myDatabase")) {

    	// Check connection
		if (mysqli_connect_errno()) {
			echo "Failed to connect to MySQL: " . mysqli_connect_error();
		} else {
			// Clear the output string and build the query
			$json_string = "";
			$query = "SELECT temperature FROM temp_reading ORDER BY sample_dttm ASC";
			
			// Execute the query and store the resulting data set in a variable (called $result)
			$result = mysqli_query($dbc,$query);
			
			// Parse the rows in $result and insert the values into a JSON string
			while($row = mysqli_fetch_array($result)) {
				$json_string = $json_string . $row['temperature'] . ",";
			}
			
			// Replace the last comma with the closing square bracket and send the
			// to the waiting client
			$json_string = "[" . substr_replace($json_string, "]", -1);
			echo $json_string;
		}
	}
	mysqli_close($dbc);
?>

As in the first PHP program, the code starts by opening a database connection and defining a small chunk of SQL — this time a query. The resulting recordset is inserted into a data structure that the code can access on a row by row basis. A while loop processes this data structure, appending the data into a comma-delimited list enclosed in square brackets, which the JSON notation for an array. This string is echoed to the client where a built-in LabVIEW function turns it into a 1D array of floats.

This technique works equally well for most complex datatypes so you should keep it in mind even if you are not working on web project. I once had an application where the client wanted to write the GUI in (God help them) C# but do all the DAQ and control in LabVIEW-generated DLLs. In this situation the C# developer had a variety of structs containing configuration data that he wanted to send me. He, likewise, wanted to receive data from me in another struct. The easy solution was to use JSON. He had a standard library that would turn his structs into JSON strings, and LabVIEW would turn those strings directly into LabVIEW clusters. Then on the return trip my LabVIEW data structure (a cluster containing a couple of arrays) could be turned, via JSON, directly into a C# struct. Very, very cool.

The Big Tease

So what is in store for next time? Well something I have been noticing is that people seem to be looking for new ways to let users interact with their applications. With that in mind I thought I might take some time to look at some nonstandard user interfaces.

For my first foray into this area I was thinking about a user interface where there are several screens showing different data, but by clicking a button you can pop the current screen out into a floating window. Of course, when you close the floating window, it will go back into being a screen the you can display in the main GUI. Should be fun.

Until Next Time…

Mike…

Leave a Reply