Problem in module SqlDB with SqlDB.noData

All except GUI problems

Problem in module SqlDB with SqlDB.noData

Postby manumart1 » Wed Jun 17, 2015 9:51 am

This problem happens to me with module SQLiteDriver.
I don't know if it also occurs with module SqlOdbc.

The problem is that when reading a table using this schema:
Code: Select all
VAR tab*: SqlDB.Table; r: INTEGER; row: SqlDB.Row;
.. read from database into tab
r := 0; tab.Read(r, row);
WHILE tab.res # SqlDB.noData DO
  INC(r); tab.Read(r, row)

when the end of tab is reached, tab.Read returns tab.res = SqlDB.noData, but then tab.Read is also updating the internal space of tab (SqlDB.Table), putting "" into the columns of type text (string) of the last succesfully row read.
Using the loop that row is the last row of the DB table, but for example
Code: Select all
tab.Read(2, row); <-- Read OK the row of index 2 from tab
tab.Read(1000, row); <-- Row #1000 does not exists in tab ==> The row of index 2 that was stored in tab (SqlDB.Table) suffers the cleaning up of its columns of type text (string) !

So, once tab.Read returns SqlDB.noData in tab.res, the result set stored in tab (SqlDB.Table) is modified because one row has lost its text values.

The docu says
PROCEDURE (t: Table) Read (row: INTEGER; VAR data: ANYREC)
Reads the row'th row of the result table into the interactor data.
t is permissible to read past the end of the table (row >= t.rows). The whole interactor is cleared in this case and t.res is set to noData.

But SqlDB.Row has pointers to arrays of chars, and these seemingly point directly to the same arrays of chars stored in the SqlDB.Table, and because of that, the cleaning up of text columns of one row of SqlDB.Table happens.

I am using a WHILE instead of a FOR
Code: Select all
FOR m := 0 TO tab.rows - 1 DO
  tab.Read(r, row)

because the docu of Sql Developer Manual says:
Note that the field table.rows cannot be computed by all database drivers, some may return MAX(INTEGER) instead. To loop over all rows of a result table, it is therefore better to avoid using table.rows in the loop termination condition. Instead, looping can be done while table.res # SqlDB.noData holds.

Finally, I put here some code of SqlDB:
Code: Select all
  fields: POINTER TO ARRAY OF String

  rows, columns, res: INTEGER;
  (t: Table) Read (row: INTEGER; VAR data: ANYREC), NEW, ABSTRACT

PROCEDURE (t: StdTable) Read (row: INTEGER; VAR data: ANYREC);
VAR item: Meta.Item;
  LookupItem(item, data, FALSE);
  DoRead(t, row, item) <----------
END Read;

PROCEDURE DoRead (t: StdTable; row: INTEGER; VAR item: Meta.Item);
  IF row < t.rows THEN
    col := 0; t.res := 0;
    ReadItem(item, t, row, col);
    ClearItem(item); <----------
    t.res := noData
END DoRead;

PROCEDURE ClearItem (item: Meta.Item);
  CASE item.typ OF
  | Meta.arrTyp: (* 18 *)
    IF item.BaseTyp() = Meta.charTyp THEN <---------- 5. SQLDB.Row.fields^[k]^ | 7. ...
      item.PutStringVal("", ok); ASSERT(ok, 22) <---------- This is where the cleaning up happens
    ELSE <---------- 3. SQLDB.Row.fields^
      i := 0; len := item.Len();
      Log.String("**     ClearItem Len array = "); Log.Int(len); Log.Ln;
      WHILE i # len DO
        item.Index(i, base); ClearItem(base);
  | Meta.recTyp: (* 17 *) <---------- 1. SQLDB.Row
    s.Scan; WHILE ~s.eos DO ClearItem(s.this); s.Scan END
  | Meta.ptrTyp: (* 19 *) <---------- 2. SQLDB.Row.fields | 4. SQLDB.Row.fields^[k] | 6. ...
    IF base.obj = Meta.varObj THEN ClearItem(base) END
END ClearItem;

Posts: 66
Joined: Tue Sep 17, 2013 6:25 am

Re: Problem in module SqlDB with SqlDB.noData

Postby manumart1 » Thu Jun 18, 2015 7:37 am

The reason of malfunctioning is in module SQLiteDriver.
Table.ReadVarString is returning the pointer to the array of char where the text data read from database is stored.
Instead, a new array should be allocated, filled in with data, and returned.
Posts: 66
Joined: Tue Sep 17, 2013 6:25 am

Return to BlackBox Framework

Who is online

Users browsing this forum: No registered users and 1 guest