LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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

Solved!
Go to solution

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

 

0 Kudos
Message 1 of 10
(5,519 Views)
Solution
Accepted by topic author Ola_A

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.

Example_VI_BD.png

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 2 of 10
(5,500 Views)

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.

0 Kudos
Message 3 of 10
(5,490 Views)

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!2016-05-03 11_41_05-test TSUT SampleStatusDates v1.vi Block Diagram on TSUT test.lvproj_My Computer .png

0 Kudos
Message 4 of 10
(5,463 Views)

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.  Smiley Wink

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 5 of 10
(5,445 Views)

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.  

 

Capture.PNG

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
Message 6 of 10
(5,441 Views)

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 Smiley Happy

 

 

2016-05-03 15_57_51-RunSQLCommand4.vi Block Diagram on TSUT test.lvproj_My Computer _.png

0 Kudos
Message 7 of 10
(5,430 Views)

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?

 

2016-05-04 14_14_02-test TSUT SampleStatusDates v1.vi Block Diagram on TSUT test.lvproj_My Computer.png

0 Kudos
Message 8 of 10
(5,406 Views)

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?  

aputman
------------------
Heads up! NI has moved LabVIEW to a mandatory SaaS subscription policy, along with a big price increase. Make your voice heard.
0 Kudos
Message 9 of 10
(5,383 Views)

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.

0 Kudos
Message 10 of 10
(5,331 Views)