Displaying SQLite table via "Sql Table Control" inside Form

Using SQLite with BlackBox: http://zinnamturm.eu/downloadsOS.htm#SQLite
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Displaying SQLite table via "Sql Table Control" inside Form

Post by manumart1 »

Hello,

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.
The two problems are:

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;
Regards,
Manuel
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by manumart1 »

Finally, instead of "Sql Table Control" (Sql > Insert Table) I will use a normal "Table Control" (Controls > Insert Table Control). It looks better (see image attached).

But I have to manually fill in this table control from the data stored in the SQL table:

Code: Select all

PROCEDURE DisplayTable* (pT: SqlDB.Table; VAR pInteractor: StdTables.Table);
(*
pT --> pInteractor
To make visible all the rows of table pT, they are copied to pInteractor, which is linked to a Table Control of a form.
*)
  VAR v: Views.View; r, c: INTEGER;
    oneRow: SqlDB.Row;
BEGIN
  IF pT = NIL THEN
    pInteractor.SetSize(0, 0);
    RETURN
  END;
  pInteractor.SetSize(pT.rows, pT.columns);
  (* Put header row *)
  pT.Read(SqlDB.names, oneRow); FOR c := 0 TO pT.columns - 1 DO pInteractor.SetLabel(c, oneRow.fields[c]$) END;
  (* Put data rows *)
  FOR r := 0 TO pT.rows - 1 DO
    pT.Read(r, oneRow);
    FOR c := 0 TO pT.columns - 1 DO pInteractor.SetItem(r, c, oneRow.fields[c]$) END
  END;
  Dialog.Update(pInteractor)
END DisplayTable;
Regards,
Manuel
Attachments
Sql Tables.png
Zinn
Posts: 123
Joined: Mon Nov 24, 2014 10:47 am
Location: Frankfurt am Main
Contact:

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by Zinn »

Thank you for pointing out the problem.
Can you send me a test program for to catch the problem
including the steps how reproduce the problem?

In the mean time you can copy the module SqlControl from BB 1.6 to BB 1.7-RC5 and compile it.
I assume that everything should work.
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by manumart1 »

See attached file.
Read comments at beginning of module PrivTestBdz.
Yes, with SqlControls of BB 1.6 there is no bug.

Regards,
Manuel
Attachments
Priv-TestBdz.txt
Encoded file list for subsystem Priv
(12.24 KiB) Downloaded 590 times
Zinn
Posts: 123
Joined: Mon Nov 24, 2014 10:47 am
Location: Frankfurt am Main
Contact:

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by Zinn »

Sorry, your test program doesn't trap. It opens the form and shows rows = 5 and cols = 3.
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by manumart1 »

The form Priv/Rsrc/FrmTestBdz.odc initially has no SqlControls.Table; you have to insert it via menu SQL > Insert Table.
To reproduce the bug, follow the steps written as comments at beginning of module PrivTestBdz.
Zinn
Posts: 123
Joined: Mon Nov 24, 2014 10:47 am
Location: Frankfurt am Main
Contact:

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by Zinn »

Code: Select all

   PROCEDURE (d: StdDirectory) NewTable (p: Controls.Prop): Views.View;
		VAR c: Table;
	BEGIN
		NEW(c); OpenLink(c, p); InitStdProp(c.sprop); 
		c.columns := 2;
		NEW(c.width, c.columns); FillArray(c.width, 0, c.columns, defColW); (* extended on demand *)
		NEW(c.mode, c.columns); FillArray(c.mode, 0, c.columns, center);
		SetupTable(c); 
		RETURN c
	END NewTable;
I change the procedure NewTable in the way as shown above and the problem is solved.
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by manumart1 »

I made a test with your changes, saved the form, and when re-opened the form I get a trap:
index out of range
SqlControls.Table.Restore
....
WHILE (col < t.table.columns) & (x < r) DO
. w := t.width[col]; <-- TRAP
col = 2, but the arrays t.width and t.mode have only elements 2 elements (0, 1) The SqlDB.Table has 3 columns (0, 1, 2).
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by manumart1 »

I have modified SqlControls, changes in color purple and with displays in Log.
Attached encoded file list:
  • Priv/Mod/TestBdz.odc
  • Priv/Rsrc/FrmTestBdz.odc
  • Sql/Mod/Controls.odc
Seems to work.
Attachments
EncodedFileList.txt
(32 KiB) Downloaded 542 times
Zinn
Posts: 123
Joined: Mon Nov 24, 2014 10:47 am
Location: Frankfurt am Main
Contact:

Re: Displaying SQLite table via "Sql Table Control" inside F

Post by Zinn »

Thank you for your help.
I uploaded the new version of SQLite
and the correction of the BB CPC Edition
to the Component Pascal Collection.
I hope that all your problems solved now.
Post Reply