Re: Simple search question

Alex <gliathit(at)ihug(dot)com(dot)au> writes:

<pasted from earlier post>
> Hi,
> after running a script which performs an insert, a new tuple is
> created, and a serial number is generated for it. I want to write the
> new tuple data back to the screen, including the new serial number.
> My question is, do I have to do a search for the tuple just inserted in
> order to get the data back again? I am thinking there must be a faster,
> more efficient way.
> Thanks,
> Alex

I had the same problem but was using Java, not PHP (I guess that
whatever I can do in JDBC, you can do in PHP ;-)).

SELECT last_value FROM <sequence>;

worked fine to retrieve the last-used serial number. I could have
used

SELECT i.* FROM <insert_table> i,<sequence> s WHERE i.serial=s.last_value;

to retrieve the tuple, but I didn't need to do this.

> This leads to another question. If someone adds another row during this,
> what will happen?

In JDBC I would turn off auto-commit mode, then commit after the
SELECT. This should ensure that the sequence doesn't get incremented
between INSERT and SELECT.

>
> Thanks,
> Alex

Hope this helps,
Peter