Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?

Op 2/13/10 11:36 AM, Eric Lee schreef:
> 
> 
> On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas <[email protected]
> <mailto:[email protected]>> wrote:
> 
>     Op 2/13/10 10:08 AM, Lester Caine schreef:
>     > Rene Veerman wrote:
>     >> Hi.
>     >>
>     >> I'm looking for the most efficient way to insert several records and
>     >> retrieve the auto_increment values for the inserted rows, while
>     >> avoiding crippling concurrency problems caused by multiple php
>     threads
>     >> doing this on the same table at potentially the same time.
>     >
>     >> Any clues are greatly appreciated..
>     >> I'm looking for the most sql server independent way to do this.
>     >
>     > Rene
>     > The 'correct' way of doing this is to use a 'sequence' which is
>     > something introduced in newer versions of the SQL standard.
>     > Firebird(Interbase) has had 'generators' since the early days (20+
>     > years) and these provide a unique number which can then be
>     inserted into
>     > the table.
>     >
>     > ADOdb emulates sequences in MySQL by creating a separate table for the
>     > insert value, so you can get the next value and work with it, without
>     > any worries. The only 'problem' is in situations were an insert is
>     > rolled back, a number is lost, but that is ACTUALLY the correct
>     result,
>     > since there is no way of knowing that a previous insert WILL
>     commit when
>     > several people are adding records in parallel.
> 
>     this is all true and correct ...
> 
>     but that doesn't answer the problem. how do you get the IDs of all
>     the records
>     that we're actually inserted in a multi-insert statement, even if
>     you generate the
>     IDs beforehand you have to check them to see if any one of the set
>     INSERT VALUEs failed.
> 
>     @Rene:
> 
>     I don't think there is a really simple way of doing this in a RDBMS
>     agnostic
>     way, each RDBMS has it's own implementation - although many are
>     alike ... and MySQL is
>     pretty much the odd one out in that respect.
> 
>     it might require a reevaluation of the problem, to either determine
>     that inserting
>     several records at once is not actually important in terms of
>     performance (this would depend
>     on how critical the speed is to you and exactly how many records
>     you're likely to be inserting
>     in a given run) and whether you can rework the logic to do away with
>     the requirement to
>     get at the id's of the newly inserted records ... possibly by
>     indentifying a unique
>     indentifier in the data that you already have.
> 
>     one way to get round the issue might be to use a generated GUID and
>     have an extra field which
>     you populate with that value for all records inserted with a single
>     query, as such it could
>     function as kind of transaction indentifier which you could use to
>     retrieve the newly
>     inserted id's with one extra query:
> 
>            $sql = "SELECT id FROM foo WHERE insert_id = '{$insertGUID}'";
> 
>     ... just an idea.
> 
>     >
> 
> 
> 
> Hi
> 
> I would like to learn more correct  way from both of you.
> May I ask what is a sequences ?

it an RDBMS feature that offers a race-condition free method of
retrieving a new unique identifier for a record you wish to enter,
the firebird RDBMS that Lester mentions refers to this as 'generators'.

to learn more I would suggest STW:

	http://lmgtfy.com/?q=sql+sequence

> 
> 
> Thanks !
> 
> 
> Regards,
> Eric
> 
>     --
>     PHP General Mailing List (http://www.php.net/)
>     To unsubscribe, visit: http://www.php.net/unsub.php
> 
>