Example Code

How to Make a Parameterized Query into a Stored Procedure Routine with MySQL Databases

Code and Documents

Attachment

When developing applications that communicate to databases, it is not uncommon to find developers using "routines" or "stored procedures" to provide custom functions to automate processes and interact with that database.  For example, let's say you have a database that stores information about members in a particular organization, and you'd like to track members who have not paid their membership fees for the month or year or what have you.  You might write a stored procedure function that searches the database for members who are late on their payments and returns their names, email addresses, and how much they currently owe.  Then, you could use software, like LabVIEW, to generate and send automatic emails to the members as a reminder to pay their fees. 

This post examines how to make such a stored procedure call to a MySQL database from LabVIEW using the Database Connectivity Toolkit.  For the sake of this example, we'll assume that a database already exists and has the characterists as listed below.  For more information about using the Database Connectivity Toolkit with MySQL, please refer to KB 42GBLU00: Using MySQL with the Database Connectivity Toolkit on Windows.

Schema (Database) Name: organization_db

Table Name: member_information

Table Data:

member_information table.png


Stored Procedure:
What's important to note in the routine below is its name, find_late_payments_routine, and its two parameters, today and minimum_due. Notice how the directions and data types have been configured for both parameters.  The code between the BEGIN and END statements marks what is actually executed when the routine gets called.  In this case, we will search the table above for all rows whose date_due field is less than the parameter today and whose amount_due field is greater than the parameter minimum_due.


find_late_payments_routine code.png

Operation:

The VI is fairly simple to understand, but there are few important points to make about it.  The Database Path input should be the path to your database.  In this example, I configured a .udl file to point to the MySQL database "organization_db". The SQL Query input for the Create Parameterized Query VI should be in the format [database].[stored procedure], which equates to organization_db.find_late_payments_routine for this example.  The parameters input of the same VI is a 1D array of parameter clusters.  the parameter cluster is made up of a string, two enums, and a variant.  You can see how this cluster is organized from the block diagram.  Lastly, be sure to use a True constant for the stored procedure? terminal.

From LabVIEW, by setting the value of the today parameter as the current date (2012-03-07 at the time of writing this post) and the minimum_due paramter as $15, running the code yields the expected Results field as follows:


results.png



Example code from the Example Code Exchange in the NI Community is licensed with the MIT license.