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;
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
raised ADABASE.CONNECTION.BASE.SQLITE.UNSUPPORTED_BY_SQLITE : SQLite does not have the capability of stored procedures
[STMT] is "MySQL.MySQL_statement" or "PostgreSQL.PostgreSQL_statement"