AsString and AsWideString only returns 255-Bytes
lazarus / 16-Databases

----------------------------------------------------------------------
QEnnay · 2020-05-08 16:09

Hello,

I am using SQLite3, Lazarus 2.0.8 and cannot seem to retrieve text
data longer than 255-bytes. Using the DBBrowser the Field is shown
correctly with valid data that is 1776 long.

ETA: I should mention that I am able to save the 1776 bytes of data
using my program, so I doubt it has anything to do with the SQLite
components and controls.

But in my program, this code, both "Cnt" only return 255-Bytes of data
in "tStr".
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const {
global }  fldLatinNames = 'db_LatinNames'; procedure
TDB.sqlMainAfterScroll(DataSet: TDataSet);var  Cnt : Integer;  tStr :
WideString;begin 
tStr:=sqlMain.FieldByName(fldLatinNames).AsWideString;  Cnt:=Length(sq
lMain.FieldByName(fldLatinNames).AsWideString); procedure
TDB.sqlMainAfterScroll(DataSet: TDataSet);var  Cnt : Integer;  tStr :
String;begin  tStr:=sqlMain.FieldByName(fldLatinNames).AsString; 
Cnt:=Length(sqlMain.FieldByName(fldLatinNames).AsString); 
I found mentions here on similar issues in 2009 and 2016 and no
resolutions that I have found.

What must I do to retrieve my full Field of data?

Thanks

----------------------------------------------------------------------
marcov · 2020-05-08 16:15

Do you have either {$mode delphi} OR

{$mode objfpc}{$H+} in that unit ?

----------------------------------------------------------------------
QEnnay · 2020-05-08 17:16

Quote from: marcov on May 08, 2020, 06:15:35 pm{$mode objfpc}{$H+} in
that unit ?

Thank you, I have that one. It assigns them automatically as all Forms
have that at the top. I deleted that line and it is still 255 only for
Both.

How do I get it to display all the set Conditionals? From my Delphi
days many years back I thought it was Ctrl+Shift+O or something, but
cannot find "Conditionals," but they may be called something else
these days.

----------------------------------------------------------------------
PaulRowntree · 2020-05-09 01:08

Quote from: QEnnay on May 08, 2020, 07:16:32 pmQuote from: marcov on
May 08, 2020, 06:15:35 pm{$mode objfpc}{$H+} in that unit ?

Thank you, I have that one. It assigns them automatically as all Forms
have that at the top. I deleted that line and it is still 255 only for
Both.

How do I get it to display all the set Conditionals? From my Delphi
days many years back I thought it was Ctrl+Shift+O or something, but
cannot find "Conditionals," but they may be called something else
these days.
Check that all units that the string passed through also have the
{$H+} option activated.

----------------------------------------------------------------------
eljo · 2020-05-09 01:20

here is a probable explanation, from a very fast read, I can't say I
agree with everything its said, but the point made looks solid and its
marked solved so... try it?

https://forum.lazarus.freepascal.org/index.php?topic=40253.0

----------------------------------------------------------------------
QEnnay · 2020-05-09 13:22

Quote from: PaulRowntree on May 09, 2020, 03:08:27 amCheck that all
units that the string passed through also have the {$H+} option
activated.

Thanks Paul, but I checked that yesterday and everything has it
defined.

----------------------------------------------------------------------
QEnnay · 2020-05-09 13:36

Quote from: eljo on May 09, 2020, 03:20:07 amhere is a probable
explanation, from a very fast read, I can't say I agree with
everything its said, but the point made looks solid and its marked
solved so... try it?

Thanks. Much of that discussion may have been true in 2018, but it
seems Lazarus/SQLite3 has moved on and -- not for the better.

Changing the VARCHAR to VARCHAR(32000) (or anything large enough for
my use) made no difference. I still got 255.

But, in that discussion the point made that VARCHAR is changed in TEXT
internally made me try using TEXT and that worked to retrieve the
data. BUT, it has mashed up other stuff as now all displayed fields of
that Data show "(MEMO)" instead of actual readable Text.

Changing the visual displayed fields to DBMEMO instead of DBEdit has
broken the program in many, many places so I had to back that out.

