Sometimes It Is You, I.E. Adventures With Stored Procedures

One area I'm a little weak on, database wise, is in dealing with stored procedures, prepared statements, etc. Primarily because we've never really had apps before that were at the level of us thinking about them or really needing them. However, I'm now working on an application where using a stored procedure seems to make sense.

Essentially we have a registration system where one can register for three broad types of things: events, online trainings, and commodities (a catch all for fund raising donations, etc). Each of these “products” has their own set of tables due to their unique natures. We have a view which does a nice little tie of product id and type for general details, but for each item a registration may (or may not) include questions that are answered by registrants. Those questions might have a text option as an answer or allow people to choose from a pick list. To view registrations (both in the cart and after check out), we need to be able to pull up the appropriate question and option text from the correct table. Doing this in ColdFusion would be easy, just a look, some variable setting and done. However, we need to do this a bunch of places, so we decided that a stored procedure might be a better option.

So I pulled out my MySQL book (the Certification Study Guide) and refreshed myself on MySQL's stored procedure and prepared statement functionality. I wrote up a procedure and it didn't work. So I turned to Google to solve the issue of it needing to allow for dynamic table names, because it had to go to different tables depending on the type of product it was. This resulted in the stored procedure I currently have for the shopping cart aspect:

DELIMITER //

	DROP PROCEDURE IF EXISTS CS_FullCartDetails;

	CREATE PROCEDURE CS_FullCartDetails (IN shoppingCartID INT)

	BEGIN

DECLARE questionsTable, optionsTable TEXT;

DECLARE productType INT;



SELECT DISTINCT base_producttypes_producttypeid INTO productType 

FROM shoppingcarts WHERE shoppingcartid = shoppingCartID LIMIT 1;



SELECT DISTINCT CONCAT(roottable, '_questions'), CONCAT(roottable, '_options') INTO questionsTable, optionsTable 

FROM base_producttypes WHERE producttypeid = productType LIMIT 1;



SELECT DISTINCT CONCAT('SELECT sd.detailid, sd.questionid, sd.optionid, sd.answer, sd.additionalfee, qt.question, ot.optiontext

FROM shoppingcarts_details sd INNER JOIN ',questionsTable,' qt ON sd.questionid = qt.questionid

LEFT JOIN ',optionsTable,' ot ON sd.optionid = ot.optionid

WHERE shoppingcarts_shoppingcartid =',shoppingCartID,';') INTO @sqlStatement;


 PREPARE Stmt FROM @sqlStatement;

	EXECUTE Stmt;

	DEALLOCATE PREPARE Stmt;

	END

	//

	DELIMITER ;

It worked, or so I thought, and I proceeded on happily. Then today, while doing further resting on the shopping cart code, I realized there was a problem. For one of my registrations, one option continued to remain unselected when editing the registration. Through a bunch of testing and aborts, I discovered that the stored procedure was not returning all of the details for that shopping cart item. I also realized it was giving the wrong question information. More checking, and it worked fine for the other two test items. The difference? The two working ones were events, the broken one was an online training.

I ran each of the individual elements of the statements by themselves, and they confirmed it “should” work. The first properly returns the OT product type ID, the second properly returns its table names. But the final one is NOT using the table names it gets. Adding questionsTable to the final concated statement as a field confirms it is looking at events. Hmmm….further evaluation is needed. I took out the last bit so I could see the results of query two:

DELIMITER //

	DROP PROCEDURE IF EXISTS CS_FullCartDetails;

	CREATE PROCEDURE CS_FullCartDetails (IN shoppingCartID INT)

	BEGIN

DECLARE questionsTable, optionsTable TEXT;

DECLARE productType INT;



SELECT DISTINCT base_producttypes_producttypeid INTO productType 

FROM shoppingcarts WHERE shoppingcartid = shoppingCartID LIMIT 1;



SELECT DISTINCT CONCAT('SELECT DISTINCT CONCAT(roottable, \'_questions\'), CONCAT(roottable, \'_options\') 

FROM base_producttypes WHERE producttypeid = ',productType,';') INTO @sqlStatement;


 PREPARE Stmt FROM @sqlStatement;

	EXECUTE Stmt;

	DEALLOCATE PREPARE Stmt;

	END

	//

	DELIMITER ;

So definitely not getting the right variables. But why? So change it again to just the first one and see what results I get…

Ah ha! Now, you dear reader may have already realized my error, eh? I finally figured it out, but only as I started writing this out (which is actually how I found some other obvious ones, I start talking it out with my partner and have a “duh” moment). The issue? The first query, which “looks” right, isn't because it isn't taking the variable I passed into the stored procedure, it's just comparing a column to itself! *head smack* So, with myself now feeling rather sheepish, we make a seemingly small change:

DELIMITER //

	DROP PROCEDURE IF EXISTS CS_FullCartDetails;

	CREATE PROCEDURE CS_FullCartDetails (IN thisCartID INT)

	BEGIN

DECLARE questionsTable, optionsTable TEXT;

DECLARE productType INT;



SELECT DISTINCT base_producttypes_producttypeid INTO productType 

FROM shoppingcarts WHERE shoppingcartid = thisCartID LIMIT 1;



SELECT DISTINCT CONCAT(roottable, '_questions'), CONCAT(roottable, '_options') INTO questionsTable, optionsTable 

FROM base_producttypes WHERE producttypeid = productType LIMIT 1;



SELECT DISTINCT CONCAT('SELECT sd.detailid, sd.questionid, sd.optionid, sd.answer, sd.additionalfee, qt.question, ot.optiontext

FROM shoppingcarts_details sd INNER JOIN ',questionsTable,' qt ON sd.questionid = qt.questionid

LEFT JOIN ',optionsTable,' ot ON sd.optionid = ot.optionid

WHERE shoppingcarts_shoppingcartid =',thisCartID,';') INTO @sqlStatement;


 PREPARE Stmt FROM @sqlStatement;

	EXECUTE Stmt;

	DEALLOCATE PREPARE Stmt;

	END

	//

	DELIMITER ;

And yay, I get exactly what I expected to get, all three options with the correct question text 🙂

So what should have seemed like an obvious lesson learned – the names of the variables you are passing into a stored procedure should NOT be the same as the name of any columns in your database. And the point of this post? It's always the little things that trip you up. I was ready to rail against the stored procedure issues of MySQL because it wasn't working, when the issue wasn't with MySQL, it did what I told it to do. I just gave it bad instructions. So while I have tons of legitimate complaints against MySQL, this isn't one of them and our stored procedure really is working awesomely now.