Problems working with stored procedures in phpMyAdmin

16/08/11

Permalink 04:39:42 pm, by mholmes, 325 words, 448 views   English (CA)
Categories: Activity log; Mins. worked: 180

Problems working with stored procedures in phpMyAdmin

The insertion code I'm writing needs to use stored procedures in order to do slightly complex things such as IF THEN ELSE structures. However, I've discovered that it's impossible to call a stored procedure which has more than one statement in it from inside phpMyAdmin because the CLIENT_MULTI_STATEMENTS flag is not set by phpMyAdmin in its connection string. This is not configurable in phpMyAdmin; you'd need to change the actual source to make this possible.

I confirmed this by running a test at the mysql command line on mysqldev.hcmc.uvic.ca (like this: mysql -u hcmc -p properties_dev). From the CLI, I could successfully define a procedure and call it, but from inside phpMyAdmin I could only define the procedure; attempts to call it result in Error 1312 (0A000): PROCEDURE proc_name can't return a result set in the given context. The same applies to SQL scripts uploaded into phpMyAdmin via the Import function.

So it seems to me that one useful working method would be one whereby I have phpMyAdmin open (for easy checking of the results of my script), and I copy my script from the local machine (where it's generated by XSLT which runs against the XML data from the spreadsheet) over to mysqldev; then I run the script from the command line, and check the results. Not ideal at all. But possible.

Another approach would be to generate not SQL but PHP, with embedded SQL, and run the PHP script; the PHP connection method enables you to set the requisite flag when making the connection. That might be easier, but again I'd need to save it and copy it over to home1t in order to be able to run it; and the extra embedding, given the scale of the operation I want to do, might be a problem. So the first option is probably the best.

It's taken me half the day to figure this out. Curses.

Pingbacks:

No Pingbacks for this post yet...

Properties

A database project to collect historical data on properties and titles.

Reports

Categories

August 2014
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

XML Feeds