PurePerl MySQL Multi-Statement and Stored Procedure

Using the PurePerl MySQL API – Part 9

Foreword: In this part of the series, I explain how to code multi-statement and stored procedure.

By: Chrysanthus Date Published: 31 Aug 2016

Introduction

This is part 9 of my series, Using the PurePerl MySQL API. In this part of the series, I explain how to code multi-statement and stored procedure. A multi-statement is more than one statement, sent in a query function; the statements are separated by semicolons. A stored procedure is a special kind of multi-statement that is saved in the MySQL server hard disk. You should have read the previous parts of the series before coming here, as this is a continuation.

Multi-Statement
The following is an example of a multi-statement coding (after selecting the database):

    my $selSt = "SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; SELECT * FROM pet WHERE death IS NOT NULL";

    if (Mysql::query($selSt) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            for (my $i=0; $i<@Mysql::Fields; ++$i)
                {
                   for (my $j=0; $j<@{$Mysql::Fields[$i]}; ++$j)
                       {
                           print @{${$Mysql::Fields[$i]}[$j]}, "\n";
                       }
                   print "\n";
                }

            print "\n";

            for (my $i=0; $i<@Mysql::Result; ++$i)
                {
                   for (my $j=0; $j<@{$Mysql::Result[$i]}; ++$j)
                       {
                           print %{${$Mysql::Result[$i]}[$j]}, "\n";
                       }
                   print "\n";
                }
        }

There are two statements in the code ($selSt). Fields (column properties) and Result are each a three dimensional structure. The outermost structure is an array (one cell). It either leads to a single element array of one hash or an array of arrays. Each row index in the outermost structure leads to a result, from one of the multi-statements. If the result is OK, then you have an array of single cell array of one hash (OK Hash). If the result is an error, then you have an array of single cell array of one hash (Error Hash). If the result is a resultset (set of rows), then you have an array of array of hashes, where each row is a hash (can also be an array). You should try the above code, to appreciate this. The useful result of a stored procedure is similar in explanation.

In particular, for the fields, each index of the outermost structure corresponds to an array of arrays. Each array of array gives the field properties of a resultset.

The structure of an error array/hash is:

    [
        {
            bodyLength => $bodyLenD,
            seqNo => $seqNo,
            errNo => $errNo,
            errCode => $error_code_D,
            SQLState => $SQLStateStr,
            info => $info,
            arrayName => 'Error'
        }
    ]

The structure of an OK array/hash is:

    [
        {
                bodyLength => $bodyLenD;
                seqNo => $seqNo;
                okNo => $okNo;
                affectedRows => $affected_Rows;
                last_insert_id => $last_insert_id;
                noOfWarnings => $noOfWarnings_D;
                info => $info;
                arrayName => 'OK';
        }
    ]

For the above two structures, the outermost array (one cell) is not shown.

The structure of a multi-resultset (not a single resultset) array/array/hash is similar to (not an example from this series):

    [
         [
             {ProductID=>1, ProductName=>"TV Set", Category=> "Entertainment", Number=>50, CostPrice=>25, SellingPrice=>30},
             {ProductID=>1, ProductName=>"VCD", Category=>"Entertainment", Number=>50, CostPrice=>20, SellingPrice=>25},
             {ProductID=>3, ProductName=>"Clothe Box", Category=>"Household", Number=>45, CostPrice=>16, SellingPrice=>21}
         ]
         [
             {ProductID=>5, ProductName=>"Banana", Category=>"Fruit", Number=>125, CostPrice=>5, SellingPrice=>7},
             {ProductID=>6, ProductName=>"Pear", Category=>"Fruit", Number=>135, CostPrice=>3, SellingPrice=>4}
         ]
    ]

Stored Procedure
The following is a code example of a stored procedure (after selecting the database):

    my $procedureStr = "CREATE PROCEDURE sampleProce (OUT parA DATE)
                        BEGIN
                            SELECT birth
                            FROM pet
                            WHERE name = 'Nelly'
                            INTO parA;
                        END";
    if (Mysql::query($procedureStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "procedure saved \n";
        }

    my $setStr = "SET \@val = NULL";
    if (Mysql::query($setStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "variable set \n";
        }

    my $procedureCall = "CALL sampleProce(\@val)";
    if (Mysql::query($procedureCall) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "procedure called \n";
        }

    my $selStr = "SELECT \@val";
    if (Mysql::query($selStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print %{$Mysql::Result[0]}, "\n";
        }

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

PurePerl MySQL Multi-Statement and Stored Procedure

Using the PurePerl MySQL API – Part 9

Introduction

Comments