ODBCBench

ODBCBench ist ein portables Beispielprogramm. Es funktioniert ohne Änderungen auf den Oberon-Systemen V4 und V3. Um das Programm auch unter Oberon/F übersetzen zu können sind lediglich Änderungen aufgrund der anderen Schnittstelle des Moduls Files notwendig.

Die wesentlichen Schritte, um Portabilität zu erreichen sind das Ermitteln der von der jeweiligen Datenbank unterstützten Datentypen und das Umsetzen der gewünschten auf die unterstützten Datentypen. Weiters wird in diesem Programm gezeigt, wie man lange (dateibasierte) Daten in eine Tabelle einfügen und wieder auslesen kann.

MODULE ODBCBench;

IMPORT SQL, SQLExt, In, Out, Files, Strings;

CONST
  nofRows = 100;

VAR
  conn: SQL.Connection;
  connStr: ARRAY 255 OF CHAR;
  mapType, mapScale: ARRAY 3 OF INTEGER; mapPrec: ARRAY 3 OF LONGINT;
  f: Files.File;

PROCEDURE LongIntValueOf (f: SQL.Field): LONGINT;
  VAR i, tmp: LONGINT;
BEGIN
  WITH f: SQL.IntField DO RETURN f.i
  | f: SQL.StringField DO i := 0; tmp := 0;
      REPEAT
        tmp := tmp * 10 + ORD(f.str[i]) - ORD("0"); INC(i)
      UNTIL f.str[i] = 0X;
      RETURN tmp
  | f: SQL.RealField DO RETURN ENTIER(f.r)
  END
END LongIntValueOf;

PROCEDURE Disconnect*;
BEGIN conn := NIL END Disconnect;

PROCEDURE Connect*;
BEGIN
  IF conn # NIL THEN Disconnect END ;
  conn := SQL.OpenUI(connStr, connStr);
  IF ~SQL.Map(conn, SQL.OberonLongInt, 0, 0, mapType[0], mapPrec[0], mapScale[0]) OR
    ~SQL.Map(conn, SQL.OberonChar, 32, 0, mapType[1], mapPrec[1], mapScale[1]) OR
    ~SQL.Map(conn, SQL.SQLLVarChar, 50000, 0, mapType[2], mapPrec[2], mapScale[2]) THEN
    HALT(44)
  END
END Connect;

PROCEDURE DropTables*;
  VAR stat: SQL.Statement;
BEGIN
  stat := SQL.PrepareStatement(conn, "DROP table Bench1"); 
  SQL.Execute(stat);
  stat := SQL.PrepareStatement(conn, "DROP table Bench2"); 
  SQL.Execute(stat);
  stat := SQL.PrepareStatement(conn, "DROP table Bench3"); 
  SQL.Execute(stat);
END DropTables;

PROCEDURE CreateTables*;
  VAR stat: SQL.Statement; str: ARRAY 256 OF CHAR;
BEGIN
  str := "CREATE TABLE Bench1 (i ";
  SQLExt.AppendType(conn, str, mapType[0], mapPrec[0], mapScale[0]);
  Strings.Append(", firstName ", str);
  SQLExt.AppendType(conn, str, mapType[1], mapPrec[1], mapScale[1]);
  Strings.Append(", val ", str);
  SQLExt.AppendType(conn, str, mapType[0], mapPrec[0], mapScale[0]);
  Strings.Append(")", str);
  stat := SQL.PrepareStatement(conn, str);
  SQL.Execute(stat);
  Out.Ln; Out.String(str);
  IF stat.ret # SQL.Success THEN Out.String(" not") END ;
  Out.String(" created.");
  str := "CREATE TABLE Bench2 (i ";
  SQLExt.AppendType(conn, str, mapType[0], mapPrec[0], mapScale[0]);
  Strings.Append(", lastName ", str);
  SQLExt.AppendType(conn, str, mapType[1], mapPrec[1], mapScale[1]);
  Strings.Append(")", str);
  stat := SQL.PrepareStatement(conn, str); 
  SQL.Execute(stat);
  Out.Ln; Out.String(str);
  IF stat.ret # SQL.Success THEN Out.String(" not") END ;
  Out.String(" created.");
  str := "CREATE TABLE Bench3 (i ";
  SQLExt.AppendType(conn, str, mapType[0], mapPrec[0], mapScale[0]);
  Strings.Append(", longData ", str);
  SQLExt.AppendType(conn, str, mapType[2], mapPrec[2], mapScale[2]);
  Strings.Append(")", str);
  stat := SQL.PrepareStatement(conn, str); 
  SQL.Execute(stat); 
  Out.Ln; Out.String(str);
  IF stat.ret # SQL.Success THEN Out.String(" not") END ;
  Out.String(" created.")
