Android Code Snippet Stored Procedure with Parameter IN / OUT

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:

'Stored Procedure DB TEST **** sp_totalerecordchiusure parameter IN
' CREATE DEFINER = 'user'@'%' PROCEDURE `sp_totalerecordchiusure`(
' IN `incasso_maggiore_di` INTEGER
' )
' Not DETERMINISTIC
' CONTAINS SQL
' SQL SECURITY DEFINER
' COMMENT ''
' BEGIN
' Select * FROM `ark_test` where incasso > incasso_maggiore_di;
' END;
'Fine **** sp_totalerecordchiusure

To recall this procedure we use the syntax:

CALL sp_totalerecordchiusure(100)

Example Procedure OUT Parameter:

'Stored Procedure DB TEST **** sp_contarecord parameter OUT
CREATE DEFINER = 'user'@'%' PROCEDURE `sp_contarecord`(
OUT `conta` INTEGER
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT COUNT(incasso) INTO conta FROM `ark_test`;
END;
'Fine **** sp_contarecord

To recall this procedure we use the syntax:

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 ):

<?
$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:

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:

I hope it can be useful
Have nice day
Marco