[perl5-dbi/DBI-Test] 1989d5: Add some basic get_info tests. Add coments to test...

[Tim Bunce]

  Branch: refs/heads/master
  Home:   https://github.com/perl5-dbi/DBI-Test
  Commit: 1989d5980acff04341820a92c18f57508a59f5c6
  
https://github.com/perl5-dbi/DBI-Test/commit/1989d5980acff04341820a92c18f57508a59f5c6
  Author: Tim Bunce 
  Date:   2014-02-08 (Sat, 08 Feb 2014)

  Changed paths:
A sandbox/tim/in/DBIT_dbh_ro/GetInfo.pm
M sandbox/tim/tumbler.pl

  Log Message:
  ---
  Add some basic get_info tests. Add coments to test finder code.

Re: get_info

[Martin J. Evans]

On 06/02/14 12:59, Tim Bunce wrote:

Hey Martin. Thanks for your recent work on this - much food for thought!

There's one more aspect of get_info that I'd appreciate your input on:

On Sat, Sep 28, 2013 at 05:01:09PM +0100, Tim Bunce wrote:


Somewhat independant of that, I'd like to identify a set of get_info
items that we recommend all drivers define.


That would be a handy thing to have, and to pass on to driver authors,
and then use to build tests for those items.

What I'm looking for here is mainly get_info items that we're likely to
need to be able to write generic tests that are self-configuring to what the
driver supports.

Could you try to put together such a list?

Tim.



I am happy to do that but as you may already have noticed, I'm not being very 
active on the test discussions - just don't have the time right now for that. 
So if anything comes up in the test discussions please pass them on to me and 
I'll manage the list.

I should have a few tuits this weekend (more if the weather is bad) to take 
this a bit further.

Martin

Re: get_info (was: Common DBI Driver Test Suite - Requirements)

[Tim Bunce]

Hey Martin. Thanks for your recent work on this - much food for thought!

There's one more aspect of get_info that I'd appreciate your input on:

On Sat, Sep 28, 2013 at 05:01:09PM +0100, Tim Bunce wrote:
> 
> Somewhat independant of that, I'd like to identify a set of get_info
> items that we recommend all drivers define.

That would be a handy thing to have, and to pass on to driver authors,
and then use to build tests for those items.

What I'm looking for here is mainly get_info items that we're likely to
need to be able to write generic tests that are self-configuring to what the
driver supports.

Could you try to put together such a list?

Tim.

Re: get_info (was: Common DBI Driver Test Suite - Requirements)

[Martin J. Evans]

On 27/01/2014 20:45, Tim Bunce wrote:

On Sat, Sep 28, 2013 at 05:24:24PM +0100, Martin J. Evans wrote:


I've added it to my TO_DO list. By all means pester me is you hear nothing.


Plese consider yourself pestered :)

Tim.



Back in Sept 2013 (and as part of the DBI Driver Test Suite - 
Requirements discussion) I said (amongst other things):


"As someone who has attempted to use DBI to multiple DBD backends (and 
even ignoring SQL differences) the biggest stumbling block is 
differences in DBDs either outside of the DBI specification or because 
DBI allows a driver to not implement something or allows a DBD multiple 
options. Just ask Peter (ribasushi) how much code is in DBIx::Class to 
work around driver differences. I've long thought (and I've a feeling 
proposed - but that might have just been in discussions with Peter over 
a pint) a capability system beyond what get_info provides. get_info is 
pretty much ODBC's SQLGetInfo and few drivers beyond DBD::ODBC really 
support it that well. Just off the top of my head I'm thinking about 
these sorts of differences:"


I've been in the position of writing DBD independent code a number of 
times over the last 10 or so years and it has nearly always resulted in 
writing a set of module wrappers above the DBD.  Five major stumbling 
blocks have been calling procedures, getting cursors back from 
procedures, when are output bound parameters available, supported case 
in table/column names (and whether to quote to maintain case) and 
last_insert_id but there are many others.


Here is a list of things DBDs might not support or do slightly 
differently with some explanation. This is just a starting list and I'll 
add to it if people reply and when I get a chance to look at DBIx::Class.


1 does it support named parameters - most do but some don't so I always 
ended up using ?


2 does it need a ':' preceding named parameters or not - I sent an email 
about this difference ages ago and I'll dig it out if necessary. DBI 
does not specify.


3 can named parameters be used multiple times in the same SQL - they 
certainly cannot in DBD::ODBC but can in DBD::Oracle.


4 does it use :N for placeholders or ? or both

5 can you mix ? and :N - you certainly can't in DBD::ODBC.

6 lobs - DBIs' lob read is not documented and so all DBDs do it a 
different way. There is a lot more to this than it seems. DBD::Oracle 
has at least 3 ways of obtaining lobs but the one we repeatedly use is 
the one which allows us to retrieve the length of a lob first before 
fetching it (perhaps in parts). We really could do with DBI having a lob 
read method.


7 returning cursors from functions or procedures - is it supported and 
how do you do it. This is something we do a lot and it is a PITA across 
multiple DBDs.


8 output parameters from procedures and functions and when they are 
available (more_results is another sticky one one here). Again, we had a 
long discussion about more_results years ago but it became so complex 
nothing came of it.


9 does it have its own execute_for_fetch - only implemented in 2 DBDs I 
know of and severely restricted in DBD::ODBC due to ODBC driver 
differences. You might say why would you care and that seems to be the 
attitude DBI takes i.e., it gets used if supported or DBI does it. It 
matters because if you know a DBD does not do it, then you might take a 
different path to using DBI's implementation.


10 can it support unicode. Obviously a contentious one and one we've 
visited many times before.


11 how do you enable unicode, sqlite_unicode etc - because of backwards 
compatibility many DBDs need a specific setting to enable unicode.


12 is last_insert_id usuable - not in some DBDs - certainly not 
DBD::ODBC. If it can't what do you do?


13 can you safely put comments in SQL - even comments including ? or :param

14 does a do create a statement handle, i.e, is do the same as 
$dbh->prepare->execute - this mostly only has implications when 
reporting errors i.e., which handle you get in an error handler.


15 can you rebind a placeholder with a different type

16 does a disconnect commit or rollback

17 does type_info/type_info_all return 19 or 20 columns - ODBC/MS added 
a new column. Although the type_info in DBI should be immune to this 
I've seen people having problems with this.


18 does it support DiscardString etc. Probably seems insignificant to 
most people but if you are reading from a database and producing JSON 
strings it can matter to you. If you don't have DiscardString you need 
to add 0 to any number or you JSON numbers end up as "N" instead of N 
especially when using JSON::XS.


19 does it support setting a type on bind_column - most don't

DBIx::Class (used by many) and the work it has to do in the background 
to compensate for differences in DBDs must be full of tests which we can 
examine to get

Re: get_info (was: Common DBI Driver Test Suite - Requirements)

[Tim Bunce]

On Sat, Sep 28, 2013 at 05:24:24PM +0100, Martin J. Evans wrote:
> 
> I've added it to my TO_DO list. By all means pester me is you hear nothing.

Plese consider yourself pestered :)

Tim.

Re: get_info (was: Common DBI Driver Test Suite - Requirements)

[Martin J. Evans]

On 28/09/2013 17:01, Tim Bunce wrote:

On Fri, Sep 27, 2013 at 09:12:09AM +0100, Martin J. Evans wrote:

On 26/09/13 23:29, Tim Bunce wrote:



I'm expecting that one of the side-effects of DBIT will be a great
improvement in support for get_info by drivers. That'll be a win for all.


It definitely is a win. I can't even begin to tell you the pain I had
writing DBD neutral code just for ODBC, Oracle, DB2, mysql and
postgres. We persevered for some time but ended up with so much of the
class methods overridden we ended up with effectively 5 different
applications - although it did not look like it from the front. Once
you include the SQL as well - it really is not fun and although I
don't use DBIx::Class myself, it must be quite a piece of code. It
will be so much nicer to do:



if ($h->get_info(parameters_need_colons)) {
   do this
} else {
   do this
}

than

if ($h->{driver} eq 'Oracle' || $h->{driver} eq 'postres') blah blah


So, Martin, would you be interested in starting a little side-project
to propose an extension to the DBI spec for get_info()?


yes, of course.


It's likely that DBIT will feed into that work, but there's clearly a
need beyond DBIT and you've clearly got (painful) experience to draw on :)


so true.


I suggest drawing up a list of things you'd like get_info to provide
info about (as you did informally in a recent email) then compare that
list with the those provided by the ANSI/ODBC standard to see which are
already covered.


will do.



Somewhat independant of that, I'd like to identify a set of get_info
items that we recommend all drivers define.

Tim.



I've added it to my TO_DO list. By all means pester me is you hear nothing.

Martin
--
Martin J. Evans
Wetherby, UK

get_info (was: Common DBI Driver Test Suite - Requirements)

[Tim Bunce]

On Fri, Sep 27, 2013 at 09:12:09AM +0100, Martin J. Evans wrote:
> On 26/09/13 23:29, Tim Bunce wrote:

> >I'm expecting that one of the side-effects of DBIT will be a great
> >improvement in support for get_info by drivers. That'll be a win for all.
> 
> It definitely is a win. I can't even begin to tell you the pain I had
> writing DBD neutral code just for ODBC, Oracle, DB2, mysql and
> postgres. We persevered for some time but ended up with so much of the
> class methods overridden we ended up with effectively 5 different
> applications - although it did not look like it from the front. Once
> you include the SQL as well - it really is not fun and although I
> don't use DBIx::Class myself, it must be quite a piece of code. It
> will be so much nicer to do:

> if ($h->get_info(parameters_need_colons)) {
>   do this
> } else {
>   do this
> }
> 
> than
> 
> if ($h->{driver} eq 'Oracle' || $h->{driver} eq 'postres') blah blah

So, Martin, would you be interested in starting a little side-project
to propose an extension to the DBI spec for get_info()?

It's likely that DBIT will feed into that work, but there's clearly a
need beyond DBIT and you've clearly got (painful) experience to draw on :)

I suggest drawing up a list of things you'd like get_info to provide
info about (as you did informally in a recent email) then compare that
list with the those provided by the ANSI/ODBC standard to see which are
already covered.

Somewhat independant of that, I'd like to identify a set of get_info
items that we recommend all drivers define.

Tim.

DBD::CSV::get_info results

[Jeff Zucker]

Here are the results for DBD::CSV, first using the XS SQL::Statement,
and then using the Perl SQL::Statement.  As you can see, there's quite a
bit of difference.

-- 
Jeff

DBD::CSV + XS VERSION OF SQL STATEMENT
--
114 SQL_CATALOG_LOCATION1 SQL_CL_START
 41 SQL_CATALOG_NAME_SEPARATOR  . 
132 SQL_CREATE_TABLE0x0001   
SQL_CT_CREATE_TABLE
  2 SQL_DATA_SOURCE_NAMEdbi:CSV:f_dir=/usr/me 
 25 SQL_DATA_SOURCE_READ_ONLY   N 
 17 SQL_DBMS_NAME   CSV   
 18 SQL_DBMS_VER00.2003.; ss-0.1021 
  6 SQL_DRIVER_NAME CSV   
  7 SQL_DRIVER_VER  00.2003.; ss-0.1021 
141 SQL_DROP_TABLE  0x0001   
SQL_DT_DROP_TABLE
 84 SQL_FILE_USAGE  1 SQL_FILE_TABLE
 28 SQL_IDENTIFIER_CASE 3
SQL_IC_SENSITIVE
 29 SQL_IDENTIFIER_QUOTE_CHAR 
172 SQL_INSERT_STATEMENT0x0001   
SQL_IS_INSERT_LITERALS
 89 SQL_KEYWORDS[snip]
 75 SQL_NON_NULLABLE_COLUMNS1
SQL_NNC_NON_NULL
 90 SQL_ORDER_BY_COLUMNS_IN_SELECT  N 
160 SQL_SQL92_PREDICATES0x3206   
SQL_SP_ISNOTNULL | SQL_SP_ISNULL | SQL_SP_LIKE | SQL_SP_COMPARISON |
SQL_SP_QUANTIFIED_COMPARISON
163 SQL_SQL92_ROW_VALUE_CONSTRUCTOR 0x0003   
SQL_SRVC_VALUE_EXPRESSION | SQL_SRVC_NULL
 45 SQL_TABLE_TERM  table 

DBD::CSV + PERL VERSION OF SQL STATEMENT

 20 SQL_ACCESSIBLE_PROCEDURES   N 
 19 SQL_ACCESSIBLE_TABLES   Y 
169 SQL_AGGREGATE_FUNCTIONS 0x007FSQL_AF_AVG |
SQL_AF_COUNT | SQL_AF_MAX | SQL_AF_MIN | SQL_AF_SUM | SQL_AF_DISTINCT |
SQL_AF_ALL
114 SQL_CATALOG_LOCATION1 SQL_CL_START
  10003 SQL_CATALOG_NAMEN 
 41 SQL_CATALOG_NAME_SEPARATOR  . 
 42 SQL_CATALOG_TERM  
  10004 SQL_COLLATING_SEQUENCE  ISO-8859-1
 87 SQL_COLUMN_ALIASN 
 74 SQL_CORRELATION_NAME2 SQL_CN_ANY
132 SQL_CREATE_TABLE0x0001   
SQL_CT_CREATE_TABLE
  2 SQL_DATA_SOURCE_NAMEdbi:CSV:f_dir=/usr/me 
 25 SQL_DATA_SOURCE_READ_ONLY   N 
 17 SQL_DBMS_NAME   CSV   
 18 SQL_DBMS_VER00.2003.; ss-1.004 
  10002 SQL_DESCRIBE_PARAMETER  N 
  6 SQL_DRIVER_NAME CSV   
  7 SQL_DRIVER_VER  00.2003.; ss-1.004 
141 SQL_DROP_TABLE  0x0001   
SQL_DT_DROP_TABLE
 84 SQL_FILE_USAGE  1 SQL_FILE_TABLE
 28 SQL_IDENTIFIER_CASE 4 SQL_IC_MIXED
 29 SQL_IDENTIFIER_QUOTE_CHAR   " 
172 SQL_INSERT_STATEMENT0x0001   
SQL_IS_INSERT_LITERALS
 89 SQL_KEYWORDS[snip]
113 SQL_LIKE_ESCAPE_CLAUSE  N 
111 SQL_NEED_LONG_DATA_LEN  N 
 75 SQL_NON_NULLABLE_COLUMNS1
SQL_NNC_NON_NULL
 85 SQL_NULL_COLLATION  1 SQL_NC_LOW
115 SQL_OJ_CAPABILITIES 0x0037SQL_OJ_LEFT |
SQL_OJ_RIGHT | SQL_OJ_FULL | SQL_OJ_NOT_ORDERED | SQL_OJ_INNER
 90 SQL_ORDER_BY_COLUMNS_IN_SELECT  N 
 39 SQL_OWNER_TERM  schema
 21 SQL_PROCEDURES  N 
 93 SQL_QUOTED_IDENTIFIER_CASE  3
SQL_IC_SENSITIVE
 11 SQL_ROW_UPDATES N 
160 SQL_SQL92_PREDICATES0x3E06   
SQL_SP_ISNOTNULL | SQL_SP_ISNULL | SQL_SP_LIKE | SQL_SP_IN |
SQL_SP_BETWEEN | SQL_SP_COMPARISON | SQL_SP_QUANTIFIED_COMPARISON
161 SQL_SQL92_RELATIONAL_JOIN_OPERATORS 0x01D8   
SQL_SRJO_FULL_OUTER_JOIN | SQL_SRJO_INNER_JOIN |
SQL_SRJO_LEFT_OUTER_JOIN | SQL_SRJO_NATURAL_JOIN |
SQL_SRJO_RIGHT_OUTER_JOIN
163 SQL_SQL92_ROW_VALUE_CONSTRUCTOR 0x0003   
SQL_SRVC_VALUE_EXPRESSION | SQL_SRVC_NULL
164 SQL_SQL92_STRING_FUNCTIONS  0x00EESQL_SSF_LOWER
| SQL_SSF_UPPER | SQL_SSF_SUBSTRING | SQL_SSF_TRIM_BOTH |
SQL_SSF_TRIM_LEADING | SQL_SSF_TRIM_TRAILING
 50 SQL_STRING_FUNCTIONS0x1C49   
SQL_FN_STR_CONCAT | SQL_FN_STR_LTRIM | SQL_FN_STR_LCASE |
SQL_FN_STR_RTRIM | SQL_FN_STR_SUBSTRING | SQL_FN_STR_UCASE
 45 SQL_TABLE_TERM  tabl

RE: :ODBC: get_info(), KEYWORDS truncated

[Jeff Urlwin]

Ok, after the discussion ended, I just took this patch as it stands.
Thanks!

Also -- I will release a new version shortly, as I have NOT received any
answer from ActiveState as to why they haven't updated DBD::ODBC.  Thus, I
intend to move on...

