SQLQuery+sqlite
lazarus / 16-Databases

----------------------------------------------------------------------
marcov · 2015-04-10 22:26

I had to make a small (local) database app and decided to try sqlite +
sqldb.

As the tutorial says I added connection,transaction,query  and I added
a grid and datasource as well as set the sqlite filename (with
absolute path)

I added the query's SQL as

Select * from "AUTHORS"

and running showed the data in the grid. (!) Moreover the database was
made over sqldb createdb (modified wiki example)

However double clicking on the query and pressing add fields always
ends up in "no such table "AUTHORS"

I checked capitalization and quotes, but am a bit lost. Any ideas what
this can be ?

----------------------------------------------------------------------
engkin · 2015-04-10 23:27

Setting SQLQuery.Active to true will create an empty file if the
database file does not exist, and you'll receive that message.

----------------------------------------------------------------------
marcov · 2015-04-11 12:41

The file exists, and during running the query works fine.

The filename (databasename) in the connection is an abs path.

Should I enter the name again in the filename property of the query? I
thought the connection was enough?

Updated: I looked some more and saw a zero byte sqlite database with
the same name in the lazarus directory.  Removing it solves the
problem.

This is strange since I have the filename with full path in the
connection!?!?!?

----------------------------------------------------------------------
engkin · 2015-04-11 16:18

I guess you filled DatabaseName without a path first and activated the
connection (directly or through SQLQuery.Active) which created the
empty file in Lazarus directory. Changing
SQLite3Connection1.DatabaseName later while the connection is still
active has no effect and will give that message "no such table
"AUTHORS". At this point SQLQuery.Active is not relevant until
SQLite3Connection1.Connected is set to false.

I think TSQLite3Connection.DoInternalConnect, at least in design time,
should check that the db exists and if not it should let us choose to
create it or not. Calling sqlite3_open on a non-existent db creates an
empty file.