On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt <[email protected]> wrote: > > > 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.
...as well as could have a trigger for these cases, encapsulating the "problem" in database... but the code in these cases should be different. > 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. If you use a sequence/generator, you should call it before INSERT. If you use auto-inc, the DMBS have support to return the last id on your session. > The only way to do this correctly is using a RETURNING clause when doing the > insert. This RETURNING clause works for all DBMS? > 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) Nice. But this is only a part of code. How do you will get the last ID and put in record? Marcos Douglas -- _______________________________________________ Lazarus mailing list [email protected] http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
