Set and Get Input and Output Parameters from a SQL Stored Procedure

Issue Details

I want to use a stored procedure with the LabVIEW Database Connectivity Toolkit.  I would also like to use the Input/Output parameters functionality that is available with this toolkit. How do I set these input parameters and read the output parameters while using a stored procedure?

Solution

The following VI snippet demonstrates both how to set the input parameters and read the output parameters (recordset) using an SQL stored procedure.

Note: This image is a LabVIEW snippet, which includes LabVIEW code that you can reuse in your project. To use a snippet, right-click the image, save it to your computer, and drag the file onto your LabVIEW diagram.

1. Open the connection to the database using the DB Tools Open Connection VI.
2. Create the SQL query using the DB Tools Create Parameterized Query VI.
3. To call a stored procedure, set the Stored Procedure? input to True on the DB Tools Create Parameterized Query VI. 
   • The SQL query will be in the form {call storedprocedure (?,?)}.  The question marks correspond to the parameters that you are setting or returning. Include a question mark for each parameter that you would like to work with, or use the functionality of the Auto-Detect Parameters input.
   • The parameter input is an array of Clusters (containing Name, Data Type, Direction and Value items) where each array element represents a column or field in the database table.  To set an input parameter and get an output parameter, set the Direction to Input/Output.
4. Reserve memory space for the output parameter before the query is executed by entering a value in the Value field of the Cluster.  
   • If this is not done, the returned output parameter will likely be truncated or not returned at all. 
   • This initial value must be at least as long as the output parameter value that you are expecting.  
5. The Value is a variant data type that contains string data. The recommended approach for defining this value is as follows:
   
   1. Create a new VI with a string control.
   2. Enter the string that you want to be the initial value.
   3. Wire the string control to a To Variant function.
   4. Create an indicator on the output of the To Variant Function.
   5. Run the VI.
   6. Change the Variant indicator to a constant.
   7. You now have a variant constant that contains string data. This can be inserted into the cluster as the Value field.
6. Set the input parameters using the DB Tools Set Parameter Value VI before running the query with the DB Tools Execute Query VI.
7. Before getting the output parameter values from the stored procedure, extract the recordset data with the DB Tools Fetch Recordset Data VI and then clear the reference to the recordset with the DB Tools Free Object VI.  You may then use the DB Tools Get Parameter Value VI to retrieve the output parameter values as specified in the index.  
8. The reference to the query can then be closed and the connection to the database terminated.  The recordset that is extracted is of the variant data type and can be converted to string data.
   • If the output parameter is not one of the data types listed on the above VI, you may use Binary as the data type to get data from the output parameter. However, once the binary data is in LabVIEW it is up to the developer to convert the data to a meaningful data type.