Save BLOB into MySQL using web2py

Question

Using the web2py framework I want to select a file via a file type input and save it's contents into a MySQL BLOB type field. The file contains binary data.

I need to use DAL for connection management but I have an SQL procedure to do the task. The problem is when I try this below I get error for the SQL syntax.

f = form.vars.element.file.read()
db.executesql( "CALL someproc('" + f + "');" )

I've tried inserting the raw binary in lots of ways and got the same or similar error. I've also tried to use MySQLdb directly like:

f = form.vars.element.file.read()
db.cursor().execute( "CALL someproc('" + f + "');" )
db.commit()

This works perfectly but I need to use the above DAL version so the problem is still open.

I've spent 3 days solving this task without luck. :( Please help!

Answer 1

I'm not sure why your second example works, given that the DAL.executesql method ultimately calls cursor().execute() in exactly the same way. In any case, if you want to use the latter syntax in conjunction with the web2py DAL object, you can access the cursor via db._adaptor.cursor. So, your second example would change to:

db = DAL('mysql://...')
db._adapter.cursor.execute( "CALL someproc('" + f + "');" )

Another option might be to use the .callproc method:

db._adapter.cursor.callproc('someproc', (f, ))

Note, db.executesql() also takes placeholders:

db.executesql('...WHERE name=%s', ('Mary', ))

but I'm not sure that would work for passing arguments to a stored procedure.