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

[James Loghry]

Not quite sure how it works with stored procedures, but you could try
using mysql_insert_id() after you call the SP.  See:
http://us3.php.net/manual/en/function.mysql-insert-id.php.

carl-olsen at mchsi.com wrote:
> I'm wondering if I can do both queries at once, such as "SELECT @article_id FROM CALL sp_Create()".
>
> Carl
>
> -------------- Original message from "Carl Olsen" <carl-olsen at mchsi.com>: -------------- 
>
>
>   
>> I like your attitude. I can't remember when the last user meeting was 
>> scheduled, but this would be a great topic! 
>>
>> Thank you! 
>>
>> Carl 
>>
>> -----Original Message----- 
>> From: Tony Bibbs [mailto:tony at tonybibbs.com] 
>> Sent: Tuesday, July 11, 2006 7:15 AM 
>> To: carl-olsen at mchsi.com; ciapug at cialug.org 
>> Subject: Re: [ciapug] Re: Re: MySQL 5 Stored Procedures (correction) 
>>
>> 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. 
>>>       
>> _______________________________________________ 
>> ciapug mailing list 
>> ciapug at cialug.org 
>> http://cialug.org/mailman/listinfo/ciapug 
>>     
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> ciapug mailing list
>> ciapug at cialug.org
>> http://cialug.org/mailman/listinfo/ciapug
>>     


-- 
James Loghry
Software Engineer
The Krell Institute
1609 Golden Aspen Dr., Suite 101
Ames, IA 50010
http://www.krellinst.org
Phone: (515) 956 - 3696
Email: loghry at krellinst.org