TBufDataset Filtering & TField issues

[tguns]

Hey there, I have a couple of issues regarding TBufDataset and TField.

I am developing a software that works with SQLite3 database. Insertion and selection works independently - data is saved by one module and read by another. Thus, it would really help to have a dataset working "offline" (and it was my understanding TBufDataset works this way).

Currently, I have a TSQLite3Dataset object select the data, then clone the whole dataset (FieldDefs and Records) to TBufDataset. Now here's the first issue:

No matter what, I can't seem to store VARCHAR columns as ftString instead of ftMemo (TFieldType). Each VARCHAR column is automaticly stored as ftMemo, and I can't seem to find a workaround. And if I change those fielddefs to ftString on my own and assign a string, its left empty. Now, I could live with ftMemo, since through OnGetText I would format it properly, BUT, ftMemo cannot be set as Index.

Which brings me to second issue. How does filtering TBufDataset work? I thought it was going to be easy, just a few lines of code, but nothing seems to work.

Code: [Select]

bufDs.Close;
bufDs.Filtered := True;
bufDs.Open;
Deletes the whole data (0 records after opening).

Code: [Select]

bufDs.Open;
bufDs.Filtered := True;
Deos nothing, even though filtering condition has changed.

Right now I am trying to filter through OnFilterRecord event, since I can't use

Code: [Select]

bufDs.Filter := 'column = ''expr'''; as column is not an index field. And when I tried to add it as an index, I got an error stating that ftMemo field cannot be set as index.

So here comes the third and final issue:

How to set field to ftString and get it work properly?

I cannot include more code as I am at Uni right now, but I'll attach if needed once I get home in a couple of hours.

Thank you in advance for ANY kind of advice or help.

[BigChimp]

Thanks for your clear post.

It may help if you search the bugtracker to see if these are known issues... you can search on e.g. bufdataset and get bugs like
20514 Adding index to Tbufdataset causes it to stop storing data
19930: TBufDataset does not support ftVarBytes or ftVariant
19807: BufDataSet doesn't support WideString fields in building Index  (=> might influence setting index on memo???)
... don't know if these are relevant to your situation though.

Also, mentioning FPC/Lazarus version and platform may help. Some of these bugs have been solved fairly recently, so upgrading may help.

Finally, I hope somebody who really knows bufdataset (and remembers more than I ) will be able to give you a solution. If not, please feel free to upload a simple test program to the bug tracker (if the bug has not already been reported).

Thanks,
BigChimp

[tguns]

I think I found the reason behind faulty TFields behaviour - SQLite3 creates tables that are defined as

Code: [Select]

CREATE TEMPORARY TABLE tmp AS SELECT * FROM ... with general SQLite3 datatypes (INTEGER for SMALLINT, INT, BIGINT,...), (TEXT for CHAR, VARCHAR,...) and when loaded into Lazarus they are naturally represented in the "biggest" way possible, so in case of TEXT its ftMemo, it seems.

Thus, I have to use standard

Code: [Select]

CREATE TEMP TABLE (column COLUMN_TYPE, column COLUMN_TYPE) script for temporary tables, then fill them with data and do further operations.

BTW, Thank you for your reply BigChimp, I searched through bugtracker, but didn't find answers to the other issues, I guess I am off to do workarounds and hacks until I got my answers .

[tatamata]

Which version of fpc?
TBufDataset filterig is not working in some fpc version as supposed.
You can try zmsql instead, which is based on TBufDataset version with working filtering.

[tguns]

Which version of fpc?
TBufDataset filterig is not working in some fpc version as supposed.
You can try zmsql instead, which is based on TBufDataset version with working filtering.

FPC version 2.4.2

Anyway, I hope I won't jinx it, but it seems I figured out all three issues - it was all down to representing temporary table columns by SQLite as TEXT rather than VARCHAR. For this reason Lazarus interpreted it as ftMemo rather than ftString. Simple workaround was necessary as stated in my previous post - create table the classic

Code: [Select]

CREATE TABLE table (COLUMN COLUMN_TYPE,... ) way and then use INSERT INTO, instead of

Code: [Select]

CREATE TABLE AS SELECT * FROM ....

Thank you guys anyway.

[BigChimp]

Ok. 2.6.0 is the newest stable version.
Lazarus 0.9.31 (IIRC) was recently released with this compiler.

If you can, you might want to upgrade as a lot of database bugs have been fixed... (not saying yours especially, but in general).

Thanks for the articulate, clear posts, BTW, that really helps