Also -- Steffen, I'm setting up a machine with Oracle 9i and ODBC
driver...I'll let you know, so that I can run your latest getinfo script, if
you still need it.

Jeff

> -Original Message-
> From: Steffen Goeldner [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, March 05, 2002 8:32 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: DBD::ODBC: get_info(), KEYWORDS truncated
>
>
> Currently, long get_info() return values (e.g. SQL_KEYWORDS)
> are truncated.
> That's because the buffer has a static size of 256.
> The attached patch allocates the buffer dynamically and
> reallocates it if needed.
>
>
> Steffen

Re: DBD::ODBC: get_info(), KEYWORDS truncated

[martin]

On 06-Mar-2002 Steffen Goeldner wrote:
> [EMAIL PROTECTED] wrote:
>> 
>> On 05-Mar-2002 Steffen Goeldner wrote:
>> > Currently, long get_info() return values (e.g. SQL_KEYWORDS)
>> > are truncated.
>> > That's because the buffer has a static size of 256.
>> > The attached patch allocates the buffer dynamically and
>> > reallocates it if needed.
>> >
>> >
>> > Steffen
>> 
>> As far as I recall you can call SQLGetInfo with a NULL InfoValuePtr but a
>> valid
>> StringLengthPtr. This then returns the size of the buffer you would need to
>> get
>> all the data back.
>> 
>> e.g.
>> 
>> if (SQL_SUCCEEDED(SQLGetInfo(hdbc, SQL_KEYWORDS, NULL, 0, &sqlsmallint))) {
>> SQLCHAR buf;
> 
> Hmm, works for the MS Access ODBC driver, but seems to be an
> undocumented feature. Other drivers (e.g. Oracle) ignore this
> call.

Fair enough, if Oracle does not handle it then it is not a useful solution - I
just thought it might simplify your patch. However, as to undocumented, there is
a hint in the ODBC programmers reference volume 2 which says:

BufferLength
Length of the *InfoValuePtr buffer. If the value in *InfoValuePtr is not a
character string, or if the InfoValuePtr is a null pointer, the BufferLength
^^
argument is ignored.

This would seem to suggest passing a NULL InfoValuePtr is valid but it does not
specifically say if StringLengthPtr is !NULL, it will be written to by
SQLGetInfo if InfoValuePtr = NULL.


>> 
>> buf = malloc(sqlsmallint + 1);
>> SQLGetInfo(hdbc, SQL_KEYWORDS, buf, sqlsmallint + 1, *sqlsmallint);
>> /* copy buf somewhere */
>> free(buf);
>> }

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

Re: DBD::ODBC: get_info(), KEYWORDS truncated

[Steffen Goeldner]

[EMAIL PROTECTED] wrote:
> 
> On 05-Mar-2002 Steffen Goeldner wrote:
> > Currently, long get_info() return values (e.g. SQL_KEYWORDS)
> > are truncated.
> > That's because the buffer has a static size of 256.
> > The attached patch allocates the buffer dynamically and
> > reallocates it if needed.
> >
> >
> > Steffen
> 
> As far as I recall you can call SQLGetInfo with a NULL InfoValuePtr but a valid
> StringLengthPtr. This then returns the size of the buffer you would need to get
> all the data back.
> 
> e.g.
> 
> if (SQL_SUCCEEDED(SQLGetInfo(hdbc, SQL_KEYWORDS, NULL, 0, &sqlsmallint))) {
> SQLCHAR buf;

Hmm, works for the MS Access ODBC driver, but seems to be an
undocumented feature. Other drivers (e.g. Oracle) ignore this
call.

> 
> buf = malloc(sqlsmallint + 1);
> SQLGetInfo(hdbc, SQL_KEYWORDS, buf, sqlsmallint + 1, *sqlsmallint);
> /* copy buf somewhere */
> free(buf);
> }


Steffen

RE: DBD::ODBC: get_info(), KEYWORDS truncated

[martin]

On 05-Mar-2002 Steffen Goeldner wrote:
> Currently, long get_info() return values (e.g. SQL_KEYWORDS)
> are truncated.
> That's because the buffer has a static size of 256.
> The attached patch allocates the buffer dynamically and
> reallocates it if needed.
> 
> 
> Steffen

As far as I recall you can call SQLGetInfo with a NULL InfoValuePtr but a valid
StringLengthPtr. This then returns the size of the buffer you would need to get
all the data back.

e.g.

if (SQL_SUCCEEDED(SQLGetInfo(hdbc, SQL_KEYWORDS, NULL, 0, &sqlsmallint))) {
SQLCHAR buf;

buf = malloc(sqlsmallint + 1);
SQLGetInfo(hdbc, SQL_KEYWORDS, buf, sqlsmallint + 1, *sqlsmallint);
/* copy buf somewhere */
free(buf);
}

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

DBD::ODBC: get_info(), KEYWORDS truncated

[Steffen Goeldner]

Currently, long get_info() return values (e.g. SQL_KEYWORDS)
are truncated.
That's because the buffer has a static size of 256.
The attached patch allocates the buffer dynamically and
reallocates it if needed.


Steffen

*** DBD-ODBC-0.38-orig/dbdimp.c Tue Feb 12 19:11:56 2002
--- dbdimp.cMon Mar 04 22:37:54 2002
***
*** 2415,2431 
  RETCODE rc;
  SV *retsv = NULL;
  int i;
! char rgbInfoValue[256];
  SWORD cbInfoValue = -2;
  
  /* See fancy logic below */
  for (i = 0; i < 6; i++)
rgbInfoValue[i] = 0xFF;
  
! rc = SQLGetInfo(imp_dbh->hdbc, ftype,
!   rgbInfoValue, sizeof(rgbInfoValue)-1, &cbInfoValue);
  if (!SQL_ok(rc)) {
dbd_error(dbh, rc, "odbc_get_info/SQLGetInfo");
/* patched 2/12/02, thanks to Steffen Goldner */
return &sv_undef;
/* return Nullsv; */
--- 2415,2438 
  RETCODE rc;
  SV *retsv = NULL;
  int i;
! int size = 256;
! char* rgbInfoValue;
  SWORD cbInfoValue = -2;
  
+ New(0, rgbInfoValue, size, char);
+ 
  /* See fancy logic below */
  for (i = 0; i < 6; i++)
rgbInfoValue[i] = 0xFF;
  
! rc = SQLGetInfo(imp_dbh->hdbc, ftype, rgbInfoValue, size-1, &cbInfoValue);
! if (cbInfoValue > size-1) {
!   Renew(rgbInfoValue, cbInfoValue+1, char);
!   rc = SQLGetInfo(imp_dbh->hdbc, ftype, rgbInfoValue, cbInfoValue, &cbInfoValue);
! }
  if (!SQL_ok(rc)) {
dbd_error(dbh, rc, "odbc_get_info/SQLGetInfo");
+   Safefree(rgbInfoValue);
/* patched 2/12/02, thanks to Steffen Goldner */
return &sv_undef;
/* return Nullsv; */
***
*** 2449,2454 
--- 2456,2462 
PerlIO_printf(DBILOGFP, "SQLGetInfo: ftype %d, cbInfoValue %d: %s\n",
  ftype, cbInfoValue, neatsvpv(retsv,0));
  
+ Safefree(rgbInfoValue);
  return sv_2mortal(retsv);
  }
  

Re: DBD::ADO: get_info()

[Thomas A . Lowery]

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

Re: DBD::ADO: get_info()

[Steffen Goeldner]

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, $l

Re: DBD::::GetInfo generator (was: quoted_identifier and get_info)

[Tim Bunce]

On Fri, Feb 15, 2002 at 06:24:13PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> > 
> 
> > p.s. Steffen, any chance you could produce a chunk of code that'll
> > connect to a DSN (specified on the command line), query all the
> > get_info values and dump the results to stdout as a DBDGetInfo
> > module?  Shouldn't be a big modification from the scripts you've
> > obviously developed already. It would obviously be a big help
> > to many driver authors.
> 
> Here is a first cut;

Thanks.

> test it, stress it, tell me what you think!

This means YOU! (as in, all driver authors reading this)

Tim.

DBD::::GetInfo generator (was: quoted_identifier and get_info)

[Steffen Goeldner]

Tim Bunce wrote:
> 

> p.s. Steffen, any chance you could produce a chunk of code that'll
> connect to a DSN (specified on the command line), query all the
> get_info values and dump the results to stdout as a DBDGetInfo
> module?  Shouldn't be a big modification from the scripts you've
> obviously developed already. It would obviously be a big help
> to many driver authors.

Here is a first cut; test it, stress it, tell me what you think!


=head1 NAME

Gen_DBD_foo_GetInfo - Generates a DBDGetInfo package.

=head1 SYNOPSIS

  set DBI_DSN=dbi:ODBC:...
  perl Gen_DBD_foo_GetInfo.pl > GetInfo.pm

=head1 DESCRIPTION

This script generates a DBDGetInfo package from the data source you
specified in the environment variable DBI_DSN.
DBDGetInfo should help a DBD author implementing the DBI get_info()
method.
Because you are just creating this package, it's very unlikly that DBD::
already provides a good implementation for get_info(). Thus you will probable
connect via DBD::ODBC.

=head1 NOTES

If you connect via DBD::ODBC, you should use version 0.38 or greater;

Please have a critical look at the data returned! ODBC driver vary dramatically
in their quality.

The generator assumes that most values are static and places these values
directly in the %info hash. A few examples show the use of CODE references
and the implementation via subroutines.
It's very likely that you have to write additional subroutines for values
depending on the session state or server version, e.g. SQL_DBMS_VER.

A possible implementation of DBDget_info() may look like:

  sub get_info {
my($dbh, $info_type) = @_;
require DBDGetInfo;
my $v = $DBDGetInfo::info{int($info_type)};
$v = $v->($dbh) if ref $v eq 'CODE';
return $v;
  }

Please replace  with the name of your driver.

=head1 SEE ALSO

DBI, DBD::ODBC

=head1 COPYRIGHT

Copyright (c) 2002 Steffen Goeldner. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.

=cut


Steffen


Gen_DBD_foo_GetInfo.tar.gz
Description: GNU Zip compressed data

Re: DBD::ODBC: get_info(), strange results

[Nick Gorham]

Jeff Urlwin wrote:

> Nick,
>
> If it were that useful, then we could provide that with a special function,
> rather than something uncontrolled as get_info...(IMHO).

Agreed, but I don't think it is useful, its only of use to apps that know
exactly what driver they are using, and how to make use of undocumented magic
in the driver. Also the SQLGetInfo call SQL_DRIVER_HLIB, that lets the app
extract extra not standard entry points in the driver.

--
Nick Gorham
Easysoft Ltd

Re: DBD::ODBC: get_info(), strange results

[Nick Gorham]

Jeff Urlwin wrote:

> Martin,
>
> Is this really a necessary/useful SQLGetInfo for DBI?  I don't seem to think
> so, other than, possibly, to prevent calling it incorrectly and return
> undef.
>

Not that much use, its only of interest if you need to bypass the driver
manager and get directly at the driver.

--
Nick Gorham
Easysoft Ltd

Re: DBD::ODBC: get_info(), strange results

[Steffen Goeldner]

Jeff Urlwin wrote:
> 
> Steffen,
> 
> I can't seem to find DBI::Const???  I checked CPAN and to see if my DBI
> distribution had it.
> 
> Any pointers would be welcome.

Oh, sorry! DBI::Const was part of an earlier DBI patch:

 

but never found the way into an official release.
It's a simple mapping between symbolic names and numeric values for
all GetInfo info types.
Currently, there is a chance for a second reincarnation:

 

It's mostly a namespace issue. In this regard, the question
arises, if it should be part of another distribution or rather
a standalone module ...
Any suggestions?


Steffen

RE: DBD::ODBC: get_info(), strange results

[Jeff Urlwin]

Steffen,

I can't seem to find DBI::Const???  I checked CPAN and to see if my DBI
distribution had it.

Any pointers would be welcome.

Thanks,

Jeff

> -Original Message-
> From: Steffen Goeldner [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 12, 2002 4:35 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: DBD::ODBC: get_info(), strange results
>
>
> Steffen Goeldner wrote:
> >
> > Steffen Goeldner wrote:
> > >
> > > DBD::ODBC's get_info() method returns strange results for some
> > > info types, e.g.:
> > >
> > >  SQL_XOPEN_CLI_YEAR  1  892942641
> > >
> > > instead of
> > >
> > >  SQL_XOPEN_CLI_YEAR  1  1995
> > >
> > > I'm quite sure the attached patch fixes that.
> >
> > Further, the script dumps for unsupported info types, e.g.:
> >
> >  SQL_MAXIMUM_STMT_OCTETS: rc == -1
> >
> > The attached patch seems to fix that.
> > However, comments from XS-experts would be appreciated.
>
> FYI: My script
>
>  <http:[EMAIL PROTECTED]/msg00882.html>
>
> still dumps for some info types, e.g.:
>
>  SQL_DRIVER_HSTMT
>  SQL_DRIVER_HDESC
>
> DBD::ODBC does not print an error. Thus, it seems this occurs
> in the ODBC driver itself (MS Access).
> Well, I think these info types are not very useful for DBI anyway.
> For now, I simple skip these info types:
>
>   next if /^SQL_DRIVER_H/;
>
>
> Steffen
>
>

RE: DBD::ODBC: get_info(), strange results

[Jeff Urlwin]

Nick,

If it were that useful, then we could provide that with a special function,
rather than something uncontrolled as get_info...(IMHO).

Regards,

Jeff


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
> Nick Gorham
> Sent: Tuesday, February 12, 2002 7:23 AM
> To: Jeff Urlwin
> Cc: [EMAIL PROTECTED]
> Subject: Re: DBD::ODBC: get_info(), strange results
>
>
> Jeff Urlwin wrote:
>
> > Martin,
> >
> > Is this really a necessary/useful SQLGetInfo for DBI?  I don't
> seem to think
> > so, other than, possibly, to prevent calling it incorrectly and return
> > undef.
> >
>
> Not that much use, its only of interest if you need to bypass the driver
> manager and get directly at the driver.
>
> --
> Nick Gorham
> Easysoft Ltd
>
>
>
>

RE: DBD::ODBC: get_info(), strange results

[Jeff Urlwin]

Martin,

Is this really a necessary/useful SQLGetInfo for DBI?  I don't seem to think
so, other than, possibly, to prevent calling it incorrectly and return
undef.

Jeff

[snip]
> > FYI: My script
> >
> >  
> >
> > still dumps for some info types, e.g.:
> >
> >  SQL_DRIVER_HSTMT
> >  SQL_DRIVER_HDESC
> >
> > DBD::ODBC does not print an error. Thus, it seems this occurs
> > in the ODBC driver itself (MS Access).
> > Well, I think these info types are not very useful for DBI anyway.
> > For now, I simple skip these info types:
> >
> >   next if /^SQL_DRIVER_H/;
>
> I would guess this happens because unlike the other SQLGetInfo
> identifiers,
> SQL_DRIVER_HSTMT and SQL_DRIVER_HDESC are INPUT values as well as
> OUTPUT ones
> i.e. you don't get anything out of SQLGetInfo(.., SQL_DRIVER_HSTMT,
> InfoValuePtr, ..) unless *InfoValuePtr is initialised first.
>
> The ODBC docs say:
>
> ==
> If the InfoType argument is SQL_DRIVER_HDESC or SQL_DRIVER_HSTMT, the
> InfoValuePtr argument is both input and output. (See the
> SQL_DRIVER_HDESC or
> SQL_DRIVER_HSTMT descriptors later in this function description for more
> information.)
>
> SQL_DRIVER_HSTMT
>
>  (ODBC 1.0)An SQLUINTEGER value, the driver's statement handle
> determined by
> the Driver Manager statement handle, which must be passed on input in
> *InfoValuePtr from the application. Note that in
>  this case, InfoValuePtr is both an input and an output argument.
> The input
> statement handle passed in *InfoValuePtr must have been allocated on the
> argument ConnectionHandle.
>
>  The application should make a copy of the Driver Manager's
> statement handle
> before calling SQLGetInfo with this information type, to ensure
> that the handle
> is not overwritten on output.
>
> This information type is implemented by the Driver Manager alone.
> ==
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>
>

RE: :ODBC: get_info(), strange results

[Jeff Urlwin]

Steffen,

Thank you!

Regards,

Jeff

> -Original Message-
> From: Steffen Goeldner [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 12, 2002 3:37 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: DBD::ODBC: get_info(), strange results
> 
> 
> DBD::ODBC's get_info() method returns strange results for some
> info types, e.g.:
> 
>  SQL_XOPEN_CLI_YEAR  1  892942641
> 
> instead of
> 
>  SQL_XOPEN_CLI_YEAR  1  1995
> 
> I'm quite sure the attached patch fixes that.
> 
> 
> Steffen

Re: DBD::ODBC: get_info(), strange results

[martin]

On 12-Feb-2002 Steffen Goeldner wrote:
> Steffen Goeldner wrote:
>> 
>> Steffen Goeldner wrote:
>> >
>> > DBD::ODBC's get_info() method returns strange results for some
>> > info types, e.g.:
>> >
>> >  SQL_XOPEN_CLI_YEAR  1  892942641
>> >
>> > instead of
>> >
>> >  SQL_XOPEN_CLI_YEAR  1  1995
>> >
>> > I'm quite sure the attached patch fixes that.
>> 
>> Further, the script dumps for unsupported info types, e.g.:
>> 
>>  SQL_MAXIMUM_STMT_OCTETS: rc == -1
>> 
>> The attached patch seems to fix that.
>> However, comments from XS-experts would be appreciated.
> 
> FYI: My script
> 
>  <http:[EMAIL PROTECTED]/msg00882.html>
> 
> still dumps for some info types, e.g.:
> 
>  SQL_DRIVER_HSTMT
>  SQL_DRIVER_HDESC
> 
> DBD::ODBC does not print an error. Thus, it seems this occurs
> in the ODBC driver itself (MS Access).
> Well, I think these info types are not very useful for DBI anyway.
> For now, I simple skip these info types:
> 
>   next if /^SQL_DRIVER_H/;

I would guess this happens because unlike the other SQLGetInfo identifiers,
SQL_DRIVER_HSTMT and SQL_DRIVER_HDESC are INPUT values as well as OUTPUT ones
i.e. you don't get anything out of SQLGetInfo(.., SQL_DRIVER_HSTMT,
InfoValuePtr, ..) unless *InfoValuePtr is initialised first.

The ODBC docs say:

==
If the InfoType argument is SQL_DRIVER_HDESC or SQL_DRIVER_HSTMT, the
InfoValuePtr argument is both input and output. (See the SQL_DRIVER_HDESC or
SQL_DRIVER_HSTMT descriptors later in this function description for more
information.)

SQL_DRIVER_HSTMT

 (ODBC 1.0)An SQLUINTEGER value, the driver's statement handle determined by
the Driver Manager statement handle, which must be passed on input in
*InfoValuePtr from the application. Note that in
 this case, InfoValuePtr is both an input and an output argument. The input
statement handle passed in *InfoValuePtr must have been allocated on the
argument ConnectionHandle.

 The application should make a copy of the Driver Manager's statement handle
before calling SQLGetInfo with this information type, to ensure that the handle
is not overwritten on output.

This information type is implemented by the Driver Manager alone.
==

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

Re: DBD::ODBC: get_info(), strange results

[Steffen Goeldner]

Steffen Goeldner wrote:
> 
> Steffen Goeldner wrote:
> >
> > DBD::ODBC's get_info() method returns strange results for some
> > info types, e.g.:
> >
> >  SQL_XOPEN_CLI_YEAR  1  892942641
> >
> > instead of
> >
> >  SQL_XOPEN_CLI_YEAR  1  1995
> >
> > I'm quite sure the attached patch fixes that.
> 
> Further, the script dumps for unsupported info types, e.g.:
> 
>  SQL_MAXIMUM_STMT_OCTETS: rc == -1
> 
> The attached patch seems to fix that.
> However, comments from XS-experts would be appreciated.

FYI: My script

 <http:[EMAIL PROTECTED]/msg00882.html>

still dumps for some info types, e.g.:

 SQL_DRIVER_HSTMT
 SQL_DRIVER_HDESC

DBD::ODBC does not print an error. Thus, it seems this occurs
in the ODBC driver itself (MS Access).
Well, I think these info types are not very useful for DBI anyway.
For now, I simple skip these info types:

next if /^SQL_DRIVER_H/;


Steffen

Re: DBD::ODBC: get_info(), strange results

[Steffen Goeldner]

Steffen Goeldner wrote:
> 
> DBD::ODBC's get_info() method returns strange results for some
> info types, e.g.:
> 
>  SQL_XOPEN_CLI_YEAR  1  892942641
> 
> instead of
> 
>  SQL_XOPEN_CLI_YEAR  1  1995
> 
> I'm quite sure the attached patch fixes that.

Further, the script dumps for unsupported info types, e.g.:

 SQL_MAXIMUM_STMT_OCTETS: rc == -1

The attached patch seems to fix that.
However, comments from XS-experts would be appreciated.


Steffen

*** DBD-ODBC-0.37-orig/dbdimp.c Sun Feb 10 19:34:36 2002
--- DBD-ODBC-0.37/dbdimp.c  Mon Feb 11 20:03:08 2002
***
*** 2426,2432 
rgbInfoValue, sizeof(rgbInfoValue)-1, &cbInfoValue);
  if (!SQL_ok(rc)) {
dbd_error(dbh, rc, "odbc_get_info/SQLGetInfo");
!   return Nullsv;
  }
  
  /* Fancy logic here to determine if result is a string or int */
--- 2426,2432 
rgbInfoValue, sizeof(rgbInfoValue)-1, &cbInfoValue);
  if (!SQL_ok(rc)) {
dbd_error(dbh, rc, "odbc_get_info/SQLGetInfo");
!   return &sv_undef;
  }
  
  /* Fancy logic here to determine if result is a string or int */
