Zeoslib Portal

Hello,

I have installed the latest snapshot (3070) on my RAD Studio XE5 Ultimate Update 2. I have only checked the 32 bit version so far as I have stuff to port over from Delphi 2010.
I need to connect my software (also) to SQLite. I got the latest DLL version, downloaded 1 week ago from the official website.

The issue is this: in Delphi 2010, ZEOS 7.1.2 appropriately recognizes VARCHAR fields as string fields, grids appropriately show those fields contents and so on.

In Delphi XE5 + this snapshot ZEOS they get recognized as memo, don't show in TEdits nor TDBGrids.

I have attached a screenshot visually showing the issue: on the top of the form I have put ZEOS components displaying a small table contents in a TEdit and TDBGrid.
At the bottom, the same database but connected by using the FireDAC components provided by default in RAD Studio XE5. You can see how with everything equal, FireDAC show the right contents while ZEOS show the data as memo. The property editor in the screenshot shows how ZEOS recognizes the VARCHAR field in question.

In the same screenshot you may also see how a CHAR(2) field gets appropriately rendered instead.

I have an explanation for this erroneous behaviour (could be wrong!): in SQLite you can declare CHAR, VARCHAR(N) and VARCHAR fields. The latter does not provide a maximum length but has still to be considered as string, not as TEXT / memo!

@ dfumagalli

What lengths should be presented VARCHAR?
2147483647; because so many allows SQLite specification.

Michal

miab3 wrote:@ dfumagalli

What lengths should be presented VARCHAR?
2147483647; because so many allows SQLite specification.

Michal

I am sorry, I am not really knowledgeable about database internals, all I can say is that in Delphi 2010 + ZEOS 7.12 my applications show those fields as they should (strings) and in Delphi XE5 + ZEOS latest snapshot they don't. The screenshot I presented is simple and repeatable enough to give proof of that.

I mean, if it worked for Delphi 2010 and SQLite did not change (I have used the same DLL) then there's something different between how the metadata is evaluated by ZEOS 7.12 and ZEOS 7.2.

I have also used other open source libraries and they all show those fields as strings: Aducom and sqlite3delphi.

The data formats allowed by SQLite are really loose (they go by affinity). The linked doc states that field size specifiers (in example: NNN in VARCHAR(NNN) ) are ignored, TEXT is affine with VARCHAR as well. Therefore I may imagine the discriminating factor in how ZEOS should classify the fields is to be found in the table DDL: if the table has been declared with a VARCHAR (any size including unspecified size) then it should interpret the field as ftString, if the table has been declared with a TEXT or BLOB field then ZEOS should interpret it as ftMemo / ftWideMemo and whatever else.

Here is the end result of this issue: a "real world" application that used to show certain data with ZEOS 7.1.2 and that now shows "Memo" in the string fields:

Notice how the DBGrids show correct data because they are pulling data from another database engine and not using ZEOS.




So far, I have only described "aesthetic" consequences. But there are functional issues as well.
This is the same application throwing an exception because dataset's Locate() used to receive a string field and now chokes on the same field returned as wide memo.

Right click browser menu + Show image to see the full witdh image.

Sorry for my absence.. Busy currently.

[quote=dfumagalli]
I have an explanation for this erroneous behaviour (could be wrong!): in SQLite you can declare CHAR, VARCHAR(N) and VARCHAR fields. The latter does not provide a maximum length but has still to be considered as string, not as TEXT / memo!
[/quote]
It isn't wrong, the more it WAS wrong before 7.2.

As Michal did alredy wrote SQLite has a maximum of 2147483647 Characters but TString/WideStringField has a Maximum of 8KB. So the Columns won't fit and a unwanted trunkation happens. 7.12 and older did use 255 Chars by defult for string fields with undeclared Maximum lengths.

We had loads of bugreports for trunkated fields in the past for SQLite and PostgresSQL. So i changed this behavior as it should be right.
I left a compatibility option to have old behavior:

Add: To your code before open your connection. Than you're a lucky man again.

EgonHugeist wrote:Sorry for my absence.. Busy currently.

It isn't wrong, the more it WAS wrong before 7.2.

As Michal did alredy wrote SQLite has a maximum of 2147483647 Characters but TString/WideStringField has a Maximum of 8KB. So the Columns won't fit and a unwanted trunkation happens. 7.12 and older did use 255 Chars by defult for string fields with undeclared Maximum lengths.

We had loads of bugreports for trunkated fields in the past for SQLite and PostgresSQL. So i changed this behavior as it should be right.
I left a compatibility option to have old behavior:

