[ciapug] Re: Re: MySQL 5 Stored Procedures (correction)

[Tony Bibbs]

More than likely, yes, you will need to do two queries.  One does the 
insert, one to get the last_insert_id.  FWIW that ORM I was speaking of, 
Propel, would have given you the id:

// Instantiate some generated Propel Object
$myObj = new SomePropelObject();

// Set some data on object
$myObj->setAttribute1($foo);
$myObj->setAttribute2($foo);

// Now save.  Propel can manage if it needs to do an INSERT
// or an UPDATE for you.
$myObj->save();

// After the save our autogenerated ID will have a value
echo $myObj->getSomePrimaryKey();

Writing SP's (and SQL in general) is over-rated.  I'd rather on meeting 
business requirements and leave the lower level database stuff to the ORM.

Speaking of which, if it hasn't been done already, I'd be willing to 
cover Propel at one of the meetings since I'm yapping about it so much.

--Tony

Carl Olsen wrote:
> I put EVERYTHING possible in my databases, but I never use a third party
> tool to do it.  I use the SQL Server Enterprise Manager for SQL Server,
> SQLyog for MySQL, and linux command line for PostgreSQL.  I guess I just
> find this stuff interesting and it works well for what I'm doing.  In regard
> to my original question, I have code for PostgreSQL stored procedures that
> just returns the value of the inserted ID without having to do two queries.
> That's why I was wondering if I was doing something wrong with MySQL.  I
> looks like I have to do two queries to do the same thing I can do with
> PostgreSQL or SQL Server with one query.