[ciapug] Re: Re: MySQL 5 Stored Procedures (correction)
Mike Parks
mparks at captainjack.com
Mon Jul 10 13:21:07 CDT 2006
No sparks, or smoke. Just don't need stored procedures, for simple websites.
Sorry, to everyone else.
Didn't think there was still any lingering ill feelings.
-mp
At 12:00 PM 7/10/2006, you wrote:
>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. Re: MySQL 5 Stored Procedures (Mike Parks)
> 2. RE: Re: MySQL 5 Stored Procedures (Carl Olsen)
> 3. RE: Re: MySQL 5 Stored Procedures (Carl Olsen)
> 4. RE: Re: MySQL 5 Stored Procedures (Carl Olsen)
> 5. RE: Re: MySQL 5 Stored Procedures (correction) (Carl Olsen)
> 6. Re: Re: MySQL 5 Stored Procedures (correction) (David Champion)
>
>
>----------------------------------------------------------------------
>
>Message: 1
>Date: Sun, 9 Jul 2006 17:39:37 -0500
>From: "Mike Parks" <parksmike at dwx.com>
>Subject: [ciapug] Re: MySQL 5 Stored Procedures
>To: <ciapug at cialug.org>
>Message-ID: <000601c6a3a8$8f881050$0400a8c0 at parksmike>
>Content-Type: text/plain; format=flowed; charset="iso-8859-1";
> reply-type=original
>
>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
> > *************************************
>
>
>
>------------------------------
>
>Message: 2
>Date: Sun, 9 Jul 2006 17:52:53 -0500
>From: "Carl Olsen" <carl-olsen at mchsi.com>
>Subject: RE: [ciapug] Re: MySQL 5 Stored Procedures
>To: <ciapug at cialug.org>
>Message-ID: <00a901c6a3aa$6a188f00$1c00a8c0 at workstation8>
>Content-Type: text/plain; charset="us-ascii"
>
>I didn't say I needed it. I'm trying to learn how it works. Thanks for the
>help.
>
>-----Original Message-----
>From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
>Of Mike Parks
>Sent: Sunday, July 09, 2006 5:40 PM
>To: ciapug at cialug.org
>Subject: [ciapug] Re: MySQL 5 Stored Procedures
>
>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
> > *************************************
>
>_______________________________________________
>ciapug mailing list
>ciapug at cialug.org
>http://cialug.org/mailman/listinfo/ciapug
>
>
>
>------------------------------
>
>Message: 3
>Date: Sun, 9 Jul 2006 17:58:54 -0500
>From: "Carl Olsen" <carl-olsen at mchsi.com>
>Subject: RE: [ciapug] Re: MySQL 5 Stored Procedures
>To: <ciapug at cialug.org>
>Message-ID: <00aa01c6a3ab$41473a80$1c00a8c0 at workstation8>
>Content-Type: text/plain; charset="us-ascii"
>
>Is this a flame?
>
>-----Original Message-----
>From: ciapug-bounces at cialug.org [mailto:ciapug-bounces at cialug.org] On Behalf
>Of Mike Parks
>Sent: Sunday, July 09, 2006 5:40 PM
>To: ciapug at cialug.org
>Subject: [ciapug] Re: MySQL 5 Stored Procedures
>
>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
> > *************************************
>
>_______________________________________________
>ciapug mailing list
>ciapug at cialug.org
>http://cialug.org/mailman/listinfo/ciapug
>
>
>
>------------------------------
>
>Message: 4
>Date: Sun, 9 Jul 2006 19:52:53 -0500
>From: "Carl Olsen" <carl-olsen at mchsi.com>
>Subject: RE: [ciapug] Re: MySQL 5 Stored Procedures
>To: <ciapug at cialug.org>
>Message-ID: <00bd01c6a3bb$2d52c9d0$1c00a8c0 at workstation8>
>Content-Type: text/plain; charset="us-ascii"
>
>For those of you who prefer PHP 4 and MySQL 3, my question was not directed
>to you folks. I'm simply looking for ways to write less code, so telling me
>I've got a monster is sort of restating the obvious. That was the point of
>my question. Thanks.
>
>The example of returning a parameter from a stored procedure with PHP that I
>found shows two queries. The first query calls the stored procedure and the
>second one does a SELECT for the parameter.
>
>Some of my code wasn't necessary for the question, so here's the actual part
>that uses the stored procedure:
>
>public function insert($name)
>{
> $rowsAffected = -1;
> $insertId = -1;
> $sql = "CALL sp_News_CreateNewsArticle(?, at article_id)";
> if ($stmt = $this->conn->prepare($sql))
> {
> $stmt->bind_param("s", $name);
> $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;
>}
>
>Here is the stored procedure:
>
>drop procedure if exists `sp_Create`;
>
>DELIMITER $$;
>
>CREATE PROCEDURE `sp_Create`(IN name VARCHAR(150), OUT article_id INT)
>BEGIN
> INSERT INTO article (sname)
> VALUES (name);
> SET article_id = LAST_INSERT_ID();
>END$$
>
>DELIMITER ;$$
>
>
>
>------------------------------
>
>Message: 5
>Date: Sun, 9 Jul 2006 19:55:27 -0500
>From: "Carl Olsen" <carl-olsen at mchsi.com>
>Subject: RE: [ciapug] Re: MySQL 5 Stored Procedures (correction)
>To: <ciapug at cialug.org>
>Message-ID: <00be01c6a3bb$890c89f0$1c00a8c0 at workstation8>
>Content-Type: text/plain; charset="us-ascii"
>
>For those of you who prefer PHP 4 and MySQL 3, my question was not directed
>to you folks. I'm simply looking for ways to write less code, so telling me
>I've got a monster is sort of restating the obvious. That was the point of
>my question. Thanks.
>
>The example of returning a parameter from a stored procedure with PHP that I
>found shows two queries. The first query calls the stored procedure and the
>second one does a SELECT for the parameter.
>
>Some of my code wasn't necessary for the question, so here's the actual part
>that uses the stored procedure:
>
>public function insert($name)
>{
> $rowsAffected = -1;
> $insertId = -1;
> $sql = "CALL sp_Create (?, at article_id)";
> if ($stmt = $this->conn->prepare($sql))
> {
> $stmt->bind_param("s", $name);
> $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;
>}
>
>Here is the stored procedure:
>
>drop procedure if exists `sp_Create`;
>
>DELIMITER $$;
>
>CREATE PROCEDURE `sp_Create`(IN name VARCHAR(150), OUT article_id INT)
>BEGIN
> INSERT INTO article (sname)
> VALUES (name);
> SET article_id = LAST_INSERT_ID();
>END$$
>
>DELIMITER ;$$
>
>
>
>------------------------------
>
>Message: 6
>Date: Mon, 10 Jul 2006 11:42:20 -0500
>From: David Champion <dchampion at visionary.com>
>Subject: Re: [ciapug] Re: MySQL 5 Stored Procedures (correction)
>To: ciapug at cialug.org
>Message-ID: <44B2836C.4030809 at visionary.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>Play nice.
>
>-dc
>
>
>
>
>------------------------------
>
>_______________________________________________
>ciapug mailing list
>ciapug at cialug.org
>http://cialug.org/mailman/listinfo/ciapug
>
>
>End of ciapug Digest, Vol 15, Issue 3
>*************************************
<><><><><><><><><><><><><><><>
Mike Parks
Captain Jack Communications
Email: mparks at captainjack.com
Technical Email: techsupport at captainjack.com
Phone: 515-964-8500
Toll-Free: 800-581-3230
More information about the ciapug
mailing list