Addresses - SQL

MODULE Addresses;

IMPORT SQL, Files, Out, In;

VAR
  conn: SQL.Connection;
  connStr: ARRAY 256 OF CHAR;

PROCEDURE Open*;
  VAR s: ARRAY 256 OF CHAR;
BEGIN
  conn := SQL.OpenUI(connStr, s)
END Open;

PROCEDURE CreateTable*;
  VAR stat: SQL.Statement;
BEGIN
  stat := SQL.PrepareStatement(conn, "CREATE TABLE Friends (firstName TEXT(32),
  lastName TEXT(32), street TEXT(32), town TEXT(32), zipCode LONG)");
  SQL.Execute(stat);
  SQL.Commit(conn)
END CreateTable;

PROCEDURE InsertMyAddress*;
  VAR stat: SQL.Statement;
BEGIN
  stat := SQL.PrepareStatement(conn, "INSERT INTO Friends VALUES ('Christoph', 'Steindl',
  'Freistaedterstrasse 315', 'Linz', 4040)");
  SQL.Execute(stat);
  SQL.Commit(conn)
END InsertMyAddress;

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.type.sqlType OF
        SQL.SQLFloat, SQL.SQLDouble: Out.LongReal(f.r, 0)
      | SQL.SQLReal: Out.Real(SHORT(f.r), 16)
      END
    | f: SQL.DateField DO Out.Int(f.year, 4); Out.Char("-"); Out.Int(f.month, 2);
      Out.Char("-"); Out.Int(f.day, 2)
    | f: SQL.TimeField DO Out.Int(f.hour, 2); Out.Char(":"); Out.Int(f.minute, 2);
      Out.Char(":"); Out.Int(f.second, 2)
    | f: SQL.TimeStampField DO Out.Int(f.year, 4); Out.Char("-"); Out.Int(f.month, 2);
      Out.Char("-"); Out.Int(f.day, 2); Out.Char(" "); Out.Int(f.hour, 2);
      Out.Char(":"); Out.Int(f.minute, 2); Out.Char(":"); Out.Int(f.second, 2);
      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.type.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 ListAll*;
  VAR stat: SQL.Statement; field: SQL.Field;
BEGIN
  stat := SQL.PrepareStatement(conn, "SELECT * FROM Friends");
  SQL.Execute(stat);
  IF (stat.ret = SQL.Success) & (stat.results # NIL) THEN
    Out.Ln; SQL.FirstField(stat.results, field);
    WHILE field # NIL DO
    	Out.String(field.name); Out.Char(" ");
    	SQL.NextField(field)
    END ;
    REPEAT
      SQL.Fetch(stat);
      IF stat.ret = SQL.Success THEN
        Out.Ln;
        SQL.FirstField(stat.results, field);
        WHILE field # NIL DO
          PutField(field); Out.Char(" ");
          SQL.NextField(field)
        END
      END
    UNTIL stat.ret # SQL.Success
  END ;
END ListAll;

PROCEDURE ListAddress*; (** ^ | "lastName" *)
  VAR name: ARRAY 32 OF CHAR; stat: SQL.Statement; field: SQL.Field;
    types: ARRAY 1 OF SQL.ParamDesc;
BEGIN
  In.Open; In.String(name);
  stat := SQL.PrepareStatement(conn, "SELECT * FROM Friends WHERE lastName = ?");
  types[0].type.sqlType := SQL.OberonChar;
  types[0].mapType.sqlType := SQL.OberonChar;
  types[0].inOut := SQL.InParam; types[0].name := "name";
  types[0].mapType.prec := 31;
  SQL.BindParameters(stat, types, 1);
  SQL.FindField(stat.params, "name", field);
  COPY(name, field(SQL.StringField).str);
  SQL.Execute(stat);
  IF (stat.ret = SQL.Success) & (stat.results # NIL) THEN
    Out.Ln; SQL.FirstField(stat.results, field);
    WHILE field # NIL DO
    	Out.String(field.name); Out.Char(" ");
    	SQL.NextField(field)
    END ;
    REPEAT
      SQL.Fetch(stat);
      IF stat.ret = SQL.Success THEN
        Out.Ln;
        SQL.FirstField(stat.results, field);
        WHILE field # NIL DO
          PutField(field); Out.Char(" ");
          SQL.NextField(field)
        END
      END
    UNTIL stat.ret # SQL.Success
  END
END ListAddress;

PROCEDURE Insert*; (** ^ | "firstName" "lastName" "street" "town" zipCode *)
  VAR firstName, lastName, street, town: ARRAY 32 OF CHAR; zipCode: LONGINT;
    stat: SQL.Statement; field: SQL.Field; types: ARRAY 5 OF SQL.ParamDesc;
    type, mapType: SQL.Type;
BEGIN
  In.Open; In.String(firstName); In.String(lastName); In.String(street);
  In.String(town); In.LongInt(zipCode);
  stat := SQL.PrepareStatement(conn, "INSERT INTO Friends VALUES (?, ?, ?, ?, ?)");
  type.sqlType := SQL.OberonChar; type.prec := 32; type.scale := 0;
  IF ~SQL.Map(conn, type, mapType) THEN HALT(69) END ;
  types[0].type := type; types[0].mapType := mapType; types[0].inOut := SQL.InParam;
  types[1] := types[0]; types[2] := types[0]; types[3] := types[0];
  types[0].name := "firstName"; types[1].name := "lastName";
  types[2].name := "street"; types[3].name := "town";
  type.sqlType := SQL.OberonLongInt; type.prec := 0; type.scale := 0;
  IF ~SQL.Map(conn, type, mapType) THEN HALT(69) END ;
  types[4].type := type; types[4].mapType := mapType; types[4].inOut := SQL.InParam;
  types[4].name := "zipCode";
  SQL.BindParameters(stat, types, 5);
  SQL.FindField(stat.params, "firstName", field);
  COPY(firstName, field(SQL.StringField).str);
  SQL.FindField(stat.params, "lastName", field);
  COPY(lastName, field(SQL.StringField).str);
  SQL.FindField(stat.params, "street", field);
  COPY(street, field(SQL.StringField).str);
  SQL.FindField(stat.params, "town", field);
  COPY(town, field(SQL.StringField).str);
  SQL.FindField(stat.params, "zipCode", field);
  field(SQL.IntField).i := zipCode;
  SQL.Execute(stat);
  SQL.Commit(conn)
END Insert;

BEGIN
  connStr := ""
END Addresses.