Procedure
AdaBase.Statement.Base.[STMT].fetch_next_set (data_present : out Boolean; data_fetched : out Boolean)

This procedure advances to the next result set on a multiple result set statement handle. This is possible to obtain through the use of stored procedures which can execute several SELECT queries serially.

It is possible for the database client to report that a result set is present, but in fact there's no data contained within. This can occur on non-SELECT queries (e.g. an INSERT statement). MySQL also produces an extra empty result set at the end of every stored procedure execution for the purposes of passing status data back.

Thus, the program should iterate on data_present variable, looping as long as it remains True. However, it should only try to read the result when the data_fetched variable is True. 

with AdaBase;
with Connect;
with Ada.Text_IO;
with AdaBase.Results.Sets;

procedure Stored_Procs is

   package CON renames Connect;
   package TIO renames Ada.Text_IO;
   package ARS renames AdaBase.Results.Sets;

   stmt_acc : CON.Stmt_Type_access;

   procedure dump_result;
   procedure dump_result
   is
      function pad (S : String) return String;
      function pad (S : String) return String
      is
         field : String (1 .. 15) := (others => ' ');
         len   : Natural := S'Length;
      begin
         field (1 .. len) := S;
         return field;
      end pad;

      row     : ARS.Datarow;
      numcols : constant Natural := stmt_acc.column_count;
   begin
      for c in Natural range 1 .. numcols loop
         TIO.Put (pad (stmt_acc.column_name (c)));
      end loop;
      TIO.Put_Line ("");
      for c in Natural range 1 .. numcols loop
         TIO.Put ("============== ");
      end loop;
      TIO.Put_Line ("");
      loop
         row := stmt_acc.fetch_next;
         exit when row.data_exhausted;
         for c in Natural range 1 .. numcols loop
            TIO.Put (pad (row.column (c).as_string));
         end loop;
         TIO.Put_Line ("");
      end loop;
      TIO.Put_Line ("");
   end dump_result;

   set_fetched : Boolean;
   set_present : Boolean;

begin

   CON.connect_database;

   declare
      stmt : aliased CON.Stmt_Type :=
             CON.DR.call_stored_procedure ("multiple_rowsets", "");
   begin
      set_fetched := stmt.successful;
      stmt_acc := stmt'Unchecked_Access;
      loop
         if set_fetched then
            dump_result;
         end if;
         stmt.fetch_next_set (set_present, set_fetched);
         exit when not set_present;
      end loop;
   end;

   CON.DR.disconnect;

end Stored_Procs;

testcases/stored_procs/stored_procs.adb


id             fruit          color          calories       
============== ============== ============== ============== 
3              avocado        green          150            
4              banana         yellow         107            
10             grapefruit     yellow         100            
1              apple          red            95             
18             orange         orange         65             
21             pineapple      yellow         50             
20             pear           yellow         45             
13             mango          orange         40             

fruit          
============== 
apple          
raspberry      
strawberry     
tomato         
cherry tomato  

team_id        abbreviation   
============== ============== 
29             PHO            
30             SJ             
31             WIN

Output using the MySQL and PostgreSQL drivers



raised ADABASE.CONNECTION.BASE.SQLITE.UNSUPPORTED_BY_SQLITE : SQLite does not have the capability of stored procedures

Output using the SQLite driver


[STMT] is "MySQL.MySQL_statement" or "PostgreSQL.PostgreSQL_statement"

See Also