I also tried the suggestion of the OnGetText mentioned there but I got
an error "Field zPath has an invalid field type (Memo) to base index
on" so had to drop that idea too.

Clearly this is an FPC (and/or Laz) thing as the data is saved OK, it
just cannot be retrieved into anything but a MEMO. Strange thinking.

----------------------------------------------------------------------
eljo · 2020-05-09 14:26

Quote from: QEnnay on May 09, 2020, 03:36:13 pmQuote from: eljo on May
09, 2020, 03:20:07 amhere is a probable explanation, from a very fast
read, I can't say I agree with everything its said, but the point made
looks solid and its marked solved so... try it?

Thanks. Much of that discussion may have been true in 2018, but it
seems Lazarus/SQLite3 has moved on and -- not for the better.
Changing the VARCHAR to VARCHAR(32000) (or anything large enough for
my use) made no difference. I still got 255.

But, in that discussion the point made that VARCHAR is changed in TEXT
internally made me try using TEXT and that worked to retrieve the
data. BUT, it has mashed up other stuff as now all displayed fields of
that Data show "(MEMO)" instead of actual readable Text.

Apparently not that much its the same problem as far as I can tell.
I'll need to investigate farther to comment any farther though.

Quote from: QEnnay on May 09, 2020, 03:36:13 pmChanging the visual
displayed fields to DBMEMO instead of DBEdit has broken the program in
many, many places so I had to back that out.

I also tried the suggestion of the OnGetText mentioned there but I got
an error "Field zPath has an invalid field type (Memo) to base index
on" so had to drop that idea too.
that sounds that you did something wrong. If you can provide a small
sample with the problem as you see it, no need for real data either
just a minimal database, one table with a few records in it (10 is
good round number for records) and a form with a grid, some dbedits 
I'll try to make it work properly and I migth be able to come up with
a way to solve your problem a bit more creative.

Quote from: QEnnay on May 09, 2020, 03:36:13 pm
Clearly this is an FPC (and/or Laz) thing as the data is saved OK, it
just cannot be retrieved into anything but a MEMO. Strange thinking.
If I remember correctly this is a sqlite thing, it does not report
correctly the size of the varchar fields and the framework has to use
some assumptions to get it to at least work. Granted those assumptions
are a bit on the conservative side of things.
In any case, since I'm not a fun of SQLite, I find my self in a
position that I have no tools to work with so the requested sample is
mandatory and probably some hints on where to download the sqlite dll
for windows (32bit preferably).

----------------------------------------------------------------------
Thaddy · 2020-05-09 14:37

The issue can only be that the Sqlite units themselve or - bot low-
level and classes- are compiled in {$H-state}.
I can not reproduce the issue, btw. using trunk and 3.2.0.
The {$H-} state is unit sensitive. It doesn't automagically become
{$H+} on use elsewhere.
It can simply be an error in FPC's make scripts (compiler options) -
inwhich case it is a bug and needs to be reorted - or a build with
wrong options specified.
Note that the AsString helpers from sysutils would also give a correct
result, since they are AnsiStrings, not ShortStrings.

----------------------------------------------------------------------
eljo · 2020-05-09 14:50

Quote from: Thaddy on May 09, 2020, 04:37:09 pmThe issue can only be
that the Sqlite units themselve or - bot low-level and classes- are
compiled in {$H-state}.
I can not reproduce the issue, btw. using trunk and 3.2.0.
The {$H-} state is unit sensitive. It doesn't automagically become
{$H+} on use elsewhere.
It can simply be an error in FPC's make scripts (compiler options) -
inwhich case it is a bug and needs to be reorted - or a build with
wrong options specified.
Note that the AsString helpers from sysutils would also give a correct
result, since they are AnsiStrings, not ShortStrings.
No its not. Go read the thread I linked earlier.  AVK did well to
track down the problem don't half ass this one do the work. As for
3.2.x if its not released it doesn't exists.

----------------------------------------------------------------------
Thaddy · 2020-05-09 18:17

Quote from: eljo on May 09, 2020, 04:50:37 pmAs for 3.2.x if its not
released it doesn't exists.
As it stands it will not get fixed for 3.0.4.

