Android Code Snippet Stored Procedure with Parameter IN / OUT

Discussion in 'Code Snippets' started by MarcoRome, Aug 2, 2017.

  1. MarcoRome

    MarcoRome Expert Licensed User

    Hi all.

    I have seen in several threads that they wondered how to perform and receive the results from stored procedures with IN / OUT parameters

    There may be a procedure to call where we need to pass values and return a set (IN) or a procedure that returns a result (OUT)

    Example Procedure IN Parameter:

    To recall this procedure we use the syntax:
    Code:
    CALL sp_totalerecordchiusure(100)
    Example Procedure OUT Parameter:

    To recall this procedure we use the syntax:
    Code:
    CALL sp_contarecord(@ris);
    Select @ris As vediamo;
    and this work with out problem in our MySql / MariaDB...etc Tools.

    As work in B4A:
    First this is file php that we copy on server ( in our example we call this file dbpw_mysqli_stored_procedure.php ):

    Code:
    <?
    $hst  = 
    "localhost";
    $db = 
    "dbname";
    $usr = 
    "username";
    $pwd = 
    "pw";

    $conn_DB=mysqli_connect($hst,$usr,$pwd) 
    or die(mysqli_connect_error());
    mysqli_select_db($conn_DB, $db) 
    or die(mysqli_connect_error());

    mysqli_query($conn_DB, 
    "SET CHARACTER SET utf8");
    mysqli_query($conn_DB, 
    "SET NAMES 'utf8'");
    mysqli_query($conn_DB, 
    "SET COLLATION_CONNECTION = 'utf8_unicode_ci'");

    $action = $_GET[
    "Action"];
    $query =  $_GET[
    "Query"];

    switch ($action) {
        
    case "normal":
          
            $sth = mysqli_query($conn_DB, $query );
            
    if (mysqli_errno()) {
                header(
    "HTTP/1.1 500 Internal Server Error");
                echo $query.
    'n';
                echo mysqli_error();
            
    }
            else
            {
                $rows = array();

                    while($r = mysqli_fetch_assoc($sth)) {
                        $rows[] = $r;
                    }
              
                print json_encode($rows);
            }
            break;
           
        case "storedout":
              
            if (mysqli_multi_query($conn_DB, $query )) {
        do {
            /* store first result set */
            if ($result = mysqli_store_result($conn_DB)) {
                $rows = array();
                //mysqli_fetch_row
                while ($r = mysqli_fetch_assoc($result)) {
                     $rows[] = $r;
                }
                print json_encode($rows);
                mysqli_free_result($result);
            }
            if (mysqli_more_results($conn_DB)) {
            }
        } while ($r = mysqli_next_result($conn_DB));
    }
          
            break;
          
        default:
            echo("User Enabled!");

    }
    ?>
    This is code in B4A:

    Code:
    Sub Activity_Create(FirstTime As Boolean)
        
    'Do not forget to load the layout file created with the visual designer. For example:
        'Activity.LoadLayout("Layout1")
     
        
    'Stored Procedure IN Parameter
        Dim queryIN As String = $"CALL sp_totalerecordchiusure(100)"$
        ExecuteRemoteQuery(queryIN, 
    "Test_In""normal")
      
        
    'Stored Procedure OUT Parameter
        Dim queryOUT As String = $"CALL `sp_contarecord`(@r);
        Select @r As vediamo;"$

        ExecuteRemoteQuery(queryOUT, 
    "Test_Out""storedout")

    End Sub

    #Region db

    Sub ExecuteRemoteQuery(Query As String, JobName As String, Action As String)
        
    Dim job As HttpJob
        job.Initialize(JobName, Me)
        job.Download2(
    "https://www.ourserver.xxxx/db/dbpw_mysqli_stored_procedure.php"Array As String("Action", Action , "Query", Query))

    End Sub

    Sub JobDone (Job As HttpJob)
        
    ProgressDialogHide
        
    Log("JobName = " & Job.JobName & ", Success = " & Job.Success)
        
    If Job.Success Then
            
    Dim res As String
            res = Job.GetString
            
    'res = Job.Tag
            Log("Response from server: " & res)
            
    Dim parser As JSONParser
            parser.Initialize(res)
            
    Select Job.JobName
              
                
    Case "Test_In"
                    
    Dim COUNTRIES As List
                    
    Dim m As Map
                    m.Initialize
                    COUNTRIES = parser.NextArray 
    'returns a list with maps
                    If COUNTRIES.Size > 0 Then
                        
    For i = 0 To COUNTRIES.Size -1
                            m = COUNTRIES.Get(i)
                            
    Log(m.Get("incasso"))
                        
    Next  
                    
    End If
                  
                
    Case "Test_Out"
                    
    Dim COUNTRIES As List
                    
    Dim m As Map
                    m.Initialize
                    COUNTRIES = parser.NextArray 
    'returns a list with maps
                    If COUNTRIES.Size > 0 Then
                        m = COUNTRIES.Get(
    0)
                        
    Log(m.Get("vediamo"))
                    
    End If
                  
            
    End Select
        
    Else
            
    Log("Error: " & Job.ErrorMessage)
        
    End If
        Job.Release
    End Sub
    The result in this case will be:

    result_stored_procedure.png

    I hope it can be useful
    Have nice day
    Marco
     
    Last edited: Aug 2, 2017
    JakeBullet70, paragkini, JNG and 4 others like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice