SqlDrivers.Table, new method ReadLong

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

SqlDrivers.Table, new method ReadLong

Post by manumart1 »

I would like to propose the addition of a new method ReadLong to the type SqlDrivers.Table.

It is necessary to being able to read an integer database column of size 64 bits.
I do not need it nowadays, but maybe in the future, and I think it is ok for the sake of completeness of the Sql subsystem.

Caution:
The new method ReadLong will invalidate the modules SqlOdbc, SqlOdbc3 and SqlObxDriv, which must now implement that new method.

Sugested changes:

Module SqlDrivers

Code: Select all

PROCEDURE (t: Table) ReadLong* (row, column: INTEGER; OUT val: LONGINT), NEW, ABSTRACT;
Module SqlDB

Code: Select all

PROCEDURE ReadItem (item: Meta.Item; t: StdTable; row: INTEGER; VAR col: INTEGER);
  VAR ... long: LONGINT; ...
...
  CASE item.typ OF
...
  | Meta.longTyp:
    tab.ReadLong(row, col, long);
    item.PutLongVal(long); INC(col)
...
END ReadItem;

PROCEDURE ClearItem (item: Meta.Item);
...
  CASE item.typ OF
...
   | Meta.longTyp:
      item.PutLongVal(0)
...
END ClearItem;
----------
As an additional benefit of the new method ReadLong, the second problem mentioned in http://community.blackboxframework.org/ ... ?f=16&t=97 is solved:
Reading all rows via While loop:
r := 0; table.Read(r, rec);
WHILE table.res # SqlDB.noData DO
...
INC(r); table.Read(r, rec);
END
But if destination record rec has a field of type Dialog.Currency, the last call to table.Read(r, rec) is made with r >= table.rows, and will return SqlDB.noData, but before, all fields of the record rec are cleared, but Dialog.Currency has a subfield of type LONGINT that NOW is well expected in procedure SqlDB.ClearItem.
Regards,
Manuel
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: SqlDrivers.Table, new method ReadLong

Post by Josef Templ »

I agree, support for LONGINT should be added to SqlDrivers.Table.

- Josef
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: SqlDrivers.Table, new method ReadLong

Post by Josef Templ »

manumart1 wrote:I would like to propose the addition of a new method ReadLong to the type SqlDrivers.Table.
Could you please download and test the support for LONGINT in
http://blackboxframework.org/unstable/i ... a1.313.zip

- Josef
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: SqlDrivers.Table, new method ReadLong

Post by Josef Templ »

Please download and test the updated version from
http://blackboxframework.org/unstable/i ... a1.319.zip.

- Josef
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: SqlDrivers.Table, new method ReadLong

Post by manumart1 »

I made some tests under blackbox-1.7-a1.319, and viewed these things:

1. The SQLite database works ok reading large integer values.
In fact, it works better than before, because (although not related to ReadLong) it does not trap when table.Read() is called with an empty table (problem 1 from http://community.blackboxframework.org/ ... ?f=16&t=97).

2. To test the SqlOdbc driver I used a Mysql table with a column of type BIGINT. SqlOdbc and SqlOdbc3 work ok.
I looked at procedure Table.ReadLong of modules SqlOdbc and SqlOdbc3, and noticed that they also work using the constant value -25. See below.

Code: Select all

PROCEDURE (t: Table) ReadLong (row, column: INTEGER; OUT val: LONGINT);
	VAR res, len: INTEGER; str: ARRAY 32 OF CHAR;
BEGIN
	ASSERT(row >= 0, 20); ASSERT(row < t.rows, 21);
	ASSERT(column >= 0, 22); ASSERT(column < t.columns, 23);
	ASSERT(t.stmt # 0, 24);
	SetPos(t, row, column, res);
	IF res = 0 THEN
(*
I searched the internet for the integer value of a constant named SQL_C_BIGINT or something like that.
I did not found nothing useful; finally I arrived to
https://github.com/graemeg/freepascal/blob/master/packages/odbc/src/odbcsql.inc
SQL_BIGINT = -5
SQL_SIGNED_OFFSET = -20;
SQL_C_SBIGINT = SQL_BIGINT+SQL_SIGNED_OFFSET === -25
*)
		Check(WinSql.SQLGetData(t.stmt, SHORT(column + 1), -25,
													SYSTEM.ADR(val), 0, len), NIL, t.stmt, res);
		IF len = WinSql.SQL_NULL_DATA THEN val := 0 END
(*
		Check(WinSql.SQLGetData(t.stmt, SHORT(column + 1), WinSql.SQL_C_WCHAR,
													SYSTEM.ADR(str), LEN(str) * SIZE(CHAR), len), NIL, t.stmt, res);
		IF len = WinSql.SQL_NULL_DATA THEN val := 0 
		ELSE Strings.StringToLInt(str, val, res);
			IF res # 0 THEN val := 0; res := 4 END
		END
*)
	END;
	t.res := res
END ReadLong;
3. I had never used before the SqlOdbc driver, and I see something that is strange to me. In SQLite I open the database connection only once, when a certain module is loaded, and close it when that module is unloaded (in fact, when BlackBox BB closes). I do not see any problem in the connection being alive all the time that the BlackBox program is running. I can execute in BB a Select statement and get, say, 7 rows. Later I use an external program to add another row to the same database (and Commit just in case), and when I re-execute in BB the same Select statement, I get 8 rows: the old 7 ones and the new one. OK.
But this does not happen under the SqlOdbc driver (Mysql). To read fresh values from database I have first to reopen the database connection db and make table := db.NewTable().

Regards
Josef Templ
Posts: 262
Joined: Tue Sep 17, 2013 6:50 am

Re: SqlDrivers.Table, new method ReadLong

Post by Josef Templ »

thanks for the tests and for the hint regarding the value of SQL_C_SBIGINT.
This seems to be an extension of ODBC3, so I added it to the SqlOdbc3 driver only.

The MySql driver behavior is not related to ReadLong.
The question is here if MySql supports multiple parallel clients at all.
This may depend on the version and table storage subsystem.
I have never used it so far.

You can download a version with the latest changes regarding this issue from http://blackboxframework.org/unstable/ under issue-#87.

- Josef
manumart1
Posts: 67
Joined: Tue Sep 17, 2013 6:25 am

Re: SqlDrivers.Table, new method ReadLong

Post by manumart1 »

The new method ReadLong was added, issue-#87, see
http://forum.blackboxframework.org/viewtopic.php?f=50&t=325
Post Reply