[ciapug] Re: MySQL 5 Stored Procedures

[Mike Parks]

Never had a website that needed anything like that monster...

99.999% of what we do can be done with semi-simple queries.



----- Original Message ----- 
From: <ciapug-request at cialug.org>
To: <ciapug at cialug.org>
Sent: Sunday, July 09, 2006 12:00 PM
Subject: ciapug Digest, Vol 15, Issue 2


> Send ciapug mailing list submissions to
> ciapug at cialug.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://cialug.org/mailman/listinfo/ciapug
> or, via email, send a message with subject or body 'help' to
> ciapug-request at cialug.org
>
> You can reach the person managing the list at
> ciapug-owner at cialug.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of ciapug digest..."
>
>
> Today's Topics:
>
>   1. MySQL 5 Stored Procedures (Carl Olsen)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Sat, 8 Jul 2006 17:31:18 -0500
> From: "Carl Olsen" <carl-olsen at mchsi.com>
> Subject: [ciapug] MySQL 5 Stored Procedures
> To: <ciapug at cialug.org>
> Message-ID: <001301c6a2de$3c2cdb90$1c00a8c0 at workstation8>
> Content-Type: text/plain; charset="us-ascii"
>
> 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.html
>
> ------------------------------
>
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
>
>
> End of ciapug Digest, Vol 15, Issue 2
> *************************************