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
