Re: Defining type_info Was: DBIstcf_DISCARD_STRING and DBIstcf_STRICT

On Tue, 05 Feb 2013 14:33:38 +0000, "Martin J. Evans"
<[email protected]> wrote:

> 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.

Most didn't specify any above 18 at all. see below

> > 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
>            },

DBD::Unify:
$ dbperl -MDP -lE'use DBI;DDumper($d->type_info_all->[0])'
{   AUTO_UNIQUE_VALUE => 11,
    CASE_SENSITIVE   => 7,
    COLUMN_SIZE      => 2,
    CREATE_PARAMS    => 5,
    DATA_TYPE        => 1,
    FIXED_PREC_SCALE => 10,
    INTERVAL_PRECISION => 18,
    LITERAL_PREFIX   => 3,
    LITERAL_SUFFIX   => 4,
    LOCAL_TYPE_NAME  => 12,
    MAXIMUM_SCALE    => 14,
    MINIMUM_SCALE    => 13,
    NULLABLE         => 6,
    NUM_PREC_RADIX   => 17,
    SEARCHABLE       => 8,
    SQL_DATA_TYPE    => 15,
    SQL_DATETIME_SUB => 16,
    TYPE_NAME        => 0,
    UNSIGNED_ATTRIBUTE => 9
    }

DBD::Pg:
$ dbperl -MDP -lE'use DBI;DDumper($d->type_info_all->[0])'
{   AUTO_UNIQUE_VALUE => 11,
    CASE_SENSITIVE   => 7,
    COLUMN_SIZE      => 2,
    CREATE_PARAMS    => 5,
    DATA_TYPE        => 1,
    FIXED_PREC_SCALE => 10,
    INTERVAL_PRECISION => 18,
    LITERAL_PREFIX   => 3,
    LITERAL_SUFFIX   => 4,
    LOCAL_TYPE_NAME  => 12,
    MAXIMUM_SCALE    => 14,
    MINIMUM_SCALE    => 13,
    NULLABLE         => 6,
    NUM_PREC_RADIX   => 17,
    SEARCHABLE       => 8,
    SQL_DATA_TYPE    => 15,
    SQL_DATETIME_SUB => 16,
    TYPE_NAME        => 0,
    UNSIGNED_ATTRIBUTE => 9
    }

DBD::Oracle:
$ dbperl -MDP -lE'use DBI;DDumper($d->type_info_all->[0])'
{   AUTO_UNIQUE_VALUE => 11,
    CASE_SENSITIVE   => 7,
    COLUMN_SIZE      => 2,
    CREATE_PARAMS    => 5,
    DATA_TYPE        => 1,
    FIXED_PREC_SCALE => 10,
    INTERVAL_PRECISION => 18,
    LITERAL_PREFIX   => 3,
    LITERAL_SUFFIX   => 4,
    LOCAL_TYPE_NAME  => 12,
    MAXIMUM_SCALE    => 14,
    MINIMUM_SCALE    => 13,
    NULLABLE         => 6,
    NUM_PREC_RADIX   => 17,
    SEARCHABLE       => 8,
    SQL_DATA_TYPE    => 15,
    SQL_DATETIME_SUB => 16,
    TYPE_NAME        => 0,
    UNSIGNED_ATTRIBUTE => 9
    }

DBD::CSV:
$ dbperl -MDP -lE'use DBI;DDumper($d->type_info_all->[0])'
{   AUTO_INCREMENT   => 11,
    CASE_SENSITIVE   => 7,
    CREATE_PARAMS    => 5,
    DATA_TYPE        => 1,
    LITERAL_PREFIX   => 3,
    LITERAL_SUFFIX   => 4,
    LOCAL_TYPE_NAME  => 12,
    MAXIMUM_SCALE    => 14,
    MINIMUM_SCALE    => 13,
    MONEY            => 10,
    NULLABLE         => 6,
    PRECISION        => 2,
    SEARCHABLE       => 8,
    TYPE_NAME        => 0,
    UNSIGNED_ATTRIBUTE => 9
    }

> > 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

-- 
H.Merijn Brand  http://tux.nl   Perl Monger  http://amsterdam.pm.org/
using perl5.00307 .. 5.17   porting perl5 on HP-UX, AIX, and openSUSE
http://mirrors.develooper.com/hpux/        http://www.test-smoke.org/
http://qa.perl.org   http://www.goldmark.org/jeff/stupid-disclaimers/