On Fri, 14 Nov 2014, Marcos Douglas wrote:
On Fri, Nov 14, 2014 at 10:39 AM, Michael Van Canneyt
<[email protected]> wrote:
On Fri, 14 Nov 2014, Marcos Douglas wrote:
On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt
<[email protected]> wrote:
On Fri, 14 Nov 2014, Reimar Grabowski wrote:
On Thu, 13 Nov 2014 13:26:32 -0300
Marcos Douglas <[email protected]> wrote:
Hi,
Take a look in Greyhound project: https://github.com/mdbs99/Greyhound
I did and could not see how it helps.
Here you can see an example using "last id":
https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285
Yes, if you are using Greyhound, which I am not.
So it looks like there's nothing in TSQLQuery that does the job for me.
I now just use another TSQLQuery with the following SQL:
SELECT LAST_INSERT_ID() AS SomeNameIChoose;
Seems to work well, reduces the lines of code as I don't have to set
parameters (my primary goal), can be reused for other inserts (being not
table dependent) and took about 1 Minute to implement.
Connections for databases that have lastinsertID you can call the method
created for this:
Function GetInsertID: int64;
There is a plan to let SQLDB retrieve the value of some fields fro the
DB,
but it is not implemented yet.
Hi Michael,
To do that is need to implement a GetInsertID method in all drivers of
SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate:
No. Not all SQL engines support lastID. Postgres and Firebird don't.
I know. For these the method returns 0 or the user need to code some
more to work.
If a DMBS does not support transaction, for example, I can't implement
transactions on SQLdb? Is better to implement transactions for all
DMBS that works and do nothing in few cases that not supported. The
same for auto-inc, IMHO.
No worries. We will implement it, but simply not in the way you suggest.
A more general mechanism is needed.
The code I posted is general, only in TSQLQuery.ApplyRecUpdate method.
In my example before, FLib instance (Greyhound's code) would be a
driver for SQLdb that will call GetLastAutoIncValue. A code for each
DBMS driver.
By the very nature of the problem: GetLastAutoIncValue simply cannot be
implemented in general.
In firebird, postgres, these values are generated using a generator/sequence.
You don't know the name of the generator, and even if you did, it is impossible
to retrieve the value that was used to fill your particular record because the
sequence/generator may have been updated several thousands of times by the time
your second statement arrives.
The only way to do this correctly is using a RETURNING clause when doing the
insert.
The idea is to add [pfRefresh] to providerflags.
When doing an insert, the fields with this flag will be added to the RETURNING
clause and used to update the buffer.
Fields of type ftautoInc can be added to this list (i.e. they automatically
get pfRefresh in their providerflags)
This approach will work on all SQL databases that support RETURNING.
(That should include any SQL database available today)
Michael.
--
_______________________________________________
Lazarus mailing list
[email protected]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
