TBufDataset Filtering & TField issues
lazarus / 16-Databases

----------------------------------------------------------------------
tguns · 2012-04-02 09:17

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 · 2012-04-02 10:01

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 · 2012-04-02 12:52

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 · 2012-04-02 13:25

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 · 2012-04-02 15:31

Quote from: tatamata on April 02, 2012, 03:25:17 pmWhich 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 · 2012-04-02 16:41

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