ZUpdateSQL and SQLite. Params missing

[bedfordch]

Hi there,
sorry if this isn't an error but my fault, as I'm quite new in the Zeos library (I use 6.3.3 stable with Delphi 6 Enterprise French).

I have a ZConnection on a SQLite-3 protocol. I've a Zquery with inner and left joins on 3 tables and I've attached a ZUpdateSQL to it. I've generated the SQL for the field I'm interested to update and I've attached a TDbGrid to the datasource attached to the ZQuery.

Now when I change the field in the column that can be changed by the QUpdateSQL and I leave the row, I get a message error:
SQL Error: SQL logic error or missing database.

I've added a SQLmonitor to see what's appening:
2008-09-30 09:20:50 cat: Connect, proto: sqlite-3, msg: CONNECT TO "C:\programmes\follomi\db.s3db" AS USER ""
2008-09-30 09:20:50 cat: Execute, proto: sqlite-3, msg: select categories.nomcategorie, souscategories.nomsouscategorie, sum(articles.qtestock) as totalstock, souscategories.estachat from categories
inner join souscategories on categories.idcategorie = souscategories.idcategorie
left join articles on souscategories.idsouscategorie = articles.idsouscategorie
group by souscategories.idsouscategorie
order by categories.idcategorie, souscategories.idsouscategorie

2008-09-30 09:20:51 cat: Execute, proto: sqlite-3, msg: PRAGMA table_info('categories')
2008-09-30 09:20:51 cat: Execute, proto: sqlite-3, msg: PRAGMA table_info('souscategories')
2008-09-30 09:20:51 cat: Execute, proto: sqlite-3, msg: PRAGMA table_info('articles')
2008-09-30 09:20:54 cat: Execute, proto: sqlite-3, msg: UPDATE souscategories SET souscategories.estachat = NULL WHERE souscategories.idsouscategorie = NULL, errcode: 1, error: SQL logic error or missing database

The update SQL in ZUpdateSQL is:
UPDATE souscategories SET
souscategories.estachat = :estachat
WHERE
souscategories.idsouscategorie = :OLD_idsouscategorie

As you can see, idsouscategorie and estachat are NULL. Don't know what I do wrong.

If anybody could help me...Thank you.

Regards

[bedfordch]

I fixed it by putting the table that needs to be changed in the first place on the sqlquery (select). It seems to work (was previously on the second place). Also added the "idsouscategorie" for the udpate.

new select:
select nomcategorie, souscategories.idsoucategorie, nomsouscategorie, sum(articles.qtestock) as totalstock, estachat from souscategories
inner join categories on categories.idcategorie = souscategories.idcategorie
left join articles on souscategories.idsouscategorie = articles.idsouscategorie
group by souscategories.idsouscategorie
order by categories.idcategorie, souscategories.idsouscategorie

And it works now.
There is no check for missing fields (I did not add the idsouscategorie in the select) and it was needed in the UpdateSQL.

Hope this help other people.

[mdaems]

Can you confirm only adding idsouscategorie in the select was not enough? That could indicate there's a bug in the statement parser. Could you please post me a minimal test project with some usage instructions (french is not a real problem if you do it by pm). Don't forget to include the database and dll.

Mark

[bedfordch]

adding idsouscategories seems to work (the params are passed) but the generated updatesql isn't correct for sqlite. The project is on your PM.

[mdaems]

The problem was a combination of different issues, partly a user error, partly a problem with the generated sql that was not compatible with SQLite.

The insert and update statements generated by the TZSQLUpdate property editor added the table name in the column list part of the insert sql and the set part of the update sql. SQLite doesn't seem to understand this and it's totally superfluous as the statements are only for one table. Removed in SVN Rev. 481. Will be ported to the 6.6-patches branch as well.

Mark