Help needed to call Stored Procedure from AJAX Event

Scriptcase 9 General
, , , ,
#1

I am trying to use a MySQL Stored Procedure in a Scriptcase form. I have two fields; ‘RecipeTitle’ and ‘Variant’. IF when adding a NEW recipe the ‘RecipeTitle’ is the same then the ‘Variant’ field which is an integer indicates the number of like 'RecipeTitle’s there are,

For Example:

If I have a Recipe called “Sam” and there is only one occurrence in the database then the Variant is 1.

RecipeTitle Variant
Sam 1

If there were two recipes called “Sam” then there would be two records with Variant 1 and 2 respectively.

RecipeTitle Variant
Sam 1
Sam 2
…

So in the case of wanting to count how many RecipeTitle records there are I need to have one IN parameter and one OUT parameter to receive the total. Would this assumption be correct??

Given the above I created an AJAX OnFocus for Variant field with the following code:
sc_lookup(rsData, “CALL stProc_RecipeVariantCount(’{RecipeTitle}’)”,“conn_mysql_dbrecipedirectory”);
{Variant} = {rsData[0][0]};

Notice that the field ‘RecipeTitle’ is the IN parameter.

I get no results which makes me think I have something wrong in Scriptcase for how I am calling the Stored Procedure. The Stored Procedure works perfectly in MySQL. See below:

BEGIN
SELECT
COUNT(RecipeTitle) INTO iResult
FROM tblrecipecatalog
WHERE
(RecipeTitle = sRecipeTitle);
END

IN sReciptTitle VARCHAR(60)
OUT iResult INT(3)

Would someone mind helping me re-write the above code in Scriptcase to return the correct number of RecipeTitle’s in the table. Thank you so much!

#2

sc_exec_sql(“CALL sp_myprocedure($params)”);
sc_lookup(result,"select @_variable_out1, @_variable_out2, … ");
{MY_FIELD} = {result}[0][0];
{MY_FIELD2} = {result}[0][1];

#3

Thank you for replying alvagar.

So my updated code in the _onFucus AJAX event would be:
​
$sRecipeTitle = {RecipeTitle}
sc_exec_sql(“CALL stProc_RecipeVariantCount($sRecipeTitle)”);
sc_lookup(result,“select @_variable_out1”);
{Variant} = {result}[0][0];

??

I don’t quite understand the @_variable_out format above. Would I use my iResult value in place of ‘variable_out1’? So it would be ‘@_iResult’.???

#4

After doing some review of what alvagar posted I dud sine checking. It turns out that I had to add the OUT variable in the sc_lookup macro. Take a look at the result which works perfectly.

$RecipeTitle ={RecipeTitle};
$iValue = 0;
sc_lookup(rsData, “CALL stProc_RecipeVariantCount(’{RecipeTitle}’, @iResult)”);
$iValue = {rsData[0][0]};
$iValue = $iValue + 1;
{Variant} = $iValue;

I added a new variable called ‘$iValue’. Thus us the value of ‘@iResult’ which is the OUT variable. I found MySQL examples online and noticed that the OUT variable was placed second in a CALL to the Stored Procedure. I believe they used the ‘@’ symbol to denote this. Here is the SQL Stored procedure itself:
BEGIN
SELECT
COUNT(RecipeTitle) INTO @iResult
FROM tblrecipecatalog
WHERE
(RecipeTitle = sRecipeTitle);
SELECT @iResult;
END

The parameter I passed was the ‘sRecipeTitle’ as a STRING. I add ‘s’ to the beginning of the variable so I know what type it is. Then see how I get the OUT variable with the ‘@’ symbol. The other thing I will do soon is to comment in my Stored Procedure so I on’t forget what I did later.