How Do I Set Input Parameters and Get Output Parameters from an SQL Stored Procedure with the Database Connectivity Toolkit? Primary Software: LabVIEW Toolkits>>Database Connectivity ToolsetPrimary Software Version: N/A Primary Software Fixed Version: N/A Secondary Software: N/A
Problem: I want to use a stored procedure with the LabVIEW Database Connectivity Toolkit. I would like to use the Input/Output parameters functionality that is available with this toolkit, but I am not sure what functions I need to use or what SQL query I need to send to the Database Tools functions. Solution: In the Database Connectivity Toolkit, you can set input parameters and get output parameters. You must first open the connection to the database using the DB Tools Open Connection VI and then create the SQL query using the DB Tools Create Parameterized Query VI as shown in the figure below.
If you are calling a stored procedure, you must set the Stored Procedure? node 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. You must include a question mark for each parameter that you would like to work with, or you can use the functionality of the Auto-Detect Parameters node. The parameter input is an array of cluster (Name, Data Type, Direction, Initial Value) where each array element represents a column or field in the database table. To Set an input parameter and Get an output parameter, the Direction must be set to Input/Output. You must also reserve memory space for the output parameter before the query is executed. If this is not done, the returned output parameter will likely be truncated or not returned at all. This can be accomplished by entering a default value in the Initial Value field of the cluster. This initial default value must be at least as long as the output parameter value that you are expecting. The Initial Value is a Variant data type. Editing this value can be tricky. The following steps describe a good way to edit variant type data. Open a blank VI » create a string constant » connect a To Variant function » attach an indicator » run the VI » change indicator to constant » you now have a variant constant that you can add to the array of cluster. After setting up the query, you must then set the input parameters using the DB Tools Set Parameter Value VI before running the query with the DB Tools Execute Query VI. Before getting the output parameter values from the stored procedure, you must 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. 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 as illustrated below. 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.
Related Links: KnowledgeBase 3229FMV3: How Do I Get Return Values of Stored Procedures in the Database Connectivity Toolkit? Developer Zone Example: Calling an SQL Stored Procedure Using the Database Connectivity Toolset Product Manuals: Database Connectivity Toolset User Manual Attachments:  SQL Input_Output.vi
Report Date: 06/20/2007 Last Updated: 07/20/2007 Document ID: 4AJCSSLC |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