***
*** 2434,2440 
retsv = newSViv(*(int *)rgbInfoValue);  /* XXX cast */
  else if (cbInfoValue != 2 && cbInfoValue != 4)/* must be string */
retsv = newSVpv(rgbInfoValue, 0);
! else if (rgbInfoValue[cbInfoValue+1] == '\0') /* must be string */
retsv = newSVpv(rgbInfoValue, 0);
  else if (cbInfoValue == 2)/* short */
retsv = newSViv(*(short *)rgbInfoValue);/* XXX cast */
--- 2434,2440 
retsv = newSViv(*(int *)rgbInfoValue);  /* XXX cast */
  else if (cbInfoValue != 2 && cbInfoValue != 4)/* must be string */
retsv = newSVpv(rgbInfoValue, 0);
! else if (rgbInfoValue[cbInfoValue] == '\0')   /* must be string */
retsv = newSVpv(rgbInfoValue, 0);
  else if (cbInfoValue == 2)/* short */
retsv = newSViv(*(short *)rgbInfoValue);/* XXX cast */

DBD::ODBC: get_info(), strange results

[Steffen Goeldner]

DBD::ODBC's get_info() method returns strange results for some
info types, e.g.:

 SQL_XOPEN_CLI_YEAR  1  892942641

instead of

 SQL_XOPEN_CLI_YEAR  1  1995

I'm quite sure the attached patch fixes that.


Steffen

*** DBD-ODBC-0.37-orig/dbdimp.c Sun Feb 10 19:34:36 2002
--- DBD-ODBC-0.37/dbdimp.c  Mon Feb 11 20:00:27 2002
***
*** 2434,2440 
retsv = newSViv(*(int *)rgbInfoValue);  /* XXX cast */
  else if (cbInfoValue != 2 && cbInfoValue != 4)/* must be string */
retsv = newSVpv(rgbInfoValue, 0);
! else if (rgbInfoValue[cbInfoValue+1] == '\0') /* must be string */
retsv = newSVpv(rgbInfoValue, 0);
  else if (cbInfoValue == 2)/* short */
retsv = newSViv(*(short *)rgbInfoValue);/* XXX cast */
--- 2434,2440 
retsv = newSViv(*(int *)rgbInfoValue);  /* XXX cast */
  else if (cbInfoValue != 2 && cbInfoValue != 4)/* must be string */
retsv = newSVpv(rgbInfoValue, 0);
! else if (rgbInfoValue[cbInfoValue] == '\0')   /* must be string */
retsv = newSVpv(rgbInfoValue, 0);
  else if (cbInfoValue == 2)/* short */
retsv = newSViv(*(short *)rgbInfoValue);/* XXX cast */

Re: quoted_identifier and get_info

[Tim Bunce]

On Mon, Feb 11, 2002 at 12:36:59PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> > 
> 
> > p.s. Steffen, any chance you could produce a chunk of code that'll
> > connect to a DSN (specified on the command line), query all the
> > get_info values and dump the results to stdout as a DBDGetInfo
> > module?  Shouldn't be a big modification from the scripts you've
> > obviously developed already. It would obviously be a big help
> > to many driver authors.
> 
> Here is my current test script:
> 
>   use DBI();
>   use DBI::Const();
> 
>   $\ = "\n";
>   $, = ": ";
> 
>   my $dbh = DBI->connect or die $DBI::errstr;
>  $dbh->{ RaiseError } = 1;
>  $dbh->{ PrintError } = 1;
> 
>   for ( @ARGV ? @ARGV : sort keys %DBI::Const::GetInfo )
>   {
> my $Val = $dbh->get_info( $DBI::Const::GetInfo{$_} );
> printf " %-35s%s\n", $_, $Val if defined $Val;
>   }
> 
> Indeed, a small modification should do it. But
> 
>  1) The script dumps if I use DBD::ODBC.
> I think it has to do with:
> 
> /* Fancy logic here to determine if result is a string or int */
> (dbdimp.c) ... 

Which probably shows up a bug in that logic that needs fixing.
What info type and return value is it failing on?

["Paging Mr Urlwin!"]

>  2) DBI::Const::GetInfo isn't part of DBI.
> Maybe we give it a second chance as SQL::CLI::GetInfo
> or something like that?

Seems like a reasonable idea. Though since it's primary role in
life will be just to hold constants rather than actually _get_ the
info, perhaps SQL::CLI::GetInfoConst, or similar, might be better.

It could also hold all the definitions for the constants associated
with the return values (if it doesn't already, I've not looked),
plus the functions to format them.

Tim.

Re: quoted_identifier and get_info

[Steffen Goeldner]

Tim Bunce wrote:
> 

> p.s. Steffen, any chance you could produce a chunk of code that'll
> connect to a DSN (specified on the command line), query all the
> get_info values and dump the results to stdout as a DBDGetInfo
> module?  Shouldn't be a big modification from the scripts you've
> obviously developed already. It would obviously be a big help
> to many driver authors.

Here is my current test script:

  use DBI();
  use DBI::Const();

  $\ = "\n";
  $, = ": ";

  my $dbh = DBI->connect or die $DBI::errstr;
 $dbh->{ RaiseError } = 1;
 $dbh->{ PrintError } = 1;

  for ( @ARGV ? @ARGV : sort keys %DBI::Const::GetInfo )
  {
my $Val = $dbh->get_info( $DBI::Const::GetInfo{$_} );
printf " %-35s%s\n", $_, $Val if defined $Val;
  }

Indeed, a small modification should do it. But

 1) The script dumps if I use DBD::ODBC.
I think it has to do with:

/* Fancy logic here to determine if result is a string or int */

(dbdimp.c) ... 

 2) DBI::Const::GetInfo isn't part of DBI.
Maybe we give it a second chance as SQL::CLI::GetInfo
or something like that?


Steffen

Re: DBD::ADO: get_info()

[Steffen Goeldner]

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: quoted_identifier and get_info

[Tim Bunce]

On Thu, Feb 07, 2002 at 01:52:39AM +, Tim Bunce wrote:
> FYI, here's how things have worked out...

I'll take the silence as agreement :)

> =item C I
>  
> B This method is experimental and may change.
>  
>   $value = $dbh->get_info( $info_type );
>  
> Returns information about the implementation, i.e. driver and data
> source capabilities, restrictions etc. It returns C for
> unknown or unimplemented information types. For example:
>  
>   $database_version  = $dbh->get_info(  18 ); # SQL_DBMS_VER
>   $max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT
>  
> See L for more detailed information
> about the information types and their meanings and possible return values.
> 
> The DBI curently doesn't provide a name to number mapping for the
> information type codes or the results. Applications are expected to use
> the integer values directly, with the name in a comment, or define
> their own named values using something like the L pragma.
>  
> Because some DBI methods make use of get_info(), drivers are strongly
> encouraged to support I the following very minimal set
> of information types to ensure the DBI itself works properly:
>  
>  Type  NameExample A Example B
>    --    
>17  SQL_DBMS_NAME   'ACCESS'  'Oracle'
>18  SQL_DBMS_VER'03.50.'  '08.01.0721'
>29  SQL_IDENTIFIER_QUOTE_CHAR   '`'   '"'
>41  SQL_CATALOG_NAME_SEPARATOR  '.'   '@'
>   114  SQL_CATALOG_LOCATION1 2

I thought about mandating a longer list, but the list would have
been very long and very hard to define the boundaries for.
This list is a) much easier to justify, and b) so small that any
self-respecting driver author would want to add more to it :)

Almost all of the get_info info is trivial to either hard-code or
fetch from the server so I really hope driver authors implement
this soon (modeled on Steffen Goeldner's great work posted here).

I should strees that ideally the results from get_info() for any
given database-specific driver should ideally match the results
from DBD::ODBC's get_info() when connected to the same database.

Tim.

p.s. Steffen, any chance you could produce a chunk of code that'll
connect to a DSN (specified on the command line), query all the
get_info values and dump the results to stdout as a DBDGetInfo
module?  Shouldn't be a big modification from the scripts you've
obviously developed already. It would obviously be a big help
to many driver authors.

Re: DBD::ADO: get_info()

[Tim Bunce]

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

[Steffen Goeldner]

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 ] 

RE: DBD::ADO: get_info()

[Henrik Tougaard]

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

[Tim Bunce]

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

[Steffen Goeldner]

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

[Steffen Goeldner]

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

[Steffen Goeldner]

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;

quoted_identifier and get_info

[Tim Bunce]

FYI, here's how things have worked out...


=item C I
 
B This method is experimental and may change.
 
  $value = $dbh->get_info( $info_type );
 
Returns information about the implementation, i.e. driver and data
source capabilities, restrictions etc. It returns C for
unknown or unimplemented information types. For example:
 
  $database_version  = $dbh->get_info(  18 ); # SQL_DBMS_VER
  $max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT
 
See L for more detailed information
about the information types and their meanings and possible return values.

The DBI curently doesn't provide a name to number mapping for the
information type codes or the results. Applications are expected to use
the integer values directly, with the name in a comment, or define
their own named values using something like the L pragma.
 
Because some DBI methods make use of get_info(), drivers are strongly
encouraged to support I the following very minimal set
of information types to ensure the DBI itself works properly:
 
 Type  NameExample A Example B
   --    
   17  SQL_DBMS_NAME   'ACCESS'  'Oracle'
   18  SQL_DBMS_VER'03.50.'  '08.01.0721'
   29  SQL_IDENTIFIER_QUOTE_CHAR   '`'   '"'
   41  SQL_CATALOG_NAME_SEPARATOR  '.'   '@'
  114  SQL_CATALOG_LOCATION1 2


=item C I
 
B This method is experimental and may change.
 
  @names = $dbh->tables( $catalog, $schema, $table, $type );
  @names = $dbh->tables;
 
Simple interface to table_info(). Returns a list of matching
table names, possibly including catalog and schema names.
 
See L for a description of the parameters.
 
If C<$dbh->EC returns true (29 is SQL_IDENTIFIER_QUOTE_CHAR)
then the table names are constructed and quoted by L
to ensure they are usable even if they contain whitespace or reserved
words etc.


=item C
 
  $sql = $dbh->quote_identifier( $name );
  $sql = $dbh->quote_identifier( $name1, $name2, $name3, \%attr );
 
Quote an identifier (table name etc.) for use in an SQL statement,
by escaping any special characters (such as double quotation marks)
it contains and adding the required type of outer quotation marks.
 
Undefined names are ignored and the remainder are quoted and then
joined together, typically with a dot (C<.>) character. For example:
 
  $id = $dbh->quote_identifier( undef, 'Her schema', 'My table' );

would, for most database types, return C<"Her schema"."My table">
(including all the double quotation marks).
 
If three names are supplied then the first is assumed to be a catalog
name and special rules may be applied based on what L
returns for SQL_CATALOG_NAME_SEPARATOR (41) and SQL_CATALOG_LOCATION (114).
For example, for Oracle:
 
  $id = $dbh->quote_identifier( 'link', 'schema', 'table' );
 
would return C<"schema"."table"@"link">.



And the code:

sub quote_identifier {
my ($dbh, @id) = @_;
my $attr = (@id > 3) ? pop @id : undef;
 
my $info = $dbh->{dbi_quote_identifier_cache} ||= [
$dbh->get_info(29)  || '"', # SQL_IDENTIFIER_QUOTE_CHAR
$dbh->get_info(41)  || '.', # SQL_CATALOG_NAME_SEPARATOR
$dbh->get_info(114) ||   1, # SQL_CATALOG_LOCATION
];
 
my $quote = $info->[0];
foreach (@id) { # quote the elements
next unless defined;
s/$quote/$quote$quote/g;# escape embedded quotes
$_ = qq{$quote$_$quote};
}
 
# strip out catalog if present for special handling
my $catalog = (@id >= 3) ? shift @id : undef;
 
# join the dots, ignoring any null/undef elements (ie schema)
my $quoted_id = join '.', grep { defined } @id;
 
if ($catalog) { # add catalog correctly
$quoted_id = ($info->[2] == 2)  # SQL_CL_END
? $quoted_id . $info->[1] . $catalog
: $catalog   . $info->[1] . $quoted_id;
}
return $quoted_id;
}

