Announcement

Collapse
No announcement yet.

Executing a stored procedure wich has output parameters and input paramaters

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Executing a stored procedure wich has output parameters and input paramaters

    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:

    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\sxlfj sfjskurlksdfjksufj\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
    Code:
    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/show...rocedure-in-SC

    jsb
    Last edited by jsbinca; 09-05-2015, 02:16 PM.

    Comment


    • #3
      I don't understand what you are saying but I tried to follow this post http://www.scriptcase.net/forum/show...tore-Procedure but nothing is working.

      Comment


      • #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:

        Code:
        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:
        PHP Code:
        /*
            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.
        /Giuseppe

        Professional Scriptcase Services
        Some Customers opinions

        Comment

        Working...
        X