Re: callproc, string formatting

I ran into this problem a few months back.  I use a prepared statement mix-in to get the job done as I wasn't able to get psycopg2 to escape strings as it should (maybe there's an API call I'm missing?).  Here is a link to a mix-in that got me started.  I made heavy modifications to it to make it work for my needs, but at least it's one option.

http://code.activestate.com/recipes/576698/

Other things to consider are:
> Is the proc in a schema that your user doesn't have in their search path?  If so, put the schema name in front of the proc name.
> Does that proc have two instances of itself with the same number of args?  If so, you may need to explicitly cast the arguments being passed to the function.  for instance, replace %(state)s with %(state)s::VARCHAR or whatever that functions parameter is for that proc.
> Does the proc exist in the database?  I know it's a dumb question, but maybe it got removed because of a drop cascade or some other such nonsense.

Eric Chamberlain


On Nov 25, 2009, at 8:23 AM, Brian Jones wrote:

Hi all,

I haven't used psycopg2 since 2006 or so, and didn't use it to call server side functions, so I'm a bit out of phase and could use some clarification:

I need to call a server side function called 'myapp.new_user'. My first attempt looked something like this:

--------------------------- snip ---------------------------
qdict = {'fname': self.fname, 'lname': self.lname, 'dob': self.dob, 'city': self.city, 'state': self.state, 'zip': self.zipcode}

sqlcall = """SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
%(dob)s, %(city)s, %(state)s, %(zip)s""" % qdict

curs.execute(sqlcall)

--------------------------- /snip ---------------------------
That fails because none of the strings are not quoted at all, ever.

Of course, I foolishly tried going back and putting quotes around all of those named string formatting args, and of course that fails when you have something like a quoted "NULL" trying to move into a date column (dob is allowed to be null). It has other issues too, like being error-prone and a PITA, but hey, it was pre-coffee time.

My next attempt looked like this:
--------------------------- snip ---------------------------
curs.execute("""SELECT * FROM myapp.new_user( %(fname)s, %(lname)s,
%(dob)s, %(city)s, %(state)s, %(zip)s""", qdict)
--------------------------- /snip ---------------------------

Effectively just replacing the "%" with a "," to pass an actual param list instead of passing only one arg that contains a string formatting operation.

But this winds up with unquoted strings as well.

So I looked into callproc a bit, but my first attempt isn't looking too promising:
--------------------------- snip ---------------------------
# you can't pass a dict to callproc
callproc_params = [self.fname, self.lname, self.dob, self.city, self.state, self.zip]
curs.callproc('myapp.new_user', callproc_params)
--------------------------- /snip ---------------------------

The error I get is:

"function myapp.new_user(unknown, unknown, unknown, unknown, unknown, unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts"

Can anyone help me understand the right way to do this, or point me to documentation on how to perform this type of operation?

thanks.
brian
--
Brian K. Jones
Python Magazine  http://www.pythonmagazine.com<http://www.pythonmagazine.com/>
My Blog          http://www.protocolostomy.com<http://www.protocolostomy.com/>
<ATT00001..c>


________________________________
Confidentiality Notice: This e-mail may contain proprietary information some of which may be legally privileged. It is for the intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by reply e-mail and delete the message. Any disclosure, copying, distribution or use of this information by someone other than the intended recipient(s) is prohibited and may be unlawful.

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg