This message is specially directed to:
- Helmut Zinn, author of BlackBox CPC-1.7-RC5 --> Module SqlControls
- Peter Cushnir and Ivan A. Kuzmitskiy, authors of the SQLite subsystem --> Module SQLiteDriver
First of all, excuse me for the length of the post.
I am using BlackBox CPC-1.7-RC5.
I want to access a SQLite table and display its rows in a Form via a "Sql Table Control", and I have found two problems:
1. The rows of the SQLite table are not displayed.
2. The "Sql Table Control" inside the form causes BB to trap when I open the form (after having saved and closed it).
To put the SQLite table inside a Form, I use the menu "Sql > Insert Table". This creates a "Sql Table Control" (SqlControls.Table). I edit its properties and link the new control to an interactor of type SqlDB.Table. (Also, to give scrollbars to the table, menu "Tools > Add Scroller")
To access the SQLite table via Sql subsystem, I copied things from modules SQLiteObx and DragoncityKarinReport:
Code: Select all
MODULE PrivTestBd;
...
CONST
gMiBd = "C:\bla\bla\bla\segsenso.db3";
gDriver = 'SQLiteDriver';
gUser = 'any';
gPassword = 'any';
...
TYPE
TiposSensor* = RECORD
id*: INTEGER;
descri*: POINTER TO ARRAY OF CHAR;;
numord*: INTEGER
END;
...
VAR
gDB: SqlDB.Database;
t*: SqlDB.Table;
...
PROCEDURE Do*()
VAR res, k: INTEGER; r: TiposSensor; ro: SqlDB.Row;
BEGIN
SqlDB.OpenDatabase(gDriver, gUser, gPassword, gMiBd, SqlDB.sync, SqlDB.showErrors, gDB, res);
ASSERT(gDB # NIL, 20);
t := gDB.NewTable();
t.Exec("SELECT ID, DESCRI, NUMORD FROM TIPOS_SENSOR ORDER BY NUMORD");
ASSERT(t.res = 0, 21);
(*
FOR k := 0 TO t.rows - 1 DO
t.Read(k, r);
ASSERT((t.res = 0) OR (t.res = SqlDB.converted) OR (t.res = SqlDB.truncated), 22);
Log.String("r = "); Log.Int(r.id); Log.Tab; Log.String(r.descri); Log.Tab; Log.Int(r.numord); Log.Ln;
t.Read(k, ro);
ASSERT((t.res = 0) OR (t.res = SqlDB.converted) OR (t.res = SqlDB.truncated), 22);
Log.String("ro = " + ro.fields[0]); Log.Tab; Log.String(ro.fields[1]); Log.Tab; Log.String(ro.fields[2]); Log.Ln
END;
*)
END Do;
...
BEGIN
Do
End PrivTestBd.1. The rows of the SQLite table are not displayed.
To display the rows of the DB table via the "Sql Table Control" of the form, BB seems to call t.Read(i, ro) (t: SqlDB.Table; ro: SqlDB.Row). Note that SqlDB.Row is a very generic type. t.Read calls to SqlDB.ReadItem and this to SQLiteDriver.ReadVarString. An here is where I have the problem. SQLiteDriver.ReadVarString only works if the column read from Database is a String, but not if it is an integer, for example. Perhaps ReadVarString should make a type conversion and return always a string.
Code: Select all
MODULE SQLiteDriver
PROCEDURE (t: Table) ReadVarString (row, column: INTEGER; OUT str: SqlDrivers.String);
VAR res: Value;
BEGIN
ASSERT(row >= 0, 20); ASSERT(row < t.rows, 21);
ASSERT(column >= 0, 22); ASSERT(column < t.columns, 23);
res := t.rset.Value(row, column);
IF res # NIL THEN
WITH res: TextValue DO
str := res.content; t.res := converted;
| res: IntValue DO <-- I added this
NEW(str, 40);
Strings.IntToString(res.content, str); t.res := converted;
| res: RealValue DO <-- I added this
NEW(str, 40);
Strings.RealToString(res.content, str); t.res := converted;
| res: BinaryValue DO <-- ...
...
ELSE
str := NIL; t.res := incompatible;
END
END
END ReadVarString;2. The "Sql Table Control" inside the form causes BB to trap when I open the form (after having saved and closed it).
This happens only with CPC-1.7-RC5 edition, and as far as I can see it is related to a change made to admits more than 40 columns. I have very little knowledge about how SqlControls module works. I only see that a variable t of type SqlControls.Table has a field named columns, and its value is cero when I save the form (and the control is externalized). Later when I open the form, the method SqlControls.Table.Internalize tries to allocate space to the dynamic arrays t.width and t.mode via NEW(t.width, t.columns), but t.columns is cero and traps. t.columns only seems to be incremented when the format (width o alignment) of a column is changed with the mouse (see method FormatOp.Do).
If I add the statement "c.columns := 10" to method StdDirectory.NewTable (see below) then the problem is partially solved, but a trap occurs if my SqlDB.Table (t.table) has more than 10 columns. Perhaps the two dynamic arrays t.width and t.mode, should be allocated like now (in Internalize), but also in procedure OpenLink, when t.table is set; there the number of columns of the new t.table could be compared with t.columns, and re-allocate the two arrays if necessary.
Code: Select all
MODULE SqlControls
PROCEDURE (d: StdDirectory) NewTable (p: Controls.Prop): Views.View;
VAR c: Table;
BEGIN
NEW(c); OpenLink(c, p); InitStdProp(c.sprop);
c.columns := 10; <-- I added this, but it is not a real solution
NEW(c.width, 10); FillArray(c.width, 0, 10, defColW); (* extended on demand *)
NEW(c.mode, 10); FillArray(c.mode, 0, 10, center);
SetupTable(c);
RETURN c
END NewTable;Manuel