Re: Defining type_info Was: DBIstcf_DISCARD_STRING and DBIstcf_STRICT

On 05/02/13 14:16, Lyle wrote:
> On 05/02/2013 09:09, Martin J. Evans wrote:
>> On 05/02/13 00:46, Lyle wrote:
>>> Hi All, I just submitted bug 83132. It's nothing major, but
>>> after upgrading to a newer DBI my comparison tool reported
>>> SQL_CHAR and SQL_NUMERIC as DBIstcf_DISCARD_STRING and
>>> DBIstcf_STRICT. I could be wrong, but it seems the DBI Constants
>>> example isn't totally clear on what gets returned.
>>>
>>>
>>> Lyle
>>>
>>
>> As I've commented on the RT that was my fault. If we decide on a
>> tag name I will move it.
>
> I saw in the change log, I didn't want to point fingers :)

> While I have you, I'm still messing with type_info_all. Looking at
> SQLGetTypeInfo from ODBC it's defined as: *SQLGetTypeInfo* returns
> information about data types supported by the data source. The driver
> returns the information in the form of an SQL result set. The data
> types are intended for use in Data Definition Language (DDL)
> statements.

Precisely and DBD::ODBC does not touch the result-set returned from SQLGetTypeInfo or any other result-set. With DBD::ODBC, you get whatever the ODBC driver returned.
  
> So as Greg said before, it's a case of the user requesting a data
> type, and being told if it's available (with details of it's local
> type name for DDL). So that said, why not map one type to all that it
> can match? Also, whether this should be taken further, and return
> type definitions that effectively emulate the one that's being asking
> for. This is what happens if you ask Oracle for an INT in your DDL,
> it just gives you a NUMBER(38):
> http://www.sysdba.de/oracle-dokumentation/11.1/server.111/b28286/sql_elements001.htm#i54335
>
>  Oracle command line output: SQL> create table test_int ( nummy INT
> ); Table created. SQL> desc test_int; Name
> Null?    Type ----------------------------------------- --------
> ---------------------------- NUMMY
> NUMBER(38)
>
> What I'm having to do is extend the output from type_info_all, to
> include such multiple mappings and emulations. I'm trying to figure
> how much of this I should try to feed back into the DBDs.

I personally have loads of code which would potentially break if the result-set from type_info_all was changed in any way. Also, as I said above, DBD::ODBC does not change any result-sets and there is no ability currently to support doing that.

Other DBDs have greater flexibility since some of them emulate SQLGetTypeInfo with a SQL statement whereas DBD::ODBC is stuck with SQLGetTypeInfo. Even if we went down the SQL route, there are so many variations in databases under ODBC it would be impossible.

> It would help a lot, and what I think is needed, is a clear
> definition as to the purpose of type_info_all, and more guidance for
> DBD developers on how mappings should be carried out.
>
> In the DBI docs it states: Since DBI and ODBC drivers vary in how
> they map their types into the ISO standard types you may need to
> search for more than one type.
>
> Should this really be something put on the user, as opposed to giving
> them the best mapping for that type if there is one? It seems like
> it's adding extra search effort for them that they shouldn't
> necessarily need to do. As you said, it appears the only guidance DBD
> authors get is to pull the information from the equivalent ODBC
> driver:
>
> Metadata.pm#Generating_a_TypeInfo_package_for_a_driver
>
> And from DBI::DBD with regards to type_info it states: Writing
> DBD::Driver::db::type_info The guidelines on writing this method are
> still not really clear. No sample implementation is available.
>
> A final bit from the documentation, in DBI.pm's POD with regard to
> type_info_all it suggests:
>
> Drivers are also free to return extra driver-specific columns of
> information - though it's recommended that they start at column index
> 50 to leave room for expansion of the DBI/ODBC specification.
>
> Although I've yet to find a driver that has followed this guidance.
> They all appear to have just adding driver specific columns into the
> next available slot (19, 20, etc.).

ODBC added a new column just recently:

perl -le 'use DBI; my $h = DBI->connect; my $x =h->type_info_all; use Data::Dumper; print Dumper($x)'
$VAR1 = [
           {
             'UNSIGNED_ATTRIBUTE' => 9,
             'MAXIMUM_SCALE' => 14,
             'INTERVAL_PRECISION' => 18,
             'CREATE_PARAMS' => 5,
             'NUM_PREC_RADIX' => 17,
             'SEARCHABLE' => 8,
             'USERTYPE' => 19,                  <----------------- see here
             'LOCAL_TYPE_NAME' => 12,
             'AUTO_INCREMENT' => 11,
             'MONEY' => 10,
             'LITERAL_PREFIX' => 3,
             'COLUMN_SIZE' => 2,
             'MINIMUM_SCALE' => 13,
             'TYPE_NAME' => 0,
             'NULLABLE' => 6,
             'DATA_TYPE' => 1,
             'SQL_DATA_TYPE' => 15,
             'CASE_SENSITIVE' => 7,
             'LITERAL_SUFFIX' => 4,
             'SQL_DATETIME_SUB' => 16
           },

> I think at this point, a big question is: Does the DBI just want to
> emulate ODBC's SQLSetTypeInfo function, or does it want to have
> something that goes a bit further and possibly does it a bit better?

I'm ok with someone defining better but I've never had a case where I needed something better. I have code that looks at most of the type_info_all fields and uses this data to work out how to create tables but it was really just a port of some old C code. Mostly, I'm not using any code which creates schemas now, just using existing ones.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com