On Wed, 10 Mar 2010 10:25:45 +0000, Martin Evans
<martin.ev...@easysoft.com> wrote:
> H.Merijn Brand and I have been talking this over on #dbi and Tim asked
> for a summary. I don't want to misrepresent what Merijn said, afterall
> he started the thread but I think it boils down to these issues:
>
> 1. the TYPE attribute on a statement is clearly documented as to what it
> should contain - "The values correspond to the international standards
> (ANSI X3.135 and ISO/IEC 9075) which, in general terms, means ODBC".
How feasible is it to include a two-way look-up table in DBI that
includes all the known type numbers and names and two functions that
can be used to convert between the two, so we can all use the same
names?
The docs that the URL points to is not free, and I guess that a table
that states the correct ODBC type names for the known and supported
numbers would be a huge win.
DBI::DBD::odbc_name (3) would return "DECIMAL"
DBI::DBD::odbc_type ("FOAT") would return 6
--8<--- addition to DBI/DBD.pm
my %odbc_types = map { ( $_->[0] => $_->[1], $_->[1] => $_->[0] ) }
[ -5 => "BIGINT" ], # SQL_BIGINT
[ -3 => "VARBINARY" ], # SQL_VARBINARY
[ -2 => "BINARY" ], # SQL_BINARY
[ -1 => "TEXT" ], # SQL_LONGVARCHAR
[ 0 => "UNKNOWN_TYPE" ], # SQL_UNKNOWN_TYPE
[ 1 => "CHAR" ], # SQL_CHAR
[ 2 => "NUMERIC" ], # SQL_NUMERIC
[ 3 => "DECIMAL" ], # SQL_DECIMAL
[ 4 => "INTEGER" ], # SQL_INTEGER
[ 5 => "SMALLINT" ], # SQL_SMALLINT
[ 6 => "FLOAT" ], # SQL_FLOAT
[ 7 => "REAL" ], # SQL_REAL
[ 8 => "DOUBLE PRECISION"], # SQL_DOUBLE
[ 9 => "DATE" ], # SQL_DATE
[ 10 => "TIME" ], # SQL_TIME
[ 11 => "TIMESTAMP" ], # SQL_TIMESTAMP
[ 12 => "VARCHAR" ], # SQL_VARCHAR
[ 16 => "BOOLEAN" ], # SQL_BOOLEAN
[ 19 => "ROW" ], # SQL_ROW
[ 20 => "REF" ], # SQL_REF
[ 30 => "BLOB" ], # SQL_BLOB
[ 40 => "CLOB" ], # SQL_CLOB
# more available ...
;
$odbc_types{DOUBLE} = $odbc_types{"DOUBLE PRECISION"};
sub odbc_type
{
my $t = shift;
defined $t or return $odbc_type{UNKNOWN_TYPE};
$t =~ m/^-?[0-9]+$/ and return 0 + $t;
$t = $odbc_types{uc $t} || $t;
return $t;
} # odbc_type
sub odbc_name
{
my $t = shift;
defined $t or return $odbc_type{0};
$t =~ m/^-?[0-9]+$/ or return $t;
$t = $odbc_types{uc $t} || $t;
return $t;
} # odbc_name
-->8---
> However, it is not clear from the docs what TYPE_NAME and DATA_TYPE
> columns should be in the column_info method and how they compare with
> the same named columns returned by the type_info method.
>
> e.g., for the column_info method:
>
> DATA_TYPE: The concise data type code
>
> this could be the database internal type number or the ODBC type
> the current opinion is that it should be the ODBC type and extra
> keys added for the internal type (e.g., ora_type, uni_type depending
> on DBD prefix) NOTE mysql already adds "mysql_is_auto_increment",
> "mysql_is_pri_key", "mysql_type_name", "mysql_values" keys.
>
> TYPE_NAME: A data source dependent data type name.
>
> for type_info:
>
> DATA_TYPE (integer) SQL data type number.
> TYPE_NAME (string) Data type name for use in CREATE TABLE statements
>
> Proposed Solution: document column_info DATA_TYPE as being the same as
> {TYPE} but allow DBDs to add other keys to the column_info result (some
> already do).
Yes. Exactly.
<brainstorming>
As a bonus, it would be extremely useful if we could have a way that
"keys" works on handles, so you do not need private_attribute_info ()
to query the extra fields. Furthermore, that method should have a
fallback for DBD's that *do* generate private attributes, but do not
implement the method. (Yes, digging into this will lift DBD::Unify to a
higher level with more features being implemented and checked along the
way)
my $sth = $dbh->prepare ("select foo from bar");
$sth->execute;
In DBD::Oracle you get extra:
{ ora_auto_lob => undef,
ora_check_sql => undef,
ora_csform => undef,
ora_est_row_width => undef,
ora_field => undef,
ora_lengths => undef,
ora_maxdata_size => undef,
ora_parse_lang => undef,
ora_placeholders => undef,
ora_rowid => undef,
ora_type => undef,
ora_types => undef
}
Note that it does not have a ora_name or ora_type_name.
but it has both ora_type *and* ora_types. Interesting.
In DBD::Pg you get extra:
{ pg_async => undef,
pg_bound => undef,
pg_cmd_status => undef,
pg_current_row => undef,
pg_direct => undef,
pg_numbound => undef,
pg_oid_status => undef,
pg_placeholder_dollaronly => undef,
pg_prepare_name => undef,
pg_prepare_now => undef,
pg_segments => undef,
pg_server_prepare => undef,
pg_size => undef,
pg_type => undef
}
Note that it does not have a pg_name or pg_type_name.
I got back no info from MySQL
foreach my $attribute (keys %{$sth}) {
...
or, to be more in OO style
foreach my $attribute ($sth->attributes) {
...
where attributes () would return the combined list of default
attributes (NAME, NAME_lc, NAME_uc, TYPE, PRECISION, SCALE,
NULLABLE, ...) *plus* the currently available attributes from the
current driver would make porting applications immensely much easier
In this case, it could be documented that the attributes () methods
returns a *list* of available keys, and hence does not have to honour
FetchHashKeyName and returns the list as-is.
That way, a portable application could scan that list for
m/^[a-z]{2,4}_(type|name|type_name)$/ to get to the internal codings
</brainstorming>
--
H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