Add:

Code: Select all

TZConnection.Properties['Undefined_Varchar_AsString_Length'] := 255; //or your expected string field size

To your code before open your connection. Than you're a lucky man again.

Hello,

first of all, no need to be sorry, I develop software as well and know pretty much how busy our days generally are.
Much more for you, as you also code for a large and free project like ZEOS and of course that takes away even more time! My /hat to you!

Regarding the VARCHAR topic: I totally see your point and the logic behind it.
I have some points to bring on the table as well: all what's excellent technically and logic wise sometimes "encounters" reality and adjustments (like the one you suggested) need to be done.

In particular, Delphi visual controls have fairly poor support for Memos. Therefore I totally agree that a true memo field (TEXT for SQL) should be treated as such.
On the contrary, VARCHAR is defined as "character varying" and looking at some quite influential source (Microsoft) they indeed span from 1 to 8000 bytes (like Delphi, coincidence? ).
I have always personally been with the idea that a "normal" VARCHAR should be a string spanning from 0 to 255 bytes, if somebody else thinks that he should define a VARCHAR field to really store 3 petabytes of data, then I believe they are more distant from a good definition than me who love to stick to "old times" numbers.

Therefore it's way more unrealistic to expect to exceed 8k for a field type *that the industry* just does not use for that, than what I expect instead: to have Delphi render textual information of (VAR)CHAR fields in a consistent way and at the best possible way: that is not by just showing "TWideMemo". Even for an end user point of view, what do they think when they encounter "WideMemo" in a TEdit? "Wide whaaaat?".

Therefore I believe the "wrong" 7.1.2 approach is actually more useful in practice and for the majority of applications than the other way around. If someone truly complains because he wants to use a VARCHAR and not the appropriate TEXT field for storing large amounts of data, then it's them who are just not observing long time good practices and long time industry standards. Not the other way around.

Anyway the concept I'd like to express is this:

Please have this "compatibility option" be something that is sure to stay in, and maybe promote it into "feature". Because there's loads and loads of code relying on this: VARCHARs acting actually as VARCHARs. Calling it "compatibility option" sounds like you plan to phase it out / deprecate it sooner or later.
Please don't deprecate it, there's long time loyal users of your library who truly rely on it!

MSEgui deals with the dillemma by several ways.
1. MSEgui widgets show the content of memo fields instead the "(MEMO)" marker.
2. There is a flag dso_stringmemo in dataset options. If it is set memo fields will be converted to normal text fields. This is possible because MSEgui datasets store text in recordbuffer by UnicodeString instead by a fixed size character array. AFAIK Zeos now uses a similar approach so it should be possible for Zeos too.
3. For varchar fields without length declaration there is no ftString length limit.
4. For Sqlite it is possible to set the field type in fielddefs if it is not defined by sqlite3_column_decltype() return value. So it is even possible to convert a column which has been defined as "TEXT" to a normal string field.
Example: a colum has been defined as "memo TEXT". In query use "select +memo as str1 ..." . The "+" is a no-op, the reported type in fielddefs is ftUnknown for "str1" which can be changed to ftString.

Martin

dfumagalli

thanks for the detailed feedback. Nope i don't plan to deprecte or remove these options. I've been trying to say: We've an option to support the known behavior.
As You and Martin saying: The controls are less helpfull for CLOBS. But it might be bossible i can do something later to give an starting string with Length of TField.DisplaySize... Just an idea.

Hello,

i use Zeoslib since lot of years seriously i cant work without it thanks for this amazing work!!!

recently i crash my drive and also my backup drive where are my components! I have needed 1 year for get back all working finding components or new version. Now i backup on 4 drives like my important suff.

For Zeoslib i used before a 7.x version get in 2012/2013 but i tried all in /branches/ 7.0 7.1 7.2 nothing work for the following problem:
sqlite3 with varchar are not display or display as (memo) in my DevExpress cxGrid or TeeChart in Delphi XE

the solution is 7.3 testing branches with Undefined_Varchar_AsString_Length as 255 in properties of TZConnection
not working for me with others 7.x i get blank or (memo) or ask tmemo, twidestring for varchar
in 7.3 sqlite varchar ask tstring correctly

is i miss something for 7.2 ?
i use 7.3 for production is it dangerous ? I trust your work

Thanks for the Undefined_Varchar_AsString_Length solution it save my life

My bad, i tried again with 7.2 and it work perfectly like with 7.3.

Thanks.