Getting output values from MySQL stored procedures

[Pat Bremkamp]

Little by little, I'm starting to get stored procedures sorted out, and this is the next step.

I have a process where I feed in a job number, then create an invoice header and the invoice lines. I wrote it first using Alpha to execute the inserts and get the result sets and that works just fine. Now I'm trying to put it in a stored procedure. (never satisfied!)

My problem is I'd like to get the invoice number that was created back out as an output variable. I've been through the WIKI, but once you get beyond State="MA", it doesn't explain things very well.

So far, I've got a stored procedure that I've successfully tested in Navicat that is
create_invoice(IN p_jobno INT, OUT p_invno INT)

and, in Alpha, I'm trying

cnx.open("::name::sda")
argz.add("argJobno",e.rv.session.__protected__jobno)
argz.set("argInvno",0,OutputArgument)

this set statement if probably part of the problem...I've tried several variations using quotes and using the word "as"...the WIKI isn't clear and there's no example... but this is the only version I've found that doesn't give an error.

cnx.execute("call create_invoice(:argJobno,:argInvno)",argz)

This also doesn't give an error, but it also dosn't work.

Next problem is, if this did work, how do I get the value back out of the output variable?
I've tried
crs=cnx.ResultSet
crs.data(1)
but that gives an error

I've also tried
vInvnum=:argInvno
which also gives an error.

I'd appreciate it if anyone could help me with any part of this puzzle.
Thanks,
Pat

[MichaelCarroll]

Re: Getting output values from MySQL stored procedures

If you were to go to the terminal window in MySQL you should be able to :
call create_invoice()
to get the value returned to the screen.

So I believe that to do this in A5 you would:


OK = cn.open("::name::sda")
IF OK THEN
argz.add("argJobno",e.rv.session.__protected__jobno)
argz.add("argInvno",OutputArgument)

sqlStatement = <<%sql%
call add_new_user(:argJobno,:argInvno) %sql%

OK = cn.execute(sqlStatement,myargs)

sqlStatement2 = <<%sql%
call add_new_user() %sql%

OK = cn.ResultSet(sqlStatement2)
OK.Data(1)

IF .NOT. OK THEN
'lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
'save_to_file(lcErrorMsg,"c:\a5wroot\m\Error.txt",.T.)
'lcErrorMsg = "\"alert('"+ js_escape(cn.CallResult.text) + space(2) + lcErrorMsgNo + space(1) + "');\""
'Result.ErrorHTML = lcErrorMsg
END IF
cn.Close()


Regards,

Michael Carroll

www.redrocksbluesky.com
www.theminimalistrunner.com
[email protected]
435-275-5170

[Pat Bremkamp]

Re: Getting output values from MySQL stored procedures

Michael,

Have you been successful using the syntax you show?

I appreciate your answering, and I'm trying to learn from it, but I'm afraid I'm confused by a couple things, so please be patient while I ask a couple questions.

1. As I read the WIKI, you have to use argz.set() for an output variable and you have to include a dummy value that is used to indicate what type the output variable is. Can you use argz.add() without the variable instead?

2. What is the purpose of the statement OK.Data(1)?

Thanks,
Pat

[MichaelCarroll]

Re: Getting output values from MySQL stored procedures

Pat,

When I sent you the reply I was in a hurry. When I got back to my office I realized it was also CRAP! So I went to work to figure it out. By the way, there is no documentation about how to do this for MySQL databases. Just MS_SQL.

So I took the liberty to write a short tutorial on how to make this work.


Michael Carroll

------------------------------------------------
How to Return Data From Running a MySQL Stored Procedure
------------------------------------------------

The first thing to note is that all of the examples for calling store procedures in the help and in the wiki video's are for MS_SQL and will not work with MySQL.

In testing your stored procedures in MySQL it is first helpful to get it to work directly from your terminal window or from the SQL Editor in MySQL Workbench.

To do this I created a simple table and then populated it:

