Re: DBD::ADO: get_info()
Thanks Steffen, I've been very busy ... hopefully I can get a new
DBD::ADO release out soon.
Tom
On Mon, Mar 04, 2002 at 04:28:08PM +0100, Steffen Goeldner wrote:
> Steffen Goeldner wrote:
> >
> > Tim Bunce wrote:
> > >
> > > On Fri, Feb 08, 2002 at 03:16:38PM +0100, Steffen Goeldner wrote:
> >
> > > >
> > > > Ok. Or, I'm thinking about a method ado_schema_dbinfo_literal(), caching
> > > > all data of the adSchemaDBInfoLiterals pseudo-table, ...
> > > > Attached are some results for the MSDAORA and Jet Provider.
> > >
> > > Seems like a good idea.
> >
> > Fine! Here a code snippet:
> >
> > [...]
>
> And here is the patch:
> diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm
>DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm
> *** DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pmThu Jan 01 01:00:00 1970
> --- DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm Fri Feb 08 22:31:11 2002
> ***
> *** 0
> --- 1,64
> + package DBD::ADO::GetInfo;
> +
> + use DBD::ADO();
> +
> + my $fmt = '%02d.%02d.%1d%1d%1d%1d'; # ODBC version string: ##.##.#
> +
> + my $sql_driver_ver = sprintf $fmt, split(/[\._]/, $DBD::ADO::VERSION);
> +
> + sub sql_catalog_name_separator {
> + my $dbh = shift;
> + DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'CATALOG_SEPARATOR') ||'.';
> + }
> + sub sql_concat_null_behavior {
> + { 1 => 0 # SQL_CB_NULL
> + , 2 => 1 # SQL_CB_NON_NULL
> + }->{$_[0]->{ado_conn}->Properties->{'NULL Concatenation Behavior'}{Value}};
> + }
> + sub sql_identifier_case {
> + { 1 => 1 # SQL_IC_UPPER
> + , 2 => 2 # SQL_IC_LOWER
> + , 4 => 3 # SQL_IC_SENSITIVE
> + , 8 => 4 # SQL_IC_MIXED
> + }->{$_[0]->{ado_conn}->Properties->{'Identifier Case Sensitivity'}{Value}};
> + }
> + sub sql_identifier_quote_char {
> + my $dbh = shift;
> + DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE') ||
> + DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE_PREFIX') ||'"';
> + }
> + sub sql_keywords {
> + my $dbh = shift;
> + my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
> + my @Keywords = ();
> + while ( my $row = $sth->fetch ) {
> + push @Keywords, $row->[0];
> + }
> + return join ',', @Keywords;
> + }
> +
> + %info = (
> + 41 => \&sql_catalog_name_separator # SQL_CATALOG_NAME_SEPARATOR
> + ,22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
> + , 6 => $INC{'DBD/ADO.pm'}# SQL_DRIVER_NAME # XXX
> + , 7 => $sql_driver_ver # SQL_DRIVER_VER# XXX
> + ,28 => \&sql_identifier_case # SQL_IDENTIFIER_CASE
> + ,29 => \&sql_identifier_quote_char# SQL_IDENTIFIER_QUOTE_CHAR
> + ,89 => \&sql_keywords # SQL_KEYWORDS
> + );
> +
> + %odbc2ado = (
> + 114 => 'Catalog Location' # SQL_CATALOG_LOCATION
> + ,42 => 'Catalog Term' # SQL_CATALOG_TERM
> + , 2 => 'Data Source Name' # SQL_DATA_SOURCE_NAME
> + ,17 => 'DBMS Name'# SQL_DBMS_NAME
> + ,18 => 'DBMS Version' # SQL_DBMS_VERSION
> + # 6 => 'Provider Name'# SQL_DRIVER_NAME # XXX
> + # 7 => 'Provider Version' # SQL_DRIVER_VER# XXX
> + ,40 => 'Procedure Term' # SQL_PROCEDURE_TERM
> + ,39 => 'Schema Term' # SQL_SCHEMA_TERM
> + ,45 => 'Table Term' # SQL_TABLE_TERM
> + ,47 => 'User Name'# SQL_USER_NAME
> + );
> +
> + 1;
> diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm DBD-ADO-2.4.02/lib/DBD/ADO.pm
> *** DBD-ADO-2.4.02-orig/lib/DBD/ADO.pmThu Dec 13 01:17:30 2001
> --- DBD-ADO-2.4.02/lib/DBD/ADO.pm Sat Mar 02 20:55:50 2002
> ***
> *** 754,759
> --- 754,792
> $sth;
> }
>
> + sub get_info {
> + my($dbh, $info_type) = @_;
> + $info_type = int($info_type);
> + require DBD::ADO::GetInfo;
> + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
> + return
>$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
> + }
> + my $v = $DBD::ADO::GetInfo::info{$info_type};
> + if (ref $v eq 'CODE') {
> + my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
> + return $get_info_cache->{$info_type} if exists
>$get_info_cache->{$info_type};
> + $v = $v->($dbh);
> + return $$v if ref $v eq 'SCALAR'; # don't cache!
> + $get_info_cache->{$info_type} = $v;
> + }
> + return $v;
> + }
> +
> + sub ado_schema_dbinfo_literal {
> + my($dbh, $literal_name) = @_;
> + my $cache = $dbh->{ado_schema_dbinfo_literal_cache};
> + unless ( defined $cache ) {
> + $dbh->trace_
Re: DBD::ADO: get_info()
Steffen Goeldner wrote:
>
> Tim Bunce wrote:
> >
> > On Fri, Feb 08, 2002 at 03:16:38PM +0100, Steffen Goeldner wrote:
>
> > >
> > > Ok. Or, I'm thinking about a method ado_schema_dbinfo_literal(), caching
> > > all data of the adSchemaDBInfoLiterals pseudo-table, ...
> > > Attached are some results for the MSDAORA and Jet Provider.
> >
> > Seems like a good idea.
>
> Fine! Here a code snippet:
>
> [...]
And here is the patch:
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm
DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm Thu Jan 01 01:00:00 1970
--- DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm Fri Feb 08 22:31:11 2002
***
*** 0
--- 1,64
+ package DBD::ADO::GetInfo;
+
+ use DBD::ADO();
+
+ my $fmt = '%02d.%02d.%1d%1d%1d%1d'; # ODBC version string: ##.##.#
+
+ my $sql_driver_ver = sprintf $fmt, split(/[\._]/, $DBD::ADO::VERSION);
+
+ sub sql_catalog_name_separator {
+ my $dbh = shift;
+ DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'CATALOG_SEPARATOR') ||'.';
+ }
+ sub sql_concat_null_behavior {
+ { 1 => 0 # SQL_CB_NULL
+ , 2 => 1 # SQL_CB_NON_NULL
+ }->{$_[0]->{ado_conn}->Properties->{'NULL Concatenation Behavior'}{Value}};
+ }
+ sub sql_identifier_case {
+ { 1 => 1 # SQL_IC_UPPER
+ , 2 => 2 # SQL_IC_LOWER
+ , 4 => 3 # SQL_IC_SENSITIVE
+ , 8 => 4 # SQL_IC_MIXED
+ }->{$_[0]->{ado_conn}->Properties->{'Identifier Case Sensitivity'}{Value}};
+ }
+ sub sql_identifier_quote_char {
+ my $dbh = shift;
+ DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE') ||
+ DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE_PREFIX') ||'"';
+ }
+ sub sql_keywords {
+ my $dbh = shift;
+ my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
+ my @Keywords = ();
+ while ( my $row = $sth->fetch ) {
+ push @Keywords, $row->[0];
+ }
+ return join ',', @Keywords;
+ }
+
+ %info = (
+ 41 => \&sql_catalog_name_separator # SQL_CATALOG_NAME_SEPARATOR
+ ,22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
+ , 6 => $INC{'DBD/ADO.pm'}# SQL_DRIVER_NAME # XXX
+ , 7 => $sql_driver_ver # SQL_DRIVER_VER# XXX
+ ,28 => \&sql_identifier_case # SQL_IDENTIFIER_CASE
+ ,29 => \&sql_identifier_quote_char# SQL_IDENTIFIER_QUOTE_CHAR
+ ,89 => \&sql_keywords # SQL_KEYWORDS
+ );
+
+ %odbc2ado = (
+ 114 => 'Catalog Location' # SQL_CATALOG_LOCATION
+ ,42 => 'Catalog Term' # SQL_CATALOG_TERM
+ , 2 => 'Data Source Name' # SQL_DATA_SOURCE_NAME
+ ,17 => 'DBMS Name'# SQL_DBMS_NAME
+ ,18 => 'DBMS Version' # SQL_DBMS_VERSION
+ # 6 => 'Provider Name'# SQL_DRIVER_NAME # XXX
+ # 7 => 'Provider Version' # SQL_DRIVER_VER# XXX
+ ,40 => 'Procedure Term' # SQL_PROCEDURE_TERM
+ ,39 => 'Schema Term' # SQL_SCHEMA_TERM
+ ,45 => 'Table Term' # SQL_TABLE_TERM
+ ,47 => 'User Name'# SQL_USER_NAME
+ );
+
+ 1;
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm DBD-ADO-2.4.02/lib/DBD/ADO.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm Thu Dec 13 01:17:30 2001
--- DBD-ADO-2.4.02/lib/DBD/ADO.pm Sat Mar 02 20:55:50 2002
***
*** 754,759
--- 754,792
$sth;
}
+ sub get_info {
+ my($dbh, $info_type) = @_;
+ $info_type = int($info_type);
+ require DBD::ADO::GetInfo;
+ if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
+ return
+$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
+ }
+ my $v = $DBD::ADO::GetInfo::info{$info_type};
+ if (ref $v eq 'CODE') {
+ my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
+ return $get_info_cache->{$info_type} if exists
+$get_info_cache->{$info_type};
+ $v = $v->($dbh);
+ return $$v if ref $v eq 'SCALAR'; # don't cache!
+ $get_info_cache->{$info_type} = $v;
+ }
+ return $v;
+ }
+
+ sub ado_schema_dbinfo_literal {
+ my($dbh, $literal_name) = @_;
+ my $cache = $dbh->{ado_schema_dbinfo_literal_cache};
+ unless ( defined $cache ) {
+ $dbh->trace_msg("-> ado_schema_dbinfo_literal: filling
+cache\n");
+ $cache = $dbh->{ado_schema_dbinfo_literal_cache} = {};
+ my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
+ while ( my $row = $sth->fetch ) {
+ $cache->{$row->[0]
Re: DBD::ADO: get_info()
Tim Bunce wrote:
>
> On Fri, Feb 08, 2002 at 03:16:38PM +0100, Steffen Goeldner wrote:
> >
> > Ok. Or, I'm thinking about a method ado_schema_dbinfo_literal(), caching
> > all data of the adSchemaDBInfoLiterals pseudo-table, ...
> > Attached are some results for the MSDAORA and Jet Provider.
>
> Seems like a good idea.
Fine! Here a code snippet:
sub ado_schema_dbinfo_literal {
my($dbh, $literal_name) = @_;
my $cache = $dbh->{ado_schema_dbinfo_literal_cache};
unless ( defined $cache ) {
$cache = $dbh->{ado_schema_dbinfo_literal_cache} = {};
my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
while ( my $row = $sth->fetch ) {
$cache->{$row->[0]} = [ @$row ];
}
}
my $row = $cache->{$literal_name};
return $row->[1] unless wantarray; # literal value
return @$row;
}
Now, in DBD::ADO::GetInfo, we can use it:
sub sql_identifier_quote_char {
my $dbh = shift;
DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE') ||
DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'QUOTE_PREFIX') ||'"';
}
sub sql_catalog_name_separator {
my $dbh = shift;
DBD::ADO::db::ado_schema_dbinfo_literal($dbh,'CATALOG_SEPARATOR') ||'.';
}
Steffen
Re: DBD::ADO: get_info()
On Fri, Feb 08, 2002 at 03:16:38PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> >
> > On Thu, Feb 07, 2002 at 11:13:22AM +0100, Steffen Goeldner wrote:
>
> > > + sub sql_identifier_quote_char {
> > > + my $dbh = shift;
> > > + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> > > + while ( my $row = $sth->fetch ) {
> > > + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> > > + }
> > > + return undef;
> > > + }
> >
> > Probably need a $sth->finish in there.
>
> Ok. Or, I'm thinking about a method ado_schema_dbinfo_literal(), caching
> all data of the adSchemaDBInfoLiterals pseudo-table, ...
> Attached are some results for the MSDAORA and Jet Provider.
Seems like a good idea.
> > > + sub sql_keywords {
> > > + my $dbh = shift;
> > > + my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
> > > + my @Keywords = ();
> > > + while ( my $row = $sth->fetch ) {
> > > + push @Keywords, $row->[0];
> > > + }
> > > + return join ',', @Keywords;
> > > + }
> >
> > Or maybe:
> > return join ',', @{ $dbh->selectcol_arrayref($sth)||[] };
>
> It doesn't work. It would work if I drop the execute() in
> selectcol_arrayref() ... ???
Ah, maybe I should add a fetchcol_array* methods.
FYI, the guts of selectcol_arrayref look like:
my @columns = ($attr->{Columns}) ? @{$attr->{Columns}} : (1);
my @values = (undef) x @columns;
my $idx = 0;
for (@columns) {
$sth->bind_col($_, \$values[$idx++]) || return;
}
my @col;
push @col, @values while $sth->fetch; # fetch fetch loop
return \@col;
Which is kind'a cute :)
But it's not worth worrying about here as it all gets cached anyway.
Tim.
Re: DBD::ADO: get_info()
Tim Bunce wrote:
>
> On Thu, Feb 07, 2002 at 11:13:22AM +0100, Steffen Goeldner wrote:
> > + sub sql_identifier_quote_char {
> > + my $dbh = shift;
> > + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> > + while ( my $row = $sth->fetch ) {
> > + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> > + }
> > + return undef;
> > + }
>
> Probably need a $sth->finish in there.
Ok. Or, I'm thinking about a method ado_schema_dbinfo_literal(), caching
all data of the adSchemaDBInfoLiterals pseudo-table, ...
Attached are some results for the MSDAORA and Jet Provider.
> > + sub sql_keywords {
> > + my $dbh = shift;
> > + my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
> > + my @Keywords = ();
> > + while ( my $row = $sth->fetch ) {
> > + push @Keywords, $row->[0];
> > + }
> > + return join ',', @Keywords;
> > + }
>
> Or maybe:
> return join ',', @{ $dbh->selectcol_arrayref($sth)||[] };
It doesn't work. It would work if I drop the execute() in
selectcol_arrayref() ... ???
> > + %info = (
> > + 22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
> > + , 6 => 'DBD/ADO.pm' # SQL_DRIVER_NAME # XXX
>
> Maybe $INC{"DBD/ADO.pm"} instead.
Ok.
> > --- DBD-ADO-2.4.02/lib/DBD/ADO.pm Wed Feb 06 20:20:22 2002
> >
> > + sub get_info {
> > + my($dbh, $info_type) = @_;
> > + require DBD::ADO::GetInfo;
> > + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
>
> int($info_type) like:
Sorry, I forgot that.
Steffen
MSDAORA
---
ADO\OpenSchema adSchemaDBInfoLiterals
Literal Literal Invalid Invalid Literal Supported Maxlen
Name Value Chars Starting
Chars
--- --- --- - --
BINARY_LITERAL 1 1 4000
CATALOG_NAME2 1 4294967295
CATALOG_SEPARATOR @ 3 1 1
CHAR_LITERAL4 1 4000
COLUMN_ALIAS5 1 30
COLUMN_NAME 6 1 30
CORRELATION_NAME7 1 30
CURSOR_NAME 8 1 30
ESCAPE_PERCENT\%9 1 2
ESCAPE_UNDERSCORE \_ 10 1 2
INDEX_NAME 11 1 30
LIKE_PERCENT %12 1 1
LIKE_UNDERSCORE _13 1 1
PROCEDURE_NAME 14 1 61
SCHEMA_NAME16 1 30
TABLE_NAME 17 1 30
TEXT_COMMAND 18 1 4294967295
USER_NAME 19 1 30
VIEW_NAME 20 1 30
QUOTE "15 1 1
QUOTE_SUFFIX "28 1 1
SCHEMA_SEPARATOR .27 1 1
Microsoft.Jet.OLEDB.4.0
---
ADO\OpenSchema adSchemaDBInfoLiterals
Literal Literal Invalid Invalid Literal Supported Maxlen
Name Value Chars Starting
Chars
--- --- --- - --
BINARY_LITERAL 1 1255
CHAR_LITERAL .!`[] 4 1255
COLUMN_ALIAS .!`[] 5 1 64
COLUMN_NAME .!`[] 6 1 64
ESCAPE_PERCENT [ 9 1 1
ESCAPE_UNDERSCORE[ 10 1 1
ESCAPE_PERCENT_SUFFIX] 29 1 1
ESCAPE_UNDERSCORE_SUFFIX ] 30 1 1
INDEX_NAME .!`[] 11 1 64
LIKE_PERCENT % 12 1 1
LIKE_UNDERSCORE _ 13 1 1
PROCEDURE_NAME .!`[] 14 1 64
TABLE_N
RE: DBD::ADO: get_info()
> From: Tim Bunce [mailto:[EMAIL PROTECTED]]
> On Thu, Feb 07, 2002 at 11:13:22AM +0100, Steffen Goeldner wrote:
[...]
> > + sub sql_identifier_quote_char {
> > + my $dbh = shift;
> > + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> > + while ( my $row = $sth->fetch ) {
> > + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX
> > + }
> > + return undef;
> > + }
>
> Probably need a $sth->finish in there.
No that is unneccessary!
The DBI docs (version 1.201) state that $sth->finish "Indicates that no more
data will be fetched from this statement handle before it is either executed
again or destroyed."
The $sth will be destroyed during the 'return' processing, so a $sth->finish
should be quite superfluos here.
--
Henrik Tougaard, Copenhagen, Denmark
- an un'finish'ed, danish DBD::Ingres maintainer.
Re: DBD::ADO: get_info()
On Thu, Feb 07, 2002 at 11:13:22AM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> >
> > On Wed, Feb 06, 2002 at 09:48:54AM +0100, Steffen Goeldner wrote:
> [...]
>
> > > I'm not sure if we can cache *every* value. Is it possible that a DBMS
> > > allows to change some properties at runtime (something like 'ALTER
> > > SESSION SET ...')?
> >
> > Umm [grumble], you're right. How about this:
> >
> > if (ref $v eq 'CODE') {
> > my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
> > return $get_info_cache->{$info_type} if exists
>$get_info_cache->{$info_type};
> > $v = $v->($dbh);
> > $get_info_cache->{$info_type} = ($v=$$v) if ref $v eq 'SCALAR';
> -
> Here, the SCALAR-ref indicates: Cache it! (missing 'not'?)
> I'd rather replace the pragma 'use short;' with 'no obfuscate;' ;-)
:-)
> + sub sql_identifier_quote_char {
> + my $dbh = shift;
> + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> + while ( my $row = $sth->fetch ) {
> + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> + }
> + return undef;
> + }
Probably need a $sth->finish in there.
> + sub sql_keywords {
> + my $dbh = shift;
> + my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
> + my @Keywords = ();
> + while ( my $row = $sth->fetch ) {
> + push @Keywords, $row->[0];
> + }
> + return join ',', @Keywords;
> + }
Or maybe:
return join ',', @{ $dbh->selectcol_arrayref($sth)||[] };
> + %info = (
> + 22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
> + , 6 => 'DBD/ADO.pm' # SQL_DRIVER_NAME # XXX
Maybe $INC{"DBD/ADO.pm"} instead.
> --- DBD-ADO-2.4.02/lib/DBD/ADO.pm Wed Feb 06 20:20:22 2002
>
> + sub get_info {
> + my($dbh, $info_type) = @_;
> + require DBD::ADO::GetInfo;
> + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
int($info_type) like:
> + my $v = $DBD::ADO::GetInfo::info{int($info_type)};
Many thanks again Steffen.
Tim.
Re: DBD::ADO: get_info(), Jet
Attached are the results for the Microsoft.Jet.OLEDB.4.0 Provider. The ODBC driver http:[EMAIL PROTECTED]/msg00525.html and DBD::ADO return quite different results (for various reasons): ODBC DBD::ADO - - SQL_CATALOG_TERM DATABASE Database SQL_DATA_SOURCE_NAME mytestDBD-ADO-2.4.02\mytest.mdb SQL_DBMS_NAMEACCESSMS Jet SQL_DRIVER_NAME odbcjt32.dll DBD/ADO.pm SQL_DRIVER_VER 04.00.601902.04. SQL_IDENTIFIER_CASE 3 4 SQL_KEYWORDS ALPHANUMERIC, ... AdminDB, ... SQL_OWNER_TERM Schema SQL_PROCEDURE_TERM QUERY STORED QUERY SQL_QUALIFIER_TERM DATABASE Database SQL_SCHEMA_TERMSchema SQL_TABLE_TERM TABLE Table SQL_USER_NAMEadmin Admin Most remarkable: SQL_IDENTIFIER_CASE. AFAIK, Access identifiers are not case-sensitive. Steffen SQL_CATALOG_LOCATION 1 SQL_CATALOG_TERM Database SQL_CONCAT_NULL_BEHAVIOR 1 SQL_DATA_SOURCE_NAME DBD-ADO-2.4.02\mytest.mdb SQL_DBMS_NAME MS Jet SQL_DBMS_VER 04.00. SQL_DBMS_VERSION 04.00. SQL_DRIVER_NAMEDBD/ADO.pm SQL_DRIVER_VER 02.04. SQL_IDENTIFIER_CASE4 SQL_IDENTIFIER_QUOTE_CHAR ` SQL_KEYWORDS AdminDB,Alphanumeric,Autoincrement,BAND,Binary,BNOT,BOR,BXOR,Byte,Comp,Compression,Container,Counter,CreateDB,Currency,Database,DateTime,Disallow,ExclusiveConnect,Float4,Float8,General,Guid,IEEEDouble,IEEESingle,Ignore,Image,Index,Inheritable,Integer1,Integer2,Integer4,Logical,Logical1,Long,LongBinary,LongChar,LongText,Memo,Money,Note,Number,Object,OLEObject,OwnerAccess,Pad,Parameters,Password,Percent,Pivot,Proc,SelectSchema,SelectSecurity,Short,Single,Space,String,Tableid,Text,Top,Transform,Uniqueidentifier,UpdateIdentity,UpdateOwner,UpdateSecurity,Varbinary,YesNo SQL_OWNER_TERM Schema SQL_PROCEDURE_TERM STORED QUERY SQL_QUALIFIER_LOCATION 1 SQL_QUALIFIER_TERM Database SQL_SCHEMA_TERMSchema SQL_TABLE_TERM Table SQL_USER_NAME Admin
Re: DBD::ADO: get_info(), MSDAORA
Attached are the results for the MSDAORA Provider. Steffen SQL_CATALOG_LOCATION 2 SQL_CATALOG_TERM Database Link SQL_CONCAT_NULL_BEHAVIOR 1 SQL_DATA_SOURCE_NAME tst SQL_DBMS_NAME Oracle SQL_DBMS_VER 08.01. Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production SQL_DBMS_VERSION 08.01. Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production SQL_DRIVER_NAMEDBD/ADO.pm SQL_DRIVER_VER 02.04. SQL_IDENTIFIER_CASE1 SQL_IDENTIFIER_QUOTE_CHAR " SQL_KEYWORDS ACCESS,AUDIT,CLUSTER,COMMENT,COMPRESS,EXCLUSIVE,FILE,IDENTIFIED,INCREMENT,INDEX,INITIAL,LOCK,LONG,MAXEXTENTS,MINUS,MODE,MODIFY,NOAUDIT,NOCOMPRESS,NOWAIT,NUMBER,OFFLINE,ONLINE,PCTFREE,RAW,RENAME,RESOURCE,ROW,ROWID,ROWLABEL,ROWNUM,SHARE,START,SUCCESSFUL,SYNONYM,SYSDATE,UID,VALIDATE,VARCHAR2 SQL_OWNER_TERM Owner SQL_PROCEDURE_TERM PL/SQL Stored Procedure SQL_QUALIFIER_LOCATION 2 SQL_QUALIFIER_TERM Database Link SQL_SCHEMA_TERMOwner SQL_TABLE_TERM Table SQL_USER_NAME TST
Re: DBD::ADO: get_info()
Tim Bunce wrote:
>
> On Wed, Feb 06, 2002 at 09:48:54AM +0100, Steffen Goeldner wrote:
[...]
> > I'm not sure if we can cache *every* value. Is it possible that a DBMS
> > allows to change some properties at runtime (something like 'ALTER
> > SESSION SET ...')?
>
> Umm [grumble], you're right. How about this:
>
> if (ref $v eq 'CODE') {
> my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
> return $get_info_cache->{$info_type} if exists
>$get_info_cache->{$info_type};
> $v = $v->($dbh);
> $get_info_cache->{$info_type} = ($v=$$v) if ref $v eq 'SCALAR';
-
Here, the SCALAR-ref indicates: Cache it! (missing 'not'?)
I'd rather replace the pragma 'use short;' with 'no obfuscate;' ;-)
> }
>
> So caching is the default and a CODE ref can indicate that it doesn't want the
> value cached by returning a ref to the value.
>
> Slightly odd but I think it's probably better than adding cache
> logic to many individual subs.
Steffen
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm
DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm Thu Jan 01 01:00:00 1970
--- DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm Mon Feb 04 20:37:27 2002
***
*** 0
--- 1,62
+ package DBD::ADO::GetInfo;
+
+ use DBD::ADO();
+
+ my $fmt = '%02d.%02d.%1d%1d%1d%1d'; # ODBC version string: ##.##.#
+
+ my $sql_driver_ver = sprintf $fmt, split (/\./, $DBD::ADO::VERSION);
+
+ sub sql_concat_null_behavior {
+ { 1 => 0 # SQL_CB_NULL
+ , 2 => 1 # SQL_CB_NON_NULL
+ }->{$_[0]->{ado_conn}->Properties->{'NULL Concatenation Behavior'}{Value}};
+ }
+ sub sql_identifier_case {
+ { 1 => 1 # SQL_IC_UPPER
+ , 2 => 2 # SQL_IC_LOWER
+ , 4 => 3 # SQL_IC_SENSITIVE
+ , 8 => 4 # SQL_IC_MIXED
+ }->{$_[0]->{ado_conn}->Properties->{'Identifier Case Sensitivity'}{Value}};
+ }
+ sub sql_identifier_quote_char {
+ my $dbh = shift;
+ my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
+ while ( my $row = $sth->fetch ) {
+ return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
+QUOTE_SUFFIX
+ }
+ return undef;
+ }
+ sub sql_keywords {
+ my $dbh = shift;
+ my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
+ my @Keywords = ();
+ while ( my $row = $sth->fetch ) {
+ push @Keywords, $row->[0];
+ }
+ return join ',', @Keywords;
+ }
+
+ %info = (
+ 22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
+ , 6 => 'DBD/ADO.pm' # SQL_DRIVER_NAME # XXX
+ , 7 => $sql_driver_ver # SQL_DRIVER_VER# XXX
+ ,28 => \&sql_identifier_case # SQL_IDENTIFIER_CASE
+ ,29 => \&sql_identifier_quote_char# SQL_IDENTIFIER_QUOTE_CHAR
+ ,89 => \&sql_keywords # SQL_KEYWORDS
+ );
+
+ %odbc2ado = (
+ 114 => 'Catalog Location' # SQL_CATALOG_LOCATION
+ ,42 => 'Catalog Term' # SQL_CATALOG_TERM
+ , 2 => 'Data Source Name' # SQL_DATA_SOURCE_NAME
+ ,17 => 'DBMS Name'# SQL_DBMS_NAME
+ ,18 => 'DBMS Version' # SQL_DBMS_VERSION
+ # 6 => 'Provider Name'# SQL_DRIVER_NAME # XXX
+ # 7 => 'Provider Version' # SQL_DRIVER_VER# XXX
+ ,40 => 'Procedure Term' # SQL_PROCEDURE_TERM
+ ,39 => 'Schema Term' # SQL_SCHEMA_TERM
+ ,45 => 'Table Term' # SQL_TABLE_TERM
+ ,47 => 'User Name'# SQL_USER_NAME
+ );
+
+ 1;
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm DBD-ADO-2.4.02/lib/DBD/ADO.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm Thu Dec 13 01:17:30 2001
--- DBD-ADO-2.4.02/lib/DBD/ADO.pm Wed Feb 06 20:20:22 2002
***
*** 755,760
--- 755,777
}
+ sub get_info {
+ my($dbh, $info_type) = @_;
+ require DBD::ADO::GetInfo;
+ if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
+ return
+$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
+ }
+ my $v = $DBD::ADO::GetInfo::info{int($info_type)};
+ if (ref $v eq 'CODE') {
+ my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
+ return $get_info_cache->{$info_type} if exists
+$get_info_cache->{$info_type};
+ $v = $v->($dbh);
+ return $$v if ref $v eq 'SCALAR'; # don't cache!
+ $get_info_cache->{$info_type} = $v;
+ }
+ return $v;
+ }
+
sub table_info {
my($dbh, $attribs) = @_;
my @tp;
Re: DBD::ADO: get_info()
On Wed, Feb 06, 2002 at 09:48:54AM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> >
> > On Tue, Feb 05, 2002 at 04:37:48PM +0100, Steffen Goeldner wrote:
> > > + sub sql_identifier_quote_char {
> > > + my $dbh = shift;
> > > + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> > > + while ( my $row = $sth->fetch ) {
> > > + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> > > + }
> > > + return undef;
> > > + }
> >
> > Umm, expensive. Need a way to cache these...
>
> O.k., but see below ...
>
> > > + sub get_info {
> > > + my($dbh, $info_type) = @_;
> > > + require DBD::ADO::GetInfo;
> > > + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
> > > + return
>$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
> > > + }
> > > + my $v = $DBD::ADO::GetInfo::info{int($info_type)};
> > > + $v = $v->($dbh) if ref $v eq 'CODE';
> >
> > How about we change that last line to
> >
> > if (ref $v eq 'CODE') {
> > my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
> > return $get_info_cache->{int($info_type)} if exists
>$get_info_cache->{int($info_type)};
> > $v = $get_info_cache->{int($info_type)} = $v->($dbh);
>
> I'm not sure if we can cache *every* value. Is it possible that a DBMS
> allows to change some properties at runtime (something like 'ALTER
> SESSION SET ...')?
Umm [grumble], you're right. How about this:
if (ref $v eq 'CODE') {
my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
return $get_info_cache->{$info_type} if exists
$get_info_cache->{$info_type};
$v = $v->($dbh);
$get_info_cache->{$info_type} = ($v=$$v) if ref $v eq 'SCALAR';
}
So caching is the default and a CODE ref can indicate that it doesn't want the
value cached by returning a ref to the value.
Slightly odd but I think it's probably better than adding cache
logic to many individual subs.
Tim.
Re: DBD::ADO: get_info()
Tim Bunce wrote:
>
> On Tue, Feb 05, 2002 at 04:37:48PM +0100, Steffen Goeldner wrote:
> > + sub sql_identifier_quote_char {
> > + my $dbh = shift;
> > + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> > + while ( my $row = $sth->fetch ) {
> > + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> > + }
> > + return undef;
> > + }
>
> Umm, expensive. Need a way to cache these...
O.k., but see below ...
>
> > + sub get_info {
> > + my($dbh, $info_type) = @_;
> > + require DBD::ADO::GetInfo;
> > + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
> > + return
>$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
> > + }
> > + my $v = $DBD::ADO::GetInfo::info{int($info_type)};
> > + $v = $v->($dbh) if ref $v eq 'CODE';
>
> How about we change that last line to
>
> if (ref $v eq 'CODE') {
> my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
> return $get_info_cache->{int($info_type)} if exists
>$get_info_cache->{int($info_type)};
> $v = $get_info_cache->{int($info_type)} = $v->($dbh);
I'm not sure if we can cache *every* value. Is it possible that a DBMS
allows to change some properties at runtime (something like 'ALTER
SESSION SET ...')?
> }
>
> There should probably be a
> $info_type = int($info_type)
> at the top to save the multiple ones later.
O.k.
Steffen
Re: DBD::ADO: get_info()
On Tue, Feb 05, 2002 at 04:37:48PM +0100, Steffen Goeldner wrote:
> + sub sql_identifier_quote_char {
> + my $dbh = shift;
> + my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
> + while ( my $row = $sth->fetch ) {
> + return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
>QUOTE_SUFFIX
> + }
> + return undef;
> + }
Umm, expensive. Need a way to cache these...
> + sub get_info {
> + my($dbh, $info_type) = @_;
> + require DBD::ADO::GetInfo;
> + if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
> + return
>$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
> + }
> + my $v = $DBD::ADO::GetInfo::info{int($info_type)};
> + $v = $v->($dbh) if ref $v eq 'CODE';
How about we change that last line to
if (ref $v eq 'CODE') {
my $get_info_cache = $dbh->{dbd_get_info_cache} ||= {};
return $get_info_cache->{int($info_type)} if exists
$get_info_cache->{int($info_type)};
$v = $get_info_cache->{int($info_type)} = $v->($dbh);
}
There should probably be a
$info_type = int($info_type)
at the top to save the multiple ones later.
Tim.
Re: DBD::ADO: get_info()
Similar to DBD::Oracle, here a implementation for DBD::ADO.
Steffen
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm
DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO/GetInfo.pm Thu Jan 01 01:00:00 1970
--- DBD-ADO-2.4.02/lib/DBD/ADO/GetInfo.pm Mon Feb 04 20:37:27 2002
***
*** 0
--- 1,62
+ package DBD::ADO::GetInfo;
+
+ use DBD::ADO();
+
+ my $fmt = '%02d.%02d.%1d%1d%1d%1d'; # ODBC version string: ##.##.#
+
+ my $sql_driver_ver = sprintf $fmt, split (/\./, $DBD::ADO::VERSION);
+
+ sub sql_concat_null_behavior {
+ { 1 => 0 # SQL_CB_NULL
+ , 2 => 1 # SQL_CB_NON_NULL
+ }->{$_[0]->{ado_conn}->Properties->{'NULL Concatenation Behavior'}{Value}};
+ }
+ sub sql_identifier_case {
+ { 1 => 1 # SQL_IC_UPPER
+ , 2 => 2 # SQL_IC_LOWER
+ , 4 => 3 # SQL_IC_SENSITIVE
+ , 8 => 4 # SQL_IC_MIXED
+ }->{$_[0]->{ado_conn}->Properties->{'Identifier Case Sensitivity'}{Value}};
+ }
+ sub sql_identifier_quote_char {
+ my $dbh = shift;
+ my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
+ while ( my $row = $sth->fetch ) {
+ return $row->[1] if $row->[0] eq 'QUOTE'; # XXX QUOTE_PREFIX,
+QUOTE_SUFFIX
+ }
+ return undef;
+ }
+ sub sql_keywords {
+ my $dbh = shift;
+ my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
+ my @Keywords = ();
+ while ( my $row = $sth->fetch ) {
+ push @Keywords, $row->[0];
+ }
+ return join ',', @Keywords;
+ }
+
+ %info = (
+ 22 => \&sql_concat_null_behavior # SQL_CONCAT_NULL_BEHAVIOR
+ , 6 => 'DBD/ADO.pm' # SQL_DRIVER_NAME # XXX
+ , 7 => $sql_driver_ver # SQL_DRIVER_VER# XXX
+ ,28 => \&sql_identifier_case # SQL_IDENTIFIER_CASE
+ ,29 => \&sql_identifier_quote_char# SQL_IDENTIFIER_QUOTE_CHAR
+ ,89 => \&sql_keywords # SQL_KEYWORDS
+ );
+
+ %odbc2ado = (
+ 114 => 'Catalog Location' # SQL_CATALOG_LOCATION
+ ,42 => 'Catalog Term' # SQL_CATALOG_TERM
+ , 2 => 'Data Source Name' # SQL_DATA_SOURCE_NAME
+ ,17 => 'DBMS Name'# SQL_DBMS_NAME
+ ,18 => 'DBMS Version' # SQL_DBMS_VERSION
+ # 6 => 'Provider Name'# SQL_DRIVER_NAME # XXX
+ # 7 => 'Provider Version' # SQL_DRIVER_VER# XXX
+ ,40 => 'Procedure Term' # SQL_PROCEDURE_TERM
+ ,39 => 'Schema Term' # SQL_SCHEMA_TERM
+ ,45 => 'Table Term' # SQL_TABLE_TERM
+ ,47 => 'User Name'# SQL_USER_NAME
+ );
+
+ 1;
diff -Nrc DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm DBD-ADO-2.4.02/lib/DBD/ADO.pm
*** DBD-ADO-2.4.02-orig/lib/DBD/ADO.pm Thu Dec 13 01:17:30 2001
--- DBD-ADO-2.4.02/lib/DBD/ADO.pm Mon Feb 04 20:45:18 2002
***
*** 755,760
--- 755,771
}
+ sub get_info {
+ my($dbh, $info_type) = @_;
+ require DBD::ADO::GetInfo;
+ if ( exists $DBD::ADO::GetInfo::odbc2ado{$info_type} ) {
+ return
+$dbh->{ado_conn}->Properties->{$DBD::ADO::GetInfo::odbc2ado{$info_type}}{Value};
+ }
+ my $v = $DBD::ADO::GetInfo::info{int($info_type)};
+ $v = $v->($dbh) if ref $v eq 'CODE';
+ return $v;
+ }
+
sub table_info {
my($dbh, $attribs) = @_;
my @tp;
DBD::ADO: get_info()
Attached is a possible implementation for DBD::ADO.
Only a few information types are supported, but it's
a beginning.
I'm somewhat unsure about SQL_DRIVER_*, because two
layers (DBD::ADO and an ADO Provider) are involved.
Steffen
*** DBD-ADO-2.4.02/lib/DBD/ADO.pm Thu Dec 13 01:17:30 2001
--- ADO.pm Sun Dec 16 09:41:35 2001
***
*** 754,759
--- 754,795
$sth;
}
+ sub get_info {
+ my($dbh, $info_type) = @_;
+
+ # XXX Caching
+ if ( $info_type eq 'SQL_KEYWORDS') {
+ my $sth = $dbh->func('adSchemaDBInfoKeywords','OpenSchema');
+ my @Keywords = ();
+ while ( my $row = $sth->fetch ) {
+ push @Keywords, $row->[0];
+ }
+ return join ',', @Keywords;
+ }
+ if ( $info_type eq 'SQL_IDENTIFIER_QUOTE_CHAR') {
+ my $sth = $dbh->func('adSchemaDBInfoLiterals','OpenSchema');
+ while ( my $row = $sth->fetch ) {
+ return $row->[1] if $row->[0] eq 'QUOTE'; # XXX
+QUOTE_PREFIX, QUOTE_SUFFIX
+ }
+ }
+ my %gi = (
+ SQL_CATALOG_TERM => 'Catalog Term'
+ , SQL_DATA_SOURCE_NAME => 'Data Source Name' # XXX SQL_DATABASE_NAME
+ , SQL_DBMS_NAME=> 'DBMS Name'
+ , SQL_DBMS_VERSION => 'DBMS Version'
+ , SQL_DRIVER_NAME => 'Provider Name' # XXX __FILE__
+ , SQL_DRIVER_VER => 'Provider Version' # XXX $DBD::ADO::VERSION
+ , SQL_PROCEDURE_TERM => 'Procedure Term'
+ , SQL_SCHEMA_TERM => 'Schema Term'
+ , SQL_TABLE_TERM => 'Table Term'
+ , SQL_USER_NAME=> 'User Name'
+ );
+ if ( exists $gi{$info_type} ) {
+ return $dbh->{ado_conn}->Properties->{$gi{$info_type}}{Value};
+ }
+ return undef;
+ }
+
sub table_info {
my($dbh, $attribs) = @_;