END CreateTables;

PROCEDURE GenerateName(i: INTEGER; VAR s: ARRAY OF CHAR);
BEGIN
  s[2] := CHR((i MOD 10) + ORD("0"));
  s[1] := CHR((i DIV 10 MOD 10) + ORD("0"));
  s[0] := CHR((i DIV 100 MOD 10) + ORD("0"));
  s[3] := 0X
END GenerateName;

PROCEDURE InsertIntoBench1*;
  VAR types: ARRAY 3 OF SQL.ParamDesc; stat: SQL.Statement;
    i: INTEGER; idx, firstName, value: SQL.Field;
BEGIN
  types[0].oberonType := SQL.OberonLongInt; types[0].sqlType := mapType[0];
  types[0].precision := mapPrec[0]; types[0].scale := mapScale[0];
  types[0].inOut := SQL.InParam; types[0].name := "idx";
  types[1].oberonType := SQL.OberonChar; types[1].sqlType := mapType[1];
  types[1].precision := mapPrec[1]; types[1].scale := mapScale[1];
  types[1].inOut := SQL.InParam; types[1].name := "firstName";
  types[2] := types[0]; types[2].name := "value";
  stat := SQL.PrepareStatement(conn, 
    "INSERT INTO Bench1 (i, firstName, val) values (?, ?, ?)");
  SQL.BindParameters(stat, types, 3);
  SQL.FindField(stat.params, "idx", idx);
  SQL.FindField(stat.params, "firstName", firstName);
  SQL.FindField(stat.params, "value", value);
  FOR i := 0 TO nofRows DO
    idx(SQL.IntField).i := i;
    GenerateName(i, firstName(SQL.StringField).str);
    value(SQL.IntField).i := LONG(i) * i;
    SQL.Execute(stat);
    IF stat.ret # SQL.Success THEN RETURN END
  END
END InsertIntoBench1;

PROCEDURE AppendToBench1*;
  VAR types: ARRAY 3 OF SQL.ParamDesc; stat: SQL.Statement;
    i: INTEGER; idx, firstName, value: SQL.Field;
BEGIN
  stat := SQL.PrepareStatement(conn, "SELECT MAX(i) FROM Bench1");
  SQL.Execute(stat);
  SQL.FirstField(stat.results, idx);
  SQL.Fetch(stat);
  i := SHORT(LongIntValueOf(idx)); INC(i);
  types[0].oberonType := SQL.OberonLongInt; types[0].sqlType := mapType[0];
  types[0].precision := mapPrec[0]; types[0].scale := mapScale[0];
  types[0].inOut := SQL.InParam; types[0].name := "idx";
  types[1].oberonType := SQL.OberonChar; types[1].sqlType := mapType[1];
  types[1].precision := mapPrec[1]; types[1].scale := mapScale[1];
  types[1].inOut := SQL.InParam; types[1].name := "firstName";
  types[2] := types[0]; types[2].name := "value";
  stat := SQL.PrepareStatement(conn, 
    "INSERT INTO Bench1 (i, firstName, val) values (?, ?, ?)");
  SQL.BindParameters(stat, types, 3);
  SQL.FindField(stat.params, "idx", idx);
  SQL.FindField(stat.params, "firstName", firstName);
  SQL.FindField(stat.params, "value", value);
  REPEAT
    idx(SQL.IntField).i := i;
    GenerateName(i, firstName(SQL.StringField).str);
    value(SQL.IntField).i := LONG(i) * i;
    SQL.Execute(stat);
    IF stat.ret # SQL.Success THEN RETURN END ;
    INC(i)
  UNTIL i MOD nofRows = 0
END AppendToBench1;

PROCEDURE InsertIntoBench2*;
  VAR types: ARRAY 2 OF SQL.ParamDesc; stat: SQL.Statement;
    i: INTEGER; idx, lastName: SQL.Field;
BEGIN
  types[0].oberonType := SQL.OberonLongInt; types[0].sqlType := mapType[0];
  types[0].precision := mapPrec[0]; types[0].scale := mapScale[0];
  types[0].inOut := SQL.InParam; types[0].name := "idx";
  types[1].oberonType := SQL.OberonChar; types[1].sqlType := mapType[1];
  types[1].precision := mapPrec[1]; types[1].scale := mapScale[1];
  types[1].inOut := SQL.InParam; types[1].name := "lastName";
  stat := SQL.PrepareStatement(conn, 
    "INSERT INTO Bench2 (i, lastName) values (?, ?)");
  SQL.BindParameters(stat, types, 2);
  SQL.FindField(stat.params, "idx", idx);
  SQL.FindField(stat.params, "lastName", lastName);
  FOR i := 0 TO nofRows DO
    idx(SQL.IntField).i := i;
    GenerateName(i, lastName(SQL.StringField).str);
    SQL.Execute(stat);
    IF stat.ret # SQL.Success THEN RETURN END
  END
