SQL

 nnnnnn¯ 
DEFINITION  SQL;

IMPORT Files;

CONST

	 InvHandle = -2;  Error = -1;  Success = 0;	 (*   return codes *)

	 SuccessWithInfo = 1;  NeedData = 99;  NoDataFound = 100;

	 showErrors = 1;  haltOnErrors = 2;  verboseErrorMsg = 3;  (*   options *)

	 InParam = 1;  InOutParam = 2;  OutParam = 4;	(*   parameter types  *)

	 NTS = -3;  NullData = -1;	(*   parameter values *)

	 OberonChar = 1;  OberonShortInt = -6;  OberonBoolean = -7;

	 OberonInteger = 5;  OberonLongInt = 4;  OberonReal = 7;

	 OberonLongReal = 8;  OberonBinary = -2;  OberonFile = -4;

	 OberonDate = 9;  OberonTime = 10;  OberonTimeStamp = 11;

	 SQLBit = -7;  SQLTinyInt = -6;  SQLBigInt = -5;

	 SQLLVarBin = -4;  SQLVarBin = -3;  SQLBin = -2;

	 SQLLVarChar = -1;  SQLChar = 1;  SQLNumeric = 2;

	 SQLDecimal = 3;  SQLInteger = 4;  SQLSmallInt = 5;

	 SQLFloat = 6;  SQLReal = 7;  SQLDouble = 8;

	 SQLDate = 9;  SQLTime = 10;  SQLTimeStamp = 11;

	 SQLVarChar = 12;

