[fpc-pascal] Documentation for sqldb

[Joost van der Sluis]

On Wed, 2007-06-20 at 22:32 +1000, John wrote:
> Is there any documentation for the SQLDB components ?  I have put a fair
> bit of effort in to looking round the FPC and Lazarus documentation and
> wiki areas, and while there are a few helpful hints here and there,  I
> have not been able to find any sort of  overview of how the components
> are supposed to work, particularly once you try to update data.  While I
> can make some guesses from looking through the source, it is really hard
> to guess from that how the writer intended them to work, and the best
> way to use them.

That's indeed very difficult to understand without documentation. And
indeed, there isn't any.

IN principle you can set ReadOnly to false and ParseSQL to true. That
way sqldb tries to parse your query. If it's a simple 'select * from
table' the TSQLQuery will be updateable. It automatically generates
update/delete and insert queries. For the 'where' clause is uses  by
default the primary key of the table. (That's a setting, upWhereKeyOnly)

For example: 'delete * from table where pk=:old_pk'

If you edit some data, those changes will be stored in an updatebuffer.
With TSQLQuery.CancelUpdates all those changes are lost. But if you
call .ApplyUpdates, it will execute one query for every changed record.
(That could be a insert, update or delete query) 

If you have a more complex query (sqldb can handle more complex queries
then the one above, but not everything, offcourse) you can provide your
own update/insert and delete queries. 

You can use parameters in those queries. The new value for a field is
stored in a parameter with the same name as the field. If you need the
old value from a field, you have to use the prefix 'old_' For example:

'update table set field1=:field1, field2=:field2, field3=:field3 where
(field1=:old_field1) and (field2=:old_field2) and (field3=:old_field3)'

Those are the basics. If you have questions, ask them here. And if you
have any time, please document it somewhere on the wiki. ;)

Joost