tag:blogger.com,1999:blog-15319370.post113118190317843450..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: MySQL 5: Prepared statement syntax and Dynamic SQLrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger113125tag:blogger.com,1999:blog-15319370.post-66300888780194920332016-06-11T10:22:47.850+02:002016-06-11T10:22:47.850+02:00VARUGHESE,
Review your insert syntax, it appears ...VARUGHESE,<br /><br />Review your insert syntax, it appears to be incorrect.<br /><br />1) There is no closing parenthesis: )<br />2) Those strings are not quoted, thus they will be interpreted as field names. I don't see what table they would be getting pulled from as there are no other tables mentioned besides the one you are inserting into.<br /><br />Quick Reference: http://www.w3schools.com/sql/sql_insert.asp<br /><br />Joe JJJhttps://www.blogger.com/profile/02502362628036074705noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58179242705453595232016-06-10T04:34:30.143+02:002016-06-10T04:34:30.143+02:00Hi Roland,
The error I am getting is produced belo...Hi Roland,<br />The error I am getting is produced below: My problem is the fetched or userdefined column names are not recognised while preparing statement. Kindly look into it.<br />Thanks and regards .. varughese<br /><br />mysql> call PROGRESSCARDDATA('UKG');<br />+--------+------+<br />| vmarks | vx |<br />+--------+------+<br />| NULL | 2 |<br />+--------+------+<br />1 row in set (0.30 sec)<br /><br />+-----------+<br />| @VMARKS |<br />+-----------+<br />| Vsubmark1 |<br />+-----------+<br />1 row in set (0.31 sec)<br /><br />+------------------------------------------------------------------------------------------------------------------<br />| @vquery<br />+------------------------------------------------------------------------------------------------------------------<br />| insert into Sch_tmpstUdResults_trm1 VALUES(vadmnno,vclass,vsection,vstuname,vgender,vsusubtype,vsusubjects,Vsubma<br />+------------------------------------------------------------------------------------------------------------------<br />1 row in set (0.34 sec)<br /><br />ERROR 1054 (42S22): Unknown column 'vadmnno' in 'field list'<br />mysql><br />----------------------------------------------------------------------------<br />Below is the entire procedure:<br /><br />#---------PROGRESS CARD PROCESSING PROCEDURE ------<br /><br /><br />DELIMITER $$<br /><br />DROP PROCEDURE IF EXISTS PROGRESSCARDDATA$$<br /><br />CREATE PROCEDURE PROGRESSCARDDATA(IN VACTclass varchar(255))<br /><br />BEGIN<br /><br />DECLARE no_more_accounts,Vsubmark1,vsubmark2,Vsubmark3,Vsubmark4,vsubmark5,Vsubmark6,Vsubmark7,vsubmark8,Vsubmark9,Vsubmark10,vsubmark11 int DEFAULT 0;<br />DECLARE vsuclass,vsusubjects,vsusubtype,vsubject1,vsubject2,vsubject3,vsubject4,vsubject5,vsubject6,vsubject7 VARCHAR(255);<br />DECLARE vsubject8,vsubject9,vsubject10,vsubject11,vsubject12 VARCHAR(255);<br />DECLARE Vptraits1,Vptraits2,Vptraits3,Vptraits4,Vptraits5,Vptraits6,Vptraits7,Vptraits8,Vptraits9,Vptraits10,Vptraits11 VARCHAR(255);<br />DECLARE vptmarks1,vptmarks2,vptmarks3,vptmarks4,vptmarks5,vptmarks6,vptmarks7,vptmarks8,vptmarks9,vptmarks10,vptmarks11 VARCHAR(255);<br />DECLARE vprofile1,vprofile2,vprofile3,vprofile4,vprofile5 VARCHAR(255);<br />DECLARE VPPmarks1,VPPmarks2,VPPmarks3,VPPmarks4,VPPmarks5,VPPmarks6,vsubmark12 VARCHAR(255);<br />DECLARE vtpercentage double DEFAULT 0;<br />DECLARE vtotal,vattendnce,vx int DEFAULT 0;<br />DECLARE vquery,vmarks,vadmnno,vclass,vsection,vstuname,vgender varchar(255);<br />DECLARE pbadrbalance,pbacrbalance double default 0;<br />DECLARE Pbtransdate date;<br /><br />DECLARE cur_subjects CURSOR FOR<br />select su_class,su_subjects,su_subtype from sch_subjects where su_class = VACTclass ; <br /><br />DECLARE Cur_marks CURSOR FOR<br />select sr_admno,sr_class,sr_section,sr_studname,sr_studgender,sr_markSubject1,sr_markSubject2,sr_markSubject3,sr_markSubject4,sr_markSubject5,sr_markSubject6,sr_markSubject7,sr_markSubject8,sr_markSubject9,sr_markSubject10,sr_markSubject11,sr_markSubject12 from sch_tmpstdresult_trm1;<br /><br />DECLARE CONTINUE HANDLER FOR NOT FOUND<br />SET no_more_accounts = 1;<br />set vx = 1;<br /><br />open cur_subjects;<br /><br />LOOP1: loop<br />FETCH cur_subjects INTO vsuclass,vsusubjects,vsusubtype;<br /><br />#set vsusubjects = concat(vsusubjects);<br />set @vmarks := concat('Vsubmark',vx);<br />set vx=vx+1;<br />select vmarks,vx;<br /><br />if no_more_accounts then<br /> close cur_subjects;<br /> LEAVE LOOP1;<br />end if;<br /><br />Open Cur_marks;<br />LOOP2: loop<br />select @VMARKS;<br /><br />FETCH Cur_marks INTO<br />vadmnno,vclass,vsection,vstuname,vgender,Vsubmark1,vsubmark2,Vsubmark3,Vsubmark4,vsubmark5,Vsubmark6,Vsubmark7,vsubmark8,Vsubmark9,Vsubmark10,vsubmark11,vsubmark12;<br /><br />set @vquery = CONCAT('insert into Sch_tmpstUdResults_trm1 VALUES(','vadmnno,vclass,vsection,vstuname,vgender,vsusubtype,vsusubjects,', @VMARKS,')');<br /><br /><br />select @vquery;<br /><br /><br />PREPARE stmt3 FROM @vquery;<br /><br />EXECUTE stmt3;<br />DEALLOCATE PREPARE stmt3;<br /><br /><br /><br />if no_more_accounts then<br />set no_more_accounts=0;<br />close Cur_marks;<br />LEAVE LOOP2;<br />end if;<br />end loop LOOP2;<br />END LOOP LOOP1;<br />END $$<br /><br />DELIMITER ;VARUGHESEhttps://www.blogger.com/profile/09468898346203658689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-10273021983870466842016-06-09T07:53:21.693+02:002016-06-09T07:53:21.693+02:00@VARUGHESE,
I did reply to your comment. I don&#...@VARUGHESE, <br /><br />I did reply to your comment. I don't see the error message - it is not in your comment AFAICS. You did paste a lot of code, but that is not the entire procedure.<br /><br />Anyway, this piece looks wrong:<br /><br />set @vquery = CONCAT('insert into Sch_tmpstUdResults_trm1 VALUES(',vadmnno,vclass,vsection,vstuname,vgender,vsusubtype,vsusubjects, <br /><br />@VMARKS);<br /><br />Print out the value of @vquery and you'll notice that the string you are generating is not valid SQL.<br /><br />rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-34749455421779185912016-06-09T02:29:30.158+02:002016-06-09T02:29:30.158+02:00Hi Roland, Hope you are looking into my problem. Y...Hi Roland, Hope you are looking into my problem. Yesterday I gave you the whole procedure and the error I am getting. <br />Await your help. Thanks and Regards<br />Varughese.VARUGHESEhttps://www.blogger.com/profile/09468898346203658689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-39450584890578694772016-06-07T07:45:46.718+02:002016-06-07T07:45:46.718+02:00@VARUGHES, what exactly is the problem? Any error ...@VARUGHES, what exactly is the problem? Any error messages?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-49767218057957312322016-06-07T04:04:47.257+02:002016-06-07T04:04:47.257+02:00Good Morning Roland, Very nice article and many go...Good Morning Roland, Very nice article and many good feed backs. Thanks for serving the society.<br />I am struck with a procedure converting columns data to row data. So I build a procedure to read each loop the new column name. Prepare statement is not identifying the variables. The code is given below, I appreciate your help.<br />.....<br />.....<br />DECLARE CONTINUE HANDLER FOR NOT FOUND<br />SET no_more_accounts = 1;<br />set vx = 1;<br /><br />open cur_subjects;<br /><br />LOOP1: loop<br />FETCH cur_subjects INTO vsuclass,vsusubjects,vsusubtype;<br /><br />#set vsusubjects = concat(vsusubjects);<br />set @vmarks := concat('Vsubmark',vx);<br />set vx=vx+1;<br />select vmarks,vx;<br /><br />if no_more_accounts then<br /> close cur_subjects;<br /> LEAVE LOOP1;<br />end if;<br /><br />Open Cur_marks;<br />LOOP2: loop<br />select @VMARKS;<br /><br />FETCH Cur_marks INTO<br />vadmnno,vclass,vsection,vstuname,vgender,Vsubmark1,vsubmark2,Vsubmark3,Vsubmark4,vsubmark5,Vsubmark6,Vsubmark7,vsubmark8,Vsubmark9,<br />Vsubmark10,vsubmark11,vsubmark12;<br /><br />set @vquery = CONCAT('insert into Sch_tmpstUdResults_trm1 VALUES(',vadmnno,vclass,vsection,vstuname,vgender,vsusubtype,vsusubjects, <br /><br />@VMARKS);<br /><br />select @VQUERY;<br />PREPARE stmt3 FROM @vquery;<br /><br />EXECUTE stmt3;<br />DEALLOCATE PREPARE stmt3;<br /><br />if no_more_accounts then<br />set no_more_accounts=0;<br />close Cur_marks;<br />LEAVE LOOP2;<br />end if;<br />end loop LOOP2;<br />END LOOP LOOP1;<br />END $$<br />DELIMITER ;VARUGHESEhttps://www.blogger.com/profile/09468898346203658689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-17482222531982049762013-01-14T08:33:36.232+01:002013-01-14T08:33:36.232+01:00@Anonymous,
you said you did this inside workben...@Anonymous, <br /><br />you said you did this inside workbench. Can workbench execute batches? <br /><br />Type <br /><br />SELECT CONNECTION_ID();<br /><br />SELECT CONNECTION_ID();<br /><br />and execute it in workbench the same as you did with your code. Does it return the same number for both queries? It should be. If not, I'd argue workbench is broken and you should use another tool. rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61122772479048138122013-01-14T02:12:35.906+01:002013-01-14T02:12:35.906+01:00I use the following in workbench:
SET @sql = NULL...I use the following in workbench:<br /><br />SET @sql = NULL;<br />SELECT<br /> GROUP_CONCAT(DISTINCT<br /> CONCAT(<br /> 'MAX(IF(property_name = ''',<br /> property_name,<br /> ''', value, NULL)) AS ',<br /> property_name<br /> )<br /> ) INTO @sql<br />FROM<br /> properties;<br />SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');<br />PREPARE stmt FROM @sql;<br />EXECUTE stmt;<br />DEALLOCATE PREPARE stmt;<br /><br />The following error was generated:<br />20:34:34 DEALLOCATE PREPARE stmt Error Code: 1243. Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE 0.001 sec<br /><br />MYSQL Version: MySQL 5.5.15 via socket<br />Workbench Version: 5.2.44<br /><br />What did I do wrong?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-84023667504186178482012-09-27T09:03:51.830+02:002012-09-27T09:03:51.830+02:00Hi All,
Can you please help me out. I have create...Hi All,<br /><br />Can you please help me out. I have created prepare statement in procedure. But when I execute procedure then it give me an error.<br />"#1243 - Unknown prepared statement handler (auto_partition) given to EXECUTE"<br /><br />CREATE PROCEDURE auto_partition()<br />BEGIN<br /> DECLARE _stmt VARCHAR(1024);<br /> SET @l_SQL := CONCAT('ALTER TABLE tbl_location_history ADD PARTITION (PARTITION ', concat( 'Part_Y', year( now( ) ) , '_M', month( now( ) ) , '_W', week( now( ))+1), ' VALUES LESS THAN (TO_DAYS(\'', DATE_FORMAT(date_add(NOW(), interval 15 day),'%Y-%m-%d'), '\')));');<br /> PREPARE _stmt FROM @l_SQL;<br /> EXECUTE _stmt;<br /> DEALLOCATE PREPARE _stmt;<br />ENDDevahttp://gmail.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-70092544587529127602012-06-13T13:34:11.269+02:002012-06-13T13:34:11.269+02:00Varun,
MySQL doesn't support dynamic sql ins...Varun, <br /><br />MySQL doesn't support dynamic sql inside dynamic sql. How to solve it depends one what you want to do exactly. What exactly are you generating, and what does it aim to do?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-20335458225714979212012-06-13T09:55:45.620+02:002012-06-13T09:55:45.620+02:00Hi Roland
I am actually making procedure where I ...Hi Roland<br /><br />I am actually making procedure where I need to prepare the 'execute stmt using @lv_constant_1,@lv_constant_2' statement. This is because the dynamic variables are being generated in the procedure itself.<br /><br />It says 'This command is not supported in the prepared statement protocol yet'. <br /><br />What can be the solution for this?Varunhttps://www.blogger.com/profile/11648034621030086321noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66896615540157282102012-04-21T02:38:36.453+02:002012-04-21T02:38:36.453+02:00Roland,
Thanks for your reply and sorry for my de...Roland,<br /><br />Thanks for your reply and sorry for my delay in getting back to the blog. Between the holidays and work, there has just been no free time!<br /><br />I believe your analysis is 100% correct in that there will likely be a small set of statements or columns that are needed to do determine if a discount is applicable. Unfortunately, as I am building the application, I only have one client and limited business knowledge and thus I am trying to build a solution that is as flexible as possible.<br /><br />That being said, I was able to build a proof of concept and wanted to post it here to get your feedback as well as provide a possible solution for others who are in my predicament.<br /><br />I have made some adjustments to the table and stored procedures previously posted, so I will repost them for clarity.<br /><br />Discounts Table:<br />CREATE TABLE IF NOT EXISTS `discounts` (<br /> `id` int(11) NOT NULL AUTO_INCREMENT,<br /> `discount_calc` varchar(1000) NOT NULL,<br /> PRIMARY KEY (`id`)<br />) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;<br /><br />Adding some data to represent discount to be applied to a fictional invoices table:<br />INSERT INTO discounts (discount_calc) <br />VALUES ('SELECT 5 FROM invoices WHERE id = 1'),<br />('SELECT 10 FROM invoices WHERE id = 2');<br />Note: Where clause is arbitrary and can be as complex as one desires.<br /><br />Invoices table:<br />CREATE TABLE `invoices` (<br /> `id` int(11) NOT NULL AUTO_INCREMENT,<br /> `total` decimal(6,2) NOT NULL DEFAULT '0.00',<br /> `discount` decimal(6,2) NOT NULL DEFAULT '0.00',<br /> PRIMARY KEY (`id`)<br />) ENGINE=InnoDB DEFAULT CHARSET=latin1;<br /><br /><br />Fictional invoices:<br />INSERT INTO invoices (id, total)<br />VALUES (1, 100),<br />(2, 300);<br /><br />Now for the good stuff! I have two stored procedures the first is a wrapper procedure and could possibly be eliminated for this example, however, I am leaving it as is it helps illustrate how the cursor is segregated from the worker procedure.<br /><br />Wrapper Procedure:<br />CREATE PROCEDURE spDiscountsApplyToInvoice(IN inInvoiceId INT)<br /> BEGIN<br /> BEGIN<br /> DECLARE inDiscountId INT;<br /> DECLARE Finished BOOLEAN DEFAULT FALSE;<br /> DECLARE Cur CURSOR FOR SELECT id FROM discounts;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;<br /><br /> OPEN Cur;<br /> LOOP1: LOOP<br /> FETCH Cur INTO inDiscountId;<br /><br /> IF Finished THEN<br /> CLOSE Cur;<br /> LEAVE LOOP1;<br /> END IF;<br /> <br /> CALL spDiscountsIterate(inDiscountId, inInvoiceId);<br /><br /> END LOOP LOOP1;<br /> END;<br /> END<br /><br />In the worker procedure, I use the MySql CONCAT function to build a string which sets a discount variable. If this returns with a value, then we update the invoices table with the applicable discount.<br />CREATE PROCEDURE spDiscountsIterate(IN inDiscountId INT, IN inInvoiceId INT)<br /> BEGIN<br /> SET @colsql = CONCAT('SET @discount = (', (SELECT discount_calc FROM discounts WHERE id = inDiscountId LIMIT 0, 1), ')');<br /> <br /> PREPARE stmt FROM @colsql;<br /> EXECUTE stmt;<br /> <br /> IF @discount <> 0 AND @discount IS NOT NULL THEN<br /> UPDATE invoices <br /> SET discount = @discount<br /> WHERE id = inInvoiceId;<br /> END IF;<br /> <br /> DEALLOCATE PREPARE stmt;<br /> END<br /><br /><br /><br /><br />After running:<br />CALL spDiscountsApplyToInvoice(1);<br /><br />We see success:<br />id – total – discount<br />1 – 100.00 – 10.00<br />2 – 300.00 – 0.00<br /><br />A production version of these stored procedures would include more inputs (such as a site_id mentioned in my previous post) to reduce unnecessary work and improve performance.<br /><br />Thoughts? Feedback? Improvements? <br /><br />Joe JJJhttps://www.blogger.com/profile/02502362628036074705noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40108085128376107262012-04-17T14:09:14.662+02:002012-04-17T14:09:14.662+02:00I want to call a stored procedure for creating tab...I want to call a stored procedure for creating table which should accept as an argument the name of the table which is to be created dynamically.How ca i do this from servlet?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-58921287829741038572011-12-01T22:56:31.795+01:002011-12-01T22:56:31.795+01:00Hi Joe J,
in this case, it depends completely on ...Hi Joe J,<br /><br />in this case, it depends completely on what statements you stored in the is_applicable column. <br /><br />What I would try to do is investigate the statements that are in there now, and try to categorize them. My assumption is that the different statements will fall into a fairly small set of types of statements. <br /><br />Then, I'd try to analyze those statement types to see which data items (tables, columns) are used to determine if the discount is applicable. <br /><br />In a further step, you'd have to analyze in what way these data items are related to the site rows. If you find that there are now relationships, then you will need to extend your database schema to store the relationships between that data and the sites to which they apply. An implicit data item of this sort is the data that determines why you assigned any given statement to a particular site.<br /><br />When you did all that you should be in a position to write a single query that can determine the discounts. By then you won't need a cursor anymore, nor would you be fored to use dynamic SQL.<br /><br />I hope this helps.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-57036639891539016172011-12-01T22:37:01.977+01:002011-12-01T22:37:01.977+01:00Roland,
First off great blog post. I am running ...Roland,<br /><br />First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures. <br /><br />My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:<br /><br />CREATE TABLE IF NOT EXISTS `discounts` (<br /> `id` int(11) NOT NULL AUTO_INCREMENT,<br /> `is_applicable` varchar(1000) NOT NULL,<br /> `site_id` int(11) NOT NULL,<br /> `amount` decimal(6,2) NOT NULL,<br /> PRIMARY KEY (`id`)<br />) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;<br /><br />The (simplified) stored procedure is as follows:<br />CREATE PROCEDURE spTest()<br /> BEGIN<br /> BEGIN<br /> DECLARE @colsql VARCHAR(1000);<br /> DECLARE Finished BOOLEAN DEFAULT FALSE;<br /> DECLARE Cur CURSOR FOR SELECT is_applicable FROM discounts;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;<br /><br /> OPEN Cur;<br /> LOOP1: LOOP<br /> FETCH Cur INTO @colsql;<br /><br /> IF Finished THEN<br /> CLOSE Cur;<br /> LEAVE LOOP1;<br /> END IF;<br /> <br /> SET @sqlStatement = CONCAT('SET @output = (', @colsql, ')');<br /> PREPARE stmt FROM @sqlStatement;<br /> EXECUTE stmt;<br /><br /> IF @output > 0 THEN<br /> -- Do Stuff<br /> END IF;<br /> <br /> DEALLOCATE PREPARE stmt;<br /> END LOOP LOOP1;<br /> END;<br /> END<br /><br />Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.<br /><br />Thanks for your time!<br />Joe JJJhttps://www.blogger.com/profile/02502362628036074705noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-75339522673275254802011-12-01T22:36:37.727+01:002011-12-01T22:36:37.727+01:00Roland,
First off great blog post. I am running ...Roland,<br /><br />First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures. <br /><br />My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:<br /><br />CREATE TABLE IF NOT EXISTS `discounts` (<br /> `id` int(11) NOT NULL AUTO_INCREMENT,<br /> `is_applicable` varchar(1000) NOT NULL,<br /> `site_id` int(11) NOT NULL,<br /> `amount` decimal(6,2) NOT NULL,<br /> PRIMARY KEY (`id`)<br />) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;<br /><br />The (simplified) stored procedure is as follows:<br />CREATE PROCEDURE spTest()<br /> BEGIN<br /> BEGIN<br /> DECLARE @colsql VARCHAR(1000);<br /> DECLARE Finished BOOLEAN DEFAULT FALSE;<br /> DECLARE Cur CURSOR FOR SELECT is_applicable FROM discounts;<br /> DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;<br /><br /> OPEN Cur;<br /> LOOP1: LOOP<br /> FETCH Cur INTO @colsql;<br /><br /> IF Finished THEN<br /> CLOSE Cur;<br /> LEAVE LOOP1;<br /> END IF;<br /> <br /> SET @sqlStatement = CONCAT('SET @output = (', @colsql, ')');<br /> PREPARE stmt FROM @sqlStatement;<br /> EXECUTE stmt;<br /><br /> IF @output > 0 THEN<br /> -- Do Stuff<br /> END IF;<br /> <br /> DEALLOCATE PREPARE stmt;<br /> END LOOP LOOP1;<br /> END;<br /> END<br /><br />Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.<br /><br />Thanks for your time!<br />Joe JJJhttps://www.blogger.com/profile/02502362628036074705noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-51703011981148206182011-09-06T14:03:04.069+02:002011-09-06T14:03:04.069+02:00zserj,
I think you should ask questions like this...zserj,<br /><br />I think you should ask questions like this on stackoverflow.com or on forums.mysql.comrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-11599480471726222702011-09-06T13:08:54.458+02:002011-09-06T13:08:54.458+02:00hello sir Bouman.
im randy, im a starter of mysql...hello sir Bouman.<br /><br />im randy, im a starter of mysql, i have a question in alter table add column,. here my code<br /><br />$title = "title";<br />$alter = mysql_query("ALTER TABLE tablename ADD '".$colname."' varchar(3) not NULL ");<br /><br />no error came up, but it didnt get my desired output,,,i wrote here because i found it very relative and interesting blog to me...thank you..zserj kianhttps://www.blogger.com/profile/15577528730749132068noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72580065158435873332011-08-14T06:42:13.632+02:002011-08-14T06:42:13.632+02:00Took me time to read all the comments, but I reall...Took me time to read all the comments, but I really enjoyed the write-up. It proved to become Pretty useful to me and I am positive to all the commenters here Its always great when you can not only be informed, but also entertained Im certain you had fun writing this write-up.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72970537029739938402011-07-20T06:50:31.384+02:002011-07-20T06:50:31.384+02:00Hi Roland,
I have a series of (16)PREPARE, EXECUTE...Hi Roland,<br />I have a series of (16)PREPARE, EXECUTE and DEALLOCATE statements (in a stored procedure), each inserting rows into a diffterent table (table 1 to table16)<br />eg.<br />------------------------<br />SET @Command1 = CONCAT("insert into TABLE1" , ...etc.. );<br />PREPARE stmt1 FROM @Command1 ;<br />EXECUTE stmt1;<br />DEALLOCATE PREPARE stmt1;<br /><br />SET @Command1 = CONCAT("insert into TABLE2" , ...etc.. );<br />PREPARE stmt1 FROM @Command1 ;<br />EXECUTE stmt1;<br />DEALLOCATE PREPARE stmt1;<br /><br />.<br />.<br />.<br /><br />SET @Command1 = CONCAT("insert into TABLE16" , ...etc.. );<br />PREPARE stmt1 FROM @Command1 ;<br />EXECUTE stmt1;<br />DEALLOCATE PREPARE stmt1;<br />-------------------------------------<br />But when I execute the stored procedure, the INSERT works intermittently. sometimes all the 16 inserts works, but sometimes it doesn't. In the last CALL of the stored procedure, the first 2 inserts (into TABLE1 and TABLE2 ) and the last 4 inserts (TABLE 13 to 16) work, but not the inserts into Table 3 to 12.<br />Can you explain why ? Can't be because I'm using the same variable/handle command1 and stmt1 ?<br />PaulAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76832322687767168432010-12-14T11:27:56.133+01:002010-12-14T11:27:56.133+01:00Hi Roland,
This is really very nice thread, i hav...Hi Roland,<br /><br />This is really very nice thread, i have been following it from the beginning..<br /><br />Thanks,<br />MuraliVMurali.Vhttps://www.blogger.com/profile/17404063184457856697noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-161914727759498452010-12-14T09:22:37.398+01:002010-12-14T09:22:37.398+01:00Hi John,
calling ROW_COUNT() itself is not expen...Hi John, <br /><br />calling ROW_COUNT() itself is not expensive. MySQL automatically computes the value, grabbing it is cheap. <br /><br />What may not be so cheap is the roundtrip to run another SQL statement. You can try and wrap multiple statements in a stored procedure to minimze the number of roundtrips. <br /><br />HTH<br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-51953629208196197582010-12-14T08:59:36.743+01:002010-12-14T08:59:36.743+01:00Hi Roland,
Thanks for the prompt reply. Your answe...Hi Roland,<br />Thanks for the prompt reply. Your answer perfectly works for me, but i wonder an extract <br />select row_count statement will cause any overhead? <br />Regards,<br />JohnAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-68041963716255781152010-12-14T07:31:39.044+01:002010-12-14T07:31:39.044+01:00Hi John!
this is an excellent question, thanks! ...Hi John! <br /><br />this is an excellent question, thanks! <br /><br />I think you should be able to figure out the number of rows by calling the ROW_COUNT() function immediately after executing the prepared statement. see: <a href="http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_row-count</a>.<br /><br />Please let me know if this works.<br /><br />kind regards, <br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-56075100929522352572010-12-14T02:38:52.286+01:002010-12-14T02:38:52.286+01:00hi Roland,
i have a question about the mysql store...hi Roland,<br />i have a question about the mysql stored procedures prepare statement. The code is something like below:<br /><br />prepare updateQuery from @updateSQL;<br />execute updateQuery;<br /><br />my question is when I execute, how do i know any record being updated? <br />Regards,<br />JohnAnonymousnoreply@blogger.com