What this blog entry does, is help my fellow geeks using MySQL databases and stored procedures. Man, I can make a Personal Digital Assistant talk over the length of a tropical jitney bus to an RFID card and then put out the result on WiFi using unmanaged code an programatically controlling a mobile device. However, sometimes I get hung up on stupid SQL database language syntax. Solving those issues takes longer than figuring out how to build a nuclear device using items purchased at WalMart that are made in China.
So I spent a long time yesterday trying to make a stored procedure work. It was a hair pulling experience. I used a prepared statement in the stored procedure because I wanted to pass the name of the table as a variable. The way that I do it, is cache the name of the table in another table row.
Well sir, when you use a table name variable in a prepared statement, everything works fine. However, inserting the table name as string into a column throws a MySQL error 1054 -- column not found. It gives the name of the string value as the column name. I would yell at the machine ... "Are you STUPID??" I googled and I gawgled and I trolled. In my dim memory, I had encountered this before.
My SQL statement worked on the command line and it worked with database tools, but it did not work in the prepared statement. After much frigging around, I found the answer.
It was okay to pass the primary table name as a variable. But if you are inserting text into a column everything is treated as a string. Even though the insert was one word "tablename" the variable value needs to have a single quote at the beginning and the end. In other words, if you want to insert the word shitsky into the table, you have to assign it to the variable as 'shitksy' with the single quotes included. Jumping Jehosaphat! Who was the nerd that programmed this horror.
Anyway, all is well that ends well. I just wanted to pass this on, so that when someone else encounters it, hopefully Google will hit this page first and save them a lot of time. You are welcome.