On 27/01/2014 20:45, Tim Bunce wrote:
On Sat, Sep 28, 2013 at 05:24:24PM +0100, Martin J. Evans wrote:
I've added it to my TO_DO list. By all means pester me is you hear nothing.
Plese consider yourself pestered :)
Tim.
Back in Sept 2013 (and as part of the DBI Driver Test Suite -
Requirements discussion) I said (amongst other things):
"As someone who has attempted to use DBI to multiple DBD backends (and
even ignoring SQL differences) the biggest stumbling block is
differences in DBDs either outside of the DBI specification or because
DBI allows a driver to not implement something or allows a DBD multiple
options. Just ask Peter (ribasushi) how much code is in DBIx::Class to
work around driver differences. I've long thought (and I've a feeling
proposed - but that might have just been in discussions with Peter over
a pint) a capability system beyond what get_info provides. get_info is
pretty much ODBC's SQLGetInfo and few drivers beyond DBD::ODBC really
support it that well. Just off the top of my head I'm thinking about
these sorts of differences:"
I've been in the position of writing DBD independent code a number of
times over the last 10 or so years and it has nearly always resulted in
writing a set of module wrappers above the DBD. Five major stumbling
blocks have been calling procedures, getting cursors back from
procedures, when are output bound parameters available, supported case
in table/column names (and whether to quote to maintain case) and
last_insert_id but there are many others.
Here is a list of things DBDs might not support or do slightly
differently with some explanation. This is just a starting list and I'll
add to it if people reply and when I get a chance to look at DBIx::Class.
1 does it support named parameters - most do but some don't so I always
ended up using ?
2 does it need a ':' preceding named parameters or not - I sent an email
about this difference ages ago and I'll dig it out if necessary. DBI
does not specify.
3 can named parameters be used multiple times in the same SQL - they
certainly cannot in DBD::ODBC but can in DBD::Oracle.
4 does it use :N for placeholders or ? or both
5 can you mix ? and :N - you certainly can't in DBD::ODBC.
6 lobs - DBIs' lob read is not documented and so all DBDs do it a
different way. There is a lot more to this than it seems. DBD::Oracle
has at least 3 ways of obtaining lobs but the one we repeatedly use is
the one which allows us to retrieve the length of a lob first before
fetching it (perhaps in parts). We really could do with DBI having a lob
read method.
7 returning cursors from functions or procedures - is it supported and
how do you do it. This is something we do a lot and it is a PITA across
multiple DBDs.
8 output parameters from procedures and functions and when they are
available (more_results is another sticky one one here). Again, we had a
long discussion about more_results years ago but it became so complex
nothing came of it.
9 does it have its own execute_for_fetch - only implemented in 2 DBDs I
know of and severely restricted in DBD::ODBC due to ODBC driver
differences. You might say why would you care and that seems to be the
attitude DBI takes i.e., it gets used if supported or DBI does it. It
matters because if you know a DBD does not do it, then you might take a
different path to using DBI's implementation.
10 can it support unicode. Obviously a contentious one and one we've
visited many times before.
11 how do you enable unicode, sqlite_unicode etc - because of backwards
compatibility many DBDs need a specific setting to enable unicode.
12 is last_insert_id usuable - not in some DBDs - certainly not
DBD::ODBC. If it can't what do you do?
13 can you safely put comments in SQL - even comments including ? or :param
14 does a do create a statement handle, i.e, is do the same as
$dbh->prepare->execute - this mostly only has implications when
reporting errors i.e., which handle you get in an error handler.
15 can you rebind a placeholder with a different type
16 does a disconnect commit or rollback
17 does type_info/type_info_all return 19 or 20 columns - ODBC/MS added
a new column. Although the type_info in DBI should be immune to this
I've seen people having problems with this.
18 does it support DiscardString etc. Probably seems insignificant to
most people but if you are reading from a database and producing JSON
strings it can matter to you. If you don't have DiscardString you need
to add 0 to any number or you JSON numbers end up as "N" instead of N
especially when using JSON::XS.
19 does it support setting a type on bind_column - most don't
DBIx::Class (used by many) and the work it has to do in the background
to compensate for differences in DBDs must be full of tests which we can
examine to get more. If someone from DBIx::Class (Peter/ribasushi?)
points me in the right direction I'll look at it.
Then there are loads of capabilities defined by SQLGetInfo in ODBC. Here
is a selection of some. Of course get_info already exists in DBI but I
doubt many DBD authors have really done a lot with this to ensure it is
correct other than maybe run it against an ODBC driver and pinched the
results. Although this method is mostly ok it is dependent on the ODBC
driver you pick.
You can find the full list of SQLGetInfo at
http://msdn.microsoft.com/en-us/library/ms711681%28v=vs.85%29.aspx
I've scoured tens of thousands of lines of DBI code looking for the
get_info calls. Here are SQLGetInfo types I regularly use from client code:
SQL_DATA_SOURCE_NAME - A character string with the data source name that
was used during connection.
SQL_DATABASE_NAME - A character string with the name of the current
database in use, if the data source defines a named object called
"database".
SQL_DBMS_NAME - A character string with the name of the DBMS product
accessed by the driver.
SQL_DBMS_VER - A character string that indicates the version of the DBMS
product accessed by the driver. The version is of the form ##.##.####
SQL_DM_VER - A character string with the version of the Driver Manager.
The version is of the form ##.##.####.####
SQL_DRIVER_NAME - A character string with the file name of the driver
used to access the data source.
SQL_DRIVER_ODBC_VER - A character string with the version of ODBC that
the driver supports. The version is of the form ##.##
SQL_DRIVER_VER - A character string with the version of the driver and
optionally, a description of the driver. At a minimum, the version is of
the form ##.##.####
SQL_IDENTIFIER_CASE - upper, lower, sensitive, mixed
SQL_IDENTIFIER_QUOTE_CHAR - The character string that is used as the
starting and ending delimiter of a quoted (delimited) identifier in SQL
statements.
SQL_LIKE_ESCAPE_CLAUSE - A character string: "Y" if the data source
supports an escape character for the percent character (%) and
underscore character (_) in a LIKE predicate and the driver supports the
ODBC syntax for defining a LIKE predicate escape character; "N" otherwise.
various SQL_MAX_xxx
SQL_ODBC_VER - A character string with the version of ODBC to which the
Driver Manager conforms. The version is of the form ##.##.0000
SQL_QUOTED_IDENTIFIER_CASE - upper, lower, sensitive, mixed
SQL_SEARCH_PATTERN_ESCAPE - A character string specifying what the
driver supports as an escape character that allows the use of the
pattern match metacharacters underscore (_) and percent sign (%) as
valid characters in search patterns.
Some of the above are also used in DBD::ODBC itself but this is a
definitive list:
SQL_ASYNC_MODE
SQL_DRIVER_ODBC_VER
SQL_DRIVER_NAME
SQL_DRIVER_VER
SQL_DBMS_NAME
SQL_DBMS_VER
SQL_MAX_COLUMN_NAME_LEN
SQL_CATALOG_NAME
SQL_SCHEMA_USAGE
Personally, the DBD differences have been more important to me when
writing DBD-neautral code than the SQLGetInfo/get_info stuff. I'd be
tempted to keep get_info as it is (but have a drive to get DBDs to
improve the support) and then add a DBD capabilities interface. It only
needs to be a little like SQLGetInfo/get_info with an added way of
detecting whether the DBD does not support it at all.
This is just to get the discussion going and I hope to add to it soon
but in the meantime I'd be very interested in:
a) any difference between DBDs I might have missed out
b) any opinion from anyone who has tried to write DBD-independent code
in Perl - what problems did you have, how did you overcome them?
c) there are modules on CPAN to try and overcome differences but they
tend to just cover a small set of DBDs - what do they do? Test::Database
is one also relevant to the current DBI driver test suite discussion and
my main problem adding DBD::ODBC was the requirement to be able to
create a database which could be torn down afterwards (the differences
in SQL to achieve that are mind boggling - have you seen the create
database SQL for DB2 and how it differs from SQL Server, MS Access,
Oracle, a_an_other_database?)
Martin
--
Martin J. Evans
Wetherby, UK
