[DB-SIG] any 2.0-compliant packages for postgresql?

[Nathan Clegg]

Ahhhh...this is probably where our experience differs then.  My experience
is that bound paramaters may only be used for literal values.  You cannot
use a paramater to specify the name of a table, column, type, or any other
object.  This is enforced, for example, by Oracle when a query with bound
paramaters is prepared by the engine.  I don't think postgresql supports
bound paramaters internally, does it?  So it's always the package, library,
or module that is inserting the values directly, thus some of the
limitations don't apply.

In short, "real" bound paramaters can't be used for the purposes you
described.  Since postgresql doesn't support "real" bound paramaters, the
situation is less obvious.



-----Original Message-----
From: db-sig-admin@python.org [mailto:db-sig-admin@python.org]On Behalf
Of Federico Di Gregorio
Sent: Wednesday, June 27, 2001 7:38 AM
To: Nathan Clegg
Cc: Python DB-SIG Mailing List
Subject: RE: [DB-SIG] any 2.0-compliant packages for postgresql?


On 27 Jun 2001 07:25:49 -0700, Nathan Clegg wrote:
> > Q: psycopg correctly put strings obtained by the Date and Time
> > constructors inside quotes  ('') as requested by the api. it should do
> > the same (and maybe escape) normal strings? if that's the case don't we
> > need a String constructor? how is the module supposed to know when a
> > string is real string and need quoting and when i am passing it just as
> > convenience but it really is a number? example...
>
> This is the part I have issue with.  The api spec does require that normal
> strings be escaped and quoted.  In regards to passing numbers as strings
for
> convenience, I would suggest doing away with it.  If you mean it to be a
> number, send a number, and furthermore specify %(name)d instead of
%(name)s.
> I appreciate conveniences, but I think this introduces the kind of
ambiguity
> that python's specification set out to squash in the first place.
> Furthermore, all of the databases I have personally used have no problem
> casting strings to numbers where required.  That is, if you pass
postgresql
> (or oracle, or mysql...) a quoted string '768' to compare against a number
> field, it with cast it to a number for you and get you the desired result.
> The module doesn't really need to implement this convenience casting
because
> the database engine already does.


so, if i try to do as follows:

curs.execute("CREATE TABLE test (a text)")
curs.execute("INSERT INTO test VALUES (%(a)s)", {'a':'some text...'})

the method call fails miserably because the generated SQL is:

    INSERT INTO test VALUES (some text...)

without quotes. footnote 5 requires argument binding to prodive quotes
and escape sequences but how can the module know is the given string
will be a string in the db too and requires quoting or it is something
different (let's say a user-defined type in psogresql) that does not
require it?

anyway, your answer at least means that psycopg isn't more broken that
all other drivers... ;)

ciao,
federico

--
Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
   God is real. Unless declared integer. -- Anonymous FORTRAN programmer


_______________________________________________
DB-SIG maillist  -  DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig