SQLite UPDATE command

phaworth · Post by **phaworth** » Tue Jul 14, 2009 9:06 pm

Anyone getting the SQLite UPDATE command to work successfully? It doesn't seem to have any effect on the database until I close the card it's issued from and re-open it.
Pete

Janschenkel · Post by **Janschenkel** » Tue Jul 14, 2009 10:01 pm

Are you using UPDATE in conjunction with the automated queries and database linked controls? Then you should probably rely on its built-in save mechanism to ensure that data is refreshed.
If you want to save all changes right away, then tick the checkbox 'Update after editing' in the Database panel of the Inspector palette, for each control. On the other hand, if you want the changes only to be persisted when the user clicks on a Save button, then drop a button onto your card, and use the Inspector palette > Database panel, to change the Action to 'Update Record'.

HTH,

Jan Schenkel.

phaworth · Post by **phaworth** » Tue Jul 14, 2009 10:14 pm

Thanks for the info. Apparently the Database Query function doesn't work correctly with SQLite (so customer support tells me) so I'm having to handle the updates myself.

I suspect that the recordset is held in memory somewhere and is not being refreshed when the database is updated physically. I'm in the process of implementing code to close the current record set and re-issue the query on the database to see if that makes it work.

Thanks,
Pete

Janschenkel · Post by **Janschenkel** » Wed Jul 15, 2009 5:55 am

Very few databases have 'live' result sets, and if they do, it probably has a proprietary syntax - and it would be optional. After all, if all result sets were live, it would conflict with the pcinciples of isolation levels.
So you were on the right track: if you're executing UPDATE on a database, you'll have to run your SELECT query anew.

Jan Schenkel.

phaworth · Post by **phaworth** » Wed Jul 15, 2009 5:58 am

Yep, I did that and all works fine now.