What is the deal with the ZTable and Memo Fields

Forum related to SQLite

Moderators: gto, cipto_kh, EgonHugeist

Post Reply
wilstyx
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 23.07.2008, 16:53

What is the deal with the ZTable and Memo Fields

Post by wilstyx »

I have checked with three different Sqlite Viewers and the memo is all there. but using a DBMemo only show the first 255 char ( or so ) are shown. I haven't seen this problem since I worked with Paradox PLEASE HELP I'm no novice but I feel like one right now ! I know this is going to be a slap on the forehead moment but I can't figure it out. I have used the Fields and tried to increase the size it works up to 255 then after that is stops
500 600 700 nothing seems to matter.
wilstyx
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 23.07.2008, 16:53

Post by wilstyx »

Well thanks for all the help. One word did it TEXT instead of Memo.
at least i got it fixed.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

Hi wilstyx,

Can you also tell us where you needed to put Text instead of Memo?

Mark
Image
wilstyx
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 23.07.2008, 16:53

Post by wilstyx »

Is this a Test ? When you do the CREATE Statement Substitute TEXT for Any MEMO or anywhere you need to use a string longer than 255. I Found the Answer in the ZDbcSqLiteUtils.pas in the dbc directory. look at the ConvertSQLiteTypeToSQLType MEMO it not converted but TEXT is
-------------------------------------------------------------------------
else if Pos('TEXT', TypeName) > 0 then
Result := stAsciiStream; // Any length up to limits of SQLITE3
--------------------------------------------------------------------------------
I had found this answer while looking at a totally different post. I knew when I found it it was a 'Slap my forehead moment' and it was. Hope that Gets me An A. I was answering my own post but you are right maybe someone else would like the information. Thanks for all the hard work Great Library.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

No, it's not a test. :lol: It's just, I'm not really experienced with SQLite implementation myself. If it had been mysql I had explained it myself.
Does this mean :
Because sqlite is quite weakly typed you can choose whatever you want to describe the field type. So you choose Memo to declare a memo field.
As it's this 'flexible', zeoslib has to guess what kind of return field it should provide. And the Memo field type you 'invented' is not in his 'guess list'.

I remember a feature request about 'column type affinity'. This request refers to the SQLite Docs on this topic

Feel free to correct me when necessary.

Mark

P.S. If somebody wants to write some more elaborate documentation on this affinity and the handling by zeoslib, please do so in the knowledge base!
Image
wilstyx
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 23.07.2008, 16:53

Post by wilstyx »

Sorry Mark,
I saw the Team member and assumed. Well you know. Since SQL lite version 3 and above has these column affinities ( from the SQLITE3 Website )
------------------------------------------------------------------------------------
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.
-----------------------------------------------------------------------------------
And of course I knew this when I started converting a customers database.
Problem was I just went on my merry way using the columns as they were
assigned originally. The problem was that Zeos does not complain about creating a Column of type MEMO. It had no problem putting the data into the database. It only when it reads a column of type MEMO It converts it to type ftString ( by default) which is limited to 255 charactors.
This could be added to the ConvertSQLiteTypeToSQLType very easy but probably should not since it is not supported by SQLITE3. As far as defaulting to a type of ftString with the 255 char limit may not be correct as SQLITE3 has no limit on string sizes as other databases do. Just my 2 cents ( with 5 cents change )
Post Reply