----------------------------------------------------------------------
PascalDragon · 2020-05-09 20:07

Quote from: Thaddy on May 09, 2020, 04:37:09 pmThe issue can only be
that the Sqlite units themselve or - bot low-level and classes- are
compiled in {$H-state}.
I can not reproduce the issue, btw. using trunk and 3.2.0.
The {$H-} state is unit sensitive. It doesn't automagically become
{$H+} on use elsewhere.
It can simply be an error in FPC's make scripts (compiler options) -
inwhich case it is a bug and needs to be reorted - or a build with
wrong options specified.

It can't be an issue with $H, because according to QEnnay it does not
work for AsWideString either which no matter the mode or string
directive will never return a ShortString.

Quote from: eljo on May 09, 2020, 04:50:37 pmAs for 3.2.x if its not
released it doesn't exists.

RC1 is released however with the official request to test it. So
Thaddy is right if he says whether it works with 3.2 or not.

----------------------------------------------------------------------
eljo · 2020-05-09 20:13

Quote from: PascalDragon link=t
RC1 is released however with the official request to test it. So
[ttThaddy[/tt] is right if he says whether it works with 3.2 or not.
That's good news, hopefully it will not take a year for the actual
release, thank you.

----------------------------------------------------------------------
QEnnay · 2020-05-10 15:51

Rather than the overly-kind "conservative" comment above regarding the
255, I suspect "overlooked" might have been more appropriate.

See here where VARCHAR essentially becomes TEXT with it's virtually
limitless size all handled as it should by by SQLite3.
https://www.sqlite.org/datatype3.html
scroll down to this 3.1.1. Affinity Name Examples
Quote(ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not
impose any length restrictions (other than the large global
SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric
values.
Why quote the behavour of a version of Laz that has not yet been
released? That's no help at all as I am about 6-hours into making this
cobbled together thing work around the 255-oversight restriction.

----------------------------------------------------------------------
LacaK · 2020-05-11 06:43

Please how do you have defined SQL table, from where you read string
data?
Column must be defined as VARCHAR with given maximum size. For
example:
VARCHAR(8000); // here is limit 32767
If you need larger varchar data you must use TEXT -> MEMO

----------------------------------------------------------------------
QEnnay · 2020-05-11 15:15

Quote from: LacaK on May 11, 2020, 08:43:33 amPlease how do you have
defined SQL table, from where you read string data?
Column must be defined as VARCHAR with given maximum size. For
example:
VARCHAR(8000); // here is limit 32767
If you need larger varchar data you must use TEXT -> MEMO

Thanks for the reply, but I mentioned above that I had tried adding
the length designation to VARCHAR and it made no difference. I tried
VARCHAR(2000), VARCHAR(32000), VARCHAR(max_int) and still got 255.
Pretty sure you will find it is an FPC/Laz Unit Definition issue.

Defining it as TEXT will display "(MEMO)" in all the data-aware db-
fields other than a Memo. A bit useless.

The link in my previous post was to the SQLIte3 website and they say
there is no limit for VARCHAR as it is treated as TEXT internally with
unlimited length.

----------------------------------------------------------------------
LacaK · 2020-05-12 08:04

Quote from: QEnnay on May 11, 2020, 05:15:31 pmThanks for the reply,
but I mentioned above that I had tried adding the length designation
to VARCHAR and it made no difference. I tried VARCHAR(2000),
VARCHAR(32000), VARCHAR(max_int) and still got 255. Pretty sure you
will find it is an FPC/Laz Unit Definition issue.
I can not believe this, because there are tests against SQLite which
works with VARCHAR fields with size > 255
So problem must be somewhere else. Can you again ZIP here your
database and test program ...

Quote from: QEnnay on May 11, 2020, 05:15:31 pmDefining it as TEXT
will display "(MEMO)" in all the data-aware db-fields other than a
Memo. A bit useless.
Yes

Quote from: QEnnay on May 11, 2020, 05:15:31 pmThe link in my previous
post was to the SQLIte3 website and they say there is no limit for
VARCHAR as it is treated as TEXT internally with unlimited length.
Yes, true