CREATE TABLE IF NOT EXISTS myTable (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`last` varchar(255) default NULL,
`first` varchar(255) default NULL,
`state` varchar(50) default NULL,
`zip` varchar(255) default NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;

INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('100','Gregory','Rigel','IL','30662');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('101','Duke','Yen','SD','29095');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('102','Lancaster','Valentine','AK','06988');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('103','Hobbs','Stewart','NV','23566');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('104','Mcdaniel','Dominique','IN','52695');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('105','Molina','Zorita','MA','53284');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('106','Whitfield','Stuart','IA','31454');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('107','Rosa','Anthony','CA','63758');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('108','Allison','Chloe','SD','88903');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('109','Faulkner','Gannon','HI','31014');
INSERT INTO `myTable` (`id`,`last`,`first`,`state`,`zip`) VALUES ('110','Cooper','Leah','WV','30786');

I then created a simple stored procedure to filter the table by the field "State". For this procedure I wanted to use an IN variable and an OUT variable.

DELIMITER $$
CREATE PROCEDURE count_names_bystate(
IN vstate VARCHAR(2),
OUT vcount INT)
BEGIN
SELECT count(first)
INTO vcount
FROM mytable
WHERE state = vstate;
END$$
DELIMITER ;

Next I went to the terminal window and tested the stored procedure.

Note: MySQL requires two things to take place when returning a variable. (1) CALL the stored procedure (2) run a SELECT statement. It is this peculiarity that makes this a different animal than calling a stored procedure in MS_SQL.

Getting it to run from the MySQL terminal window is critical. So here is the two commands to run and return the result from our stored procedure:

CALL count_names_bystate("CA",@vcount);
SELECT @vcount AS "count";

Now lets run it from the terminal window:


mysql> CALL count_names_bystate("CA",@vcount);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @vcount AS "count";
+-------+
| count |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)


"Cool" this is exactly what we want it to do. Now lets go do the same with xbasic from a basic a5w web page.

Here is the code that you can copy into a blank a5w page. After this page I will break the code down into its parts

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta name="generator" content="Alpha Five HTML Editor Version 10 Build 3739-3629">
<!-- must use in order to make XP Themes render -->
<meta HTTP-EQUIV="MSThemeCompatible" content="Yes" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>Testing a stored procedure and returning something cool!</title>


<%a5
dim vMsg as c = ""

dim cn as sql::connection
dim rs as sql::ResultSet
dim args as sql::arguments
dim ok as l
dim vstate as c

dim lcErrorMsg as c
dim lcErrorMsgNo as n

args.add("vstate","CA")
'args.set("vcount",@vcount,OutputArgument)

ok = cn.open("::name::minimalist")
cn.PortableSQLEnabled = .f.
if ok then
sqlStatement = <<%sql%
call count_names_bystate(:vstate,@vcount);
%sql%
OK = cn.execute(sqlStatement,args)
lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
save_to_file(lcErrorMsg,"c:\Error.txt",.T.)
if ok then
sqlStatement = <<%sql%
SELECT @vcount as count;
%sql%
OK = cn.execute(sqlStatement,args)
lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
save_to_file(lcErrorMsg,"c:\Error.txt",.T.)
if ok then
rs = cn.ResultSet
cn.close()
end if
end if
end if
rs.data(1)

IF .NOT. OK THEN
lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
save_to_file(lcErrorMsg,"c:\Error.txt",.T.)
END IF
dim mytext as c
mytext = rs.data(1)

vMsg = mytext + " is the count" 'put the count of the records here

%>

</head>
<body bgColor=#ffffff><%a5 ?"What is the count: " + vMsg %>
<br></body></html>



Ok here are my notes on how this works:

I stole this from looking at Bob Moore's code - I call it the "Im OK code". At each step of the process there is a if "If OK" statement. I kind of like it because when your are running the debugger you can easily see where the code was not "OK". It works for me.

Also I am using an error message at each of these steps, especially when I want to troubleshoot MySQL code, to write to a text file. You cannot believe how helpful this is.

OK = cn.execute(sqlStatement,args)
lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
save_to_file(lcErrorMsg,"c:\Error.txt",.T.)

It returns something like this when it does not work:

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

1064 - 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call count_names_bystate('CA',@vcount)' at line 1'

And when it does work:

Success
Time - 20110122043600441


Now on to the important stuff:

-- Arguments --

This is what I stated out with, and then commented out the args.set line.

args.add("vstate","CA")
'args.set("vcount",@vcount,OutputArgument)

Here you see that I have set this up like one of the help documents suggests in the Wiki - this leads you down a false path:

http://www.alphafivewiki.com/SQL+Arg...numerated+Type
SQL::ArgumentUsage Enumerated Type
This is an enumerated type with one of the values below.
⁃ InputArgument : Data value must be set before calling.
⁃ InputOutputArgument : Data value must be set before calling.
⁃ OutputArgument : Value is irrelevant, but must be set to determine the type before calling.

From reading this you would think that you needed to use the args.set command with the OutputArgument. But with MySQL you do not!

So what do you have to do? We are basically going to replicate what is going on when we use the terminal window to run the MySQL code directly against the database.

Step 1
Setup and get ready to the connect to our MySQL datbase:

dim cn as sql::connection
dim rs as sql::ResultSet
dim args as sql::arguments
dim ok as l
dim vstate as c

dim lcErrorMsg as c
dim lcErrorMsgNo as n

Step 2
Add any arguments that are needed. In this case we are just passing the state parameter.
args.add("vstate","CA")


Step 3
Lets get it done and connect.

ok = cn.open("::name::minimalist")

But wait. We have to speak MySQL soon after we connect so we need to let AlphaFive know this.