Tim.

Re: DBD::ADO: get_info()

[Tim Bunce]

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

[Steffen Goeldner]

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

[Tim Bunce]

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

[Steffen Goeldner]

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;

Re: DBD::Oracle: get_info()

[Tim Bunce]

On Mon, Feb 04, 2002 at 09:45:08AM -0800, Jeff Zucker wrote:
> > 
> > The drivers could certainly try looking up $info_type in whichever
> > SQL::Statement they're using (giving it 'first refusal' to answer
> > any requests). SQL::Statement would only define values that are
> > appropriate to it.
> > 
> > If the lookup in SQL::Statement doesn't return a defined value the
> > lookup the $info_type in the drivers own hash.
> 
> I'm not sure I can think of anything that would go in "the driver's own
> hash".  It seems to me that in this case everything would be either in
> the $dbh or in SQL::Statement's configuration.

By "driver's own hash" I probably meant what you mean by $dbh.

> > I do plan to define a minimum set of values that drivers will be
> > expected to support.
> 
> I'm looking forward to seeing that :-)

Me too :-)

Tim.

Re: DBD::Oracle: get_info()

[Jeff Zucker]

Tim Bunce wrote:
> 
> On Fri, Feb 01, 2002 at 10:46:12AM -0800, Jeff Zucker wrote:
> >
> > So is this the way to do get_info() for DBD::CSV and DBD::AnyData?
> 
> Well it's certainly one way.
> 
> > sub get_info {
> >   my($dbh, $info_type) = @_;
> >   my $v;
> >   if ($SQL::Statement::VERSION > 1) {
> > require DBD::SQL_Statement::GetInfo;
> > $v = $DBD::SQL_Statement::GetInfo::info{int($info_type)};
> >   }
> >   else {
> >   ...
> 
> There's probably cleaner ways of doing it.
> 
> The drivers could certainly try looking up $info_type in whichever
> SQL::Statement they're using (giving it 'first refusal' to answer
> any requests). SQL::Statement would only define values that are
> appropriate to it.
> 
> If the lookup in SQL::Statement doesn't return a defined value the
> lookup the $info_type in the drivers own hash.

I'm not sure I can think of anything that would go in "the driver's own
hash".  It seems to me that in this case everything would be either in
the $dbh or in SQL::Statement's configuration.  If the $dbh values are
returned as SQL::Statement::GetInfo::info coderefs, then I can keep all
of the GetInfo stuff there rather than having to duplicate it in each
DBD that subclasses SQL::Statement.

> I do plan to define a minimum set of values that drivers will be
> expected to support.

I'm looking forward to seeing that :-)

-- 
Jeff

Re: DBD::Oracle: get_info()

[Tim Bunce]

On Mon, Feb 04, 2002 at 08:53:46AM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> > 
> > Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
> > and make get_info do:
> > 
> >   sub get_info {
> > my($dbh, $info_type) = @_;
> > require DBD::Oracle::GetInfo;
> > my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
> > $v = &$v($dbh, $info_type) if ref $v eq 'CODE';
>  --
> The implementation (currently) doesn't need that parameter.

Sure, I figure few will - but it might be handy in some cases.

Tim.

> Steffen
> diff -Nrc DBD-Oracle-1.12.orig/Oracle/GetInfo.pm DBD-Oracle-1.12/Oracle/GetInfo.pm
> *** DBD-Oracle-1.12.orig/Oracle/GetInfo.pmThu Jan 01 01:00:00 1970
> --- DBD-Oracle-1.12/Oracle/GetInfo.pm Sun Feb 03 21:36:11 2002

Wonderful - thanks.

Tim.

Re: DBD::Oracle: get_info()

[Steffen Goeldner]

Tim Bunce wrote:
> 
> Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
> and make get_info do:
> 
>   sub get_info {
> my($dbh, $info_type) = @_;
> require DBD::Oracle::GetInfo;
> my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
> $v = &$v($dbh, $info_type) if ref $v eq 'CODE';
 --
The implementation (currently) doesn't need that parameter.


Steffen

diff -Nrc DBD-Oracle-1.12.orig/Oracle/GetInfo.pm DBD-Oracle-1.12/Oracle/GetInfo.pm
*** DBD-Oracle-1.12.orig/Oracle/GetInfo.pm  Thu Jan 01 01:00:00 1970
--- DBD-Oracle-1.12/Oracle/GetInfo.pm   Sun Feb 03 21:36:11 2002
***
*** 0 
--- 1,66 
+ package DBD::Oracle::GetInfo;
+ 
+ use DBD::Oracle();
+ 
+ my $fmt = '%02d.%02d.%1d%1d%1d%1d';   # ODBC version string: ##.##.#
+ 
+ my $sql_driver_ver = sprintf $fmt, split (/\./, $DBD::Oracle::VERSION);
+ 
+ sub sql_dbms_version {
+ my $dbh = shift;
+ return sprintf $fmt, @{DBD::Oracle::db::ora_server_version($dbh)};
+ }
+ sub sql_data_source_name {
+ my $dbh = shift;
+ return 'dbi:Oracle:' . $dbh->{Name};
+ }
+ sub sql_user_name {
+ my $dbh = shift;
+ # XXX OPS$
+ return $dbh->{CURRENT_USER};
+ }
+ 
+ %info = (
+ 117 =>  0 # SQL_ALTER_DOMAIN
+ ,   114 =>  2 # SQL_CATALOG_LOCATION
+ , 10003 => 'N'# SQL_CATALOG_NAME
+ ,41 => '@'# SQL_CATALOG_NAME_SEPARATOR
+ ,42 => 'Database Link'# SQL_CATALOG_TERM
+ ,87 => 'Y'# SQL_COLUMN_ALIAS
+ ,22 =>  1 # SQL_CONCAT_NULL_BEHAVIOR
+ ,   127 =>  0 # SQL_CREATE_ASSERTION
+ ,   130 =>  0 # SQL_CREATE_DOMAIN
+ , 2 => \&sql_data_source_name # SQL_DATA_SOURCE_NAME
+ ,17 => 'Oracle'   # SQL_DBMS_NAME
+ ,18 => \&sql_dbms_version # SQL_DBMS_VERSION
+ , 6 => 'DBD/Oracle.pm'# SQL_DRIVER_NAME
+ , 7 =>  $sql_driver_ver   # SQL_DRIVER_VER
+ ,   136 =>  0 # SQL_DROP_ASSERTION
+ ,   139 =>  0 # SQL_DROP_DOMAIN
+ ,28 =>  1 # SQL_IDENTIFIER_CASE
+ ,29 => '"'# SQL_IDENTIFIER_QUOTE_CHAR
+ ,34 =>  0 # SQL_MAX_CATALOG_NAME_LEN
+ ,30 => 30 # SQL_MAX_COLUMN_NAME_LEN
+ , 10005 => 30 # SQL_MAX_IDENTIFIER_LEN
+ ,32 => 30 # SQL_MAX_OWNER_NAME_LEN
+ ,34 =>  0 # SQL_MAX_QUALIFIER_NAME_LEN
+ ,32 => 30 # SQL_MAX_SCHEMA_NAME_LEN
+ ,35 => 30 # SQL_MAX_TABLE_NAME_LEN
+ ,   107 => 30 # SQL_MAX_USER_NAME_LEN
+ ,90 => 'N'# SQL_ORDER_BY_COLUMNS_IN_SELECT
+ ,39 => 'Owner'# SQL_OWNER_TERM
+ ,40 => 'Procedure'# SQL_PROCEDURE_TERM
+ ,   114 =>  2 # SQL_QUALIFIER_LOCATION
+ ,41 => '@'# SQL_QUALIFIER_NAME_SEPARATOR
+ ,42 => 'Database Link'# SQL_QUALIFIER_TERM
+ ,93 =>  3 # SQL_QUOTED_IDENTIFIER_CASE
+ ,39 => 'Owner'# SQL_SCHEMA_TERM
+ ,14 => '\\'   # SQL_SEARCH_PATTERN_ESCAPE
+ ,13 =>  sub {"$_[0]->{Name}"} # SQL_SERVER_NAME
+ ,94 => '$#'   # SQL_SPECIAL_CHARACTERS
+ ,45 => 'Table'# SQL_TABLE_TERM
+ ,46 =>  3 # SQL_TXN_CAPABLE
+ ,47 => \&sql_user_name# SQL_USER_NAME
+ );
+ 
+ 1;
diff -Nrc DBD-Oracle-1.12.orig/Oracle.pm DBD-Oracle-1.12/Oracle.pm
*** DBD-Oracle-1.12.orig/Oracle.pm  Fri Aug 31 18:27:18 2001
--- DBD-Oracle-1.12/Oracle.pm   Sun Feb 03 21:32:21 2002
***
*** 313,318 
--- 313,327 
  }
  
  
+ sub get_info {
+   my($dbh, $info_type) = @_;
+   require DBD::Oracle::GetInfo;
+   my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
+   $v = $v->($dbh) if ref $v eq 'CODE';
+   return $v;
+ }
+ 
+ 
  sub table_info {
my($dbh, $attr) = @_;
# XXX add knowledge of temp tables, etc
***
*** 578,583 
--- 587,603 
$sth->bind_param_inout(":param",  \$msg->[2], 4000);
$sth->execute or return undef;
return 1;
+ }
+ 
+ sub ora_server_version {
+   my $dbh = shift;
+   return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
+   $dbh->{ora_server_version} =
+  [ split /\./, $dbh->selectrow_array(<<'SQL', undef, 'Oracle%') .''];
+ SELECT version
+   FROM product_component_version
+  WHERE product LIKE ?
+ SQL
  }
  
  }   # end of package DBD::Oracle::db

Re: DBD::Oracle: get_info()

[Tim Bunce]

On Fri, Feb 01, 2002 at 10:46:12AM -0800, Jeff Zucker wrote:
> Tim Bunce wrote:
> > 
> > Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
> > and make get_info do:
> 
> So is this the way to do get_info() for DBD::CSV and DBD::AnyData?

Well it's certainly one way.

> The
> situation for those is a bit more complex since the return values depend
> not only on the driver version in use, but also on the SQL::Statement
> version in use.
> 
> sub get_info {
>   my($dbh, $info_type) = @_;
>   my $v;
>   if ($SQL::Statement::VERSION > 1) {
> require DBD::SQL_Statement::GetInfo;
> $v = $DBD::SQL_Statement::GetInfo::info{int($info_type)};
>   }
>   else {
> require DBD::SQL_Statement_XS::GetInfo;
> $v = $DBD::SQL_Statement_XS::GetInfo::info{int($info_type)};
>   }
>   $v = &$v($dbh, $info_type) if ref $v eq 'CODE';
>   return $v;
> }

There's probably cleaner ways of doing it.

The drivers could certainly try looking up $info_type in whichever
SQL::Statement they're using (giving it 'first refusal' to answer
any requests). SQL::Statement would only define values that are
appropriate to it.

If the lookup in SQL::Statement doesn't return a defined value the
lookup the $info_type in the drivers own hash.

> The situation for DBD::Excel will be even more complex since it will
> depend on the DBD in use, the SQL::Statement in use,

Those are catered for but what I described above.

> and on the version of Excel in use.

Well that's why you've got the $dbh passed to you :)
Fetch and cache the version and use code refs to do-the-right-thing.

> If one wanted to get even more complex, one would need
> to determine the versions of sub-modules also (e.g.
> Spreadsheet::ParseExcel in the Excel case or XML::Twig/XML::Parser when
> AnyData is handling XML).  To know how detailed to get, we'd have to
> know the kinds of things get_info() will ultimately be used for.  In
> other words, what constants will be required by DBI and how will they be
> used?

I do plan to define a minimum set of values that drivers will be
expected to support.

> I know that I will be using the ones related to SQL syntax for my
> own purposes in SQL::Statement, but which of the others will actually be
> used by DBI to change DBI behaviour?
> 
> Also: shouldn't we be wrapping the requires in evals?

Yes, and the execution of code refs. Then something like:

return $dbh->set_err(1,$@) if $@;

Tim.

Re: DBD::Oracle: get_info()

[Jeff Zucker]

Tim Bunce wrote:
> 
> Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
> and make get_info do:

So is this the way to do get_info() for DBD::CSV and DBD::AnyData?  The
situation for those is a bit more complex since the return values depend
not only on the driver version in use, but also on the SQL::Statement
version in use.

sub get_info {
  my($dbh, $info_type) = @_;
  my $v;
  if ($SQL::Statement::VERSION > 1) {
require DBD::SQL_Statement::GetInfo;
$v = $DBD::SQL_Statement::GetInfo::info{int($info_type)};
  }
  else {
require DBD::SQL_Statement_XS::GetInfo;
$v = $DBD::SQL_Statement_XS::GetInfo::info{int($info_type)};
  }
  $v = &$v($dbh, $info_type) if ref $v eq 'CODE';
  return $v;
}

The situation for DBD::Excel will be even more complex since it will
depend on the DBD in use, the SQL::Statement in use, and on the version
of Excel in use.  If one wanted to get even more complex, one would need
to determine the versions of sub-modules also (e.g.
Spreadsheet::ParseExcel in the Excel case or XML::Twig/XML::Parser when
AnyData is handling XML).  To know how detailed to get, we'd have to
know the kinds of things get_info() will ultimately be used for.  In
other words, what constants will be required by DBI and how will they be
used?  I know that I will be using the ones related to SQL syntax for my
own purposes in SQL::Statement, but which of the others will actually be
used by DBI to change DBI behaviour?

Also: shouldn't we be wrapping the requires in evals?

-- 
Jeff

Re: DBD::Oracle: get_info()

[Steffen Goeldner]

Tim Bunce wrote:
> 
> On Fri, Feb 01, 2002 at 09:21:09AM +0100, Steffen Goeldner wrote:
> > A new patch for DBD::Oracle:
> >
> >  - get_info() now takes a numeric value
> >  - SQL_DBMS_VERSION is supported
> >
> > More info types if desired. (I don't like a blindfold
> > adoption of the Oracle ODBC driver results.)
> 
> Great, many thanks.
> 
> Could I ask that you move the hash setup outside the function
> and put in there only the values that are constant.
> 
> Then in the function do something like
> 
>   sub get_info {
> my($dbh, $info_type) = @_;
> my $v = $get_info_constants{int($info_type)};
> return $v if defined $v;
> ... handle the other values ...
>   }
> 
> I'd like this to be a good and efficient model for other drivers
> to follow.
> 
> Umm, here's another idea... for the non-constant items put them into
> the %get_info_constants hash as anonymous subs that return the appropriate
> value. Then in get_info do:
> 
>   sub get_info {
> my($dbh, $info_type) = @_;
> my $v = $get_info_constants{int($info_type)};
> $v = &$v($dbh) if ref $v eq 'CODE';
> return $v;
>   }
> 
> Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
> and make get_info do:
> 
>   sub get_info {
> my($dbh, $info_type) = @_;
> require DBD::Oracle::GetInfo;
> my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
> $v = &$v($dbh, $info_type) if ref $v eq 'CODE';
> return $v;
>   }

Two Umm's more and nothing remains to be done :-)


Steffen

Re: DBD::Oracle: get_info()

[Tim Bunce]

On Fri, Feb 01, 2002 at 09:21:09AM +0100, Steffen Goeldner wrote:
> A new patch for DBD::Oracle:
> 
>  - get_info() now takes a numeric value
>  - SQL_DBMS_VERSION is supported
> 
> More info types if desired. (I don't like a blindfold
> adoption of the Oracle ODBC driver results.)

Great, many thanks.

Could I ask that you move the hash setup outside the function
and put in there only the values that are constant.

Then in the function do something like

  sub get_info {
my($dbh, $info_type) = @_;
my $v = $get_info_constants{int($info_type)};
return $v if defined $v;
... handle the other values ...
  }

I'd like this to be a good and efficient model for other drivers
to follow.

Umm, here's another idea... for the non-constant items put them into
the %get_info_constants hash as anonymous subs that return the appropriate
value. Then in get_info do:

  sub get_info {
my($dbh, $info_type) = @_;
my $v = $get_info_constants{int($info_type)};
$v = &$v($dbh) if ref $v eq 'CODE';
return $v;
  }

Umm, carrying on from there... move %get_info_constants into DBD::Oracle::GetInfo
and make get_info do:

  sub get_info {
my($dbh, $info_type) = @_;
require DBD::Oracle::GetInfo;
my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
$v = &$v($dbh, $info_type) if ref $v eq 'CODE';
return $v;
  }

:-)

Thanks!

Tim.

