MODULE SQL;
IMPORT ODBC, Modules, KernelLog, SYSTEM, Files, Kernel, Heaps;
CONST
NTS = -3;
DataAtExec = -2;
BlockSize = 1024;
Error* = -1;
Success* = 0;
SuccessWithInfo* = 1;
NeedData* = 99;
NoDataFound* = 100;
InParam* = 1;
InOutParam* = 2;
OutParam* = 4;
SqlBitType* = -7;
SqlTinyIntType* = -6;
SqlBigIntType* = -5;
SqlLongVarBinaryType* = -4;
SqlVarBinaryType* = -3;
SqlBinaryType* = -2;
SqlLongVarCharType* = -1;
SqlCharType* = 1;
SqlNumericType* = 2;
SqlDecimalType* = 3;
SqlLongIntType* = 4;
SqlIntType* = 5;
SqlFloatType* = 6;
SqlRealType* = 7;
SqlLongRealType* = 8;
SqlDateType* = 9;
SqlTimeType* = 10;
SqlTimeStampType* = 11;
SqlVarCharType* = 12;
CharType* = 1;
LongIntType* = 4;
IntType* = 5;
ShortIntType* = -6;
RealType* = 7;
LongRealType* = 8;
DateType* = 9;
TimeType* = 10;
TimeStampType* = 11;
FileType* = -4;
BinaryType* = -2;
BooleanType* = -7;
FetchNext* = 1;
FetchFirst* = 2;
FetchLast* = 3;
FetchPrevious* = 4;
FetchAbsolute* = 5;
FetchRelative* = 6;
SQLAttrCursorScrollable = -(1);
SQLNonScrollable = 0;
SQLScrollable = 1;
SQLAttrODBCCursors = 110;
SQLCursorUseIfNeeded = 0;
SQLCursorUseODBC = 1;
SQLCursorUseDriver = 2;
TYPE
SourcesHandler* = PROCEDURE(name, desc: ARRAY OF CHAR);
ErrorProc* = PROCEDURE(state, msg: ARRAY OF CHAR; errorcode: LONGINT);
Connection* = POINTER TO ConnectionDesc;
Statement* = POINTER TO StatementDesc;
Row* = POINTER TO RowDesc;
ErrBuff = POINTER TO ErrBuffDesc;
ConnectionDesc = RECORD
hdbc: ODBC.HDBC;
closed: BOOLEAN;
stmt: Statement;
res*: INTEGER;
END;
StatementDesc = RECORD
next: Statement;
hstmt: ODBC.HSTMT;
c: Connection;
results, params: Row;
firstExec, errBuffered: BOOLEAN;
error: ErrBuff;
res*: INTEGER;
END;
ErrBuffDesc = RECORD
state: ARRAY 6 OF CHAR;
msg: ARRAY 512 OF CHAR;
native: LONGINT;
next: ErrBuff
END;
Field* = POINTER TO FieldDesc;
FieldDesc* = RECORD
next, prev: Field;
dir: INTEGER;
name*: ARRAY 32 OF CHAR;
len*: LONGINT;
sqlType*: INTEGER;
isNull*: BOOLEAN;
nullable*: BOOLEAN
END;
IntField* = POINTER TO IntFieldDesc;
IntFieldDesc* = RECORD(FieldDesc)
i*: LONGINT
END;
NumericField* = POINTER TO NumericFieldDesc;
NumericFieldDesc* = RECORD(FieldDesc)
str*: ARRAY 256 OF CHAR
END;
StringField* = POINTER TO StringFieldDesc;
StringFieldDesc* = RECORD(FieldDesc)
str*: ARRAY 256 OF CHAR
END;
RealField* = POINTER TO RealFieldDesc;
RealFieldDesc* = RECORD(FieldDesc)
r*: LONGREAL
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;
FileField* = POINTER TO FileFieldDesc;
FileFieldDesc* = RECORD(FieldDesc)
f*: Files.File
END;
BinaryField* = POINTER TO BinaryFieldDesc;
BinaryFieldDesc* = RECORD(FieldDesc)
b*: ARRAY 256 OF SYSTEM.BYTE
END;
BooleanField* = POINTER TO BooleanFieldDesc;
BooleanFieldDesc* = RECORD(FieldDesc)
b*: BOOLEAN
END;
SentinelField = POINTER TO SentinelFieldDesc;
SentinelFieldDesc = RECORD(FieldDesc)
END;
RowDesc* = RECORD
dsc: Field;
cols*: INTEGER
END;
ParamType* = RECORD
oberonType*, sqlType*, inOut*: INTEGER;
name*: ARRAY 32 OF CHAR;
END;
VAR
PROCEDURE AllocRow(VAR row: Row);
VAR sentinel: SentinelField;
BEGIN
NEW(row); NEW(sentinel); row.dsc:= sentinel;
sentinel.next:= sentinel; sentinel.prev:= sentinel
END AllocRow;
PROCEDURE AppendField(r: Row; f: Field);
BEGIN
r.dsc.prev.next:= f; f.prev:= r.dsc.prev; f.next:= r.dsc; r.dsc.prev:= f
END AppendField;
PROCEDURE FirstField*(r: Row; VAR f: Field);
BEGIN
IF r.dsc.next = r.dsc THEN f:= NIL ELSE f:= r.dsc.next END
END FirstField;
PROCEDURE NextField*(VAR f: Field);
BEGIN
IF f.next IS SentinelField THEN f:= NIL ELSE f:=f.next END
END NextField;
PROCEDURE PrevField*(VAR f: Field);
BEGIN
IF f.prev IS SentinelField THEN f:= NIL ELSE f:= f.prev END
END PrevField;
PROCEDURE FindField*(r: Row; name: ARRAY OF CHAR; VAR f: Field);
VAR cur: Field;
BEGIN
cur:= r.dsc.next;
WHILE ~(cur IS SentinelField) DO
IF cur.name = name THEN f:= cur; RETURN END;
cur:= cur.next
END;
f:= NIL
END FindField;
PROCEDURE PrintError(state, msg: ARRAY OF CHAR; errorCode: LONGINT);
BEGIN
KernelLog.String(state); KernelLog.String(msg); KernelLog.Ln
END PrintError;
PROCEDURE DummyEnum(state, msg: ARRAY OF CHAR; code: LONGINT);
END DummyEnum;
PROCEDURE InsertError(stmt: Statement; state, msg: ARRAY OF CHAR; nativeCode: LONGINT);
VAR err: ErrBuff;
BEGIN
NEW(err); stmt.errBuffered:= TRUE; err.next:= stmt.error; stmt.error:= err;
COPY(state, err.state); COPY(msg, err.msg); err.native:= nativeCode
END InsertError;
PROCEDURE EnumErrors*(c: Connection; s: Statement; enum: ErrorProc);
VAR localErr: ErrBuff; errorState: ARRAY 6 OF CHAR; errorMsg: ARRAY 512 OF CHAR; nativeError: LONGINT;
BEGIN
IF s # NIL THEN
IF s.errBuffered THEN
localErr:= s.error;
WHILE localErr # NIL DO
enum(s.error.state, s.error.msg, s.error.native); localErr:= localErr.next
END;
s.errBuffered:= FALSE; s.error:= NIL
END;
ODBC.StatementError(s.hstmt, errorState, nativeError, errorMsg, s.res);
WHILE s.res # 100 DO
enum(errorState, errorMsg, nativeError);
ODBC.StatementError(s.hstmt, errorState, nativeError, errorMsg, s.res);
END;
ODBC.ConnectionError(s.c.hdbc, errorState, nativeError, errorMsg, s.res);
WHILE s.res # 100 DO
enum(errorState, errorMsg, nativeError);
ODBC.ConnectionError(s.c.hdbc, errorState, nativeError, errorMsg, s.res);
END
ELSIF c # NIL THEN
ODBC.ConnectionError(c.hdbc, errorState, nativeError, errorMsg, c.res);
WHILE c.res # 100 DO
enum(errorState, errorMsg, nativeError);
ODBC.ConnectionError(c.hdbc, errorState, nativeError, errorMsg, c.res);
END
END
END EnumErrors;
PROCEDURE FinalizeConnection(obj: ANY);
VAR c: Connection;
BEGIN
c:= obj(Connection); KernelLog.String("SQL.FinalizeConnection: ");
IF ~c.closed THEN
KernelLog.String("closing connection"); KernelLog.Ln;
ODBC.Commit(c.hdbc, c.res);
ODBC.Disconnect(c.hdbc, c.res);
ODBC.FreeConnect(c.hdbc, c.res)
ELSE
KernelLog.String("connection already closed"); KernelLog.Ln
END
END FinalizeConnection;
PROCEDURE Terminate;
BEGIN
KernelLog.String("SQL.Terminate: calling Kernel.GC"); KernelLog.Ln;
Kernel.GC;
KernelLog.String("SQL.Terminate: calling Kernel.GC a second time"); KernelLog.Ln;
Kernel.GC
END Terminate;
PROCEDURE Open*(source, user, passwd: ARRAY OF CHAR): Connection;
VAR connect: ODBC.HDBC; connection: Connection;
finalizerNode: Heaps.FinalizerNode;
BEGIN
NEW(connection); NEW(connection.hdbc); connection.stmt:= NIL; connection.closed:= FALSE;
ODBC.AllocConnect(connection.hdbc, connection.res);
ODBC.SetConnectAttr(connection.hdbc, SQLAttrODBCCursors, SQLCursorUseODBC, 0, connection.res);
ODBC.Connect(connection.hdbc, source, user, passwd, connection.res);
NEW(finalizerNode); finalizerNode.finalizer := FinalizeConnection;
Heaps.AddFinalizer(connection, finalizerNode);
RETURN connection
END Open;
PROCEDURE Close*(c: Connection);
VAR stmt: Statement;
BEGIN
stmt:= c.stmt; c.closed:= TRUE;
ODBC.Disconnect(c.hdbc, c.res);
ODBC.FreeConnect(c.hdbc, c.res);
END Close;
PROCEDURE BindParameters*(s: Statement; types: ARRAY OF ParamType; numParams: INTEGER; VAR paramRow: Row);
TYPE arr6 = ARRAY 6 OF CHAR; arr16 = ARRAY 16 OF CHAR;
VAR i, parType: INTEGER; if: IntField; sf: StringField; rf: RealField; df: DateField; tf: TimeField; tsf: TimeStampField;
ff: FileField; bf: BinaryField; boolf: BooleanField; buffer: ARRAY BlockSize OF CHAR;
BEGIN
AllocRow(s.params); s.params.cols:= numParams;
FOR i:= 0 TO numParams-1 DO
CASE types[i].oberonType OF
CharType:
NEW(sf); AppendField(s.params, sf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 255, 0, sf.str, sf.len, s.res)
| LongIntType, IntType, ShortIntType:
NEW(if); AppendField(s.params, if);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 0, 0, if.i, if.len, s.res)
| RealType, LongRealType:
NEW(rf); AppendField(s.params, rf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 0, 0, rf.r, rf.len, s.res)
| DateType:
NEW(df); AppendField(s.params, df);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 0, 0,
SYSTEM.VAL(arr6, df.year), df.len, s.res)
| TimeType:
NEW(tf); AppendField(s.params, tf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 0, 0,
SYSTEM.VAL(arr6, tf.hour), tf.len, s.res)
| TimeStampType:
NEW(tsf); AppendField(s.params, tsf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 0, 0,
SYSTEM.VAL(arr16, tsf.year), tsf.len, s.res)
| FileType:
NEW(ff); ff.f:= Files.New(""); AppendField(s.params, ff);
IF (types[i].sqlType = SqlVarCharType) OR (types[i].sqlType = SqlLongVarCharType) THEN
parType:= CharType
ELSIF (types[i].sqlType = SqlVarBinaryType) OR (types[i].sqlType = SqlLongVarBinaryType) THEN
parType:= BinaryType
ELSIF (types[i].sqlType = SqlFloatType) THEN
parType:= RealType
ELSIF (types[i].sqlType = SqlNumericType) OR (types[i].sqlType = SqlDecimalType) THEN
parType:= CharType
ELSIF types[i].sqlType = SqlBigIntType THEN
parType:= CharType
ELSE
parType:= types[i].sqlType
END;
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, parType, types[i].sqlType, 0, 0, buffer, ff.len, s.res)
| BinaryType:
NEW(bf); AppendField(s.params, bf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 255, 0, bf.b, bf.len, s.res)
| BooleanType:
NEW(boolf); AppendField(s.params, boolf);
ODBC.BindParameter(s.hstmt, i+1, types[i].inOut, types[i].oberonType, types[i].sqlType, 255, 0, boolf.b, boolf.len, s.res)
ELSE HALT(99)
END;
s.params.dsc.prev.dir:= types[i].inOut; COPY(types[i].name, s.params.dsc.prev.name);
IF s.res < 0 THEN s.params:= NIL; paramRow:= NIL; RETURN END
END;
paramRow:= s.params
END BindParameters;
PROCEDURE PrepareStatement*(c: Connection; sqlStatement: ARRAY OF CHAR; VAR resultRow: Row): Statement;
TYPE arr6 = ARRAY 6 OF CHAR; arr16 = ARRAY 16 OF CHAR;
VAR stmt: Statement; cols, i, type, scale, oldres: INTEGER; name: ARRAY 256 OF CHAR; nullable: BOOLEAN;
prec, nativeBuf: LONGINT; stateBuf: ARRAY 6 OF CHAR; msgBuf: ARRAY 512 OF CHAR;
if: IntField; sf: StringField; rf: RealField; df: DateField; tf: TimeField; tsf: TimeStampField; cur: Field;
bf: BinaryField; ff: FileField; boolf: BooleanField;
nf: NumericField;
BEGIN
NEW(stmt); NEW(stmt.hstmt); ODBC.AllocStmt(c.hdbc, stmt.hstmt, stmt.res);
stmt.next:= c.stmt; c.stmt:= stmt;
stmt.c:= c; stmt.results:= NIL; stmt.params:= NIL; stmt.firstExec:= TRUE; stmt.errBuffered:= FALSE; resultRow:= NIL;
ODBC.Prepare(stmt.hstmt, sqlStatement, stmt.res);
IF stmt.res < 0 THEN
RETURN stmt
ELSE
ODBC.Execute(stmt.hstmt, stmt.res);
IF stmt.res < 0 THEN
oldres:= stmt.res; ODBC.StatementError(stmt.hstmt, stateBuf, nativeBuf, msgBuf, stmt.res);
IF stateBuf = "07001" THEN
EnumErrors(c, stmt, DummyEnum); stmt.errBuffered:= FALSE; stmt.firstExec:= FALSE
ELSE
stmt.errBuffered:= TRUE; NEW(stmt.error);
COPY(stateBuf, stmt.error.state); COPY(msgBuf, stmt.error.msg); stmt.error.native:= nativeBuf;
stmt.firstExec:= FALSE; stmt.res:= oldres; RETURN stmt
END
END;
ODBC.NumResultCols(stmt.hstmt, cols, stmt.res);
IF cols > 0 THEN
AllocRow(resultRow); resultRow.cols:= cols;
FOR i:= 1 TO cols DO
ODBC.DescribeCol(stmt.hstmt, i, name, type, prec, scale, nullable, stmt.res);
CASE type OF
1, 12: NEW(sf); AppendField(resultRow, sf); sf.len:= prec;
ODBC.BindCol(stmt.hstmt, i, CharType, sf.str, sf.len, stmt.res);
| 2, 3: NEW(nf); AppendField(resultRow, nf); nf.len:= prec + 2;
ODBC.BindCol(stmt.hstmt, i, CharType, nf.str, nf.len, stmt.res);
| 4, 5, -6: NEW(if); AppendField(resultRow, if);
IF type = 4 THEN if.len:= 11 ELSIF type = 5 THEN if.len:= 6 ELSE if.len:= 4 END;
ODBC.BindCol(stmt.hstmt, i, LongIntType, if.i, if.len, stmt.res);
| 6, 7, 8: NEW(rf); AppendField(resultRow, rf);
IF type = 7 THEN rf.len:= 13 ELSE rf.len:= 22 END;
ODBC.BindCol(stmt.hstmt, i, LongRealType, rf.r, rf.len, stmt.res);
| 9: NEW(df); AppendField(resultRow, df); df.len:= prec;
ODBC.BindCol(stmt.hstmt, i, DateType, SYSTEM.VAL(arr6, df.year), df.len, stmt.res);
| 10: NEW(tf); AppendField(resultRow, tf); tf.len:= prec;
ODBC.BindCol(stmt.hstmt, i, TimeType, SYSTEM.VAL(arr6, tf.hour), tf.len, stmt.res);
| 11: NEW(tsf); AppendField(resultRow, tsf); IF scale > 0 THEN tsf.len:= 20+scale ELSE tsf.len:= 19 END;
ODBC.BindCol(stmt.hstmt, i, TimeStampType, SYSTEM.VAL(arr16, tsf.year), tsf.len, stmt.res);
| -2, -3: NEW(bf); AppendField(resultRow, bf); bf.len:= prec;
ODBC.BindCol(stmt.hstmt, i, LongIntType, bf.b, bf.len, stmt.res);
| -1, -4: NEW(ff); ff.f:= Files.New(""); AppendField(resultRow, ff); ff.len:= prec;
| -5: NEW(sf); AppendField(resultRow, sf); sf.len:= 20;
ODBC.BindCol(stmt.hstmt, i, CharType, sf.str, sf.len, stmt.res);
| -7: NEW(boolf); AppendField(resultRow, boolf); boolf.len:= 5;
ODBC.BindCol(stmt.hstmt, i, LongIntType, boolf.b, boolf.len, stmt.res);
ELSE HALT(99)
END;
resultRow.dsc.prev.sqlType:= type; COPY(name, resultRow.dsc.prev.name);
resultRow.dsc.prev.nullable:= nullable;
IF stmt.res < 0 THEN
resultRow:= NIL; RETURN stmt
END
END;
stmt.results:= resultRow;
END
END;
RETURN stmt
END PrepareStatement;
PROCEDURE Execute*(s: Statement);
VAR parDesc, cur: Field; buffer: ARRAY BlockSize OF CHAR; r: Files.Rider; cnt: LONGINT;
BEGIN
IF s.firstExec THEN
s.firstExec:= FALSE; RETURN
END;
cur:= s.params.dsc.next;
WHILE ~(cur IS SentinelField) DO
IF cur.isNull THEN cur.len:= -1
ELSIF cur IS StringField THEN cur.len:= NTS
ELSIF cur IS FileField THEN cur.len:= DataAtExec
ELSE cur.len:= 0
END;
cur:= cur.next
END;
ODBC.Execute(s.hstmt, s.res);
cur:= s.params.dsc.next;
WHILE s.res = NeedData DO
ODBC.ParamData(s.hstmt, parDesc^, s.res);
IF s.res = NeedData THEN
WHILE ~(cur IS SentinelField) & ~(cur IS FileField) DO cur:= cur.next END;
IF cur IS SentinelField THEN
KernelLog.String("Warning: Field used for PutData is not a FileField"); KernelLog.Ln; HALT(99)
ELSIF cur IS FileField THEN
cnt:= 0; cur(FileField).f.Set(r, 0);
REPEAT INC(cnt);
cur(FileField).f.ReadBytes(r, buffer, 0, BlockSize); ODBC.PutData(s.hstmt, buffer, BlockSize-r.res, s.res);
IF s.res < 0 THEN RETURN END;
UNTIL r.eof;
cur:= cur.next;
END
ELSE
END
END;
cur:= s.params.dsc.next;
WHILE ~(cur IS SentinelField) DO
IF cur.len = -1 THEN cur.isNull:= TRUE ELSE cur.isNull:= FALSE END;
cur:= cur.next
END;
END Execute;
PROCEDURE RowCount*(s: Statement; VAR rows: LONGINT);
BEGIN
ODBC.RowCount(s.hstmt, rows, s.res);
END RowCount;
PROCEDURE Fetch*(s: Statement);
VAR cur: Field; col: INTEGER; resSize, actSize: LONGINT; buffer: ARRAY BlockSize OF CHAR; r: Files.Rider;
BEGIN
ODBC.Fetch(s.hstmt, s.res);
IF (s.res < 0) OR (s.res = 100) THEN RETURN END;
FirstField(s.results, cur); col:= 1;
WHILE ~(cur IS SentinelField) DO
IF cur IS FileField THEN
cur(FileField).f := Files.New( "" );
cur(FileField).f.Set(r, 0);
REPEAT
ODBC.GetData(s.hstmt, col, -2, buffer, resSize, s.res);
IF s.res < 0 THEN
RETURN
END;
IF resSize > LEN(buffer) THEN actSize:= LEN(buffer) ELSE actSize:= resSize END;
IF actSize > 0 THEN cur(FileField).f.WriteBytes(r, buffer, 0, actSize) END;
UNTIL s.res # 1;
ELSE
END;
INC(col); cur:= cur.next
END;
cur:= s.results.dsc.next;
WHILE ~(cur IS SentinelField) DO
IF cur.len = -1 THEN cur.isNull:= TRUE ELSE cur.isNull:= FALSE END;
cur:= cur.next
END;
END Fetch;
PROCEDURE FetchExtended*(s: Statement; fetchType: INTEGER; rowToFetch: LONGINT; VAR numFetchedRows: LONGINT; VAR rowStatus: INTEGER );
VAR
cur: Field;
col: INTEGER;
resSize,
actSize: LONGINT;
buffer: ARRAY BlockSize OF CHAR;
r: Files.Rider;
BEGIN
ODBC.ExtendedFetch(s.hstmt, fetchType, rowToFetch, numFetchedRows, rowStatus, s.res);
IF (s.res < 0) OR (s.res = 100) THEN RETURN END;
FirstField(s.results, cur); col:= 1;
WHILE ~(cur IS SentinelField) DO
IF cur IS FileField THEN
cur(FileField).f.Set(r, 0);
REPEAT
ODBC.GetData(s.hstmt, col, -2, buffer, resSize, s.res);
IF s.res < 0 THEN
RETURN
END;
IF resSize > LEN(buffer) THEN actSize:= LEN(buffer) ELSE actSize:= resSize END;
IF actSize > 0 THEN cur(FileField).f.WriteBytes(r, buffer, 0, actSize) END;
UNTIL s.res # 1;
ELSE
END;
INC(col); cur:= cur.next
END;
cur:= s.results.dsc.next;
WHILE ~(cur IS SentinelField) DO
IF cur.len = -1 THEN cur.isNull:= TRUE ELSE cur.isNull:= FALSE END;
cur:= cur.next
END;
END FetchExtended;
PROCEDURE Commit*(c: Connection);
BEGIN
ODBC.Commit(c.hdbc, c.res)
END Commit;
PROCEDURE Rollback*(c: Connection);
BEGIN
ODBC.Rollback(c.hdbc, c.res)
END Rollback;
PROCEDURE EnumDataSources*(enum: SourcesHandler; VAR res: INTEGER);
VAR name, desc: ARRAY 256 OF CHAR;
BEGIN
ODBC.DataSources(2, name, desc, res);
IF res < 0 THEN RETURN END;
WHILE res = 0 DO
enum(name, desc);
ODBC.DataSources(1, name, desc, res);
IF res < 0 THEN RETURN END;
END;
END EnumDataSources;
PROCEDURE EnumDrivers*(enum: SourcesHandler; VAR res: INTEGER);
VAR name, desc: ARRAY 256 OF CHAR;
BEGIN
ODBC.Drivers(2, name, desc, res);
IF res < 0 THEN RETURN END;
WHILE res = 0 DO
enum(name, desc);
ODBC.Drivers(1, name, desc, res);
IF res < 0 THEN RETURN END;
END;
END EnumDrivers;
PROCEDURE Tables*(c: Connection; VAR row: Row): Statement;
VAR stmt: Statement; i, cols, type, scale: INTEGER; if: IntField; sf: StringField; rf: RealField; cur: Field;
name: ARRAY 256 OF CHAR; prec: LONGINT; nullable: BOOLEAN;
BEGIN
NEW(stmt); NEW(stmt.hstmt); ODBC.AllocStmt(c.hdbc, stmt.hstmt, stmt.res); stmt.c:= c; row:= NIL; stmt.firstExec:= TRUE;
ODBC.Tables(stmt.hstmt, ODBC.nullString, ODBC.nullString, ODBC.nullString, ODBC.nullString, stmt.res);
IF stmt.res < 0 THEN
row:= NIL; RETURN stmt
ELSE
ODBC.NumResultCols(stmt.hstmt, cols, stmt.res);
AllocRow(row); row.cols:= cols; stmt.results:= row;
FOR i:= 1 TO cols DO
ODBC.DescribeCol(stmt.hstmt, i, name, type, prec, scale, nullable, stmt.res);
CASE type OF
1, 12: NEW(sf); AppendField(row, sf);
ODBC.BindCol(stmt.hstmt, i, CharType, sf.str, sf.len, stmt.res);
ELSE HALT(99)
END;
row.dsc.prev.sqlType:= type; COPY(name, row.dsc.prev.name); row.dsc.prev.nullable:= nullable;
IF stmt.res < 0 THEN
row:= NIL; RETURN stmt
END
END
END;
RETURN stmt
END Tables;
PROCEDURE GetTypeName*(c: Connection; sqlType: INTEGER; VAR typeName: ARRAY OF CHAR; VAR res: INTEGER);
VAR stmt: Statement; len: LONGINT;
BEGIN
NEW(stmt); NEW(stmt.hstmt); ODBC.AllocStmt(c.hdbc, stmt.hstmt, stmt.res);
typeName[0]:= 0X;
ODBC.GetTypeInfo(stmt.hstmt, sqlType, stmt.res);
IF stmt.res < 0 THEN
res:= stmt.res; RETURN
END;
ODBC.BindCol(stmt.hstmt, 1, CharType, typeName, len, stmt.res);
ODBC.Fetch(stmt.hstmt, stmt.res);
res:= stmt.res
END GetTypeName;
PROCEDURE CloseStatement*(s: Statement);
VAR prevStmt, stmt: Statement;
BEGIN
ODBC.FreeStmt(s.hstmt, 1, s.res);
stmt := s.c.stmt;
WHILE (stmt # NIL) & (stmt # s) DO
prevStmt := stmt; stmt:= stmt.next
END;
IF prevStmt # NIL THEN prevStmt.next := s.next ELSE s.c.stmt := s.next END;
END CloseStatement;
BEGIN
Modules.InstallTermHandler(Terminate)
END SQL.
(**
Remarks:
Before you can execute a SQL statement you need to open a connection to the data source. Use procedure
Open to get a connection. When you no longer need the connection you can close it with Close.
In the following the basic steps used in commonly routines are described. It is assumed that there is already
an open connection
1. A simple SQL statement which needs no input and delievers no output:
example: "INSERT INTO addresses VALUES ('Markus', 'Dätwyler')"
- Prepare the statement with PrepareStatement. As the statement retrieves no data, resultRow is NIL.
- Execute the statement with procedure Execute
- The number of rows affected by the execution of this statement can be get with procedure RowCount.
2. A SQL statement with retrieves data:
example: "SELECT * FROM addresses WHERE name = 'Dätwyler' "
- Prepare the statement with PrepareStatement. A result row containig a field for each column in the table
addresses is generated.
- Execute the statement.
- Retrieve data from the result set with the procedure Fetch. Each call to Fetch delievers the next row in the
result set. When there is no more data to get when calling Fetch the variable res will be set to NoDataFound.
After fetching a row the fields containing the data can be accessed using resultRow generated by
PrepareStatement and the procedures FirstField, NextField, PrevField and FindField.
3. A SQL statement which needs input:
example: "INSERT INTO addresses VALUES (?, ?)"
- Prepare the statement with PrepareStatement. As the statement retrieves no data, resultRow is NIL.
- Create a row to hold the input data of the parameters with procedure BindParameters. paramRow
will now contain this row.
- Set the values of the parameters. To access the fields representing the parameters use paramRow and the
procedures FirstField, NextField, PrevField and FindField.
- Execute the statement.
- The number of rows affected by the execution of this statement can be get with procedure RowCount.
4. A SQL statement which needs input and retrieves data:
example: "SELECT * FROM addresses WHERE name LIKE ?"
- Prepare the statement with procedure PrepareStatement. resultRow will be created and contain a field
for each column in the result set.
- Create a row to hold the parameter values with BindParameters.
- Set the values of the parameters. Access the parameters using paramRow and FirstField, NextField,
PrevField and FindField.
- Execute the statement.
- Fetch the data from the result set.
**)