On Tue, 05 Feb 2013 14:33:38 +0000, "Martin J. Evans" <martin.ev...@easysoft.com> 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/
