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 program starts, it prompts the user for the database name. The program then prompts for an SQL statement. SQL comments and statement delimiters are not accepted. 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
100 !
! Program: SQL_Monitor
! 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.
!
program SQL_Monitor
exec sql include sqlca
exec sql begin declare section
declare string dbname ! Database name
exec sql end declare section
linput 'SQL Database'; dbname ! Prompt for database name
if (dbname = '') then
exit program
end if
print '-- SQL Terminal Monitor --'
exec sql whenever sqlerror stop ! Connection errors are fatal
exec sql connect :dbname
call Run_Monitor
exec sql whenever sqlerror continue
print 'SQL: Exiting monitor program.'
exec sql rollback
exec sql disconnect
end program ! SQL_Monitor
!
! Subroutine: 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
! return to the main program.
!
! If the statement is not a SELECT statement
! then EXECUTE it, otherwise open a cursor a process
! a dynamic SELECT statement (using Execute_Select).
!
200 sub Run_Monitor
! Declare the global SQLCA and the SQLDA records
exec sql include sqlca
exec sql include sqlda
common (sqlda_area) IISQLDA sqlda
exec sql begin declare section
declare string stmt_buf ! SQL statement input buffer
exec sql end declare section
declare integer stmt_num ! SQL statement number
declare integer rows ! Rows affected
external byte function Read_Stmt ! Function to read input
external integer function Execute_Select ! and to execute SELECTs
exec sql declare stmt statement ! Dynamic SQL statement
sqlda::sqln = IISQ_MAX_COLS ! Initialize the SQLDA
stmt_num = 1
!
! Now we are set for input. Call Read_Stmt each time through
! the loop. Read_Stmt prompts the user for input (into
! stmt_buf) and returns 0 if end-of-file was typed.
!
while (Read_Stmt(stmt_num, stmt_buf))
stmt_num = stmt_num + 1
! SQL errors cause current statement to be aborted.
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 = sqlerrd(2)
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 (sqlcode < 0) then
call Print_Error
else
print '[' + str$(rows) + ' row(s)]'
end if
next ! While reading statements
end sub ! 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 cursor.
!
300 function integer execute_select
! Declare the global SQLCA and the SQLDA records
exec sql include sqlca
exec sql include sqlda
common (sqlda_area) IISQLDA sqlda
declare integer rows ! Counter for rows fetched
external byte function Print_Header ! Function to set up header
exec sql declare csr cursor for stmt ! Cursor for dynamic statement
!
! Print the result column names and set up the result data
! types and variables. Print_Header returns 0 if it fails.
!
if (not Print_Header) then
Execute_Select = 0
exit function
end if
exec sql whenever sqlerror goto Close_Csr
rows = 0
! Open the dynamic cursor.
exec sql open csr
! Fetch and print each row.
while (sqlcode = 0)
exec sql fetch csr using descriptor :sqlda
if (sqlcode = 0) then
rows = rows + 1 ! Count the rows
call Print_Row
end if
next ! While there are more rows
Close_Csr:
! Display error message if the SQLERROR condition was set.
if (sqlcode < 0) then
call Print_Error
end if
exec sql whenever sqlerror continue
exec sql close csr for readonly
Execute_Select = rows
end function ! 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 chosen out
! of a global 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 (-1) if successfully set up the SQLDA for
! result variables,
! FALSE (0) if an error occurred.
!
400 function byte Print_Header
! Declare global SQLDA record
exec sql include sqlda
common (sqlda_area) IISQLDA sqlda
!
! Global result data storage. This area includes an array
! of numerics (integers, floats and indicator variables), as
! well as a large character buffer from which sub-strings are
! chosen for string retrieval.
!
declare word constant CHAR_MAX = 2500
record num_store ! Pool of numeric variables
long int4
double flt8
word indicator
end record num_store
record char_store ! Pool of string data
word buf_used
string charbuf(CHAR_MAX) = 1
end record char_store
common (result_area) num_store nums(IISQ_MAX_COLS), &
char_store chars
declare integer i ! Index into SQLVAR
declare integer base_type ! Base type w/o nullability
declare byte nullable ! Is column nullable
declare integer ch_len ! Required character length
!
! Verify that there are enough result variables.
! If not print error and return.
!
if (sqlda::sqld > sqlda::sqln) then
print 'SQL Error: SQLDA requires ' + &
str$(sqlda::sqld) + &
' variables, but has only ' + &
str$(sqlda::sqln) + '.'
Print_Header = 0 ! FALSE
exit function
end if
!
! For each column print the number and title. For example:
! [1] name [2] age [3] salary
! While processing each column determine the type of the
! column and to where SQLDATA and SQLIND must point in
! order to retrieve type-compatible results. Note that the
! index into SQLVAR begins at 0 and not 1 because the
! array is zero-based.
!
chars::buf_used = 1 ! Nothing used yet
for i = 0 to sqlda::sqld - 1 ! For each column
! Print column name and number
print '[' + str$(i+1) + '] ' + &
left$(sqlda::sqlvar(i)::sqlnamec, &
sqlda::sqlvar(i)::sqlnamel) + ' ';
!
! Process the column for type and length information. Use
! global result area from which variables can be allocated.
!
! Find the base-type of the result (non-nullable).
if (sqlda::sqlvar(i)::sqltype > 0) then
base_type = sqlda::sqlvar(i)::sqltype
nullable = 0 ! FALSE
else
base_type = -sqlda::sqlvar(i)::sqltype
nullable = -1 ! TRUE
end if
!
! Collapse all different types into one of 4-byte integer,
! 8-byte floating-point, or fixed length character. Figure
! out where to point SQLDATA and SQLIND - which member of
! the global result storage area will retrieve the data.
!
select base_type
case IISQ_INT_TYPE ! Use 4-byte integer
sqlda::sqlvar(i)::sqltype = IISQ_INT_TYPE
sqlda::sqlvar(i)::sqllen = 4
sqlda::sqlvar(i)::sqldata = loc(nums(i)::int4)
case IISQ_FLT_TYPE, IISQ_MNY_TYPE ! Use 8-byte float
sqlda::sqlvar(i)::sqltype = IISQ_FLT_TYPE
sqlda::sqlvar(i)::sqllen = 8
sqlda::sqlvar(i)::sqldata = loc(nums(i)::flt8)
case IISQ_CHA_TYPE, IISQ_VCH_TYPE, IISQ_DTE_TYPE
!
! Determine the length of the sub-string required
! from the large character buffer. If we have enough
! space left then point at the start of the
! corresponding sub-string, otherwise print an
! error and return.
!
! Note that for DATE types we must set the length.
!
if (base_type = IISQ_DTE_TYPE) then
ch_len = IISQ_DTE_LEN
else
ch_len = sqlda::sqlvar(i)::sqllen
end if
if ((chars::buf_used + ch_len) > CHAR_MAX) then
print 'SQL Error: Character data overflow.' + &
' Need more than ' + &
str$(CHAR_MAX) + ' bytes.'
Print_Header = 0 ! FALSE
exit function
end if ! If too many characters
!
! Grab space out of the large character buffer and
! keep track of the amount of space used so far.
!
sqlda::sqlvar(i)::sqltype = IISQ_CHA_TYPE
sqlda::sqlvar(i)::sqllen = ch_len
sqlda::sqlvar(i)::sqldata = &
loc(chars::charbuf(chars::buf_used))
chars::buf_used = chars::buf_used + ch_len
case else ! Bad data type
print 'SQL Error: Unknown data type returned: ' + &
str$(sqlda::sqlvar(i)::sqltype)
Print_Header = 0 ! FALSE
exit function
end select ! Of checking types
! If nullable then point at a null indicator and negate type id
if (nullable) then
sqlda::sqlvar(i)::sqltype = -sqlda::sqlvar(i)::sqltype
sqlda::sqlvar(i)::sqlind = loc(nums(i)::indicator)
else
sqlda::sqlvar(i)::sqlind = 0
end if
next i ! End of processing each column
print '' ! Add separator line
print '--------------------------------------'
Print_header = -1 ! TRUE
end function ! Print_Header
!
! Subroutine: 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.
!
500 sub Print_Row
! Declare global SQLDA record
exec sql include sqlda
common (sqlda_area) IISQLDA sqlda
!
! Global result data storage. Variables from these
! pools were pointed at by the Print_Header routine.
!
declare word constant char_max = 2500
record num_store ! Pool of numeric variables
long int4
double flt8
word indicator
end record num_store
record char_store ! Pool of string data
word buf_used
string charbuf(char_max) = 1
end record char_store
common (result_area) num_store nums(IISQ_MAX_COLS), &
char_store chars
declare integer i ! Index into SQLVAR
declare integer ch ! Index for print characters
declare integer ch_len ! Required character length
!
! 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'.
!
chars::buf_used = 1 ! No characters printed yet
for i = 0 to sqlda::sqld - 1 ! For each column
print '[' + str$(i+1) + '] '; ! Print column number
! If nullable and is NULL then print 'N/A'
if (sqlda::sqlvar(i)::sqltype > 0) and &
(nums(i)::indicator = -1) then
print 'N/A';
else
!
! The type is either not nullable, or nullable
! but not NULL. Print the result using very basic
! output formats.
!
select abs(sqlda::sqlvar(i)::sqltype)
case IISQ_INT_TYPE
print str$(nums(i)::int4);
case IISQ_FLT_TYPE
! This format may lose precision
print str$(nums(i)::flt8);
case IISQ_CHA_TYPE
!
! Use a current-length sub-string from the large
! character buffer, as allocated in Print_Header.
!
ch_len = sqlda::sqlvar(i)::sqllen
for ch = 0 to ch_len - 1
print chars::charbuf(chars::buf_used + ch);
next ch
chars::buf_used = chars::buf_used + ch_len
end select ! Of different types
end if ! If null or not
if (i < sqlda::sqld - 1) then ! Add trailing space
print ' ';
end if
next i ! End of each column
print '' ! Print new line
end sub ! Print_Row
!
! Subroutine: Print_Error
! Purpose: SQLCA error detected. Retrieve the error message
! and print it.
!
600 sub Print_Error
exec sql include sqlca
exec sql begin declare section
declare string error_buf ! For error text retrieval
exec sql end declare section
exec sql inquire_sql (:error_buf = errortext)
print 'SQL Error:'
print error_buf
end sub ! Print_Error
!
! Function: Read_Stmt
! Purpose: Read a statement from standard input. This
! routine issues a prompt with the current statement
! number, and reads the statement from the screen into
! the parameter 'stmt_buf'. No special scanning is done
! to look for terminators, string delimiters or line
! continuations.
!
! This routine can be extended to allow line
! continuation, SQL-style comments, and a semicolon
! terminator.
! Parameters:
! stmt_num - Statement number for prompt.
! stmt_buf - Input statement buffer.
! Returns:
! TRUE (-1) - If a statement is typed in.
! FALSE (0) - If end-of-file is typed in,
! or an error occurred.
!
700 function byte Read_Stmt(integer stmt_num, string stmt_buf)
declare byte was_input ! Return value
stmt_buf = ''
was_input = -1 ! TRUE
! Ignore empty lines and stop on error
while (stmt_buf = '') and (was_input = -1)
when error in
print ' ' + str$(stmt_num);
linput ' '; stmt_buf
use
was_input = 0 ! FALSE
end when
next
Read_Stmt = was_input
end function ! Read_Stmt