5. Embedded SQL for Ada : Sample Applications : The SQL Terminal Monitor Application
 
Share this page                  
The SQL Terminal Monitor Application
This application executes SQL statements that are read in from the terminal. The application reads statements from input and writes results to output. Dynamic SQL is used to process and execute the statements.
When the application starts, it prompts the user for the database name. The program then prompts for an SQL statement. The preprocessor does not accept SQL comments and statement delimiters. The SQL statement is processed using dynamic SQL, and results and SQL errors are written to output. At the end of the results, the program displays an indicator of the number of rows affected. The loop is then continued and the program prompts you for another SQL statement. When end-of-file is typed in, the application rolls back any pending updates and disconnects from the database.
The user's SQL statement is prepared using prepare and describe. If the SQL statement is not a select statement, then it is run using execute and the number of rows affected is printed. If the SQL statement is a select statement, a dynamic SQL cursor is opened, and all the rows are fetched and printed. The routines that print the results do not try to tabulate the results. A row of column names is printed, followed by each row of the results.
Keyboard interrupts are not handled. Fatal errors, such as allocation errors, and boundary condition violations are handled by rolling back pending updates and disconnecting from the database session.
Sample Application
--
-- I/O utilities
-- This example assumes package Long_Float_Text_IO
-- has been instantiated to use the D_FLOAT format.
--
with text_io;                 use text_io;
with integer_text_io;         use integer_text_io;
with short_integer_text_io;   use short_integer_text_io;
with long_float_text_io;      use long_float_text_io;
-- Declare the SQLCA and the SQLDA records
exec sql include sqlca;
exec sql include sqlda;

-- Dynamic SQL statement and cursor
exec sql declare stmt statement;
exec sql declare csr cursor for stmt;
--
-- Program: SQL_Monitor
-- Purpose: Main entry of SQL Monitor application.
--
procedure SQL_Monitor is
     exec sql begin declare section;
        dbname: String(1..50) := (1..50 => ' '); -- Database name
        dblen:  Natural;
       dbrun:  Boolean := false;               -- connected to db
     exec sql end declare section;
     -- Global SQLDA. Discriminant SQLN is implicitly set
     -- to IISQ_MAX_COLS
     sqlda: IISQLDA(IISQ_MAX_COLS);
     --
     -- Constants and types needed to declare global storage for
     -- SELECT results.
     --
     -- Length of large string pool from which slices will
     -- be allocated
     MAX_STRING: constant := 3000;
     -- Different numeric types for result variables
     type Numerics is
         record
             n_int: Integer;       -- 4-byte integers
             n_flt: Long_Float;    -- 8-byte floating-points
             n_ind: Short_Integer; -- 2-byte null indicators
         end record;
     type Numerics_Array is array(Short_Integer range <>)
                        of Numerics;
     -- Large string pool from which to allocate slices
     type Strings is
             record
                s_len: Integer;                 -- Length used
                s_data: String(1..MAX_STRING);  -- and data area
           end record;
      -- Record of numerics and strings
      type Results is
              record
                    nums: Numerics_Array(1..IISQ_MAX_COLS);
                    str:  Strings;
              end record;
      --
      -- Global result storage area - set up by Print_Header,
      -- filled when executing the FETCH statement, and
      -- displayed by Print_Row.
      --
      res: Results;
      -- Forward defined procedures and functions
      -- Main body of monitor
      procedure Run_Monitor;
      -- Execute dynamic SELECT statements
      function Execute_Select return Integer;
      -- Print the column headers for a dynamic SELECT
      function Print_Header return Boolean;
      -- Print a result row for a dynamic SELECT
      procedure Print_Row;
      -- Print an error message
      procedure Print_Error;
      -- Read a statement from input
      procedure Read_Stmt(stmt_num: in Integer; stmt_buf:
                            in out String);
      --
      -- Procedure: Run_Monitor
      -- Purpose: Run the SQL monitor. Initialize the global
      --          SQLDA with the number of SQLVAR elements.
      --          Loop while prompting
      --          the user for input and processing the SQL
      --          statement; if end-of-file is typed then control
      --          is returned to the main program exception
      --          handler from Read_Stmt.
      --
      --          If the statement is not a SELECT statement
      --          then execute it, otherwise open a cursor and
      --          process a dynamic select statement
      --          (using Execute_Select).
      --
      procedure Run_Monitor is
      exec sql begin declare section;
        stmt_buf: String(1..1000); -- SQL statement input buffer
        stmt_num: Integer;         -- SQL statement number
        rows: Integer;             -- # of rows affected
      exec sql end declare section;
