Zeoslib Portal

I'm having trouble with TZUpdateSQL on Delphi 7 + Zeos 6.6.2 RC2 + SQLite 3.5.9.

I have an autoincrement primary key field on the table I want to update. But I don't know what kind of RefreshSQL I should give to the ZUpdateSQL so the primary key field automatically retrieved after a post (and the record pointer stays on the record I have just posted).

Here is the reproduction of my situation:

the table
---------
CREATE TABLE [mytable] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[name] VARCHAR(20));

the ZQuery
----------
object ZQuery1: TZQuery
Connection = ZConnection1
UpdateObject = ZUpdateSQL1
SQL.Strings = (
'select * from mytable'
'order by name')
Params = <>
SequenceField = 'id'
Left = 64
Top = 16
end

the ZUpdateSQL
--------------
object ZUpdateSQL1: TZUpdateSQL
DeleteSQL.Strings = (
'DELETE FROM mytable'
'WHERE'
' ((id IS NULL AND :OLD_id IS NULL) OR (id = :OLD_id))')
InsertSQL.Strings = (
'INSERT INTO mytable'
' (name)'
'VALUES'
' (:name)')
ModifySQL.Strings = (
'UPDATE mytable SET'
' mytable.name = :name'
'WHERE'
' ((id IS NULL AND :OLD_id IS NULL) OR (id = :OLD_id))')
UseSequenceFieldForRefreshSQL = True
Left = 160
Top = 16
ParamData = <
item
DataType = ftUnknown
Name = 'name'
ParamType = ptUnknown
end
item
DataType = ftUnknown
Name = 'OLD_id'
ParamType = ptUnknown
end>
end


I have tried to fill the RefreshSQL with these lines but all failed:
- select * from mytable where id = :id
- select * from mytable where id = :OLD_id
- select * from mytable where id = :NEW_id

I hope someone could help me. Thank you.

Well i had a similar problem and if you read the Sqlite3 documentation its states you must write a NULL to the INTEGER PRIMARY KEY for it to update. Also i think it might be in the way you are creating that field it just need to read INTEGER PRIMARY KEY the NOT NULL and AUTOINCREMENT may be throwing it off.
Hope this solves your problem it did mine. I still trying to learn this and there are a lot of things that seem like you have to fight more today than in the past.

For refresh on insert try this in your refreshSQL:

select * from my_table where id = :id or id is null order by id desc

(id is your INTEGER PRIMARY KEY AUTOINCREMENT field)

Hi,

This is still on the TODO list. I have the impression this could be realised the way it's done for mysql. There the last_insert_id is fetched after every inserted record with an API call. I saw this API call is also available in the sqlite APi.

Somebody willing to give it a try? I know I could do it myself, but as I'm not using sqlite a lot this wouldn't be a first priority job for me.

Using refresh_sql you'll have to be smarter, I'm afraid. No way you can get the id of an inserted record unless you can write a unique where-clause without using the id field (eg by including all other fields which combination must be unique in your dataset).

Mark

Hi,

You can use in refreshSQL:

SELECT * FROM mytable
WHERE
( (:OLD_ID is not null) AND (ID = :OLD_ID))
OR
( (:OLD_ID is null) AND (RowID = (select max(rowid) from mytable)) )


Alex

Hi!!!
Try this RefreshSQL:
select * from mytable where id = (select @@identity)

Edited:
Most correctly:

Hi,

Is a TZUpdateSQL component really necessary in your case? When updating a TZQuery without it, the new autoincrement field should be updated after the post immediately.
Since rev 762 (on january 8.) it should work even better. The native last_increment API function is now used instead of a select statement.

Mark