TYPE

	 Type = RECORD

		 sqlType: INTEGER;

		 prec: LONGINT; (*   maximum number of significant digits *)

		 scale: INTEGER (*   -"- right to period *)

	END ;

	 Field = POINTER TO FieldDesc;

	 FieldDesc = RECORD

		 name: ARRAY 32 OF CHAR; (*   of the column or parameter *)

		 len: LONGINT;

		 type: Type;

		 isNull,  nullable: BOOLEAN

	END ;

	 IntField = POINTER TO IntFieldDesc;

	 IntFieldDesc = RECORD (FieldDesc)  i: LONGINT END ;

	 FileField = POINTER TO FileFieldDesc;

	 FileFieldDesc = RECORD (FieldDesc)  f: Files.File END ;

	 StringField = POINTER TO StringFieldDesc;

	 StringFieldDesc = RECORD (FieldDesc)  str: ARRAY 256 OF CHAR END ;

	 BinaryField = POINTER TO BinaryFieldDesc;

	 BinaryFieldDesc = RECORD (FieldDesc)  b: ARRAY 256 OF CHAR END ;

	 BooleanField = POINTER TO BooleanFieldDesc;

	 BooleanFieldDesc = RECORD (FieldDesc)  b: BOOLEAN END ;

	 DateField = POINTER TO DateFieldDesc;

	 DateFieldDesc = RECORD (FieldDesc)  year,  month,  day: INTEGER END ;

	 TimeField = POINTER TO TimeFieldDesc;

	 TimeFieldDesc = RECORD (FieldDesc)  hour,  minute,  second: INTEGER END ;

	 TimeStampField = POINTER TO TimeStampFieldDesc;

	 TimeStampFieldDesc = RECORD (FieldDesc)

		 year,  month,  day,  hour,  minute,  second: INTEGER;

		 fraction: LONGINT

	END ;

	 RealField = POINTER TO RealFieldDesc;

	 RealFieldDesc = RECORD (FieldDesc)  r: LONGREAL END ;

	 ParamDesc = RECORD

		 type,  mapType: Type;

		 inOut: INTEGER;

		 name: ARRAY 32 OF CHAR

	END ;

	 Row = POINTER TO RowDesc;

	 RowDesc = RECORD  cols: INTEGER END ;

	 Connection = POINTER TO ConnectionD;

	 ConnectionD = RECORD

		 ret: INTEGER; (*   error code of last operation  *)

		 dbc-: LONGINT (*   ODBC connection handle *)

	END ;

	 Statement = POINTER TO StatementD;

	 StatementD = RECORD

		 ret: INTEGER; (*   error code of last operation  *)

		 stmt-: LONGINT; (*   ODBC statement handle *)

		 conn-: Connection; (*   associated connection *)

		 results,  params: Row (*   row of results and parameters *)

	END ;

	 EnumDataSourcesCB = PROCEDURE (dataSource, description: ARRAY OF CHAR;

			VAR continue: BOOLEAN);

	 EnumDataTypesCB = PROCEDURE (typeName: ARRAY OF CHAR;

			dataType: INTEGER; prec: LONGINT; literalPrefix, literalSuffix,

			createPars: ARRAY OF CHAR; nullable: INTEGER; caseSensitive: BOOLEAN;

			searchable, unsigned: INTEGER; money: BOOLEAN; autoInc: INTEGER;

			localTypeName: ARRAY OF CHAR; minScale, maxScale: INTEGER;

			VAR continue: BOOLEAN);

	 EnumDriversCB = PROCEDURE (driver, attributes: ARRAY OF CHAR;

			VAR continue: BOOLEAN);

\
 env-: LONGINT;	(*   environment handle for the ODBC session *)

 nullString-: ARRAY 2 OF CHAR;

 options: SET;

	PROCEDURE  Open (source, user, passwd: ARRAY OF CHAR): Connection; (*   opens a 

			  connection to the specified database source with given user identification and password. *)

	PROCEDURE  OpenUI (connStrIn: ARRAY OF CHAR;

			VAR connStrOut: ARRAY OF CHAR): Connection; (*   opens a connection as specified

			  in connStrIn with possibly further user-interaction via dialog boxes. connStrOut 

			  is the full connection string that can be used the next time to establish the connection 

			  again. *)

	PROCEDURE  ShowError (ret: INTEGER; str: ARRAY OF CHAR; env, dbc,

			stmt: LONGINT);

	PROCEDURE  SetOption; (*   sets or resets the specified option, valid options are 1

			  (showErrors), 2 (haltOnErrors), and 3 (verboseErrorMsg). By specifying a negative value 

			  the option is reset. *)

	PROCEDURE  PrepareStatement (c: Connection;

			sqlString: ARRAY OF CHAR): Statement;  (*   prepares an SQL statement for execution.

			  If the sqlString returns any results (e.g. a SELECT statement) the member variable results

			  of the statement will point to a row containing a field for each column of the result set,

			  otherwise the member variable results of the statement is NIL. After PrepareStatement the

			  record field len of each element of results contains the maximum number of characters needed

			  to represent the corresponding data. The value in this record field len will only be valid until

			  a call to Execute, so if you need this information you must check it between the calls to

			  PrepareStatement and to Execute. *)

	PROCEDURE  ErrC (str: ARRAY OF CHAR; c: Connection);

	PROCEDURE  Tables (c: Connection): Statement; (*   returns a prepared statement holding 

			  a list of all table names in the specified data source. *)

	PROCEDURE  Commit (c: Connection); (*   commits all statements associated with the

			  specified connection c (if supported by the database). *)

	PROCEDURE  Rollback (c: Connection); (*   rolls back all statements executed on the 

			  connection c since the last Commit (if supported by the database).  *)

	PROCEDURE  Map (c: Connection; type: Type; VAR mapType: Type): BOOLEAN;

			 (*   maps the desired sqlType with the specified precision and scale to the data type that 

			   is supported by the connection (e.g. BIGINT may be mapped to CHAR(20), sqlType=-5,

			  mapType=1, mapPrec=20). *)

	PROCEDURE  GetTypeName (c: Connection; sqlType: INTEGER; VAR typeName,

			createParams: ARRAY OF CHAR);  (*   returns the name of the SQL data type as it is

			  used in the specified data source connection (e.g. BIGINT may be mapped to CHAR(20),

			  sqlType=-5, mapType=1, mapPrec=20).  *)

	PROCEDURE  GetConnOption (c: Connection; option: INTEGER; info: Field);

			(*   returns the current setting of the specified option. Valid values for option and info are 

			  listed in the section . The info field must be either an IntField or a StringField 

			  depending on the value of option. *)

	PROCEDURE  SetConnOption (c: Connection; option: INTEGER; info: Field);

			(*   sets options that govern aspects of the connection. Valid values for option and field are 

			  listed in the section . The info field must be either an IntField or a StringField 

			  depending on the value of option. *)

	PROCEDURE  GetInfo (c: Connection; infoNr: INTEGER; info: Field);

			(*   returns general information about the driver and data source associated with the connection.

			  Valid values for infoNr and info are listed in the section . The info field 

			  must be either an IntField or a StringField depending on the value of infoNr. *)

	PROCEDURE  NewConnection (): Connection; (*   returns a newly initialized connection

			  which is not yet connected with a data source. *)

	PROCEDURE  BindParameters (s: Statement; types: ARRAY OF ParamDesc;

			numParams: INTEGER); (*   binds the parameter fields to the statement s. The 

			  array of parameters contains a description of each parameter: types[i].type determines the type

			  to be used in Oberon (i.e. what sort of Field (IntField, FileField, ...) should be 

			  added to params). types[i].mapType determines the type used in the data source (SQLInteger, 

			  SQLLVarBin, ...). types[i].inOut determines for which operation the parameter is used:

			  data transfer from the applcation to the database => InParam (parameter used in an INSERT

			  statement or in a stored procedure), to data transfer from the database to the application 

			  => OutParam (parameter is used in a stored procedure to get data), or bidirectional data 

			  transfer => InOutParam (parameter is used in a stored procedure for input and output). 

			  types[i].name can be used to name the parameter (i.e. you can find the corresponding Field in 

			  the member variable params of the statement with the procedure FindField). *)

	PROCEDURE  Execute (s: Statement); (*   executes the previously prepared statement. If the 

			  statement delivers any data it can be retrieved after Execute with the procedure Fetch.

			  IMPORTANT: a statement which does not need parameters and does not return any data (e.g.

			  "DROP TABLE MyTable") will take effect even if you do not call Execute for this statement. *)

	PROCEDURE  RowCount (s: Statement; VAR rows: LONGINT); (*   returns the number of 

			  rows affected by the execution of s. This is not the number of rows which are delivered by a

			  SELECT statement but rather the number of rows affected by an UPDATE or DELETE statement.

			  If you want the number of rows in the result set of a SELECT statement use "SELECT 

			  COUNT(*) ..." or increment a counter variable after each successful call to Fetch. *)

	PROCEDURE  Fetch (s: Statement); (*   fetches the next result row for statement s (statement s 

			  must, of course, have been executed before calling Fetch *)

	PROCEDURE  ErrS (str: ARRAY OF CHAR; s: Statement);

	PROCEDURE  GetStatementOption (s: Statement; option: INTEGER; info: Field);

			(*   returns the current setting of the specified statement option. Valid values for option and 

			  info are listed in the section . The info field must be either an IntField or a 

			  StringField depending on the value of option. *)

	PROCEDURE  SetStatementOption (s: Statement; option: INTEGER; info: Field);

			(*   sets options related with the statement. To set options associated with the connection, use 

			  SetConnOption. Valid values for option and info are listed in the section . The info 

			  field must be either an IntField or a StringField depending on the value of option. *)

	PROCEDURE  NewStatement (c: Connection): Statement; (*   returns a newly initialized 

			  statement which is associated with the connection c. *)

	PROCEDURE  GenerateResultRow (s: Statement); (*   creates the result row with the

			  necessary fields for the prepared statement s. *)

	PROCEDURE  FirstField (r: Row; VAR f: Field); (*   sets f to the first field in row r . 

			  If row = NIL, f is NIL too. *)

	PROCEDURE  NextField (VAR f: Field); (*   sets f to the next field in the row containing f,

			  NIL if there are no more fields. *)

	PROCEDURE  PrevField (VAR f: Field); (*   sets f to the previous field in the row containing f, 

			  NIL if there is no previous field. *)

	PROCEDURE  FindField (r: Row; name: ARRAY OF CHAR; VAR f: Field); (*   sets f to the 

			  field named name in row r. *)

	PROCEDURE  EnumDataSources (cb: EnumDataSourcesCB); (*   enumerates all data sources,

			  by setting the parameter continue of the callback procedure to FALSE you can end the enumeration

			  process. *)

	PROCEDURE  EnumDataTypes (c: Connection; cb: EnumDataTypesCB;

			sqlType: INTEGER); (*   enumerates all data types supported by the database for the specified 

			  sqlType (may be 0 in order to enumerate all data types), by setting the parameter continue of

			  the callback procedure to FALSE you can end the enumeration process.  *)

	PROCEDURE  EnumDrivers (cb: EnumDriversCB); (*   enumerates all ODBC database drivers,

			  by setting the parameter continue of the callback procedure to FALSE you can end the

			  enumeration process. *)

	PROCEDURE  SQLError (env, dbc, stmt: LONGINT; VAR sqlState: ARRAY OF CHAR;

			VAR nativeErr: LONGINT; VAR errMsg: ARRAY OF CHAR): INTEGER;

END SQL.