> 
> Steffen
> *** DBD-Oracle-1.12.orig/Oracle.pmFri Aug 31 18:27:18 2001
> --- Oracle.pm Thu Jan 31 22:33:59 2002
> ***
> *** 313,318 
> --- 313,368 
>   }
>   
>   
> + sub get_info {
> + my($dbh, $info_type) = @_;
> + # XXX Caching
> + my $fmt = '%02d.%02d.%1d%1d%1d%1d';   # ODBC version string: ##.##.#
> + if($info_type == 18) {# SQL_DBMS_VERSION
> + return sprintf $fmt, @{ora_server_version($dbh)};
> + }
> + elsif ($info_type ==  7) {# SQL_DRIVER_VER
> + return sprintf $fmt, split (/\./, $DBD::Oracle::VERSION);
> + }
> + my %gi = (
> + 117 =>  0 # SQL_ALTER_DOMAIN
> + ,   114 =>  2 # SQL_CATALOG_LOCATION
> + , 10003 => 'N'# SQL_CATALOG_NAME
> + ,41 => '@'# SQL_CATALOG_NAME_SEPARATOR
> + ,42 => 'Database Link'# SQL_CATALOG_TERM
> + ,87 => 'Y'# SQL_COLUMN_ALIAS
> + ,22 =>  1 # SQL_CONCAT_NULL_BEHAVIOR
> + ,   130 =>  0 # SQL_CREATE_DOMAIN
> + , 2 => "dbi:Oracle:$dbh->{Name}"  # SQL_DATA_SOURCE_NAME
> + ,17 => 'Oracle'   # SQL_DBMS_NAME
> + , 6 => __FILE__   # SQL_DRIVER_NAME
> + ,   139 =>  0 # SQL_DROP_DOMAIN
> + ,28 =>  1 # SQL_IDENTIFIER_CASE
> + ,29 => '"'# SQL_IDENTIFIER_QUOTE_CHAR
> + ,34 =>  0 # SQL_MAX_CATALOG_NAME_LEN
> + ,30 => 30 # SQL_MAX_COLUMN_NAME_LEN
> + , 10005 => 30 # SQL_MAX_IDENTIFIER_LEN
> + ,32 => 30 # SQL_MAX_OWNER_NAME_LEN
> + ,34 =>  0 # SQL_MAX_QUALIFIER_NAME_LEN
> + ,32 => 30 # SQL_MAX_SCHEMA_NAME_LEN
> + ,35 => 30 # SQL_MAX_TABLE_NAME_LEN
> + ,   107 => 30 # SQL_MAX_USER_NAME_LEN
> + ,39 => 'Owner'# SQL_OWNER_TERM
> + ,40 => 'Procedure'# SQL_PROCEDURE_TERM
> + ,   114 =>  2 # SQL_QUALIFIER_LOCATION
> + ,41 => '@'# SQL_QUALIFIER_NAME_SEPARATOR
> + ,42 => 'Database Link'# SQL_QUALIFIER_TERM
> + ,93 =>  3 # SQL_QUOTED_IDENTIFIER_CASE
> + ,39 => 'Owner'# SQL_SCHEMA_TERM
> + ,14 => '\\'   # SQL_SEARCH_PATTERN_ESCAPE
> + ,13 => "$dbh->{Name}" # SQL_SERVER_NAME
> + ,94 => '$#'   # SQL_SPECIAL_CHARACTERS
> + ,45 => 'Table'# SQL_TABLE_TERM
> + ,47 => "$dbh->{CURRENT_USER}" # SQL_USER_NAME # XXX OPS$
> + );
> + return $gi{$info_type};
> + }
> + 
> + 
>   sub table_info {
>   my($dbh, $attr) = @_;
>   # XXX add knowledge of temp tables, etc

Re: DBD::Oracle: get_info()

[Steffen Goeldner]

A new patch for DBD::Oracle:

 - get_info() now takes a numeric value
 - SQL_DBMS_VERSION is supported

More info types if desired. (I don't like a blindfold
adoption of the Oracle ODBC driver results.)


Steffen

*** DBD-Oracle-1.12.orig/Oracle.pm  Fri Aug 31 18:27:18 2001
--- Oracle.pm   Thu Jan 31 22:33:59 2002
***
*** 313,318 
--- 313,368 
  }
  
  
+ sub get_info {
+   my($dbh, $info_type) = @_;
+   # XXX Caching
+   my $fmt = '%02d.%02d.%1d%1d%1d%1d';   # ODBC version string: ##.##.#
+   if($info_type == 18) {# SQL_DBMS_VERSION
+   return sprintf $fmt, @{ora_server_version($dbh)};
+   }
+   elsif ($info_type ==  7) {# SQL_DRIVER_VER
+   return sprintf $fmt, split (/\./, $DBD::Oracle::VERSION);
+   }
+   my %gi = (
+   117 =>  0 # SQL_ALTER_DOMAIN
+   ,   114 =>  2 # SQL_CATALOG_LOCATION
+   , 10003 => 'N'# SQL_CATALOG_NAME
+   ,41 => '@'# SQL_CATALOG_NAME_SEPARATOR
+   ,42 => 'Database Link'# SQL_CATALOG_TERM
+   ,87 => 'Y'# SQL_COLUMN_ALIAS
+   ,22 =>  1 # SQL_CONCAT_NULL_BEHAVIOR
+   ,   130 =>  0 # SQL_CREATE_DOMAIN
+   , 2 => "dbi:Oracle:$dbh->{Name}"  # SQL_DATA_SOURCE_NAME
+   ,17 => 'Oracle'   # SQL_DBMS_NAME
+   , 6 => __FILE__   # SQL_DRIVER_NAME
+   ,   139 =>  0 # SQL_DROP_DOMAIN
+   ,28 =>  1 # SQL_IDENTIFIER_CASE
+   ,29 => '"'# SQL_IDENTIFIER_QUOTE_CHAR
+   ,34 =>  0 # SQL_MAX_CATALOG_NAME_LEN
+   ,30 => 30 # SQL_MAX_COLUMN_NAME_LEN
+   , 10005 => 30 # SQL_MAX_IDENTIFIER_LEN
+   ,32 => 30 # SQL_MAX_OWNER_NAME_LEN
+   ,34 =>  0 # SQL_MAX_QUALIFIER_NAME_LEN
+   ,32 => 30 # SQL_MAX_SCHEMA_NAME_LEN
+   ,35 => 30 # SQL_MAX_TABLE_NAME_LEN
+   ,   107 => 30 # SQL_MAX_USER_NAME_LEN
+   ,39 => 'Owner'# SQL_OWNER_TERM
+   ,40 => 'Procedure'# SQL_PROCEDURE_TERM
+   ,   114 =>  2 # SQL_QUALIFIER_LOCATION
+   ,41 => '@'# SQL_QUALIFIER_NAME_SEPARATOR
+   ,42 => 'Database Link'# SQL_QUALIFIER_TERM
+   ,93 =>  3 # SQL_QUOTED_IDENTIFIER_CASE
+   ,39 => 'Owner'# SQL_SCHEMA_TERM
+   ,14 => '\\'   # SQL_SEARCH_PATTERN_ESCAPE
+   ,13 => "$dbh->{Name}" # SQL_SERVER_NAME
+   ,94 => '$#'   # SQL_SPECIAL_CHARACTERS
+   ,45 => 'Table'# SQL_TABLE_TERM
+   ,47 => "$dbh->{CURRENT_USER}" # SQL_USER_NAME # XXX OPS$
+   );
+   return $gi{$info_type};
+ }
+ 
+ 
  sub table_info {
my($dbh, $attr) = @_;
# XXX add knowledge of temp tables, etc

Re: DBD::Oracle: get_info(), SQL_DBMS_VERSION

[Steffen Goeldner]

Tim Bunce wrote:
> 
> >  SELECT version
> >FROM product_component_version
> >   WHERE product LIKE 'Oracle%'
> > should be a quite portable way.
> 
> I'd be a little nervous of that returning more than one row.
> But lets wait and see if anyone complains :)

How about a test:

#!perl -w

sub ok ($$;$) {
my($n, $ok, $warn) = @_;
++$t;
die "sequence error, expected $n but actually $t"
if $n and $n != $t;
($ok) ? print "ok $t\n"
  : print "# failed test $t at line ".(caller)[2]."\nnot ok $t\n";
if (!$ok && $warn) {
$warn = $DBI::errstr || "(DBI::errstr undefined)" if $warn eq '1';
warn "$warn\n";
}
}

use DBI;
$| = 1;

my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';
my $dbh = DBI->connect('dbi:Oracle:', $dbuser, '');

unless($dbh) {
warn "Unable to connect to Oracle ($DBI::errstr)\nTests skiped.\n";
print "1..0\n";
exit 0;
}

print "1..$tests\n";

#

my $cnt = $dbh->selectrow_array(<<'SQL', undef, 'Oracle%');
SELECT count(*)
  FROM product_component_version
 WHERE product LIKE ?
SQL
ok(0, $cnt == 1);

my $server_version = $dbh->func('ora_server_version');
ok(0, $server_version);
ok(0, $server_version->[0] >= 7 && $server_version->[0] <= 9);

#

exit 0;
BEGIN { $tests = 3 }

__END__

Re: DBD::Oracle: get_info(), SQL_DBMS_VERSION

[Steffen Goeldner]

Tim Bunce wrote:
> 
> > *** DBD-Oracle-1.12.orig/Oracle.pmFri Aug 31 18:27:18 2001
> > --- Oracle.pm Fri Jan 25 11:37:32 2002
> > ***
> > *** 580,585 
> > --- 580,601 
> >   return 1;
> >   }
> >
> > + sub _server_version {
> 
> Might as well call it ora_server_version as well.
> 
> > + my $dbh = shift;
> > + return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
> > + $dbh->{ora_server_version} = [];
> > + my $sth = $dbh->prepare(<<'SQL') or return [];
> > + SELECT version
> > +   FROM product_component_version
> > +  WHERE product LIKE 'Oracle%'
> > + SQL
> > + $sth->execute or return [];
> > + my $row = $sth->fetch or return [];
> > + $dbh->{ora_server_version} = [ split /\./, $row->[0] ];
> > + $sth->finish;
> 
> Tsk, tsk. I'd use $dbh->selectrow_array :)

Ok, ok, here comes the compact edition:

*** DBD-Oracle-1.12.orig/Oracle.pm  Fri Aug 31 18:27:18 2001
--- Oracle.pm   Fri Jan 25 22:08:33 2002
***
*** 580,585 
--- 580,595 
return 1;
  }
  
+ sub ora_server_version {
+   my $dbh = shift;
+   return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
+   $dbh->{ora_server_version} =
+  [ split /\./, $dbh->selectrow_array(<<'SQL', undef, 'Oracle%') .''];
+ SELECT version
+   FROM product_component_version
+  WHERE product LIKE ?
+ SQL
+ 
  }   # end of package DBD::Oracle::db
  
  

Re: DBD::Oracle: get_info(), SQL_DBMS_VERSION

[Tim Bunce]

On Fri, Jan 25, 2002 at 12:21:42PM +0100, Steffen Goeldner wrote:
> > 
> >  select * from v$version;
> > 
> >  select * from PRODUCT_COMPONENT_VERSION;
> > 
> >  set serveroutput on
> >  declare
> >v varchar2(255);
> >c varchar2(255);
> >  begin
> >dbms_utility.db_version( v, c );
> >dbms_output.put_line( v );
> >dbms_output.put_line( c );
> >  end;
> >  /
> > 
> >  select dbms_utility.port_string from dual;
> > 
> > The attached file shows the results for Oracle8.
> > Unfortunately, I have no Oracle7 available. Is anybody so kind
> > and provides the results for Oracle7?
> 
> Looking at the results from Oracle8:
>   <http://www.xray.mpe.mpg.de/mailing-lists/dbi/2002-01/msg00525.html>
> and Oracle7:
>   <http://www.xray.mpe.mpg.de/mailing-lists/dbi/2002-01/msg00728.html>
> something like
>  SELECT version
>FROM product_component_version
>   WHERE product LIKE 'Oracle%'
> should be a quite portable way.

I'd be a little nervous of that returning more than one row.
But lets wait and see if anyone complains :)

> The attached patch shows a possible implementation.
> 
> For get_info(SQL_DBMS_VERSION), we could use something like
> 
>   sprintf '%02d.%02d.%1d%1d%1d%1d', @{$dbh->{ora_server_version}}
> 
> 
> Steffen
> *** DBD-Oracle-1.12.orig/Oracle.pmFri Aug 31 18:27:18 2001
> --- Oracle.pm Fri Jan 25 11:37:32 2002
> ***
> *** 580,585 
> --- 580,601 
>   return 1;
>   }
>   
> + sub _server_version {

Might as well call it ora_server_version as well.

> + my $dbh = shift;
> + return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
> + $dbh->{ora_server_version} = [];
> + my $sth = $dbh->prepare(<<'SQL') or return [];
> + SELECT version
> +   FROM product_component_version
> +  WHERE product LIKE 'Oracle%'
> + SQL
> + $sth->execute or return [];
> + my $row = $sth->fetch or return [];
> + $dbh->{ora_server_version} = [ split /\./, $row->[0] ];
> + $sth->finish;

Tsk, tsk. I'd use $dbh->selectrow_array :)

:-)

Thanks Steffen.

(When I get to DBD::Oracle again, in a week or two hopefully, I
might ask you to resend all your patches just so I know I have the
latest of all of them.)

Tim.

Re: DBD::Oracle: get_info(), SQL_DBMS_VERSION

[Steffen Goeldner]

Steffen Goeldner wrote:
> 
> Steffen Goeldner wrote:
> >
> > I think, all currently supported information types are
> > stable across most Oracle releases. Other types may
> > depend on a specific version, thus SQL_DBMS_VERSION is
> > a top entry in the TODO list. I guess OCIServerVersion
> > can help in obtaining the version number, however it
> > returns the complete banner. I hope I find a reliable
> > way to parse that string. Suggestions welcome!
> 
> I collected some alternatives to retrieve SQL_DBMS_VERSION:
> 
>  select * from v$version;
> 
>  select * from PRODUCT_COMPONENT_VERSION;
> 
>  set serveroutput on
>  declare
>v varchar2(255);
>c varchar2(255);
>  begin
>dbms_utility.db_version( v, c );
>dbms_output.put_line( v );
>dbms_output.put_line( c );
>  end;
>  /
> 
>  select dbms_utility.port_string from dual;
> 
> The attached file shows the results for Oracle8.
> Unfortunately, I have no Oracle7 available. Is anybody so kind
> and provides the results for Oracle7?
> 

Looking at the results from Oracle8:

  <http://www.xray.mpe.mpg.de/mailing-lists/dbi/2002-01/msg00525.html>

and Oracle7:

  <http://www.xray.mpe.mpg.de/mailing-lists/dbi/2002-01/msg00728.html>

something like

 SELECT version
   FROM product_component_version
  WHERE product LIKE 'Oracle%'

should be a quite portable way.
The attached patch shows a possible implementation.

For get_info(SQL_DBMS_VERSION), we could use something like

  sprintf '%02d.%02d.%1d%1d%1d%1d', @{$dbh->{ora_server_version}}


Steffen

*** DBD-Oracle-1.12.orig/Oracle.pm  Fri Aug 31 18:27:18 2001
--- Oracle.pm   Fri Jan 25 11:37:32 2002
***
*** 580,585 
--- 580,601 
return 1;
  }
  
+ sub _server_version {
+   my $dbh = shift;
+   return $dbh->{ora_server_version} if defined $dbh->{ora_server_version};
+   $dbh->{ora_server_version} = [];
+   my $sth = $dbh->prepare(<<'SQL') or return [];
+ SELECT version
+   FROM product_component_version
+  WHERE product LIKE 'Oracle%'
+ SQL
+   $sth->execute or return [];
+   my $row = $sth->fetch or return [];
+   $dbh->{ora_server_version} = [ split /\./, $row->[0] ];
+   $sth->finish;
+   return $dbh->{ora_server_version};
+ }
+ 
  }   # end of package DBD::Oracle::db
  
  

Re: DBD::Oracle: get_info(), SQL_DBMS_VERSION

[Steffen Goeldner]

Steffen Goeldner wrote:
> 
> I think, all currently supported information types are
> stable across most Oracle releases. Other types may
> depend on a specific version, thus SQL_DBMS_VERSION is
> a top entry in the TODO list. I guess OCIServerVersion
> can help in obtaining the version number, however it
> returns the complete banner. I hope I find a reliable
> way to parse that string. Suggestions welcome!

I collected some alternatives to retrieve SQL_DBMS_VERSION:

 select * from v$version;

 select * from PRODUCT_COMPONENT_VERSION;

 set serveroutput on
 declare
   v varchar2(255);
   c varchar2(255);
 begin
   dbms_utility.db_version( v, c );
   dbms_output.put_line( v );
   dbms_output.put_line( c );
 end;
 /

 select dbms_utility.port_string from dual;

The attached file shows the results for Oracle8.
Unfortunately, I have no Oracle7 available. Is anybody so kind
and provides the results for Oracle7?

 
Steffen


