0Syntax10.Scn.FntSyntax10b.Scn.Fnt A"StampElemsAlloc4 Jun 96 8FoldElemsNew8a8.88)88%8@8o8;88(8[8@8 8i88+88&88/88>8d88Q8 88 88 88 88 88 88 8<8?J8MODULE DataEditor; (* CS, 2 Jan 96 -  *) IMPORT Files, Viewers, MenuViewers, Oberon, Fonts, Texts, TextFrames, In, Out, Dialogs, DialogFrames, DialogTexts, DialogStaticTexts, SQL, Strings; TYPE Panel* = POINTER TO PanelDesc; PanelDesc* = RECORD (Dialogs.PanelDesc) table-: ARRAY 32 OF CHAR; conn-: SQL.Connection; stat-: SQL.Statement; new-: BOOLEAN END ; VAR w: Texts.Writer; defFont: Fonts.Font; p: Panel; PROCEDURE WString (str: ARRAY OF CHAR); BEGIN Texts.WriteString(w, str) END WString;  PROCEDURE WInt (i: LONGINT); BEGIN Texts.WriteInt(w, i, 0) END WInt;  PROCEDURE WChar (ch: CHAR); BEGIN Texts.Write(w, ch) END WChar;  PROCEDURE DisplayedString(idx: INTEGER; VAR s: ARRAY OF CHAR); VAR r: Texts.Reader; i: INTEGER; name: ARRAY 32 OF CHAR; o: Dialogs.Object; BEGIN name := "value"; name[7] := 0X; name[5] := CHR(idx DIV 10 MOD 10 + ORD("0")); name[6] := CHR(idx MOD 10 + ORD("0")); o := p.NamedObject(name); Texts.OpenReader(r, o(DialogTexts.Item).GetText(), 0); i := -1; REPEAT INC(i); Texts.Read(r, s[i]) UNTIL s[i] = 0X END DisplayedString;  PROCEDURE ValueOfField (i: INTEGER; VAR field: SQL.Field); BEGIN WITH field: SQL.StringField DO DisplayedString(i, field.str); field.isNull := field.str = ""; IF field.str = "TRUE" THEN field.str := "1" ELSIF field.str = "FALSE" THEN field.str := "0" END END END ValueOfField;  PROCEDURE IsNull (i: INTEGER): BOOLEAN; VAR str: ARRAY 256 OF CHAR; BEGIN DisplayedString(i, str); RETURN str = "" END IsNull;  PROCEDURE InitParam (VAR par: SQL.ParamDesc; field: SQL.Field); BEGIN IF field IS SQL.FileField THEN par.type.sqlType := field.type.sqlType ELSE par.type.sqlType := SQL.OberonChar END ; par.mapType.sqlType := field.type.sqlType; par.inOut := SQL.InParam; par.mapType.prec := field.type.prec; par.mapType.scale := field.type.scale; END InitParam;  PROCEDURE CreateParams (VAR str: ARRAY OF CHAR; VAR types: ARRAY OF SQL.ParamDesc; VAR params: INTEGER); VAR first: BOOLEAN; field: SQL.Field; BEGIN Strings.Append(" WHERE ", str); SQL.FirstField(p.stat.results, field); first := TRUE; WHILE field # NIL DO IF ~(field IS SQL.FileField) THEN IF ~first THEN Strings.Append(" AND ", str) ELSE first := FALSE END ; Strings.Append("(", str); Strings.Append(field.name, str); IF field.isNull THEN Strings.Append(" IS NULL)", str) ELSE Strings.Append(" = ?)", str); types[params].type.sqlType := field.type.sqlType; types[params].mapType.sqlType := field.type.sqlType; types[params].inOut := SQL.InParam; types[params].mapType.prec := field.type.prec; types[params].mapType.scale := field.type.scale; INC(params) END END ; SQL.NextField(field) END ; END CreateParams;  PROCEDURE CopyFile(fIn, fOut: Files.File); VAR r, w: Files.Rider; buffer: ARRAY 1024 OF CHAR; 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 Copy (old, new: SQL.Field); BEGIN new.isNull := old.isNull; new.type.prec := old.type.prec; new.type.scale := old.type.scale; IF ~old.isNull THEN WITH old: SQL.IntField DO new(SQL.IntField).i := old.i | old: SQL.StringField DO new(SQL.StringField).str := old.str | old: SQL.RealField DO new(SQL.RealField).r := old.r | old: SQL.DateField DO WITH new: SQL.DateField DO new.year := old.year; new.month := old.month; new.day := old.day END | old: SQL.TimeField DO WITH new: SQL.TimeField DO new.hour := old.hour; new.minute := old.minute; new.second := old.second END | old: SQL.TimeStampField DO WITH new: SQL.TimeStampField DO new.year := old.year; new.month := old.month; new.day := old.day; new.hour := old.hour; new.minute := old.minute; new.second := old.second; new.fraction := old.fraction END | old: SQL.FileField DO | old: SQL.BinaryField DO new(SQL.BinaryField).b := old.b | old: SQL.BooleanField DO new(SQL.BooleanField).b := old.b END END END Copy;  PROCEDURE SetParams (VAR newField: SQL.Field); VAR field: SQL.Field; BEGIN SQL.FirstField(p.stat.results, field); WHILE field # NIL DO IF ~(field IS SQL.FileField) & ~field.isNull THEN Copy(field, newField); SQL.NextField(newField) END ; SQL.NextField(field) END END SetParams;  PROCEDURE PutField (VAR field: SQL.Field; o: Dialogs.Object); VAR r: Files.Rider; ch: CHAR; f: SQL.Field; txt: Texts.Text; BEGIN IF ~field.isNull THEN f := field; WITH f: SQL.IntField DO WInt(f.i) | f: SQL.StringField DO WString(f.str) | f: SQL.RealField DO CASE f.type.sqlType OF SQL.SQLFloat, SQL.SQLDouble: Texts.WriteLongReal(w, f.r, 20) | SQL.SQLReal: Texts.WriteReal(w, SHORT(f.r), 20) END | f: SQL.DateField DO WInt(f.year); WChar("-"); WInt(f.month); WChar("-"); WInt(f.day) | f: SQL.TimeField DO WInt(f.hour); WChar(":"); WInt(f.minute); WChar(":"); WInt(f.second) | f: SQL.TimeStampField DO WInt(f.year); WChar("-"); WInt(f.month); WChar("-"); WInt(f.day); WChar(" "); WInt(f.hour); WChar(":"); WInt(f.minute); WChar(":"); WInt(f.second); WChar("."); WInt(f.fraction) | f: SQL.BooleanField DO IF f.b THEN WString("TRUE") ELSE WString("FALSE") END | f: SQL.FileField DO WString("Long "); IF f.type.sqlType = SQL.SQLLVarChar THEN WString("character") ELSE WString("binary") END ; WString(" data (len: "); WInt(Files.Length(f.f)); WChar(")"); IF f.type.sqlType = SQL.SQLLVarChar THEN WString(": "); Files.Set(r, f.f, 0); Files.Read(r, ch); WHILE ~r.eof DO WChar(ch); Files.Read(r, ch) END END ELSE (* SQL.BinaryField *) END END ; txt := o(DialogTexts.Item).GetText(); Texts.Delete(txt, 0, txt.len); Texts.Append(txt, w.buf) END PutField;  PROCEDURE Show; VAR field: SQL.Field; i: INTEGER; name: ARRAY 32 OF CHAR; BEGIN SQL.FirstField(p.stat.results, field); i := 0; name := "value"; name[7] := 0X; WHILE field # NIL DO name[5] := CHR(i DIV 10 MOD 10 + ORD("0")); name[6] := CHR(i MOD 10 + ORD("0")); PutField(field, p.NamedObject(name)); SQL.NextField(field); INC(i) END END Show;  PROCEDURE New*; VAR field: SQL.Field; BEGIN p := Dialogs.cmdPanel(Panel); p.new := TRUE; SQL.FirstField(p.stat.results, field); WHILE field # NIL DO field.isNull := TRUE; SQL.NextField(field) END ; Show END New;  PROCEDURE First*; VAR str: ARRAY 256 OF CHAR; BEGIN p := Dialogs.cmdPanel(Panel); p.new := FALSE; str := "SELECT * FROM "; Strings.Append(p.table, str); p.stat := SQL.PrepareStatement(p.conn, str); IF p.stat.ret = SQL.Success THEN SQL.Execute(p.stat); SQL.ErrS("Execute", p.stat); SQL.Fetch(p.stat); SQL.ErrS("Fetch", p.stat); Show ELSE SQL.ErrS("PrepareStatement", p.stat) END END First;  PROCEDURE Next*; BEGIN p := Dialogs.cmdPanel(Panel); p.new := FALSE; SQL.Fetch(p.stat); IF p.stat.ret = SQL.NoDataFound THEN New; Out.Ln; Out.String("no more data available") ELSE SQL.ErrS("Fetch", p.stat) END ; Show END Next;  PROCEDURE Delete*; VAR stat: SQL.Statement; str: ARRAY 512 OF CHAR; types: ARRAY 30 OF SQL.ParamDesc; newField: SQL.Field; params: INTEGER; BEGIN p := Dialogs.cmdPanel(Panel); IF ~p.new THEN str := "DELETE FROM "; Strings.Append(p.table, str); params := 0; CreateParams(str, types, params); (* WHERE clause *) stat := SQL.PrepareStatement(p.conn, str); IF stat.ret = SQL.Success THEN SQL.BindParameters(stat, types, params); SQL.ErrS("BindParameters", stat); SQL.FirstField(stat.params, newField); SetParams(newField); SQL.Execute(stat); SQL.ErrS("Execute", stat); IF stat.ret = SQL.Success THEN Next END ELSE SQL.ErrS("PrepareStatement", stat) END ELSE p.new := FALSE; Next END END Delete;  PROCEDURE Update*; VAR stat: SQL.Statement; str: ARRAY 512 OF CHAR; types: ARRAY 60 OF SQL.ParamDesc; newField, field: SQL.Field; first: BOOLEAN; params, i: INTEGER; BEGIN p := Dialogs.cmdPanel(Panel); IF ~p.new THEN str := "UPDATE "; Strings.Append(p.table, str); Strings.Append(" SET ", str); SQL.FirstField(p.stat.results, field); first := TRUE; params := 0; WHILE field # NIL DO IF ~first THEN Strings.Append(", ", str) ELSE first := FALSE END ; Strings.Append(field.name, str); Strings.Append(" = ?", str); InitParam(types[params], field); INC(params); SQL.NextField(field) END ; CreateParams(str, types, params); (* WHERE clause *) stat := SQL.PrepareStatement(p.conn, str); IF stat.ret = SQL.Success THEN SQL.BindParameters(stat, types, params); SQL.ErrS("BindParameters", stat); SQL.FirstField(stat.params, newField); SQL.FirstField(p.stat.results, field); i := 0; WHILE field # NIL DO IF field IS SQL.FileField THEN CopyFile(field(SQL.FileField).f, newField(SQL.FileField).f) ELSE ValueOfField(i, newField) END ; SQL.NextField(field); SQL.NextField(newField); INC(i) END ; SetParams(newField); SQL.Execute(stat); SQL.ErrS("Execute", stat) ELSE SQL.ErrS("PrepareStatement", stat) END ELSE p.new := FALSE; str := "INSERT INTO "; Strings.Append(p.table, str); Strings.Append(" VALUES (", str); SQL.FirstField(p.stat.results, field); first := TRUE; params := 0; WHILE field # NIL DO IF ~first THEN Strings.Append(", ", str) ELSE first := FALSE END ; Strings.Append(" ?", str); InitParam(types[params], field); INC(params); SQL.NextField(field) END ; Strings.Append(")", str); stat := SQL.PrepareStatement(p.conn, str); IF stat.ret = SQL.Success THEN SQL.BindParameters(stat, types, params); SQL.ErrS("BindParameters", stat); SQL.FirstField(stat.params, newField); SQL.FirstField(p.stat.results, field); i := 0; WHILE field # NIL DO IF field IS SQL.FileField THEN newField.isNull := TRUE ELSE ValueOfField(i, newField) END ; SQL.NextField(field); SQL.NextField(newField); INC(i) END ; SQL.Execute(stat); SQL.ErrS("Execute", stat) ELSE SQL.ErrS("PrepareStatement", stat) END END END Update;  PROCEDURE Search*; VAR stat: SQL.Statement; str: ARRAY 512 OF CHAR; types: ARRAY 30 OF SQL.ParamDesc; newField, field: SQL.Field; first: BOOLEAN; i, j, params, chars: INTEGER; searchOp: ARRAY 256 OF CHAR; BEGIN p := Dialogs.cmdPanel(Panel); p.new := FALSE; str := "SELECT * FROM "; Strings.Append(p.table, str); Strings.Append(" WHERE ", str); SQL.FirstField(p.stat.results, field); first := TRUE; i := 0; params := 0; WHILE field # NIL DO IF ~(field IS SQL.FileField) & ~IsNull(i) THEN IF ~first THEN Strings.Append(" AND ", str) ELSE first := FALSE END ; Strings.Append("(", str); Strings.Append(field.name, str); DisplayedString(i, searchOp); IF (searchOp[0] >= "<") & (searchOp[0] <= ">") OR (searchOp[0] = "#") THEN IF searchOp[1] = "=" THEN chars := 2 ELSE chars := 1 END ELSIF (searchOp[0] = "L") & (searchOp[1] = "I") & (searchOp[2] = "K") & (searchOp[3] = "E") & (searchOp[4] = " ") THEN chars := 4 ELSE chars := 0 END ; IF chars = 0 THEN Strings.Append(" = ?)", str) ELSE Strings.Append(" ", str); searchOp[chars] := 0X; Strings.Append(searchOp, str); Strings.Append(" ?)", str) END ; InitParam(types[params], field); INC(params) END ; INC(i); SQL.NextField(field) END ; stat := SQL.PrepareStatement(p.conn, str); IF stat.ret = SQL.Success THEN SQL.BindParameters(stat, types, params); SQL.ErrS("BindParameters", stat); SQL.FirstField(stat.params, newField); SQL.FirstField(p.stat.results, field); i := 0; WHILE field # NIL DO IF ~(field IS SQL.FileField) & ~IsNull(i) THEN DisplayedString(i, searchOp); IF (searchOp[0] >= "<") & (searchOp[0] <= ">") OR (searchOp[0] = "#") THEN IF searchOp[1] = "=" THEN chars := 3 ELSE chars := 2 END ELSIF (searchOp[0] = "L") & (searchOp[1] = "I") & (searchOp[2] = "K") & (searchOp[3] = "E") & (searchOp[4] = " ") THEN chars := 5 ELSE chars := 0 END ; j := 0; WHILE searchOp[chars] # 0X DO newField(SQL.StringField).str[j] := searchOp[chars]; INC(j); INC(chars) END ; SQL.NextField(newField) END ; INC(i); SQL.NextField(field) END ; SQL.Execute(stat); SQL.ErrS("Execute", stat); IF stat.ret = SQL.Success THEN p.stat := stat; Next END ELSE SQL.ErrS("PrepareStatement", stat) END END Search;  PROCEDURE Open*; VAR table, str: ARRAY 256 OF CHAR; i, x, y: INTEGER; editor: Panel; name: DialogStaticTexts.Item; value: DialogTexts.Item; file: Files.File; r: Files.Rider; m: TextFrames.Frame; v: Viewers.Viewer; df: DialogFrames.Frame; field: SQL.Field; BEGIN In.Open; In.Name(table); IF ~In.Done THEN In.Open; In.String(table) END ; IF In.Done THEN Oberon.AllocateSystemViewer (Oberon.Mouse.X, x, y); file := Files.Old ("DataEditor.Dlg"); ASSERT (file # NIL); Files.Set (r, file, 0); NEW (editor); editor.Load (r); COPY(table, editor.table); NEW (df); df.Open (DialogFrames.Handle, editor); Oberon.AllocateSystemViewer (Oberon.Mouse.X, x, y); m := TextFrames.NewMenu (table, "System.Close System.Copy System.Grow "); v := MenuViewers.New (m, df, TextFrames.menuH, x, y); editor.conn := SQL.OpenUI("", str); IF (editor.conn # NIL) & (editor.conn.ret = SQL.Success) THEN str := "SELECT * FROM "; Strings.Append(table, str); editor.stat := SQL.PrepareStatement(editor.conn, str); IF editor.stat.ret = SQL.Success THEN SQL.Execute(editor.stat); SQL.ErrS("Execute", editor.stat); SQL.FirstField(editor.stat.results, field); y := -52; i := 0; str := "value"; str[7] := 0X; WHILE field # NIL DO NEW(name); name.Init; name.SetDim(4, y, 122, 20, FALSE); name.SetString(field.name); name.SetFont(defFont); editor.Insert(name, FALSE); NEW(value); value.Init; value.SetDim(130, y, 350, 22, FALSE); str[5] := CHR(i DIV 10 MOD 10 + ORD("0")); str[6] := CHR(i MOD 10 + ORD("0")); value.SetName(str); editor.Insert(value, FALSE); DEC(y, 24); INC(i); SQL.NextField(field) END ; SQL.Fetch(editor.stat); SQL.ErrS("Fetch", editor.stat); p := editor; Show ELSE SQL.ErrS("PrepareStatement", editor.stat); Viewers.Close(v) END ELSE Viewers.Close(v) END END END Open;  BEGIN Texts.OpenWriter(w); defFont := Fonts.This("Syntax10.Scn.Fnt") END DataEditor. Debugger.Data DataEditor System.Free DataEditor ~ Dialog.Edit DataEditor.Dlg Dialog.Open DataEditor.Dlg Dialog.Open Insert.Dlg DataEditor.Open ^ Customer Orders Product Friends Modules Changes AdressKartei DataEditor.Open Bench1