Execute two MySQL-commands like SET @a='foo'; SELECT @a; in Labiew?

[Ola_A]

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

[aputman · Accepted solution]

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.

[ijustlovemath]

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.

[Ola_A]

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!

[aputman]

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.  

[aputman]

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.  

[Ola_A]

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

[Ola_A]

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?

[aputman]

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?  

[Ola_A]

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.