[Lazarus] TSQLQuery and creating a new record

[Joost van der Sluis]

On Sun, 2010-05-16 at 15:46 -0500, Albert Zuurbier wrote:
> Over the past few days I have tried creating and editing new records
> with the TSQLQuery component. I succeeded, but with quite a
> workaround.
>  
> As stated in bug report 0016076 (not authored by me) TSQLQuery gives
> errors if you try to create a new record and think TSQLQuery would all
> handle it. The issue addressed in the bug report is that all databases
> address making new records in different ways. The suggestion is to
> provide additional SQL lines to gain control over that process. Here I
> would like to discuss (and hopefully learn) about other things I
> found.

It always amazes me how people can have huge problems with the most
simple things. It probably means that there has to be something wrong,
somewhere. (Documentation?) Are you familiar with datasets in Delphi?

The cited bug report is only slightly related to your problem, I think.
The bug-report is about autoincrement fields. Those weren't detected
properly. The problem that's not solved yet is that the generated number
is not set in the current dataset, only when the dataset is fetched
again.

> 1) TSQLQuery doesn't really create a new record at insert or append.
> Instead, the only thing that is done is adding a row in the dataset
> [InsertInternal doesn't do anything]. The insert is not done and at
> ApplyUpdates the insert is lost because the dataset doesn't know the
> difference between updates and inserts. The insert is lost, because
> the new row is used to try to update a record that doesn't exist
> result in a fail. However, the InsertSQL line is executed which leads
> to a new record and the next observation.

If this doesn't work, you have to provide an example in which case it
doesn't. Normally it does.
 
> 2) The InsertSQL is executed, but since these are fixed strings I
> cannot put my new values into the strings. Trying to change the
> InsertSQL leads to an error because you cannot change the InsertSQL on
> an active dataset (rightfully so). So, the InsertSQL can create a new
> record but not with the right values. What I would like to see here is
> a insert statement with parameters and a possibility to provide
> parameters. Is that already there?

This contradicts the first point? In your first point InsertSQL wasn't
executed?

And you are right offcourse that a InsertSQL property would be useless
without parameters. 

If you want to provide your own InsertSQL (instead the one generated by
sqldb) try: 'insert into table(field1,field2) values (:field1,:field2)' 

Updatesql:
'update table set field=:field, field2=:field2 where
(field1=:old_field1) and (field2=:old_field2)'

A delete statement should be easy now.

> 3) At ApplyUpdates the dataset is forwarded to the database as a list
> of updates. Which doesn't necessarily have to be true. What I would
> suggest here is an implementation of the Unit of Work pattern
> (http://martinfowler.com/eaaCatalog/unitOfWork.html). Using the Unit
> of Work pattern you can make a difference between update, insert and
> delete actions. The pattern also allows to make the processing of the
> dataset one compact unit with some favorable effects on atomicity and
> concurrency.

That's in principle how it is working. Only heavily optimized for the
job at hand (cached-updates in a dataset) and with a lot of extra
functionality and flexibility.

> 4) Creating an ID for a new record can be done at two moments a) at
> the moment you want the new record b) at the moment you really save
> the new record. With (a) you can use the ID in references immediately
> without too much complexity, with (b) you don't waist IDs at the
> moment you are not sure you will ever save the record. So, it would be
> nice to have a property to choose when the InsertSQL is executed.

What's more flexible then letting you call ApplyUpdates whenever you
want?

It also depends on the used database-engine if you can use a or b. And a
is always the best option. But there are a few database-engines from
which the developers weren't that good at database design when they made
their first implementation. (Access, MS SQL, MySQL) So they added the b
design. But nowadays they all can use a also. (Except for Access, but,
hey, that's not a serious database, and they don't pretend to be one
too.)

Nice thing with the TSQLQuery design is that you can use both. But
option b needs some extra code in some cases. (Unless we can think about
some way to do this automatically, see the bug report you started with)

> Of course, I am not experienced enough (with FreePascal) to implement
> this or even try to. But I hope my suggestions will help the
> development of TSQLQuery. I noticed there is quite a bit of discussion
> about the database components lately, but I don't know the development
> plans. Also, if I have overlooked anything I am happy to learn.

There is always discussion about the database components. That will
never stop. ;) But you didn't add any new information to the discussion.
(Most people don't) Unless maybe that the first step didn't work for
you, but I think that you did something wrong there.

Joost.