[EMAIL PROTECTED]> select * from v$version;

BANNER

Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
PL/SQL Release 8.1.7.2.0 - Production
CORE8.1.7.2.1   Production
TNS for 32-bit Windows: Version 8.1.7.2.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

[EMAIL PROTECTED]>
[EMAIL PROTECTED]> select * from PRODUCT_COMPONENT_VERSION;
rows will be truncated


PRODUCT  VERSION
 -
NLSRTL   3.4.1.0.0
Oracle8i Enterprise Edition  8.1.7.2.1
PL/SQL   8.1.7.2.0
TNS for 32-bit Windows:  8.1.7.2.0

[EMAIL PROTECTED]>
[EMAIL PROTECTED]> set serveroutput on
[EMAIL PROTECTED]> declare
  2v varchar2(255);
  3c varchar2(255);
  4  begin
  5dbms_utility.db_version( v, c );
  6dbms_output.put_line( v );
  7dbms_output.put_line( c );
  8  end;
  9  /
8.1.7.2.1
8.1.7.0.0

PL/SQL procedure successfully completed.

[EMAIL PROTECTED]>
[EMAIL PROTECTED]> select dbms_utility.port_string from dual;

PORT_STRING
--
IBMPC/WIN_NT-8.1.0

Re: DBI: get_info() constants

[Tim Bunce]

Thanks. I'll get back to you after next week.

Tim.

On Thu, Jan 10, 2002 at 11:08:07AM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> > 
> > On Mon, Nov 26, 2001 at 09:21:49PM +, Tim Bunce wrote:
> > > On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> > >
> > > On reflection I agree that DBI attributes should be consistently
> > > fetched by name, not a numeric value.
> > 
> > I'm having second thoughts about this.
> > 
> > > The $h->get_info method is still open to question. Since we have
> > > to provide a name to number mapping anyway then we could define the
> > > $h->get_info method to support either.
> > 
> > I think a name to number mapping could be optional.
> > 
> > If we drop the use of handle-attributes as a way to access SQLGetInfo()
> 
> No problem. For DBI.pm, we only have to provide another
> documentation (see attachment).
> 
> > values and just provide a $dbh->get_info($) method then that method
> > would naturally take a number, as per SQLGetInfo() in ODBC & SQL/CLI.
> > 
> > Then it's up to the developer to either pass a numeric value
> > 
> >   $rollback_behaviour = $dbh->get_info(24); # 24=SQL_CURSOR_ROLLBACK_BEHAVIOR
> > 
> > or make use of an optional name to number mapping that the DBI can
> > provide (but not use internally):
> > 
> >   use DBI qw(:get_info);
> >   $rollback_behaviour = $dbh->get_info(SQL_CURSOR_ROLLBACK_BEHAVIOR);
> 
> Exporting these symbols depends on the implementation of the
> get_info constants. If you accepted DBI::Const - see:
> 
>   http:[EMAIL PROTECTED]/msg00559.html
> 
> - then DBI.pm will need it's own import() routine, e.g.:
> 
>   sub import
>   {
> my $class   = shift;
> my $caller  = caller;
> my @exports = ();
> 
> for my $tag ( @_ ) {
>   if ( $tag eq ':get_info') {
> require DBI::Const;
> while ( my ( $key, $val ) = each %DBI::Const::GetInfo ) {
>   *{ $caller . '::' . $key } = sub () { $val };
> }
>   }
>   else {
> push @exports, $tag;
>   }
> }
> $class->export( $caller, @exports );
>   }
> 
> 
> Steffen
> *** DBI-1.20.orig/DBI.pm  Sat Aug 25 01:33:52 2001
> --- DBI.pmWed Jan 09 20:11:54 2002
> ***
> *** 2625,2630 
> --- 2625,2657 
>   Apache::DBI module for one example usage.
>   
>   
> + =item C I
> + 
> + B This method is experimental and may change.
> + 
> +   $value = $dbh->get_info( $info_type );
> + 
> + Returns information about the implementation, i.e. driver and data source
> + capabilities, restrictions etc.
> + 
> + For more detailed information about the information types and their
> + meanings, you can refer to:
> + 
> +   http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlgetinfo.asp
> + 
> + If that URL ceases to work then use the MSDN search facility at:
> + 
> +   http://search.microsoft.com/us/dev/
> + 
> + and search for C using the exact phrase option.
> + The link you want will probably just be called C and will
> + be part of the Data Access SDK.
> + 
> + See also pages 95, 226, 328 of the current SQL/CLI Working Draft:
> + 
> +   
>http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF
> + 
> + 
>   =item C I
>   
>   B This method is experimental and may change.

Re: DBI: get_info() constants

[Steffen Goeldner]

Tim Bunce wrote:
> 
> On Mon, Nov 26, 2001 at 09:21:49PM +, Tim Bunce wrote:
> > On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> >
> > On reflection I agree that DBI attributes should be consistently
> > fetched by name, not a numeric value.
> 
> I'm having second thoughts about this.
> 
> > The $h->get_info method is still open to question. Since we have
> > to provide a name to number mapping anyway then we could define the
> > $h->get_info method to support either.
> 
> I think a name to number mapping could be optional.
> 
> If we drop the use of handle-attributes as a way to access SQLGetInfo()

No problem. For DBI.pm, we only have to provide another
documentation (see attachment).

> values and just provide a $dbh->get_info($) method then that method
> would naturally take a number, as per SQLGetInfo() in ODBC & SQL/CLI.
> 
> Then it's up to the developer to either pass a numeric value
> 
>   $rollback_behaviour = $dbh->get_info(24); # 24=SQL_CURSOR_ROLLBACK_BEHAVIOR
> 
> or make use of an optional name to number mapping that the DBI can
> provide (but not use internally):
> 
>   use DBI qw(:get_info);
>   $rollback_behaviour = $dbh->get_info(SQL_CURSOR_ROLLBACK_BEHAVIOR);

Exporting these symbols depends on the implementation of the
get_info constants. If you accepted DBI::Const - see:

  http:[EMAIL PROTECTED]/msg00559.html

- then DBI.pm will need it's own import() routine, e.g.:

  sub import
  {
my $class   = shift;
my $caller  = caller;
my @exports = ();

for my $tag ( @_ ) {
  if ( $tag eq ':get_info') {
require DBI::Const;
while ( my ( $key, $val ) = each %DBI::Const::GetInfo ) {
  *{ $caller . '::' . $key } = sub () { $val };
}
  }
  else {
push @exports, $tag;
  }
}
$class->export( $caller, @exports );
  }


Steffen

*** DBI-1.20.orig/DBI.pmSat Aug 25 01:33:52 2001
--- DBI.pm  Wed Jan 09 20:11:54 2002
***
*** 2625,2630 
--- 2625,2657 
  Apache::DBI module for one example usage.
  
  
+ =item C I
+ 
+ B This method is experimental and may change.
+ 
+   $value = $dbh->get_info( $info_type );
+ 
+ Returns information about the implementation, i.e. driver and data source
+ capabilities, restrictions etc.
+ 
+ For more detailed information about the information types and their
+ meanings, you can refer to:
+ 
+   http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlgetinfo.asp
+ 
+ If that URL ceases to work then use the MSDN search facility at:
+ 
+   http://search.microsoft.com/us/dev/
+ 
+ and search for C using the exact phrase option.
+ The link you want will probably just be called C and will
+ be part of the Data Access SDK.
+ 
+ See also pages 95, 226, 328 of the current SQL/CLI Working Draft:
+ 
+   
+http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF
+ 
+ 
  =item C I
  
  B This method is experimental and may change.

Re: DBI: get_info() constants

[Tim Bunce]

On Mon, Nov 26, 2001 at 09:21:49PM +, Tim Bunce wrote:
> On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> 
> On reflection I agree that DBI attributes should be consistently
> fetched by name, not a numeric value.

I'm having second thoughts about this.

> The $h->get_info method is still open to question. Since we have
> to provide a name to number mapping anyway then we could define the
> $h->get_info method to support either.

I think a name to number mapping could be optional.

If we drop the use of handle-attributes as a way to access SQLGetInfo()
values and just provide a $dbh->get_info($) method then that method
would naturally take a number, as per SQLGetInfo() in ODBC & SQL/CLI.

Then it's up to the developer to either pass a numeric value

  $rollback_behaviour = $dbh->get_info(24); # 24=SQL_CURSOR_ROLLBACK_BEHAVIOR

or make use of an optional name to number mapping that the DBI can
provide (but not use internally):

  use DBI qw(:get_info);
  $rollback_behaviour = $dbh->get_info(SQL_CURSOR_ROLLBACK_BEHAVIOR);

(Not using handle-attributes is a good-thing because this would
polute the attribute namespace in a messy way.)

Tim.

Re: DBI: get_info()

[Steffen Goeldner]

Now that more and more drivers support get_info(), it's
time to provide some documentation for this method.


Steffen

*** DBI-1.20.orig/DBI.pmSat Aug 25 01:33:52 2001
--- DBI.pm  Sun Dec 16 20:25:13 2001
***
*** 2625,2630 
--- 2625,2661 
  Apache::DBI module for one example usage.
  
  
+ =item C I
+ 
+ B This method is experimental and may change.
+ 
+   $value = $dbh->get_info( $info_type );
+ 
+ Returns information about the implementation, i.e. driver and data source
+ capabilities, restrictions etc.
+ 
+ These values are available as L too:
+ 
+   $value = $dbh->{ $info_type };
+ 
+ For more detailed information about the information types and their
+ meanings, you can refer to:
+ 
+   http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlgetinfo.asp
+ 
+ If that URL ceases to work then use the MSDN search facility at:
+ 
+   http://search.microsoft.com/us/dev/
+ 
+ and search for C using the exact phrase option.
+ The link you want will probably just be called C and will
+ be part of the Data Access SDK.
+ 
+ See also pages 95, 226, 328 of the current SQL/CLI Working Draft:
+ 
+   
+http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF
+ 
+ 
  =item C I
  
  B This method is experimental and may change.
***
*** 3229,3234 
--- 3260,3271 
  cache needs refilling.
  
  See also the L statement handle attribute.
+ 
+ 
+ =item C  (read-only)
+ 
+ Currently, this is a dual interface to L.
+ 
  
  =back
  

DBD::Oracle: get_info()

[Steffen Goeldner]

Attached is a possible implementation for DBD::Oracle.
Only a few information types are supported, but it's
a beginning.
I think, all currently supported information types are
stable across most Oracle releases. Other types may
depend on a specific version, thus SQL_DBMS_VERSION is
a top entry in the TODO list. I guess OCIServerVersion
can help in obtaining the version number, however it
returns the complete banner. I hope I find a reliable
way to parse that string. Suggestions welcome!


Steffen

*** DBD-Oracle-1.12.orig/Oracle.pm  Fri Aug 31 18:27:18 2001
--- Oracle.pm   Sun Dec 16 18:03:01 2001
***
*** 313,318 
--- 313,357 
  }
  
  
+ sub get_info {
+   my($dbh, $info_type) = @_;
+   # XXX Caching
+   my %gi = (
+ SQL_CATALOG_LOCATION =>  2
+   , SQL_CATALOG_NAME => 'N'
+   , SQL_CATALOG_NAME_SEPARATOR   => '@'
+   , SQL_CATALOG_TERM => 'Database Link'
+   , SQL_DATABASE_NAME=>  undef
+   , SQL_DATA_SOURCE_NAME => 'dbi:Oracle:' . $dbh->{Name}
+   , SQL_DBMS_NAME=> 'Oracle'
+ # , SQL_DBMS_VERSION =>  # XXX OCIServerVersion
+   , SQL_DRIVER_NAME  => __FILE__
+   , SQL_DRIVER_VER   => $DBD::Oracle::VERSION  # XXX ##.##.
+   , SQL_IDENTIFIER_QUOTE_CHAR=> '"'
+   , SQL_MAX_CATALOG_NAME_LEN =>  0
+   , SQL_MAX_COLUMN_NAME_LEN  => 30
+   , SQL_MAX_IDENTIFIER_LEN   => 30
+   , SQL_MAX_OWNER_NAME_LEN   => 30
+   , SQL_MAX_QUALIFIER_NAME_LEN   =>  0
+   , SQL_MAX_SCHEMA_NAME_LEN  => 30
+   , SQL_MAX_TABLE_NAME_LEN   => 30
+   , SQL_MAX_USER_NAME_LEN=> 30
+   , SQL_OWNER_TERM   => 'Owner'
+   , SQL_PROCEDURE_TERM   => 'Procedure'
+   , SQL_QUALIFIER_LOCATION   =>  2
+   , SQL_QUALIFIER_NAME_SEPARATOR => '@'
+   , SQL_QUALIFIER_TERM   => 'Database Link'
+   , SQL_SCHEMA_TERM  => 'Owner'
+   , SQL_SEARCH_PATTERN_ESCAPE=> '\\'
+   , SQL_SERVER_NAME  => $dbh->{Name}
+   , SQL_SPECIAL_CHARACTERS   => '$#'
+   , SQL_TABLE_TERM   => 'Table'
+   , SQL_USER_NAME=> $dbh->{CURRENT_USER} ||''  # XXX OPS$
+   );
+   return $gi{$info_type};
+ }
+ 
+ 
  sub table_info {
my($dbh, $attr) = @_;
# XXX add knowledge of temp tables, etc

DBD::ADO: get_info()

[Steffen Goeldner]

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) = @_;

Re: DBI: get_info() constants

[Steffen Goeldner]

Tim Bunce wrote:
> 
> You have _way_ too much free time Steffen!

Since I'm using perl, DBI, ...
 
> The only things that comes to mind are that there's a specific api
> call for require'ing a file that should be used to avoid static's
> and threading issues.

I used eval_pv() because of it's builtin error handling. Unfortunately,
require_pv() is very silent about error conditions. Now I use the hash
itself as indicator of successful load:

hv = get_hv("DBI::Const::GetInfo", FALSE);
if (!hv) {
require_pv("DBI/Const.pm");
hv = get_hv("DBI::Const::GetInfo", FALSE);
if (!hv)
croak("Can't get %s", "%DBI::Const::GetInfo");
}
if (hv_exists(hv, key, keylen)) {
meth_name = "get_info";
}
else {
croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
}

> And the return value probably needs to be
> made mortal (sv_2mortal(...)).

I changed

return newSVsv(valuesv);

to

return sv_2mortal(newSVsv(valuesv));

However, I got:

  Attempt to free unreferenced scalar ...

I think the call to dbih_get_attr_k_sql_ needs to be changed too:

valuesv = dbih_get_attr_k_sql_(h, keysv, key, keylen);

is now:

valuesv = newSVsv(dbih_get_attr_k_sql_(h, keysv, key, keylen));


Steffen

*** DBI-1.20.orig/DBI.xsSat Aug 25 00:10:48 2001
--- DBI-1.20/DBI.xs Thu Nov 29 10:02:26 2001
***
*** 1243,1248 
--- 1243,1285 
  
  
  static SV *
+ dbih_get_attr_k_sql_(SV *h, SV *keysv, char *key, STRLEN keylen)
+ {
+ dPERINTERP;
+ dSP;
+ HV*hv;
+ char  *meth_name;
+ SV*valuesv;
+ 
+ hv = get_hv("DBI::Const::GetInfo", FALSE);
+ if (!hv) {
+   require_pv("DBI/Const.pm");
+   hv = get_hv("DBI::Const::GetInfo", FALSE);
+   if (!hv)
+   croak("Can't get %s", "%DBI::Const::GetInfo");
+ }
+ if (hv_exists(hv, key, keylen)) {
+   meth_name = "get_info";
+ }
+ else {
+   croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
+ }
+ 
+ PUSHMARK(SP);
+ XPUSHs(h);
+ XPUSHs(keysv);
+ PUTBACK;
+ if (call_method(meth_name, G_SCALAR) != 1)
+   croak("Can't locate DBI object method \"%s\"", meth_name);
+ SPAGAIN;
+ valuesv = POPs;
+ PUTBACK;
+ 
+ return sv_2mortal(newSVsv(valuesv));
+ }
+ 
+ 
+ static SV *
  dbih_get_attr_k(h, keysv, dbikey) /* XXX split into dr/db/st 
funcs */
  SV *h;
  SV *keysv;
***
*** 1407,1412 
--- 1444,1452 
  else if (keylen==9  && strEQ(key, "BegunWork")) {
valuesv = boolSV(DBIc_has(imp_xxh,DBIcf_BegunWork));
  }
