Installation guide

8. Data Types supported by mxODBC
8. Data Types supported by mxODBC
mxODBC tries to maintain as much of the available information across the
Python-ODBC bridge as possible. In order to implement this, mxODBC converts
between the ODBC and the Python world by using native data types in both
worlds.
You should note however, that some ODBC drivers return data using different
types than the ones accepted for input, e.g. a database might accept a time value,
convert it internally to a timestamp and then return it in a subsequent SELECT as
timestamp value.
mxODBC cannot know that the value only contains valid time information and no
date information and thus converts the output data into an mxDateTime DateTime
instance instead of an
mx.DateTime.DateTimeDelta instance (which would
normally be returned for time values).
The included mx/ODBC/Misc/test.pyc can help to check for this behavior. It tests
many common column types and other database features which are useful to
know when writing applications for a particular database backend.
8.1 mxODBC Parameter Binding
When defining SQL statements that use parameters, mxODBC provides a way to
bind Python values to those parameters called parameter binding.
Instead of using the literal parameter values in the SQL statement passed to the
cursor.execute*() methods, you can use a parameter binding character or
character sequence to define the parameter locations in the SQL statement and
then pass the Python parameter values to the
cursor.execute*() methods as
additional parameter. The ODBC driver or the database backend will then take the
values and use them to run the SQL statement.
Example:
Use
"SELECT * FROM MyTable WHERE A=? AND B=?" and (1, 2)
(parameter binding) instead of
"SELECT * FROM MyTable WHERE A=1 AND
B=2"
(embedding parameters literally).
This has both a performance and a security advantage.
Performance is much better if the database backend can easily identify whether it
has already created an access plan for a SQL statement by simply looking at the
parameterized version of the statement, than first having to convert a SQL
139