Zeos truncates data

vannus · Post by **vannus** » 05.02.2011, 10:18

Using other software I can see that there is more than 255 characters in the database, but using ZTable or ZQuery only gives the first 255 characters.

How do I get to see all my data?

Wild_Pointer · Post by **Wild_Pointer** » 06.02.2011, 14:55

vannus,

please tell what DBMS you are using (Postgresql, Firebird or other), what version of Zeos library you have, OS and compiler (Delphi, Lazarus..) with version.

vannus · Post by **vannus** » 06.02.2011, 16:13

SQLite 3.7.4
Zeos 6.6.6 stable
Windows 7
Delphi 7

Wild_Pointer · Post by **Wild_Pointer** » 09.02.2011, 08:07

vannus,

I can not help you alot with SQLite as I'm not using it, but maybe there are some analogy with Postgresql. I noticed, that I also get long varchar fields truncated to 255. The answer to "why?" lies in libpq - client library to postgres. Somehow if the field is bigger than 255 then PQfsize returns -1, witch means "... the data type is variable-length."
Maybe its the same (or alike) with SQLite ?... You should investigate procedure TZSQLiteResultSet.Open. It is where ColumnsInfo is defined.
It would be nice to hear from you how is it in SQLite...

Good luck!

vannus · Post by **vannus** » 10.02.2011, 00:34

Thanks for the tip, I thought I would get it sorted when I looked at TZSQLiteResultSet.Open at first, but the problem looks to be a lot deeper in the code :S

I've found that

1- zeos defaults to 255 chars for fields with undefined field size (in sqlite)

2- replacing that with sqlite default field size (1,000,000,000) will cause an EOutOfMemory Exception

3- going with a large enough field size (5000 in my case) will get replaced somewhere after TZSQLiteResultSet.Open

I've tried to find where the code that is shrinking the field size is, but can't.
It seems to be between
. InternalInitFieldDefs;
. ColumnList := ConvertFieldsToColumnInfo(Fields);
in
. TZAbstractRODataset.InternalOpen;

strangely, I can't debug BindFields(True);

I think I'll have to use a nasty workaround of splitting the data over multiple fields

Wild_Pointer · Post by **Wild_Pointer** » 10.02.2011, 08:19

vannus,
Did you look into

Code: Select all

ColumnType := ConvertSQLiteTypeToSQLType(TypeName^,
          FieldPrecision, FieldDecimals);

Does it return correct fieldtype and precision?

vannus · Post by **vannus** » 10.02.2011, 10:04

If you set field size & precision in the sqlite database then

Code: Select all

ColumnType := ConvertSQLiteTypeToSQLType(TypeName^, 
          FieldPrecision, FieldDecimals);

returns the correct fieldtype, size & precision.

However during TZAbstractRODataset.InternalOpen which happens later on, those values get changed
- my 200 length fields become 255
- my 5000 length fields become 1000

Wild_Pointer · Post by **Wild_Pointer** » 10.02.2011, 10:15

However during TZAbstractRODataset.InternalOpen which happens later on, those values get changed
- my 200 length fields become 255
- my 5000 length fields become 1000

This is really strange as I observed no such behavior with postgres (with fields less than 255 char long). I expect Postgres and SQLite behave alike in abstract units... Now I'm at work, so I can't test this, but maybe this night I'll investigate this... No promises though.

Good luck!

vannus · Post by **vannus** » 10.02.2011, 14:19

I created a test application from scratch and tried the same db (with field sizes defined) - and it reads the data correctly.

The changing field sizes problem must be something to do with my original application and not Zeos.

I'll post something in the SQLite section to discuss the 255 being the default string length.

Thanks for your help!

marcov · Post by **marcov** » 10.02.2011, 15:29

Zeos might cache some values that are read when the components are created in the properties of the created components. I had some trouble with this too, and suddenly noticed that NEW screens and tzqueries did work properly. Comparing the components on a DFM level then showed the problem.

vannus · Post by **vannus** » 11.02.2011, 03:49

Yep, replaced the ZReadOnlyQuery with ZTable (hopefully data structure wont change) and it now behaves.

Just need to remember to always define field size for SQLite db's in future.

Wild_Pointer · Post by **Wild_Pointer** » 11.02.2011, 08:07

vannus,

i would discourage you using ZTable on tables that are growing as it will slow your application in future. ZTable is good for tables of not changing data - that you write once and new records are added rarely. If the table is used for general operation (sales, logging and so on) then the ZQuery should be used with "WHERE" condition that filters only relevant records. But I'm sure you already know this.

Good luck!

Post by **mdaems** » 02.04.2011, 22:33

vannus,

If that replacing the ZQuery by a ZTable solved your problems, I guess replacing it by a ZQuery again my work too. (see comment by marcov)

Mark