cn.PortableSQLEnabled = .f.


Step 4
We are going to speak MySQL:

sqlStatement = <<%sql%
call count_names_bystate(:vstate,@vcount);
%sql%
OK = cn.execute(sqlStatement,args)

Here we pass the argument for vstate and we put the "@vcount" just as we did in from the terminal window. Nothing is being passed to this MySQL variable. It just needs to take up space here.

(BIG NOTE HERE! -- I tried running these sqlStatements as one statement. No error is thrown, but no results are posted to the ResultSet. --)

This is just the first phrase - so lets pass the next one. This is the phrase that returns the value we created in our stored procedure.

sqlStatement = <<%sql%
SELECT @vcount as count;
%sql%
OK = cn.execute(sqlStatement,args)

Here we again replicate the terminal commands. The @vcount shows up again and is the MySQL variable and of course we have to tell it how to present itself. One row and we are going to call it "count".

Also, the logs are now reporting back with "success" in or error.txt file.



Step 5
Lets see if all of our work has paid off and we get a return value. Also lets close our database connection. Even though we have closed the connection we can still extract the data as we have assigned it to the variable rs.

rs = cn.ResultSet
cn.close()


Just standard xbasic ResultSet. We know that there is only one row so we are going to use the command

rs.data(1) to extract the value.


Step 6
Lets do something with the value and display it on our new web page.

dim mytext as c
mytext = rs.data(1)

vMsg = mytext + " is the count"

vMsg is the variable that we are going to use to pass this information to our web page.

<body bgColor=#ffffff><%a5 ?"What is the count: " + vMsg %>
<br></body></html>


Step 7
Execute that page by clicking on the lightning bolt icon. A selected browser will open up and display your results.


References:
MySQL Stored procedure Tutorial: http://forums.mysql.com/read.php?98,358569,358569


If you find any errors or suggestions please let me know and I will post a corrected document.

Regards,

Michael Carroll

www.redrocksbluesky.com
www.theminimalistrunner.com
[email protected]
435-275-5170

[Pat Bremkamp]

Re: Getting output values from MySQL stored procedures

Michael,

Thanks for the detailed explanation. That was much more than I expected, but just what I needed! Maybe Alpha should use it in their WIKI. I'll try it this morning.

The things you pointed out that I was missing that stand out for me...
1. Don't try to set up an output argument in Xbasic. The args.set isn't needed and the @value syntax doesn't work in Xbasic.

2. MySQL will hang on to the @value after the stored procedure closes, so it can be retrieved with a select statement...(that also means I need to be careful with my variable names as the number of stored procedures grows).

Again, thanks.
Pat

[Pat Bremkamp]

Re: Getting output values from MySQL stored procedures

Just tested, and it works great!

I hope I can return the favor some time.

I'm always amazed at what can be done with Alpha. In this case, the stored procedure is part of a process where you click a button on the "Job" grid and it creates an invoice and invoice line, saves it as a pdf in a directory on the server, creates an email, attaches the pdf and emails it, and creates a note in the job notes table ... all from one button press and all in about 10 seconds!

Pat

[MichaelCarroll]

Re: Getting output values from MySQL stored procedures

In my post I gave credit to the look of the code structure I was using to Bob Moore. I failed to mention Frank Bicknell as the key contributor to not only to the structure, but to the error messaging code as well.

One of the things that has helped to accelerate my understanding of Alpha Five is to see clear well written code. That kind of clarity takes a lot of time and effort to develop. Kudo's to all those that work to make the process of programming AlphaFive "cool". (Yea my 17 year old son is cringing.)

Pat - as for your previous comment - I feel the same.

Regards,

Michael Carroll

www.redrocksbluesky.com
www.theminimalistrunner.com
[email protected]
435-275-5170

[Steve Workings]

Re: Getting output values from MySQL stored procedures

Far Out Michael. Thanks for taking the time to do this.

[Ezslim]

Re: Getting output values from MySQL stored procedures

I'm a little late to the party here relative to Pat's question, but here is what I do to return values from a stored procedure.

Mysql stored procedure example:
insert into foo (id,invoice_nbr) values (null,passed_in_invoice);
set @last_id=(select last_insert_id());
-- @last_id now contains the value of id (auto increment field) from last insert

select invoice_nbr as value_to_alpha from foo where id=@last_key;

Alpha code:
sql_exp="call mysql_stored_proc (:invoice_number)"
if .not. cn.execute(sql_exp,args)
if left(cn.CallResult.text,12)="Database API"
cn.close()
end
end if
end if
rs=cn.ResultSet
rs.data("value_to_alpha") 'here is the value returned by the stored procedure

In short, all you need to do is have a SELECT statement at the end of your sql stored procedure and the results of it will be available in the connection resultset after the stored procedure runs.

[Pat Bremkamp]

