How do I create / retrieve the results of a MySQL stored procedure using Lasso 8.5?

Instructions on creating and retrieving the results of MySQL 5 stored procedures using Lasso 8.5 and the [ResultSet] tag.
3-19-2010 by Tami Williams

Why

Here's why you might do this:

Your setup involves different programming languages all talking to the same MySQL database and you don't want to have to re-create the query logic in every programming language.

Creating the MySQL stored procedure

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure.
Create a stored procedure which retrieves all products from the asktami database products table:

[var:'sqlProc' = "DROP PROCEDURE IF EXISTS asktami.GetAllProducts;
CREATE PROCEDURE GetAllProducts()
SELECT * FROM asktami.products;"]

[inline: 
-database='asktami',
-username='xxx',
-password='xxx',
-sql=$sqlProc]
[/inline]

delete the procedure named "GetAllProducts" if it already exists
create a stored procedure named "GetAllProducts" in a database named asktami.

As you can see, the query that it runs is very simple:

SELECT * FROM asktami.products;

The CREATE command is this:

CREATE PROCEDURE GetAllProducts()
SELECT * FROM asktami.products;

The DROP command is this:

DROP PROCEDURE IF EXISTS asktami.GetAllProducts;

Calling/running the stored procedure (and retrieving the results)

The command looks like this:

Call GetAllProducts();

And is used like this:

[inline: 
-database='asktami',
-username='xxx',
-password='xxx',
-sql='Call GetAllProducts();']
  [ResultSet: 1]
 	<b>ResultSet 1
 	<br>Without the [ResultSet] tag 0 records will display inside the [Records] container.</b>
  	 [Records] 
  	 <b>[loop_count]</b> [Field: 'id'] [Field: 'Name']<br> 
   	[/Records] 
  [/ResultSet]
[/inline]

Alternative methods to display the results using the [ResultSet] tag

An inline which uses a -SQL action can return multiple result sets. Each SQL statement within the -SQL action is separated by a semi-colon and generates its own result set. This allows multiple SQL statements to be issued and for the results of each statement to be reviewed individually.

This is one way to display the results:

[inline:
-database='asktami',
-username='xxx',
-password='xxx',
-sql='Call GetAllProducts();']

 [ResultSet_Count] Result Sets 
 [Loop: ResultSet_Count] <hr color="green">
 [ResultSet: Loop_Count]
 <h3>ResultSet [Loop_Count]</h3> 
   [Records] 
   <b>[loop_count]</b> [Field: 'id'] [Field: 'Name']<br> 
   [/Records] 
  [/ResultSet] 
 [/Loop] 
 
[/inline]

The result looks like this:

This is another method:

[inline: 
-InlineName='MyResults',
-database='asktami',
-username='xxx',
-password='xxx',
-sql='Call GetAllProducts();']
[/inline]
 
[ResultSet_Count: -InlineName='MyResults'] Result Sets 
<br>
[Loop: (ResultSet_Count: -InlineName='MyResults')]
[ResultSet: Loop_Count, -InlineName='MyResults']
Found [Found_Count] Records in Result Set [Loop_Count].
[/ResultSet]
[/Loop]

<hr>

 [Loop: (ResultSet_Count: -InlineName='MyResults')] 
 <hr color="purple">
 <h3>ResultSet [Loop_Count]</h3> 
  [ResultSet: Loop_Count, -InlineName='MyResults'] 
   [Records] 
    <b>[loop_count]</b> [Field: 'id'] [Field: 'Name']<br> 
   [/Records] 
  [/ResultSet] 
 [/Loop]