END InsertIntoBench2;

PROCEDURE PutField* (VAR field: SQL.Field);
  VAR r: Files.Rider; ch: CHAR; f: SQL.Field;
BEGIN
  IF field.len = SQL.NullData THEN Out.String("NULL")
  ELSE
    f := field;
    WITH f: SQL.IntField DO Out.Int(f.i, 0)
    | f: SQL.StringField DO Out.String(f.str)
    | f: SQL.RealField DO
      CASE field.sqlType OF
        SQL.SQLFloat, SQL.SQLDouble: Out.LongReal(f.r, 16)
      | SQL.SQLReal: Out.Real(SHORT(f.r), 16)
      END
    | f: SQL.DateField DO Out.Int(f.year, 0); Out.Char(\-"); 
      Out.Int(f.month, 0); Out.Char(\-"); Out.Int(f.day, 0)
    | f: SQL.TimeField DO Out.Int(f.hour, 0); Out.Char(":"); 
      Out.Int(f.minute, 0); Out.Char(":"); Out.Int(f.second, 0)
    | f: SQL.TimeStampField DO Out.Int(f.year, 0); Out.Char(\-"); 
      Out.Int(f.month, 0); Out.Char(\-"); Out.Int(f.day, 0); 
      Out.Char(" "); Out.Int(f.hour, 0); Out.Char(":");
      Out.Int(f.minute, 0); Out.Char(":"); Out.Int(f.second, 0); 
      Out.Char("."); Out.Int(f.fraction, 0)
    | f: SQL.BooleanField DO
      IF f.b THEN Out.String("TRUE") ELSE Out.String("FALSE") END
    | f: SQL.FileField DO
      Out.String("Length of data: "); Out.Int(Files.Length(f.f), 0);
      IF field.sqlType = SQL.SQLLVarChar THEN
        Files.Set(r, f.f, 0); Files.Read(r, ch);
        WHILE ~r.eof DO Out.Char(ch); Files.Read(r, ch) END
      END
    ELSE (* SQL.BinaryField *)
    END
  END
END PutField;

PROCEDURE ShowResults* (s: SQL.Statement);
  VAR field: SQL.Field; rows: LONGINT;
BEGIN
  IF s.ret = SQL.Success THEN
    IF s.results # NIL THEN
      Out.Ln; SQL.FirstField(s.results, field);
      WHILE field # NIL DO 
        Out.String(field.name); Out.Char(9X); SQL.NextField(field)
      END ;
      REPEAT
        SQL.Fetch(s); 
        IF s.ret = SQL.Success THEN
          Out.Ln;
          SQL.FirstField(s.results, field);
          WHILE field # NIL DO
            PutField(field); Out.Char(9X);
            SQL.NextField(field)
          END
        END
      UNTIL s.ret # SQL.Success
    ELSE
      SQL.RowCount(s, rows);
      IF rows # -1 THEN 
        Out.Ln; Out.String("Number of rows affected: "); Out.Int(rows, 0)
      END
    END
  ELSE
    SQL.ShowError(s.ret, "ShowResults", SQL.env, conn.dbc, s.stmt)
  END ;
END ShowResults;

PROCEDURE Execute*;
  VAR stat: SQL.Statement; str: ARRAY 256 OF CHAR;
BEGIN
  In.Open; In.String(str);
  stat := SQL.PrepareStatement(conn, str);
  SQL.Execute(stat);
  ShowResults(stat)
END Execute;

PROCEDURE ExecuteIntParam*;
  VAR stat: SQL.Statement; str: ARRAY 256 OF CHAR;
    types: ARRAY 1 OF SQL.ParamDesc; par1: SQL.Field;
BEGIN
  In.Open; In.String(str);
  types[0].oberonType := SQL.OberonLongInt; types[0].sqlType := mapType[0];
  types[0].precision := mapPrec[0]; types[0].scale := mapScale[0];
  types[0].inOut := SQL.InParam; types[0].name := "par1";
  stat := SQL.PrepareStatement(conn, str);
  SQL.BindParameters(stat, types, 1);
  SQL.FindField(stat.params, "par1", par1);
  In.LongInt(par1(SQL.IntField).i);
  SQL.Execute(stat);
  ShowResults(stat)
END ExecuteIntParam;

PROCEDURE ExecuteStringParam*;
  VAR stat: SQL.Statement; str: ARRAY 256 OF CHAR;
    types: ARRAY 1 OF SQL.ParamDesc; par1: SQL.Field;
BEGIN
  In.Open; In.String(str);
  types[0].oberonType := SQL.SQLVarChar; types[0].sqlType := mapType[1];
  types[0].precision := mapPrec[1]; types[0].scale := mapScale[1];
  types[0].inOut := SQL.InParam; types[0].name := "par1";
  stat := SQL.PrepareStatement(conn, str);
  SQL.BindParameters(stat, types, 1);
  SQL.FindField(stat.params, "par1", par1);
  In.String(par1(SQL.StringField).str);
  SQL.Execute(stat);
  ShowResults(stat)
END ExecuteStringParam;

PROCEDURE CreateLongData*;
  VAR r: Files.Rider; i: INTEGER;
BEGIN
  f := Files.New(""); Files.Set(r, f, 0);
  FOR i := 0 TO 19999 DO Files.Write(r, CHR((i MOD 32) + ORD(" "))) END ;
END CreateLongData;

PROCEDURE VerifyLongData(f: Files.File);
  VAR r: Files.Rider; ch: CHAR; i: INTEGER;
BEGIN
  Files.Set(r, f, 0);
  FOR i := 0 TO 19999 DO
    Files.Read(r, ch);
    IF ch # CHR((i MOD 32) + ORD(" ")) THEN 
      Out.String("wrong character in file");
      RETURN 
    END
  END
END VerifyLongData;

PROCEDURE CopyFile(fIn, fOut: Files.File);
  VAR r, w: Files.Rider; buffer: ARRAY 1024 OF CHAR; pos: LONGINT;
BEGIN
  Files.Set(r, fIn, 0); Files.Set(w, fOut, 0);
  REPEAT
    Files.ReadBytes(r, buffer, 1024); 
    Files.WriteBytes(w, buffer, 1024 - r.res)
  UNTIL r.eof;
END CopyFile;

PROCEDURE InsertLongData*;
  VAR types: ARRAY 2 OF SQL.ParamDesc; stat: SQL.Statement;
    i: INTEGER; idx, longData: SQL.Field;
BEGIN
  types[0].oberonType := SQL.OberonLongInt; types[0].sqlType := mapType[0];
  types[0].precision := mapPrec[0]; types[0].scale := mapScale[0];
  types[0].inOut := SQL.InParam; types[0].name := "idx";
  types[1].oberonType := SQL.SQLLVarChar; types[1].sqlType := mapType[2];
  types[1].inOut := SQL.InParam; types[1].name := "longData";
  types[1].precision := mapPrec[2]; types[1].scale := mapScale[2];
  stat := SQL.PrepareStatement(conn, 
    "INSERT INTO Bench3 (i, longData) values (?, ?)");
  SQL.BindParameters(stat, types, 2);
  SQL.FindField(stat.params, "idx", idx);
  SQL.FindField(stat.params, "longData", longData);
  FOR i := 0 TO 10 DO
    Out.Ln; Out.String("Inserting row number "); Out.Int(i, 0);
    idx(SQL.IntField).i := i;
    CopyFile(f, longData(SQL.FileField).f);
    SQL.Execute(stat);
    IF stat.ret # SQL.Success THEN RETURN END
  END
END InsertLongData;

PROCEDURE GetLongData*;
  VAR stat: SQL.Statement;
    i: INTEGER; idx, longData: SQL.Field;
BEGIN
  stat := SQL.PrepareStatement(conn, "SELECT * FROM Bench3");
  SQL.FindField(stat.results, "i", idx);
  SQL.FindField(stat.results, "longData", longData);
  SQL.Execute(stat);
  IF stat.ret = SQL.Success THEN
    i := 0;
    REPEAT
      SQL.Fetch(stat);
      IF stat.ret = SQL.Success THEN
        Out.Ln; Out.String("Fetching row number "); Out.Int(i, 0);
        IF idx(SQL.IntField).i # i THEN 
          Out.String("wrong index: "); Out.Int(idx(SQL.IntField).i, 0)
        END ;
        VerifyLongData(longData(SQL.FileField).f);
        INC(i)
      END
    UNTIL stat.ret # SQL.Success;
  END
END GetLongData;

BEGIN
  connStr := ""
END ODBCBench.