Problem in module SqlDB with SqlDB.noData

All except GUI problems
Post Reply
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Problem in module SqlDB with SqlDB.noData

Post by manumart1 »

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)
END;
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)
END
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

String = POINTER TO ARRAY OF CHAR;
Row = RECORD 
  fields: POINTER TO ARRAY OF String
END;

Table = POINTER TO ABSTRACT RECORD 
  ..
  rows, columns, res: INTEGER;
  ..
  (t: Table) Read (row: INTEGER; VAR data: ANYREC), NEW, ABSTRACT
END;


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);
  ELSE
    ClearItem(item); <----------
    t.res := noData
  END;
..
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);
        INC(i)
      END
    END
  | Meta.recTyp: (* 17 *) <---------- 1. SQLDB.Row
    s.ConnectTo(item);
    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. ...
    item.Deref(base);
    IF base.obj = Meta.varObj THEN ClearItem(base) END
  END
..
END ClearItem;
Regards,
Manuel
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: Problem in module SqlDB with SqlDB.noData

Post by manumart1 »

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.
Post Reply