List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:June 15 2005 2:10pm
Subject:Re: Prepared Statements in Stored Procedures.
View as plain text  
Hi Rob,


> I've been playing around with stored procedures and I seem to be able to
> consistantly crash msqld but I'm unsure wether it's because I'm doing
> something I shouldn't (SP newbie :]) or wether the issue's a bug in mysql.
>
> I know I'm 'pushing my luck' with the code below but I was trying it out
of
> interest more than anything.
>
> For reference the new table is being created by the SP but mysqld then
> crashes.  I'm running 5.0.6 installed from the Redhat 9 RPMS under Fedora
> Core 3.

Well, obviously, it should never crash ... but ...

What's the point in preparing these statements?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `userauth`.`DeleteUser`$$
>
> CREATE PROCEDURE `userauth`.`DeleteUser` (IN i_uid int, IN sz_username
> char(100), IN sz_deletedby char(100))
> BEGIN DECLARE tablename char(120);
> SET tablename=CONCAT('DEL_',REPLACE(sz_deletedby,'-','_'));
> SET @SQLcmd=CONCAT('CREATE TABLE ', tablename, ' SELECT * FROM usertest
WHERE
> uid = "',i_uid,'" AND username = "',sz_username,'"');
> prepare stmt from @SQLcmd;
> execute stmt;
> deallocate prepare stmt;
> SET @SQLcmd=CONCAT('ALTER TABLE ', tablename, ' ADD COLUMN datedeleted
> TIMESTAMP FIRST');
> prepare stmt from @SQLcmd;
> execute stmt;
> deallocate prepare stmt;
> SET @SQLcmd=CONCAT('DROP TABLE ',tablename);
> prepare stmt from @SQLcmd;
> execute stmt;
> deallocate prepare stmt;
> END$$
>
> DELIMITER ;
>
> CREATE TABLE `usertest` (
>   `uid` int(11) NOT NULL auto_increment,
>   `username` varchar(100) NOT NULL,
>   PRIMARY KEY  (`uid`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> INSERT INTO usertest VALUES('502','dvs');
>
> CALL DeleteUser('502','dvs','rhall-superuser');

Thread
Prepared Statements in Stored Procedures.Rob Hall15 Jun
  • Re: Prepared Statements in Stored Procedures.Martijn Tonies15 Jun
    • Re: Prepared Statements in Stored Procedures.Rob Hall15 Jun
  • Re: Prepared Statements in Stored Procedures.Gleb Paharenko15 Jun
  • Re: Prepared Statements in Stored Procedures.Paul DuBois15 Jun