Executing a stored procedure wich has output parameters and input paramaters

Scriptcase 8 Discussion
#1

Hello I’ve been look for a way to execute my stored procedure and after reading macros I tought this is going to work for me but I am mistaken and don’t know how to execute it to get the parameter value I need. Here is my code:

sql_exec_sql('call myprocedure(' . $reponse . ',' . {v_name} . ',' . {v_quant} . ',' . {v_par} . ',' .[id_retu] . ',' . [call_retu] . ')');

$reponse and global variables are the one which will receive one of the output parameters

here is the error that I am getting:
Parse error: syntax error, unexpected ‘:’ in C:\Program Files\NetMake\v81\wwwroot\scriptcase\app\sms\sxlfjsfjskurlksdfjksufj\connexion_apl.php on line 1361

so can any one help me by explaining how to execute store procedure(also when it will return a dataset) from a scriptcase application.

#2 

Parse error: syntax error, unexpected ':' in C:\Program Files\NetMake\v81\wwwroot\scriptcase\app\sms\sxlfj sfjskurlksdfjksufj\connexion_apl.php on line 1361

What’s there, and a few lines before?? It looks like you have some sort of a typo somewhere.

Also you might have a look there.

http://www.scriptcase.net/forum/showthread.php?5864-Run-MySQL-stored-procedure-in-SC

jsb

#3

I don’t understand what you are saying but I tried to follow this post http://www.scriptcase.net/forum/showthread.php?4920-Any-examples-of-Store-Procedure but nothing is working.

#4

One thing to take into consideration.
sql_exec_sql() macro is for SQL sentences don’t returning anything.

For clarification take a look into this.
Create this 2 procedures into your database:


DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_output_select`$$

CREATE PROCEDURE `sp_output_select`(IN IN_param VARCHAR(15), OUT OUT_param1 VARCHAR(15))
BEGIN
	SET OUT_param1 = CONCAT('OUT ', IN_param);
	SELECT IN_param;
END$$

DROP PROCEDURE IF EXISTS `sp_somevar`$$

CREATE PROCEDURE `sp_somevar`(IN IN_param VARCHAR(15), OUT OUT_param1 VARCHAR(15))
BEGIN
	SET OUT_param1 = CONCAT('OUT ', IN_param);
END$$

DELIMITER ;

Now create a blank application, paste this code, and execute it:

/*
	Store procedure with output into a var
*/

echo '********************** Store procedure with output into a var **********************<br>';
echo '$qry = "CALL sp_somevar(\'IN Text\', @out_param)"';
echo '<br>';
$qry = "CALL sp_somevar('Test', @out_param)";

echo 'sc exec_sql($qry) <- First underscore is omitted because SC is parsing it inside echo';
echo '<br>';
sc_exec_sql($qry);
echo '<br>';

echo "<br>OUTPUT:<br>";

echo "We can't output because exec_sql don't returns anything.";
echo '<br>';

echo '<br>';
echo "What happens if we use sc lookup?.";
echo '<br>';

sc_lookup(arr_qry, $qry);
echo "<br>OUTPUT:<br>";
var_dump($arr_qry);
echo " <br> ************* <br>";
echo '<br>';
echo "With lookup either, because we are bounding the output to a MySQL user var, and we have to SELECT it, then....";
echo '<br>';


$qry = "SELECT @out_param";
sc_lookup(arr_qry, $qry);
echo '$qry = "SELECT @out_param";<br>
sc lookup(arr_qry, $qry);';
echo "<br>OUTPUT:<br>";
var_dump($arr_qry);
echo " <br> ************* <br>";

echo '<br>';
echo "Now, we can see the output.";
echo '<br>';
echo '******************************************************************<br>';


echo '********************** Store procedure with output into a var and SELECT **********************<br>';

echo '$qry = "CALL sp_output_select(\'Other test\', @other_out_param)"';
echo '<br>';

$qry = "CALL sp_output_select('IN Text', @other_out_param)";

echo 'sc lookup(arr_qry, $qry);<br>';
sc_lookup(arr_qry, $qry);

echo "<br>OUTPUT:<br>";
var_dump($arr_qry);
echo " <br> ************* <br>";

$qry = "SELECT @other_out_param";
sc_lookup(arr_qry, $qry);
echo '$qry = "SELECT @other_out_param";<br>';
echo "<br>OUTPUT:<br>";
var_dump($arr_qry);
echo " <br> ************* <br>";

I hope now it’s clear.

#5

This solution doesnt work for SqlServer.

Call a sqlserver stored procedure with an output variable