05-02-2016 08:18 AM
I like to test some MySQL stored procedures that have OUT parameters. In Toad for MySQL I can write
CALL CleverProcedure(42, @var); SELECT @var;
And I will get the value of @var out parameter. If I try the same in my standard, simple "RunSQL.vi" I get syntax errors when using more than one MySQL command together. I am using 'DB Tools Execute Query VI" on the Database/Advanced palette.
I guess there is some quick way to do this? It is only for testing purposes, so I don't mind the solutiong beeing quick and dirty!
/Ola
Solved! Go to Solution.
05-02-2016 11:38 AM
Use the Create Parameterized Query VI. The query text will be the name of your stored procedure and you'll need to build a parameter array for input and output parameters. Stored Procedure input should be true. Wire that into the Execute Query VI, followed by the Get Parameter Value VI.
Something like this should work (untested). You'll need to work out the connection details.
05-02-2016 01:00 PM
I don't believe the DCT has support for one-liners like that, due to a limitation in ADODB. You'll need to use the ODBC .NET library instead.
05-03-2016 04:42 AM
aputman, thanks for the solution. It works, to call the stored procedure like this, but I don't get the "DB Tools Get Parameter Value VI" to work to get the output parameter back. After the "DB Tools Execute Query VI" I still only get back the input parameter, the outputs are empty. I have the type of my output parameters as Date/time, and use the Date/Time (CR) version of the Get Parameter vi.
However I get the data back with my standard "Fetch All" + "Database Variant to Data" in a double for loop, so I can use the solution!
05-03-2016 08:36 AM
I'm glad that it is working for you. If you want to use output parameter, it sounds to me like your stored procedure is not defined properly. Make sure your output parameter is defined with the OUTPUT keyword and that you are actually writing a value into that variable. If you want to share your procedure, I can take a look at it and see what I see.
05-03-2016 08:40 AM - edited 05-03-2016 08:41 AM
Also for a little less Rube'ness on your data conversion, set the type input on the "Database Variant to Data" vi as an empty string array. Then you don't need the two nested for loops.
05-03-2016 09:02 AM
That is a clever idea, thanks!
My MySQL retreival procedure now looks like this. More comments?
I actually don't know where I got these procedures from, they normally just work. But that doesn't mean they cannot be improved
05-04-2016 07:15 AM
aputman, the Stored Procedure works, if I use it in Toad for example it performs as expected. I cannot share it with you, sorry.
One idea, there are two of possible directions other than Input and Output, namely Input/Ouput and Return Value, can one of these function better?
05-05-2016 08:31 AM
What do you mean "function better"? If it works, don't try to fix it.
The other direction inputs depend on your stored procedure. Do you have a return statement? Do you have a parameter that you pass in, modify, and output back to Labview?
05-10-2016 06:13 AM
Sorry if I am unclear. When I used the "DB Tools Get Parameter Value" I got just null when reading the output parameters. If I read the values with "DB Tools Fetch Record Set Data" I get the values I request. They are defined as OUT in MySQL.
Again, I don't have to solve this since I get the result I need.