Displaying SQLite table via "Sql Table Control" inside Form

Using SQLite with BlackBox: http://zinnamturm.eu/downloadsOS.htm#SQLite

Displaying SQLite table via "Sql Table Control" inside Form

Postby manumart1 » Wed Feb 11, 2015 5:20 pm

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: 66
Joined: Tue Sep 17, 2013 6:25 am

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

Postby manumart1 » Fri Feb 13, 2015 8:52 am

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

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

Postby Zinn » Fri Feb 20, 2015 6:48 am

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.
Zinn
 
Posts: 58
Joined: Mon Nov 24, 2014 10:47 am

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

Postby manumart1 » Fri Feb 20, 2015 10:53 am

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 210 times
manumart1
 
Posts: 66
Joined: Tue Sep 17, 2013 6:25 am

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

Postby Zinn » Sat Feb 21, 2015 4:50 pm

Sorry, your test program doesn't trap. It opens the form and shows rows = 5 and cols = 3.
Zinn
 
Posts: 58
Joined: Mon Nov 24, 2014 10:47 am

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

Postby manumart1 » Sat Feb 21, 2015 10:44 pm

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.
manumart1
 
Posts: 66
Joined: Tue Sep 17, 2013 6:25 am

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

Postby Zinn » Mon Feb 23, 2015 6:42 pm

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.
Zinn
 
Posts: 58
Joined: Mon Nov 24, 2014 10:47 am

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

Postby manumart1 » Mon Feb 23, 2015 9:53 pm

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: 66
Joined: Tue Sep 17, 2013 6:25 am

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

Postby manumart1 » Tue Feb 24, 2015 11:56 am

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 179 times
manumart1
 
Posts: 66
Joined: Tue Sep 17, 2013 6:25 am

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

Postby Zinn » Mon Mar 02, 2015 7:05 am

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.
Zinn
 
Posts: 58
Joined: Mon Nov 24, 2014 10:47 am

Next

Return to SQLite

Who is online

Users browsing this forum: No registered users and 1 guest

cron