Ubuntu SQLite3 Locate loPartialKey
lazarus / 1-Linux

----------------------------------------------------------------------
ssw · 2007-10-29 08:11

I have a standard installation from the Gutsy repositories of Lazarus
0.9.22-1, fp 2.0.4-5 and SQLite3 3.4.2-1build1 and fp source code
2.0.4 from SourceForge.

For the most part, this has worked really well, but there is a problem
with 'locate' when used with a partial key to position a dbGrid on a
particular row.  The following is one example of code used to test
this:

  searchOptions := [loPartialKey];
  locateSuccess := dstCusType.locate('name', locateName,
searchOptions)

Where locateName holds the partial string I want to find in field
'name' of the customer type table.  Note that, if the full string is
used in locateName, the code works fine.

I would like to know if others have managed to enable partial key
lookups over SQLite3 and perhaps where I might be going wrong.  The
customer type table has an index on the name field and I'm using a
data module.  Everything else has worked well so far.

----------------------------------------------------------------------
Leledumbo · 2007-10-29 09:25

Please update your Lazarus to latest 0.9.23 snapshot, fpc to 2.2.0 (or
2.2.1). Try it first, since you're using a quite old version. There
might be changes to sqlite unit.

----------------------------------------------------------------------
ssw · 2007-10-29 20:45

That seemed like a reasonable idea...

..so I downloaded the fpc, fpc-src and lazarus .rpms from the snapshot
of the 29th and converted them to .debs.  Before they were installed I
completely removed the original lazarus and fpc packages from the
system.  I also removed the .lazarus folder in my home directory.
 Installation seemed to go well and the IDE came up nicely, but when I
tried to compile I got:

"...Fatal: Can't find unit contnrs used by Graphics"

Not being familiar with Lazarus, I don't know if this is a simple
configuration issue or something more serious.  It may even be related
to conversion of the .rpms to .debs (I used the -k option to make sure
the version numbers were maintained).  I suspect my next move should
be to redo the alien conversion but with the --script option, so
that's what I'm going to do.

----------------------------------------------------------------------
ssw · 2007-10-29 22:07

I managed to get the snapshot Lazarus and fpc installed and running -
including compilation of simple apps - then I added the sqlite3
component.  This prompted a recompile which finished cleanly.  The
only trouble was, when Lazarus restarted the tSQLite3Dataset component
was nowhere to be found.

A bit of a pain...

----------------------------------------------------------------------
ssw · 2007-10-30 06:45

One small step at a time...

Starting lazarus as root and adding the sqlite3 component successfully
installed tsqlite3dataset.

Next problem, accessing text fields of a SQLite3 table returns (MEMO)
for each text value.  I'll have to have a think about this.

----------------------------------------------------------------------
ssw · 2007-10-30 07:14

Sidestepping the diversionary problems and getting back to the
original requirement to do locate on partial key values, it looks like
the issue is still there.

Locate continues to be successful if the full key value is used but
fails for partial keys.  So, onwards to Firebird.

----------------------------------------------------------------------
ssw · 2007-10-30 20:14

Final entry on this one.

I have abandoned the pursuit of partial key lookup for now, using SQL
to filter the dataset results instead.  So, the dataset isn't
positioned on a row but it does show only those rows which match the
locate string - code to do this is:

     SQLite3Dataset1.SQL := 'select name from cusType where (name like
''' + filterString + '%'')';
     SQLite3Dataset1.active := false;
     SQLite3Dataset1.active := true;

filterString is the string variable seeded with the keystroke data.  A
bit ham-fisted but it does for now - I remain open to better
suggestions.

The problem I had with SQLite3 data being displayed as (MEMO) was
resolved by defining the table fields in the database as varChar(20),
they were originally defined as Text.  SQLite doesn't generally take
any notice of the data type but it appears that fpc does.

So, problem put aside for now.

----------------------------------------------------------------------
LuizAmérico · 2007-12-19 20:10

I implemented loPartialKey in TSqlite3Dataset. Take a look at
http://www.geocities.com/camara_luiz/.

BTW: Is better to post questions regarding sqlite in the Databases
forum where i always look. I found this topic incidentally while
searching for info to implement loPartialKey ;-)