-- Copyright © Oracle Corporation 1995. All Rights Reserved. -- This SQL module provides the SQL procedures needed by the -- SQL$ALL_DATATYPES.ADA program. -- -- The procedures create a schema and table, insert a row in the -- table, declare, open, and use a cursor to fetch a row, update a row, -- and delete the schema. -- -- The program also shows how to use SQL to convert a date to the -- 16-character string format used in versions of Rdb/VMS prior to V4.1. -- -- Because this module uses parameter names prefixed with colons, -- in compliance with the ANSI/ISO standard, you must supply -- the /ANSI_PARAMETERS qualifier on the module language command line. -- -- In order to compile the generated Ada package that lets you use this -- module with an Ada program, you must supply the /PACKAGE_COMPILATION -- qualifier on the module language command line. -- -- To build this example, issue the following commands: -- -- $ ACS CREATE LIBRARY [.ADALIB] -- $ ACS SET LIB [.ADALIB] -- $ ADA/NOLIS SYS$LIBRARY:SQL$STANDARD -- $ SQLMOD -- SQL$ALL_DATATYPES_ADA/PACK -- $ ADA SQL$ALL_DATATYPES -- $ ACS LINK SQL_ALL_DATATYPES SQL$ALL_DATATYPES_ADA /EXEC=SQL$ALL_DATATYPES -- $ RUN SQL$ALL_DATATYPES -- -- If you use the /EXEC= qualifier, you should not have to rename the file. -- -------------------------------------------------------------------------- -- Header Information Section -------------------------------------------------------------------------- MODULE SQL_ALL_DATATYPES -- Module name LANGUAGE ADA -- Language of calling program AUTHORIZATION SQL_SAMPLE -- Provides default authorization -- identifier PARAMETER COLONS -- Parameters are prefixed by colons -------------------------------------------------------------------------- -- DECLARE Statements Section -------------------------------------------------------------------------- DECLARE CONVERSIONS CURSOR FOR SELECT * FROM ALL_DATATYPES_TABLE -------------------------------------------------------------------------- -- Procedure Section -------------------------------------------------------------------------- -- This procedure creates a schema called ALL_DATATYPES and creates -- a single table called ALL_DATATYPES_TABLE. -- The columns represent all of the data types for Ada supported by -- SQL. Note that LONG VARCHAR and FLOAT(n) data type examples have -- been left out of the table. LONG VARCHAR is equivalent to -- VARCHAR(16383); FLOAT(1) through FLOAT(24) are equivalent to REAL; -- and FLOAT(25) through FLOAT(53) are equivalent to DOUBLE PRECISION. PROCEDURE CREATE_DATABASE SQLCODE; CREATE DATABASE FILENAME ALL_DATATYPES CREATE TABLE ALL_DATATYPES_TABLE ( CHAR_COL CHAR(10), VARCHAR_COL VARCHAR(40), TINYINT_COL TINYINT, SMALLINT_COL SMALLINT, INTEGER_COL INTEGER, REAL_COL REAL, DOUBLE_PREC_COL DOUBLE PRECISION, DATE_COL DATE ); -- This procedure deletes a database. PROCEDURE DROP_DATABASE SQLCODE; DROP DATABASE FILENAME ALL_DATATYPES; -- This procedure inserts a row in the table. PROCEDURE STORE_ALL_DATATYPES SQLCODE :P_CHAR_COL CHAR(10) :P_CHAR_IND SMALLINT :P_VARCHAR_COL VARCHAR(40) :P_VARCHAR_IND SMALLINT :P_TINYINT_COL TINYINT :P_TINYINT_IND SMALLINT :P_SMALLINT_COL SMALLINT :P_SMALLINT_IND SMALLINT :P_INTEGER_COL INTEGER :P_INTEGER_IND SMALLINT :P_REAL_COL REAL :P_REAL_IND SMALLINT :P_DOUBLE_PREC_COL DOUBLE PRECISION :P_DOUBLE_PREC_IND SMALLINT :P_DATE_COL DATE :P_DATE_IND SMALLINT; INSERT INTO ALL_DATATYPES_TABLE (CHAR_COL, VARCHAR_COL, TINYINT_COL,SMALLINT_COL,INTEGER_COL, REAL_COL, DOUBLE_PREC_COL, DATE_COL) VALUES (:P_CHAR_COL INDICATOR :P_CHAR_IND, :P_VARCHAR_COL INDICATOR :P_VARCHAR_IND, :P_TINYINT_COL INDICATOR :P_TINYINT_IND, :P_SMALLINT_COL INDICATOR :P_SMALLINT_IND, :P_INTEGER_COL INDICATOR :P_INTEGER_IND, :P_REAL_COL INDICATOR :P_REAL_IND, :P_DOUBLE_PREC_COL INDICATOR :P_DOUBLE_PREC_IND, :P_DATE_COL INDICATOR :P_DATE_IND); -- This procedure opens the cursor that has been declared for -- ALL_DATATYPES_TABLE. PROCEDURE OPEN_CURSOR SQLCODE; OPEN CONVERSIONS; -- This procedure closes the cursor that has been declared for -- ALL_DATATYPES_TABLE. PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE CONVERSIONS; -- This procedure fetches all the data from the opened cursor. PROCEDURE FETCH_ALL_DATATYPES SQLCODE :P_CHAR_COL CHAR(10) :P_CHAR_IND SMALLINT :P_VARCHAR_COL VARCHAR(40) :P_VARCHAR_IND SMALLINT :P_TINYINT_COL TINYINT :P_TINYINT_IND SMALLINT :P_SMALLINT_COL SMALLINT :P_SMALLINT_IND SMALLINT :P_INTEGER_COL INTEGER :P_INTEGER_IND SMALLINT :P_REAL_COL REAL :P_REAL_IND SMALLINT :P_DOUBLE_PREC_COL DOUBLE PRECISION :P_DOUBLE_PREC_IND SMALLINT :P_DATE_COL DATE :P_DATE_IND SMALLINT; FETCH CONVERSIONS INTO :P_CHAR_COL INDICATOR :P_CHAR_IND, :P_VARCHAR_COL INDICATOR :P_VARCHAR_IND, :P_TINYINT_COL INDICATOR :P_TINYINT_IND, :P_SMALLINT_COL INDICATOR :P_SMALLINT_IND, :P_INTEGER_COL INDICATOR :P_INTEGER_IND, :P_REAL_COL INDICATOR :P_REAL_IND, :P_DOUBLE_PREC_COL INDICATOR :P_DOUBLE_PREC_IND, :P_DATE_COL INDICATOR :P_DATE_IND; -- This procedure fetches all of the data from the opened cursor and -- also converts DATE_COL to a text data type. PROCEDURE FETCH_CONVERT_ALL_DATATYPES SQLCODE :P_CHAR_COL CHAR(10) :P_CHAR_IND SMALLINT :P_VARCHAR_COL VARCHAR(40) :P_VARCHAR_IND SMALLINT :P_TINYINT_COL TINYINT :P_TINYINT_IND SMALLINT :P_SMALLINT_COL SMALLINT :P_SMALLINT_IND SMALLINT :P_INTEGER_COL INTEGER :P_INTEGER_IND SMALLINT :P_REAL_COL REAL :P_REAL_IND SMALLINT :P_DOUBLE_PREC_COL DOUBLE PRECISION :P_DOUBLE_PREC_IND SMALLINT :P_STRING_DATE_DSRI CHAR(16) :P_DATE_IND SMALLINT; FETCH CONVERSIONS INTO :P_CHAR_COL INDICATOR :P_CHAR_IND, :P_VARCHAR_COL INDICATOR :P_VARCHAR_IND, :P_TINYINT_COL INDICATOR :P_TINYINT_IND, :P_SMALLINT_COL INDICATOR :P_SMALLINT_IND, :P_INTEGER_COL INDICATOR :P_INTEGER_IND, :P_REAL_COL INDICATOR :P_REAL_IND, :P_DOUBLE_PREC_COL INDICATOR :P_DOUBLE_PREC_IND, :P_STRING_DATE_DSRI INDICATOR :P_DATE_IND; -- This procedure updates a row in ALL_DATATYPES_TABLE. PROCEDURE UPDATE_ALL_DATATYPES SQLCODE :P_CHAR_COL CHAR(10) :P_CHAR_IND SMALLINT :P_TINYINT_COL TINYINT :P_TINYINT_IND SMALLINT :P_SMALLINT_COL SMALLINT :P_SMALLINT_IND SMALLINT :P_INTEGER_COL INTEGER :P_INTEGER_IND SMALLINT; UPDATE ALL_DATATYPES_TABLE SET CHAR_COL = :P_CHAR_COL INDICATOR :P_CHAR_IND, TINYINT_COL = :P_TINYINT_COL INDICATOR :P_TINYINT_IND, SMALLINT_COL = :P_SMALLINT_COL INDICATOR :P_SMALLINT_IND, INTEGER_COL = :P_INTEGER_COL INDICATOR :P_INTEGER_IND WHERE CURRENT OF CONVERSIONS; -- This procedure rolls back the transaction. PROCEDURE ROLLBACK_TRANSACTION SQLCODE; ROLLBACK;