SQL

Das Modul SQL bietet im Vergleich zu EmbeddedSQL eine etwas erweiterte Funktionalität an. Es sind eigene Datentypen für die Datenkommunikation zwischen dem Programm und der Datenbank definiert, die es zum Beispiel auch erlauben, beliebig lange binäre Daten zu übertragen (BLOBs, binary large objects). Solche dateibasierten Datentypen werden im Modul EmbeddedSQL nicht unterstützt.

 nnnnnnnn¯ 
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;

			 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;

 nullString-: ARRAY 2 OF CHAR;

 options: SET;

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

	PROCEDURE  OpenUI (connStrIn: ARRAY OF CHAR;

			VAR connStrOut: ARRAY OF CHAR): Connection;

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

			stmt: LONGINT);

	PROCEDURE  SetOption;

	PROCEDURE  PrepareStatement (c: Connection;

			sqlString: ARRAY OF CHAR): Statement;

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

	PROCEDURE  Tables (c: Connection): Statement;

	PROCEDURE  Commit (c: Connection);

	PROCEDURE  Rollback (c: Connection);

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

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

			createParams: ARRAY OF CHAR);

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

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

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

	PROCEDURE  NewConnection (): Connection;

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

			numParams: INTEGER);

	PROCEDURE  Execute (s: Statement);

	PROCEDURE  RowCount (s: Statement; VAR rows: LONGINT);

	PROCEDURE  Fetch (s: Statement);

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

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

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

	PROCEDURE  NewStatement (c: Connection): Statement;

	PROCEDURE  GenerateResultRow (s: Statement);

	PROCEDURE  FirstField (r: Row; VAR f: Field);

	PROCEDURE  NextField (VAR f: Field);

	PROCEDURE  PrevField (VAR f: Field);

	PROCEDURE  FindField (r: Row; name: ARRAY OF CHAR; VAR f: Field);

	PROCEDURE  EnumDataSources (cb: EnumDataSourcesCB);

	PROCEDURE  EnumDataTypes (c: Connection; cb: EnumDataTypesCB;

			sqlType: INTEGER);

	PROCEDURE  EnumDrivers (cb: EnumDriversCB);

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

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

END SQL.

Um mit einer Datenbank arbeiten zu können, muß zuerst eine Verbindung zur Datenbank hergestellt werden. Die Datenstruktur Connection stellt eine solche Verbindung dar. Nach dem Aufbau der Verbindung kann man SQL-Anweisungen ausführen. Man kann natürlich mit mehreren Verbindungen gleichzeitig arbeiten, sie werden automatisch geschlossen, wenn sie nicht mehr benötigt werden.

• Allgemeine Operationen
• Datenbankverbindung
• SQL-Anweisungen
• Datentypen
  • Datentypen für Zeichenketten
  • Datentypen für Zahlen
  • Datentypen für Datum und Uhrzeit
• Umsetzung von Oberon- auf Datenbankdatentypen
• Dateibasierte Datentypen
• Beispiele
  • "`Static SQL"'-Anweisungen
  • SQL-Anweisungen mit Parameter-Platzhaltern
  • Abfragen