begin                               -- Run_Monitor
      -- Now we are set for input
      stmt_num := 0;

      -- Loop till end-of-file is detected.
      loop
            --
            -- Prompt and read the next statement. If Read_Stmt
            -- end-of-file was detected then end_error is signaled
            -- and control is returned to the main program.
            --
            stmt_num := stmt_num + 1;
            Read_Stmt(stmt_num, stmt_buf);
            -- Handle database errors
            exec sql whenever sqlerror goto Stmt_Err;
            --
            -- PREPARE and DESCRIBE the statement. If the
            -- statement is not a SELECT then EXECUTE it,
            -- otherwise inspect the
            -- contents of the SQLDA and call Execute_Select.
            --
            exec sql prepare stmt from :stmt_buf;
            exec sql describe stmt into :sqlda;
            --
            -- If SQLD = 0 then this is not a SELECT
            -- statement. Otherwise call Execute_Select to process
            -- a dynamic cursor.
            if (sqlda.sqld = 0) then
                exec sql execute stmt;
                rows := sqlca.sqlerrd(3);
            else
                rows := Execute_Select;
            end if;                      -- If SELECT or not
            exec sql whenever sqlerror continue;
            <<Stmt_Err>>
            --
            -- Only display error message if we arrived here
            -- because of the SQLERROR condition. Otherwise print
            -- the rows processed and continue with the loop.
            if (sqlca.sqlcode < 0) then
                    Print_Error;
            else
                  put("[");
                  put(rows, 1);
                  put_line(" row(s)]");
            end if;
            end loop; -- While reading statements
 
end Run_Monitor;
--
-- Function: Execute_Select
-- Purpose:  Run a dynamic SELECT statement. The SQLDA has
--           already been described. This routine calls
--           Print_Header to print column headers
--           and set up result storage information.
--           A Dynamic SQL cursor is then opened
--           and each row is fetched and printed by Print_Row.
--           Any error causes the cursor to be closed.
-- Returns:
--           Number of rows fetched from the cursor.
--
 
function Execute_Select return Integer is
            rows: Integer := 0;             -- Counter of rows fetched
begin                       -- Execute_Select
    --
    -- Print result column names and set up the result data types
    -- and variables. Print_Header returns FALSE if the dynamic
    -- set-up fails.
    --
    if (Print_Header) then
          exec sql whenever sqlerror goto Select_Error;
          -- Open the dynamic cursor
          exec sql open csr for readonly;
          -- Fetch and print each row
          rows := 0;
          while (sqlca.sqlcode = 0) loop
              exec sql fetch csr using descriptor :sqlda;
              if (sqlca.sqlcode = 0) then
                  rows := rows + 1; -- Count the rows
                  Print_Row;
              end if;
          end loop;                  -- While there are more rows
        <<Select_Error>>
          -- Display error message if SQLERROR condition is set.
          if (sqlca.sqlcode < 0) then
                     Print_Error;
          end if;
                    exec sql whenever sqlerror continue;
                     exec sql close csr;
        end if; -- If Print_Header
        return rows;
end Execute_Select;
--
-- Function: Print_Header
-- Purpose: A statement has just been described so set up the
--          SQLDA for result processing. Print all the column
--          names and allocate result variables for retrieving
--          data. The result variables are allocated out of
--          a pool of numeric variables (integers, floats and
--          2-byte indicators) and a large character buffer.
--          The SQLDATA and SQLIND fields are pointed at the
--          addresses of the result variables.
-- Returns:
--          TRUE if successfully set up the SQLDA for result
--          variables, FALSE if an error occurred.
--
 
function Print_Header return Boolean is
      nullable: Boolean;     -- Null indicator required
      chlen: Short_Integer;  -- Current string length