Re: Getting output values from MySQL stored procedures

Thanks, Scott. I'll be using that.

I did notice something you are doing that I haven't been doing. In your insert, it looks like you are specifically listing the autoincrement field and setting it to a value of null.

I have been not including autoincrement fields in the list of fields, and that hasn't bitten me yet, but should I be doing what you are doing?

Pat

[asawyer13]

Re: Getting output values from MySQL stored procedures

It is normal to NOT include the autoincrement field in the select list, and let MySQL just insert the next value for you.

I believe in MS SQL Server, you can't put the autoincrement field in the insert clause, though I may not remember that properly.

Alan

[MichaelCarroll]

Re: Getting output values from MySQL stored procedures

Ezslim,

I like this way much better than what I am doing. It is much simpler.

However, I cannot get it to work. I get it to work from the terminal, but not in A5. Can you take a look and see what I missed?


Here is the stored procedure with the SELECT statement:

DELIMITER $$
CREATE PROCEDURE `count_names_bystate`( 
IN vstate VARCHAR(2), 
OUT vcount INT)
BEGIN 
SELECT count(first) 
INTO vcount 
FROM mytable 
WHERE state = vstate; 
SELECT @vcount as count;
END
$$

Here is the results from the terminal:

[FONT="Courier New"]mysql> call count_names_bystate("ut",@vcount);
+-------+
| count |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)[/FONT]

Here is my a5w page code:

<%a5
dim vMsg as c = ""
	
		dim cn as sql::connection
		dim rs as sql::ResultSet
		dim args as sql::arguments
		dim ok as l
		dim js as c = ""
		dim vstate as c

		dim lcErrorMsg as c
		dim lcErrorMsgNo as c
		
		args.add("vstate","ut")
		
		ok = cn.open("::name::minimalist")
		cn.PortableSQLEnabled = .f.
			if ok then
				sqlStatement = <<%sql%
				call count_names_bystate(:vstate,@vcount);
				%sql%
				OK = cn.execute(sqlStatement,args)
					lcErrorMsg = cn.CallResult.text + crlf() + "Time - " + ctime(Now()) + lcErrorMsgNo + crlf()
					save_to_file(lcErrorMsg,"c:\Error.txt",.T.)
				if ok then
					rs = cn.ResultSet
				end if
			end if
		dim mytext as c
		mytext = rs.data("Count")
		cn.close()

vMsg = mytext + " is the count" 'put the count of the records here
%>

</head>
<body bgColor=#ffffff><%a5 ?"What is the count?: " + vMsg %> 
<br></body></html>

The code in the page returns nothing. Hmmm...

Much thanks in advance,

Michael Carroll

www.redrocksbluesky.com
www.theminimalistrunner.com
[email protected]
435-275-5170

I'm a little late to the party here relative to Pat's question, but here is what I do to return values from a stored procedure.

Mysql stored procedure example:
insert into foo (id,invoice_nbr) values (null,passed_in_invoice);
set @last_id=(select last_insert_id());
-- @last_id now contains the value of id (auto increment field) from last insert

select invoice_nbr as value_to_alpha from foo where id=@last_key;

Alpha code:
sql_exp="call mysql_stored_proc (:invoice_number)"
if .not. cn.execute(sql_exp,args)
if left(cn.CallResult.text,12)="Database API"
cn.close()
end
end if
end if
rs=cn.ResultSet
rs.data("value_to_alpha") 'here is the value returned by the stored procedure

In short, all you need to do is have a SELECT statement at the end of your sql stored procedure and the results of it will be available in the connection resultset after the stored procedure runs.

[Pat Bremkamp]

Re: Getting output values from MySQL stored procedures

Michael,

I should let John speak for himself, but I think you want to do it slightly differently

Try
SET @vcount = vcount

Then, since the @ variable is the one that persists beyond the end of the stored procedure, then after the stored procedure is complete, do
SELECT @vcount as whatever...

Pat

[Ezslim]

Re: Getting output values from MySQL stored procedures

Michael,
If I was building the stored procedure I would do it like this:

CREATE PROCEDURE `count_names_bystate`(IN vstate VARCHAR(2))
BEGIN
SELECT COUNT(FIRST) AS vcount FROM mytable WHERE state=vstate;
END$$

My Alpha code would be this to get the value:

sql_exp="call count_names_bystate (:the_state)"
if .not. cn.execute(sql_exp,args)
if left(cn.CallResult.text,12)="Database API"
cn.close()
end
end if
end if
rs=cn.ResultSet
dim mytext as C
mytext = rs.data("vcount") 'here is the value returned by the stored procedure

[Ezslim]

Re: Getting output values from MySQL stored procedures

By the way, please help the forum newbie and explain how to put my "code" in a code box so it retains the indentation. :-)

I hate it when my code is not indented.