+ else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
+   valuesv = newSVsv(dbih_get_attr_k_sql_(h, keysv, key, keylen));
+ }
  else {/* finally check the actual hash just in case   */
svp = hv_fetch((HV*)SvRV(h), key, keylen, FALSE);
if (svp)

Re: DBI: get_info() constants

[Tim Bunce]

You have _way_ too much free time Steffen!

The only things that comes to mind are that there's a specific api
call for require'ing a file that should be used to avoid static's
and threading issues. And the return value probably needs to be
made mortal (sv_2mortal(...)).

Tim.

On Wed, Nov 28, 2001 at 02:09:55PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
> > 
> > On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> > > >
> > > else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
> > >   /* XXX DBIt_ST ? */
> > >   valuesv = dbih_get_info(h, key, keylen);
> > 
> > That presumes that _all_ attributes begining with SQL_ are
> > 'get_info' attributes. That may not be the case. I'd suggest
> > 
> > else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4) && gi_code = 
>get_info_name2code(key)) 
> >
> 
> That's right! My brain was focused on get_info. However,
> dbih_get_attr_k is big enough, I renamed dbih_get_info to
> the more generic dbih_get_attr_k_sql_:
> 
> else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
>   valuesv = dbih_get_attr_k_sql_(h, keysv, key, keylen);
> 
> and let it handle all SQL_* requests:
> 
>   static SV *
>   dbih_get_attr_k_sql_(SV *h, SV *keysv, char *key, STRLEN keylen)
>   {
> dPERINTERP;
> dSP;
> HV*hv;
> char  *meth_name;
> SV*valuesv;
>     static bool loaded = FALSE;
> 
> if (!loaded) {
>   eval_pv("require DBI::Const", TRUE);
>   loaded = TRUE;
> }
> if ((hv = get_hv("DBI::Const::GetInfo", FALSE)) && hv_exists(hv, key, keylen)) {
>   meth_name = "get_info";
> }
>   /*
> other SQL_* requests:
> else if ((hv = get_hv("DBI::Const::Get???", FALSE)) && hv_exists(hv, key, 
>keylen)) {
>   meth_name = "get_???";
> }
>   */
> else {
>   croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
> }
> 
> PUSHMARK(SP);
> XPUSHs(h);
> XPUSHs(keysv);
> PUTBACK;
> if (call_method(meth_name, G_SCALAR) != 1)
>   croak("Can't locate DBI object method \"%s\"", meth_name);
> SPAGAIN;
> valuesv = POPs;
> PUTBACK;
> 
> return newSVsv(valuesv);
>   }
> 
> Comments are highly appreciated!
> 
> > > Of course, dbih_get_info needs to be written. I think, it should
> > >
> > >  1) map the symbolic name to the numeric code
> > > (via %DBI::Const::GetInfo?)
> > >  2) call $dbh->get_info($code)
> > 
> > There's also the question of whether each particular item of 'info'
> > can be cached for the handle it's being called on (see cacheit in
> > dbih_get_attr_k).
> > 
> 
> Here I need further investigation ...
> 
> > Yeap. Though you could argue that using hash fetching should be the
> > primary API to get_info and just not bother supporting importing
> > numeric constants. And I don't think there's a good performance
> > argument for numeric constants. So lets just skip the import :)
> 
> Great!
> 
> For testing purposes, I added a few more lines of code to DBI,
> see my attachment.
> 
> 
> Steffen
> diff -Nrc DBI-1.20.orig/DBI.xs DBI-1.20/DBI.xs
> *** DBI-1.20.orig/DBI.xs  Sat Aug 25 00:10:48 2001
> --- DBI-1.20/DBI.xs   Wed Nov 28 10:33:59 2001
> ***
> *** 1243,1248 
> --- 1243,1283 
>   
>   
>   static SV *
> + dbih_get_attr_k_sql_(SV *h, SV *keysv, char *key, STRLEN keylen)
> + {
> + dPERINTERP;
> + dSP;
> + HV*hv;
> + char  *meth_name;
> + SV*valuesv;
> + static bool loaded = FALSE;
> + 
> + if (!loaded) {
> + eval_pv("require DBI::Const", TRUE);
> + loaded = TRUE;
> + }
> + if ((hv = get_hv("DBI::Const::GetInfo", FALSE)) && hv_exists(hv, key, keylen)) 
>{
> + meth_name = "get_info";
> + }
> + else {
> + croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
> + }
> + 
> + PUSHMARK(SP);
> + XPUSHs(h);
> + XPUSHs(keysv);
> + PUTBACK;
> + if (call_method(meth_name, G_SCALAR) != 1)
> + croak("Can't locate DBI object method \"%s\"", meth_name);
> + SPAGAIN;
> + valuesv = POPs;
> + PUTBACK;
> + 
> + return newSVsv(valuesv);
> + }
> + 
> + 
>

Re: DBI: get_info() constants

[Steffen Goeldner]

Tim Bunce wrote:
> 
> On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> > >
> > else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
> >   /* XXX DBIt_ST ? */
> >   valuesv = dbih_get_info(h, key, keylen);
> 
> That presumes that _all_ attributes begining with SQL_ are
> 'get_info' attributes. That may not be the case. I'd suggest
> 
> else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4) && gi_code = 
>get_info_name2code(key)) 
>

That's right! My brain was focused on get_info. However,
dbih_get_attr_k is big enough, I renamed dbih_get_info to
the more generic dbih_get_attr_k_sql_:

else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
valuesv = dbih_get_attr_k_sql_(h, keysv, key, keylen);

and let it handle all SQL_* requests:

  static SV *
  dbih_get_attr_k_sql_(SV *h, SV *keysv, char *key, STRLEN keylen)
  {
dPERINTERP;
dSP;
HV*hv;
char  *meth_name;
SV*valuesv;
static bool loaded = FALSE;

if (!loaded) {
eval_pv("require DBI::Const", TRUE);
loaded = TRUE;
}
if ((hv = get_hv("DBI::Const::GetInfo", FALSE)) && hv_exists(hv, key, keylen)) {
meth_name = "get_info";
}
  /*
other SQL_* requests:
else if ((hv = get_hv("DBI::Const::Get???", FALSE)) && hv_exists(hv, key, keylen)) 
{
meth_name = "get_???";
}
  */
else {
croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
}

PUSHMARK(SP);
XPUSHs(h);
XPUSHs(keysv);
PUTBACK;
if (call_method(meth_name, G_SCALAR) != 1)
croak("Can't locate DBI object method \"%s\"", meth_name);
SPAGAIN;
valuesv = POPs;
PUTBACK;

    return newSVsv(valuesv);
  }

Comments are highly appreciated!

> > Of course, dbih_get_info needs to be written. I think, it should
> >
> >  1) map the symbolic name to the numeric code
> > (via %DBI::Const::GetInfo?)
> >  2) call $dbh->get_info($code)
> 
> There's also the question of whether each particular item of 'info'
> can be cached for the handle it's being called on (see cacheit in
> dbih_get_attr_k).
> 

Here I need further investigation ...

> Yeap. Though you could argue that using hash fetching should be the
> primary API to get_info and just not bother supporting importing
> numeric constants. And I don't think there's a good performance
> argument for numeric constants. So lets just skip the import :)

Great!

For testing purposes, I added a few more lines of code to DBI,
see my attachment.


Steffen

diff -Nrc DBI-1.20.orig/DBI.xs DBI-1.20/DBI.xs
*** DBI-1.20.orig/DBI.xsSat Aug 25 00:10:48 2001
--- DBI-1.20/DBI.xs Wed Nov 28 10:33:59 2001
***
*** 1243,1248 
--- 1243,1283 
  
  
  static SV *
+ dbih_get_attr_k_sql_(SV *h, SV *keysv, char *key, STRLEN keylen)
+ {
+ dPERINTERP;
+ dSP;
+ HV*hv;
+ char  *meth_name;
+ SV*valuesv;
+ static bool loaded = FALSE;
+ 
+ if (!loaded) {
+   eval_pv("require DBI::Const", TRUE);
+   loaded = TRUE;
+ }
+ if ((hv = get_hv("DBI::Const::GetInfo", FALSE)) && hv_exists(hv, key, keylen)) {
+   meth_name = "get_info";
+ }
+ else {
+   croak("Can't get %s->{%s}: unrecognised attribute", neatsvpv(h,0), key);
+ }
+ 
+ PUSHMARK(SP);
+ XPUSHs(h);
+ XPUSHs(keysv);
+ PUTBACK;
+ if (call_method(meth_name, G_SCALAR) != 1)
+   croak("Can't locate DBI object method \"%s\"", meth_name);
+ SPAGAIN;
+ valuesv = POPs;
+ PUTBACK;
+ 
+ return newSVsv(valuesv);
+ }
+ 
+ 
+ static SV *
  dbih_get_attr_k(h, keysv, dbikey) /* XXX split into dr/db/st 
funcs */
  SV *h;
  SV *keysv;
***
*** 1406,1411 
--- 1441,1449 
  }
  else if (keylen==9  && strEQ(key, "BegunWork")) {
valuesv = boolSV(DBIc_has(imp_xxh,DBIcf_BegunWork));
+ }
+ else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) {
+   valuesv = dbih_get_attr_k_sql_(h, keysv, key, keylen);
  }
  else {/* finally check the actual hash just in case   */
svp = hv_fetch((HV*)SvRV(h), key, keylen, FALSE);
diff -Nrc DBI-1.20.orig/lib/DBD/NullP.pm DBI-1.20/lib/DBD/NullP.pm
*** DBI-1.20.orig/lib/DBD/NullP.pm  Sat Aug 25 00:10:50 2001
--- DBI-1.20/lib/DBD/NullP.pm   Wed Nov 28 10:30:41 2001
***
*** 58,63 
--- 58,69 
$outer;
  }
  
+ sub get_info {
+   my $dbh = shift;
+   my $git = shift;
+   return 'GetInfo: ' . $git;
+ }
+ 
 

Re: DBI: get_info() constants

[Tim Bunce]

On Mon, Nov 26, 2001 at 04:25:16PM +0100, Steffen Goeldner wrote:
> > 
> > As internal implementation, fine. As user interface, probably not.
> > I think I'd rather stick with the "use DBI qw(...)", but allow
> > things like "use DBI qw(:get_info_subsetfoo)". Few applications
> > will use more than a handful of distinct GetInfo calls anyway.
> > 
> > Another issue here is the difference between
> > 
> > $h->{SQL_FOO}
> 
> AFAIK, there are few options: $h->{SQL_FOO} triggers a FETCH and
> FETCH calls dbih_get_attr_k, right?

After passing through the driver, which may choose to handle it itself.

>  dbih_get_attr_k(h, keysv, dbikey)
> SV *h;
> SV *keysv;
> int dbikey;
>  {
> ...
> else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) { 
>   /* XXX DBIt_ST ? */
>   valuesv = dbih_get_info(h, key, keylen);

That presumes that _all_ attributes begining with SQL_ are
'get_info' attributes. That may not be the case. I'd suggest

else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4) && gi_code = 
get_info_name2code(key)) {
  ..

> Of course, dbih_get_info needs to be written. I think, it should
> 
>  1) map the symbolic name to the numeric code
> (via %DBI::Const::GetInfo?)
>  2) call $dbh->get_info($code)

There's also the question of whether each particular item of 'info'
can be cached for the handle it's being called on (see cacheit in
dbih_get_attr_k).

> And, of course, I need a remedy for my XS-phobia ;-)

:)

> > and $h->{SQL_FOO()}  (or $h->{+SQL_FOO} etc)
> 
> Personally, I would not import these symbols (I like namespaces).
> I know, other people like that ...

On reflection I agree that DBI attributes should be consistently
fetched by name, not a numeric value.

The $h->get_info method is still open to question. Since we have
to provide a name to number mapping anyway then we could define the
$h->get_info method to support either.

> If the constants are in a separate module, they have to write:
> 
>  use DBI::Const qw( :GetInfo )
> 
> Or is
> 
>  use DBI qw(...)
> 
> the ultimate goal?

I don't see a benefit in creating an 'aribitrary' split in where
constants come from from the users point of view. (The DBI can
perform the same magic behind the scenes.)

> Whatever you prefer, it's likely that we need a special import()
> method, especially if the constants are implemented as hash values.

Yeap. Though you could argue that using hash fetching should be the
primary API to get_info and just not bother supporting importing
numeric constants. And I don't think there's a good performance
argument for numeric constants. So lets just skip the import :)

Tim.

Re: DBI: get_info() constants

[Steffen Goeldner]

Tim Bunce wrote:
> 
> On Fri, Nov 23, 2001 at 03:16:32PM +0100, Steffen Goeldner wrote:
> > Tim Bunce wrote:
> >   (see <http:[EMAIL PROTECTED]/msg00499.html>)
> > >
> > > [And I'd ideally like to be able to detect and warn about misspelt
> > > SQL_ attributes.  And I'd also like to make it easy to map the names
> > > into their official numbers (to make life easier for drivers,
> > > especially DBD::ODBC).  So I was thinking in terms of having the
> > > DBI build process pre-process a list of all SQL_* GetInfo attributes
> > > (and possibly *all* SQL_* macros) into some efficiently loadable form.]
> >
> > Recently, I extented the list of datatype codes for DBI, see:
> >
> >  <http:[EMAIL PROTECTED]/msg00483.html>
> >
> > These constants are defined via ALIAS. I'm really not very familiar
> > with XS, but if this is such an 'efficiently loadable form', then
> > the same approach could be applied to the SQLGetInfo() constants:
> >
> >  MODULE = DBI   PACKAGE = DBI::Const::GetInfo
> >
> >  I32
> >  constant()
> >  PROTOTYPE:
> >  ALIAS:
> >   SQL_MAXIMUM_DRIVER_CONNECTIONS= SQL_MAXIMUM_DRIVER_CONNECTIONS
> >   SQL_MAXIMUM_CONCURRENT_ACTIVITIES = SQL_MAXIMUM_CONCURRENT_ACTIVITIES
> >   ...
> >   SQL_OJ_CAPABILITIES   = SQL_OJ_CAPABILITIES
> >  CODE:
> >  RETVAL = ix;
> >  OUTPUT:
> >  RETVAL
> >
> >
> > Or is this the wrong track?
> 
> It could. The downside is more expensive DBI startup.
> 

How about a separate module, e.g. DBI::Const? It may contain an
ordinary hash:

 our %DBI::Const::GetInfo =
 (
   SQL_ACTIVE_CONNECTIONS => 0
   ...
 , SQL_OJ_CAPABILITIES=> 65003
 );

Advantage: The load could be avoided or delayed.
(I know, the downside is: It's not constant.)

> > As you see, I prefer a separate namespace for SQLGetInfo() constants.
> > Why?
> >
> >  1) I like namespaces ;-)
> 
> Me too. Namespaces are good.
> 
> >  2) I encountered some difficulties if namespaces are not preserved,
> > see e.g.:
> >  <http:[EMAIL PROTECTED]/msg00538.html>
> >  3) The requirement to list known constants is stated in
> > DBI-1.20/ToDo:
> >
> > > Consider making all SQL_ known to the DBI. Want to check for
> > > valid spelling. Also would like C{Attrib}> to
> > > work. Maybe auto load a module that defines a private hash
> > > that just serves to list known attributes.
> > > Or DBI->SQL_FOO and/or DBI::SQL_FOO.
> >
> > With %DBI::Const::GetInfo::, we would have a hash for that
> > purpose.
> >
> > Opinions?
> 
> As internal implementation, fine. As user interface, probably not.
> I think I'd rather stick with the "use DBI qw(...)", but allow
> things like "use DBI qw(:get_info_subsetfoo)". Few applications
> will use more than a handful of distinct GetInfo calls anyway.
> 
> Another issue here is the difference between
> 
> $h->{SQL_FOO}

AFAIK, there are few options: $h->{SQL_FOO} triggers a FETCH and
FETCH calls dbih_get_attr_k, right? Thus, we'll need something like:

 dbih_get_attr_k(h, keysv, dbikey)
SV *h;
SV *keysv;
int dbikey;
 {
...
else if (htype==DBIt_DB && strnEQ(key, "SQL_", 4)) { 
/* XXX DBIt_ST ? */
valuesv = dbih_get_info(h, key, keylen);
}
else {
...

Of course, dbih_get_info needs to be written. I think, it should

 1) map the symbolic name to the numeric code
(via %DBI::Const::GetInfo?)
 2) call $dbh->get_info($code)

And, of course, I need a remedy for my XS-phobia ;-)

> and $h->{SQL_FOO()}  (or $h->{+SQL_FOO} etc)

Personally, I would not import these symbols (I like namespaces).
I know, other people like that ...
If the constants are in a separate module, they have to write:

 use DBI::Const qw( :GetInfo )

Or is

 use DBI qw(...)

the ultimate goal?
Whatever you prefer, it's likely that we need a special import()
method, especially if the constants are implemented as hash values.

> 
> Driver code should naturally be written to use the numbers.
> But users are _bound_ to use the $h->{SQL_FOO} form so there needs
> to be an efficient mechanism to transparently convert the name to
> a number.
> 
> Tim.


Steffen

Re: DBI: get_info() constants

[Tim Bunce]

