[ciapug] MySQL 5 Stored Procedures

[Carl Olsen]

I'm wondering if anyone has started using stored procedures in MySQL 5.

 

When MySQL version 4.1.3 was released, I started using mysqli prepared
statements and started using them all the time.

 

When I started using stored procedures in MySQL 5, I couldn't find a lot of
documentation.

 

With prepared statements I can get the id of the last inserted record using
$stmt->insert_id;

 

However, with stored procedures this doesn't seem to work and I end up with
something like this in my class file (It does two queries to get the output
parameter from the stored procedure - it works just fine but seems to be a
lot of code compared to using a prepared statement without the stored
procedure):

 

            public function
insert($category_id,$newsletter_id,$name,$abstract,$display_date,$active_dat
e,$expire_date,$username,$approved)

            {

                        $rowsAffected = -1;

                        $insertId = -1;

                        $name = $this->clean($name);

                        $abstract = $this->clean($abstract);

                        $display_date = $this->clean($display_date);

                        $active_date = $this->clean($active_date);

                        $expire_date = $this->clean($expire_date);

                        $username = $this->clean($username);

                        $approved = (int)($approved);

                        $sql = "CALL
sp_News_CreateNewsArticle(?,?,?,?,?,?,?,?,?, at article_id)";

                        if ($stmt = $this->conn->prepare($sql))

                        {

                                    $stmt->bind_param("iissssssi",
$category_id, $newsletter_id, $name, $abstract, $display_date, $active_date,
$expire_date, $username, $approved);

                                    $stmt->execute();

                                    $rowsAffected = $stmt->affected_rows;

                                    $stmt->fetch();

                                    $stmt2 = $this->conn->prepare("SELECT
@article_id");

                                    $stmt2->execute();

                                    $stmt2->bind_result($article_id);

                                    $stmt2->fetch();

                                    $insertId = $article_id;

                                    $stmt2->free_result();

                                    $stmt2->close();

                                    $stmt->free_result();

                                    $stmt->close();

                        }

                        if ($rowsAffected < 1)

                        {

                                    throw new Exception("News Article insert
error.");

                        }

                        return $insertId;

            }

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/ciapug/attachments/20060708/e2d5cb88/attachment.htm