begin                        -- Print_Header
    --
    -- Verify that there are enough result variables.
    -- If not print an error and return.
    --
    if (sqlda.sqld >= sqlda.sqln) then
        put("SQL Error: SQLDA requires ");
        put(sqlda.sqld, 1);
        put(" variables, but has only ");
        put(sqlda.sqln, 1);
        put_line(".);
        return FALSE;
    end if; -- If enough result variables
    --
    -- For each column print the number and title. For example:
    --    [1] name [2] age [3] salary
    -- While processing each column determine the column type
    -- and to where SQLDATA and SQLIND must point in order to
    -- retrieve type-compatible results.
    --
    res.str.s_len := 1;               -- No string space used yet
    for col in 1 .. sqlda.sqld loop    -- For each column
        declare
                         sqv: IISQL_VAR renames sqlda.sqlvar(col); -- Shorthand
        begin
            -- Print column name and number
            put("[");
            put(col, 1);
            put("] ");
            put(sqv.sqlname.sqlnamec(1..Integer
                        (sqv.sqlname.sqlnamel)));
            if (col < sqlda.sqld) then
                 put(" ");      -- Separator space
            end if;
            --
            -- Process the column for type and length
            -- information. Use
            -- result storage area from which variables can
            -- be allocated.
            if (sqv.sqltype < 0) then
                          -- Null indicator handled later
                nullable := TRUE;
            else
                nullable := FALSE;
            end if;
            case (abs(sqv.sqltype)) is
                - Integers - use 4-byte integer
                when IISQ_INT_TYPE =>
                      sqv.sqltype := IISQ_INT_TYPE;
                      sqv.sqllen := 4;
                      sqv.sqldata := res.nums(col).n_int'Address;
                -- Floating points - use 8-byte float
                when IISQ_MNY_TYPE | IISQ_FLT_TYPE =>
                      sqv.sqltype := IISQ_FLT_TYPE;
                      sqv.sqllen := 8;
                      sqv.sqldata := res.nums(col).n_flt'Address;
                -- Character strings
           when IISQ_DTE_TYPE | IISQ_CHA_TYPE | IISQ_VCH_TYPE =>
                  --
                  -- Determine the length of the slice required
                  -- from the large character buffer. If we have
                  -- enough space left then point at the start of
                  -- the corresponding slice, otherwise print an
                  -- error and return.
                      --
                      -- Note that for DATE types we must set the
                      -- length.
                      --
                      if (abs(sqv.sqltype) = IISQ_DTE_TYPE) then
                          chlen := IISQ_DTE_LEN;
                      else
                          chlen := sqv.sqllen;
                      end if;
                      -- Enough room in large string buffer ?
                  if (res.str.s_len + Integer(chlen) > MAX_STRING)
                          then
                          new_line;
                        put_line("SQL Error: Character result data "
                                   & "overflow.");
                          return FALSE;
                      end if;
                  --
                  -- Allocate space out of the large character
                  -- buffer and keep track of the amount of space
                  -- used so far.
                  --
                      sqv.sqltype := IISQ_CHA_TYPE;
                      sqv.sqllen  := chlen;
                      sqv.sqldata :=
                           res.str.s_data(res.str.s_len)'Address;
                  res.str.s_len := res.str.s_len + Integer(chlen);
                -- Bad data type
                when others =>
                      new_line;
                  put("SQL Error: Unknown data type returned: ");
                      put(sqv.sqltype, 1);
                      put_line(".);
                      return FALSE;
            end case;                 -- Of data types
            -- If nullable then point at null indicator and
            -- toggle type id
            if (nullable) then
                  sqv.sqltype := -sqv.sqltype;
                  sqv.sqlind := res.nums(col).n_ind'Address;
            else
                  sqv.sqlind := IISQ_ADR_ZERO;
            end if;
        end;                             -- Declare (rename) block
    end loop;                            -- For processing columns
    new_line;                            -- Print separating line
    put_line("---------------------------------------");
    return TRUE;

end Print_Header;
--
--  Procedure: Print_Row
--  Purpose:  For each element inside the SQLDA, print the value.
--            Print its column number too in order to identify it
--            with the column name printed earlier. If the value
--            is NULL print "N/A". This routine prints the values
--            using very basic formats and does not try to
--             tabulate the results.
--
procedure Print_Row is
    chlen: Short_Integer; -- Index into string slices
begin                   -- Print_Row
    --
    -- For each column, print the column number and the data. The
    -- number identifies the column with the column name printed
    -- in Print_Header. NULL columns are printed as "N/A".
    --
    res.str.s_len := 1;       -- No characters printed yet
    for col in 1 .. sqlda.sqld loop
        declare
            sqv: IISQL_VAR renames sqlda.sqlvar(col); -- Shorthand
        begin
            put("[");              -- Print column number
            put(col, 1);
            put("] ");
            -- If nullable and is NULL then print "N/A"
            if (sqv.sqltype < 0) and (res.nums(col).n_ind = -1) then
                put("N/A");
            else
                --
                -- Using the base type set up in Print_Header
                -- determine how to print the results. All types
                -- are printed using
                -- very basic formats.
                --
                case (abs(sqv.sqltype)) is
                            when IISQ_INT_TYPE =>
                            put(res.nums(col).n_int, 1);
                            when IISQ_FLT_TYPE =>
                            put(res.nums(col).n_flt, 1, 4, 0);
                            when IISQ_CHA_TYPE =>
                        -- Use a current-length slice from the
                        -- large character buffer, as allocated
                        -- in Print_Header.
                        -- Track number of characters printed.
                        chlen := sqv.sqllen;
                        put(res.str.s_data(res.str.s_len ..
                             res.str.s_len + Integer(chlen) - 1));
                        res.str.s_len :=
                               res.str.s_len + Integer(chlen);
                            when others => -- Bad data type
                        put("<type = ");
                        put(sqv.sqltype, 1);
                        put(">");
                end case;                 -- Of data types
            end if;                       -- If null or not
        end;                              -- Declare (rename) block
        if (col < sqlda.sqld) then       -- Add trailing space
             put(" ");
        end if;
    end loop;                             -- For processing columns
    new_line;                             -- Print end of line
end Print_Row;
--
-- Procedure:  Print_Error
-- Purpose:    SQLCA error detected. Retrieve the error message
--             and print it.
--
procedure Print_Error is
      exec sql begin declare section;
            error_buf: String(1..200); -- SQL error text retrieval
      exec sql end declare section;
begin
      exec sql inquire_sql (:error_buf = ERRORTEXT);
      put_line("SQL Error:");
      put_line(error_buf);
end Print_Error;
--
-- Procedure: Read_Stmt
-- Purpose:   Reads a statement from standard input. This routine
--          issues a prompt with the current statement number,
--          and reads the response into the parameter "stmt_buf".
--          No special scanning is done to look for terminators,
--          string delimiters or line continuations.
--
--          On eof-of-file end_error is raised and processed in
--          the main program.
--
--       The routine can be extended to allow line continuations,
--       SQL-style comments and a semicolon terminator.
-- Parameters:
--            stmt_num - Statement number for prompt.
--            stmt_buf - Buffer to fill from input.
--
procedure Read_Stmt (stmt_num: in Integer; stmt_buf: in out String) is
        slen: Natural;

begin                     -- Read_Stmt
      stmt_buf := (1..stmt_buf'length => ' ');
      slen := 0;

      while (slen = 0) loop          -- Ignore empty lines
             put(stmt_num, 3);
             put("> ");
             get_line(stmt_buf, slen);
      end loop;

end Read_Stmt;
--
-- Program: SQL_Monitor Main
-- Purpose: Main entry of SQL Monitor application. Prompt for
--           database name and connect to the database. Run the
--           monitor and disconnect from the database. Before
--           disconnecting roll
--           back any pending updates.
--
begin                                 -- Main Program
      put("SQL Database: ");           -- Prompt for database name
      get_line(dbname, dblen);
      if (dblen = 0) then
           return;
      end if;
      put_line("-- SQL Terminal Monitor --");
      -- Treat connection errors as fatal errors
      exec sql whenever sqlerror stop;
      exec sql connect :dbname;
      dbrun := TRUE;
      Run_Monitor;
      exec sql whenever sqlerror continue;
      exception
          when others =>       -- exit on EOF and other errors
              if (dbrun) then
                    put_line("SQL: Exiting monitor program.");
                    exec sql rollback;
                    exec sql disconnect;
              end if;

end SQL_Monitor;