On Fri, Nov 23, 2001 at 03:16:32PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
>   (see )
> >
> > [And I'd ideally like to be able to detect and warn about misspelt
> > SQL_ attributes.  And I'd also like to make it easy to map the names
> > into their official numbers (to make life easier for drivers,
> > especially DBD::ODBC).  So I was thinking in terms of having the
> > DBI build process pre-process a list of all SQL_* GetInfo attributes
> > (and possibly *all* SQL_* macros) into some efficiently loadable form.]
> 
> Recently, I extented the list of datatype codes for DBI, see:
> 
>  
> 
> These constants are defined via ALIAS. I'm really not very familiar
> with XS, but if this is such an 'efficiently loadable form', then
> the same approach could be applied to the SQLGetInfo() constants:
> 
>  MODULE = DBI   PACKAGE = DBI::Const::GetInfo
> 
>  I32
>  constant()
>  PROTOTYPE:
>  ALIAS:
>   SQL_MAXIMUM_DRIVER_CONNECTIONS= SQL_MAXIMUM_DRIVER_CONNECTIONS
>   SQL_MAXIMUM_CONCURRENT_ACTIVITIES = SQL_MAXIMUM_CONCURRENT_ACTIVITIES
>   ...
>   SQL_OJ_CAPABILITIES   = SQL_OJ_CAPABILITIES
>  CODE:
>  RETVAL = ix;
>  OUTPUT:
>  RETVAL
> 
> 
> Or is this the wrong track?

It could. The downside is more expensive DBI startup.

> As you see, I prefer a separate namespace for SQLGetInfo() constants.
> Why?
> 
>  1) I like namespaces ;-)

Me too. Namespaces are good.

>  2) I encountered some difficulties if namespaces are not preserved,
> see e.g.:
>  
>  3) The requirement to list known constants is stated in
> DBI-1.20/ToDo:
> 
> > Consider making all SQL_ known to the DBI. Want to check for
> > valid spelling. Also would like C{Attrib}> to
> > work. Maybe auto load a module that defines a private hash
> > that just serves to list known attributes.
> > Or DBI->SQL_FOO and/or DBI::SQL_FOO.
> 
> With %DBI::Const::GetInfo::, we would have a hash for that
> purpose.
> 
> Opinions?

As internal implementation, fine. As user interface, probably not.
I think I'd rather stick with the "use DBI qw(...)", but allow
things like "use DBI qw(:get_info_subsetfoo)". Few applications
will use more than a handful of distinct GetInfo calls anyway.

Another issue here is the difference between

$h->{SQL_FOO}
and $h->{SQL_FOO()}  (or $h->{+SQL_FOO} etc)

Driver code should naturally be written to use the numbers.
But users are _bound_ to use the $h->{SQL_FOO} form so there needs
to be an efficient mechanism to transparently convert the name to
a number.

Tim.

DBI: get_info() constants

[Steffen Goeldner]

Tim Bunce wrote:
  (see )
>
> [And I'd ideally like to be able to detect and warn about misspelt
> SQL_ attributes.  And I'd also like to make it easy to map the names
> into their official numbers (to make life easier for drivers,
> especially DBD::ODBC).  So I was thinking in terms of having the
> DBI build process pre-process a list of all SQL_* GetInfo attributes
> (and possibly *all* SQL_* macros) into some efficiently loadable form.]

Recently, I extented the list of datatype codes for DBI, see:

 

These constants are defined via ALIAS. I'm really not very familiar
with XS, but if this is such an 'efficiently loadable form', then
the same approach could be applied to the SQLGetInfo() constants:

 MODULE = DBI   PACKAGE = DBI::Const::GetInfo

 I32
 constant()
 PROTOTYPE:
 ALIAS:
SQL_MAXIMUM_DRIVER_CONNECTIONS= SQL_MAXIMUM_DRIVER_CONNECTIONS
SQL_MAXIMUM_CONCURRENT_ACTIVITIES = SQL_MAXIMUM_CONCURRENT_ACTIVITIES
...
SQL_OJ_CAPABILITIES   = SQL_OJ_CAPABILITIES
 CODE:
 RETVAL = ix;
 OUTPUT:
 RETVAL


Or is this the wrong track?
As you see, I prefer a separate namespace for SQLGetInfo() constants.
Why?

 1) I like namespaces ;-)
 2) I encountered some difficulties if namespaces are not preserved,
see e.g.:

 

 3) The requirement to list known constants is stated in
DBI-1.20/ToDo:

> Consider making all SQL_ known to the DBI. Want to check for
> valid spelling. Also would like C{Attrib}> to
> work. Maybe auto load a module that defines a private hash
> that just serves to list known attributes.
> Or DBI->SQL_FOO and/or DBI::SQL_FOO.

With %DBI::Const::GetInfo::, we would have a hash for that
purpose.

Opinions?


Steffen

Re: DBI: get_info(), MS Access test case

[Steffen Goeldner]

Test environment:

  MS ODBC Test 2.60.0905
  MS Access Driver (*.mdb) 4.00.6019

Attached is the (slightly edited) output of 'Get Info All'.


Steffen

   20 SQL_ACCESSIBLE_PROCEDURES "Y"  
   19 SQL_ACCESSIBLE_TABLES "Y"  
0 SQL_ACTIVE_CONNECTIONS64   
  116 SQL_ACTIVE_ENVIRONMENTS   0
1 SQL_ACTIVE_STATEMENTS 0
  117 SQL_ALTER_DOMAIN  0x   
   86 SQL_ALTER_TABLE   0x0003   SQL_AT_ADD_COLUMN 
| SQL_AT_DROP_COLUMN
10021 SQL_ASYNC_MODE0SQL_AM_NONE
  120 SQL_BATCH_ROW_COUNT   0x   
  121 SQL_BATCH_SUPPORT 0x   
   82 SQL_BOOKMARK_PERSISTENCE  0x0050   SQL_BP_UPDATE | 
SQL_BP_SCROLL
  114 SQL_CATALOG_LOCATION  1SQL_CL_START
10003 SQL_CATALOG_NAME  "Y"  
   41 SQL_CATALOG_NAME_SEPARATOR"."  
   42 SQL_CATALOG_TERM  "DATABASE"   
   92 SQL_CATALOG_USAGE 0x000D   
SQL_CU_DML_STATEMENTS | SQL_CU_TABLE_DEFINITION | SQL_CU_INDEX_DEFINITION
10004 SQL_COLLATION_SEQ ""   
   87 SQL_COLUMN_ALIAS  "Y"  
   22 SQL_CONCAT_NULL_BEHAVIOR  1SQL_CB_NON_NULL
   53 SQL_CONVERT_BIGINT0x   
   54 SQL_CONVERT_BINARY0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   55 SQL_CONVERT_BIT   0x   
   56 SQL_CONVERT_CHAR  0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   57 SQL_CONVERT_DATE  0x   
   58 SQL_CONVERT_DECIMAL   0x   
   59 SQL_CONVERT_DOUBLE0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   60 SQL_CONVERT_FLOAT 0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   48 SQL_CONVERT_FUNCTIONS 0x0001   SQL_FN_CVT_CONVERT
   61 SQL_CONVERT_INTEGER   0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
  123 SQL_CONVERT_INTERVAL_DAY_TIME 0x   
  124 SQL_CONVERT_INTERVAL_YEAR_MONTH   0x   
   71 SQL_CONVERT_LONGVARBINARY 0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   62 SQL_CONVERT_LONGVARCHAR   0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   63 SQL_CONVERT_NUMERIC   0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   64 SQL_CONVERT_REAL  0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   65 SQL_CONVERT_SMALLINT  0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   66 SQL_CONVERT_TIME  0x   
   67 SQL_CONVERT_TIMESTAMP 0x   
   68 SQL_CONVERT_TINYINT   0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   69 SQL_CONVERT_VARBINARY 0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_CVT_VARCHAR | SQL_CVT_WVARCHAR
   70 SQL_CONVERT_VARCHAR   0x008001FA   SQL_CVT_NUMERIC | 
SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT | SQL_CVT_REAL | SQL_CVT_DOUBLE | 
SQL_

Re: DBI: get_info(), Oracle test case - Oracle ODBC

[Steffen Goeldner]

Test environment:

  MS ODBC Test 2.60.0905
  Oracle ODBC 8.1.7

Attached is the (slightly edited) output of 'Get Info All'.
Please note: Some values depend on the server release. However,
I couldn't encounter any SQL query activities (in v$sqlarea).


Steffen

   20 SQL_ACCESSIBLE_PROCEDURES "Y"  
   19 SQL_ACCESSIBLE_TABLES "Y"  
0 SQL_ACTIVE_CONNECTIONS0
  116 SQL_ACTIVE_ENVIRONMENTS   1
1 SQL_ACTIVE_STATEMENTS 0
  117 SQL_ALTER_DOMAIN  0x   
   86 SQL_ALTER_TABLE   0x000FB049   SQL_AT_ADD_COLUMN 
| SQL_AT_ADD_CONSTRAINT | SQL_AT_ADD_COLUMN_DEFAULT | SQL_AT_ADD_TABLE_CONSTRAINT | 
SQL_AT_DROP_TABLE_CONSTRAINT_CASCADE | SQL_AT_CONSTRAINT_NAME_DEFINITION | 
SQL_AT_CONSTRAINT_INITIALLY_DEFERRED | SQL_AT_CONSTRAINT_INITIALLY_IMMEDIATE | 
SQL_AT_CONSTRAINT_DEFERRABLE
10021 SQL_ASYNC_MODE0SQL_AM_NONE
  120 SQL_BATCH_ROW_COUNT   0x   
  121 SQL_BATCH_SUPPORT 0x   
   82 SQL_BOOKMARK_PERSISTENCE  0x0058   
SQL_BP_TRANSACTION | SQL_BP_UPDATE | SQL_BP_SCROLL
  114 SQL_CATALOG_LOCATION  2SQL_CL_END
10003 SQL_CATALOG_NAME  "N"  
   41 SQL_CATALOG_NAME_SEPARATOR"@"  
   42 SQL_CATALOG_TERM  "Database Link"  
   92 SQL_CATALOG_USAGE 0x0003   
SQL_CU_DML_STATEMENTS | SQL_CU_PROCEDURE_INVOCATION
10004 SQL_COLLATION_SEQ ""   
   87 SQL_COLUMN_ALIAS  "Y"  
   22 SQL_CONCAT_NULL_BEHAVIOR  1SQL_CB_NON_NULL
   53 SQL_CONVERT_BIGINT0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   54 SQL_CONVERT_BINARY0x   
   55 SQL_CONVERT_BIT   0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   56 SQL_CONVERT_CHAR  0x0002F1FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT | SQL_CVT_DATE | SQL_CVT_TIMESTAMP
   57 SQL_CONVERT_DATE  0x00028101   SQL_CVT_CHAR | 
SQL_CVT_VARCHAR | SQL_CVT_DATE | SQL_CVT_TIMESTAMP
   58 SQL_CONVERT_DECIMAL   0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   59 SQL_CONVERT_DOUBLE0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   60 SQL_CONVERT_FLOAT 0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   48 SQL_CONVERT_FUNCTIONS 0x0001   SQL_FN_CVT_CONVERT
   61 SQL_CONVERT_INTEGER   0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
  123 SQL_CONVERT_INTERVAL_DAY_TIME 0x   
  124 SQL_CONVERT_INTERVAL_YEAR_MONTH   0x   
   71 SQL_CONVERT_LONGVARBINARY 0x   
   62 SQL_CONVERT_LONGVARCHAR   0x   
   63 SQL_CONVERT_NUMERIC   0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REAL | SQL_CVT_DOUBLE | SQL_CVT_VARCHAR | SQL_CVT_BIT | SQL_CVT_TINYINT | 
SQL_CVT_BIGINT
   64 SQL_CONVERT_REAL  0x71FF   SQL_CVT_CHAR | 
SQL_CVT_NUMERIC | SQL_CVT_DECIMAL | SQL_CVT_INTEGER | SQL_CVT_SMALLINT | SQL_CVT_FLOAT 
| SQL_CVT_REA

DBI: get_info()

[Steffen Goeldner]

Both, SQL/CLI and ODBC define a function 'SQLGetInfo' for obtaining
information about the implementation:

  SQLRETURN SQLGetInfo
  (
SQLHDBC   ConnectionHandle
  , SQLUSMALLINT  InfoType
  , SQLPOINTERInfoValuePtr
  , SQLSMALLINT   BufferLength
  , SQLSMALLINT*  StringLengthPtr
  );

A perlish interface (which already exists) may look like:

  $value = $dbh->get_info( $info_type );

To be even more perlish, Tim suggests that these values are
available as database handle attributes too:

  $value = $dbh->{ $info_type };

See http:[EMAIL PROTECTED]/msg00499.html
for more information.

- ODBC defines more information types than SQL/CLI:

 ODBC SQL/CLI
  --- --- ---
  SQL_ACTIVE_CONNECTIONS0
  SQL_MAXIMUM_DRIVER_CONNECTIONS0   0
  SQL_MAX_DRIVER_CONNECTIONS0
  SQL_INFO_FIRST0
  SQL_ACTIVE_STATEMENTS 1
  SQL_MAX_CONCURRENT_ACTIVITIES 1
  SQL_MAXIMUM_CONCURRENT_ACTIVITIES 1   1
  SQL_DATA_SOURCE_NAME  2   2
  SQL_DRIVER_HDBC   3
  SQL_DRIVER_HENV   4
  SQL_DRIVER_HSTMT  5
  SQL_DRIVER_NAME   6
  SQL_DRIVER_VER7
  SQL_FETCH_DIRECTION   8   8
  SQL_ODBC_API_CONFORMANCE  9
  SQL_ODBC_VER 10
  SQL_ROW_UPDATES  11
  SQL_ODBC_SAG_CLI_CONFORMANCE 12
  SQL_SERVER_NAME  13  13
  SQL_SEARCH_PATTERN_ESCAPE14  14
  SQL_ODBC_SQL_CONFORMANCE 15
  SQL_DBMS_NAME17  17
  SQL_DBMS_VER 18
  SQL_DBMS_VERSION 18
  SQL_ACCESSIBLE_TABLES19
  SQL_ACCESSIBLE_PROCEDURES20
  SQL_PROCEDURES   21
  SQL_CONCAT_NULL_BEHAVIOR 22
  SQL_CURSOR_COMMIT_BEHAVIOR   23  23
  SQL_CURSOR_ROLLBACK_BEHAVIOR 24
  SQL_DATA_SOURCE_READ_ONLY25  25
  SQL_DEFAULT_TXN_ISOLATION26
  SQL_DEFAULT_TRANSACTION_ISOLATION26
  SQL_EXPRESSIONS_IN_ORDERBY   27
  SQL_IDENTIFIER_CASE  28  28
  SQL_IDENTIFIER_QUOTE_CHAR29
  SQL_MAXIMUM_COLUMN_NAME_LENGTH   30  30
  SQL_MAX_COLUMN_NAME_LEN  30
  SQL_MAXIMUM_CURSOR_NAME_LENGTH   31  31
  SQL_MAX_CURSOR_NAME_LEN  31
  SQL_MAX_OWNER_NAME_LEN   32
  SQL_MAXIMUM_SCHEMA_NAME_LENGTH   32  32
  SQL_MAX_SCHEMA_NAME_LEN  32
  SQL_MAX_PROCEDURE_NAME_LEN   33
  SQL_MAX_CATALOG_NAME_LEN 34
  SQL_MAXIMUM_CATALOG_NAME_LENGTH  34  34
  SQL_MAX_QUALIFIER_NAME_LEN   34
  SQL_MAX_TABLE_NAME_LEN   35
  SQL_MAXIMUM_TABLE_NAME_LENGTH35
  SQL_MULT_RESULT_SETS 36
  SQL_MULTIPLE_ACTIVE_TXN  37
  SQL_OUTER_JOINS  38
  SQL_OWNER_TERM   39
  SQL_SCHEMA_TERM  39
  SQL_PROCEDURE_TERM   40
  SQL_CATALOG_NAME_SEPARATOR   41
  SQL_QUALIFIER_NAME_SEPARATOR 41
  SQL_QUALIFIER_TERM   42
  SQL_CATALOG_TERM 42
  SQL_SCROLL_CONCURRENCY   43  43
  SQL_SCROLL_OPTIONS   44
  SQL_TABLE_TERM   45
  SQL_TXN_CAPABLE  46
  SQL_TRANSACTION_CAPABLE  46  46
  SQL_USER_NAME47  47
  SQL_CONVERT_FUNCTIONS48
  SQL_NUMERIC_FUNCTIONS49
  SQL_STRING_FUNCTIONS 50
  SQL_SYSTEM_FUNCTIONS 51
  SQL_TIMEDATE_FUNCTIONS   52
  SQL_CONVERT_BIGINT   53
  SQL_CONVERT_BINARY   54
  SQL_CONVERT_BIT  55
  SQL_CONVERT_CHAR 56
  SQL_CONVERT_DATE 57
  SQL_CONVERT_DECIMAL  58
  SQL_CONVERT_DOUBLE   59
  SQL_CONVERT_FLOAT60
  SQL