Re: get_info (was: Common DBI Driver Test Suite - Requirements)

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