On 23.05.2013 22:27, Chris Clark wrote:
> On Thu, 23 May 2013 17:44:35 +0200, M.-A. Lemburg <m...@egenix.com> wrote:
>> I've done some research on escaping parameter markers. Here's what
>> I came up with:
>>
>> ODBC: Supports only qmark ('?'). The standard doesn't specify an
>> escape sequence for ? in SQL statements.
>>
>> JDBC: Supports qmark ('?') and named (':param'). The standard
>> doesn't specify an escape sequence for either of those
>> in SQL statements.
>
> Being pedantic; JDBC requires qmark ('?').
>
> The JDBC spec. only documents named (':param') for execute procedure ( e.g.
> http://docs.oracle.com/javase/7/docs/api/java/sql/CallableStatement.html#setString%28java.lang.String
> ).
> Any JDBC driver that implements named parms for queries, like SELECT, has
> added an extension, for
> example Oracle (or is an ORM feature).
Thanks for the clarification. Indeed, the prepared statement does not
expose methods for named binding:
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
>> Oracle: Supports named (':param'). There's no mention of escaping
>> the colon in their documentation. Interesting aside: The
>> parameters can be bound based on name and based on
>> position (if unambiguous), so I guess we'll have to spell
>> out that with named we always mean binding by name.
>
> It depends on the interface/api. If using ODBC it is qmark ('?').
Right. ODBC doesn't have a way of binding by name. It only
supports binding by position - and it doesn't differentiate
between prepared statements and callable ones.
> If using something like ProC, then yes it is named only.
>
>
>> Since specifically PostgreSQL uses '?' as operator, there has
>> been some discussion on how to resolve the issue, but I haven't
>> found their resolution on the net:
>>
>> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000053.html
>> http://stackoverflow.com/questions/14779896/does-the-jdbc-spec-prevent-from-being-used-as-an-operator-outside-of-quotes
>>
>>
>> In PostgreSQL, it's possible to avoid using '?' and '::'
>> in SQL by simply using appropriate functions or CASTs instead,
>> so you don't really need escaping.
>>
>> Since neither of those standards defines an escape mechanism for
>> the parameter markers, I guess the DB-API should not either and
>> instead leave this for the database modules to handle in whatever
>> way is appropriate for the database backend.
>
> Agreed.
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, May 24 2013)
>>> Python Projects, Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope/Plone.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2013-07-01: EuroPython 2013, Florence, Italy ... 38 days to go
::::: Try our mxODBC.Connect Python Database Interface for free ! ::::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
