Re: Errors in DBI docs

[Martin J. Evans]

On 22/12/12 17:56, Lyle wrote:

On 22/12/2012 09:14, Tim Bunce wrote:

I have a copy of the SQL:2011 standard, yet I cannot seem to find a
list of numbers correlating to data types? Is this DBI specific? Is
it still supported? Am I missing something?

It's not DBI specific. I've attached the copy I have.


Thanks. This has led me to the relevant section of the 2011 (which is the same 
as 2008) SQL/CLI page 80:
 Table 7, "Codes used for implementation data types in SQL/CLI", specifies 
the codes associated with the
SQL data types used in implementation descriptor areas.

Now my current challenge is finding the ODBC definitions, which has thus far 
been unfruitful. I've been right through the current ODBC documentation:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710252%28v=vs.85%29.aspx
Failing that, tried finding the ODBC SDK. Searches in MSDN downloads only 
seemed to turn up MDAC:
http://www.microsoft.com/en-us/download/details.aspx?id=21995
Which didn't include the header files mentioned here (which I assume must 
contain the codes I seek):
http://msdn.microsoft.com/en-us/library/windows/desktop/ms713603%28v=vs.85%29.aspx

I did manage to find some second hand references to ODBC data type codes from 
SQL Server 2000:
http://msdn.microsoft.com/en-us/library/aa179135%28v=SQL.80%29.aspx
and open XML:
http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.parameter.aspx

On google I found some notes from Denis Box at Fermilab USA, who had the same 
troubling finding them (interesting as he references your book as the only 
place he could find them):
http://home.fnal.gov/~dbox/SQL_API_Portability.html

... Ah, found a copy of the header files, they come with the Windows 7 SDK:
http://www.microsoft.com/en-us/download/details.aspx?id=3138
By default it'll try to install all sorts, but you only need to select the 
header files. sqlext.h contains the ODBC SQL extended datatype codes.


You can just download a recent unixODBC source tar ball and you'll find all the 
headers in there. They won't be identical to the ones on Windows but the ODBC 
constants should be.

ftp://ftp.unixodbc.org/pub/unixODBC/


I'm still doing this post because:
A) Others who get stuck looking for this may find this post. Google brings up 
the DBI archives quite a lot for non DBI specific searches on this kind of thing
B) Checking the header file doesn't seem like the ideal solution. Somebody 
might know of an official MS resource where this is properly defined?


If I can help with this, let me know, I can submit patches.

I'd be delighted to get patches updating those. Thanks!


Working on it. If no one comes back with a better resource, should I add a 
description of how to find the ODBC codes from the header files?


Lyle




Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: Errors in DBI docs

[Lyle]

On 22/12/2012 09:14, Tim Bunce wrote:

I have a copy of the SQL:2011 standard, yet I cannot seem to find a
list of numbers correlating to data types? Is this DBI specific? Is
it still supported? Am I missing something?

It's not DBI specific. I've attached the copy I have.


Thanks. This has led me to the relevant section of the 2011 (which is 
the same as 2008) SQL/CLI page 80:
Table 7, "Codes used for implementation data types in SQL/CLI", 
specifies the codes associated with the

SQL data types used in implementation descriptor areas.

Now my current challenge is finding the ODBC definitions, which has thus 
far been unfruitful. I've been right through the current ODBC documentation:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms710252%28v=vs.85%29.aspx
Failing that, tried finding the ODBC SDK. Searches in MSDN downloads 
only seemed to turn up MDAC:

http://www.microsoft.com/en-us/download/details.aspx?id=21995
Which didn't include the header files mentioned here (which I assume 
must contain the codes I seek):

http://msdn.microsoft.com/en-us/library/windows/desktop/ms713603%28v=vs.85%29.aspx

I did manage to find some second hand references to ODBC data type codes 
from SQL Server 2000:

http://msdn.microsoft.com/en-us/library/aa179135%28v=SQL.80%29.aspx
and open XML:
http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.parameter.aspx

On google I found some notes from Denis Box at Fermilab USA, who had the 
same troubling finding them (interesting as he references your book as 
the only place he could find them):

http://home.fnal.gov/~dbox/SQL_API_Portability.html

... Ah, found a copy of the header files, they come with the Windows 7 SDK:
http://www.microsoft.com/en-us/download/details.aspx?id=3138
By default it'll try to install all sorts, but you only need to select 
the header files. sqlext.h contains the ODBC SQL extended datatype codes.


I'm still doing this post because:
A) Others who get stuck looking for this may find this post. Google 
brings up the DBI archives quite a lot for non DBI specific searches on 
this kind of thing
B) Checking the header file doesn't seem like the ideal solution. 
Somebody might know of an official MS resource where this is properly 
defined?



If I can help with this, let me know, I can submit patches.

I'd be delighted to get patches updating those. Thanks!


Working on it. If no one comes back with a better resource, should I add 
a description of how to find the ODBC codes from the header files?



Lyle

Re: Errors in DBI docs

[Tim Bunce]

On Sat, Dec 22, 2012 at 09:14:06AM +, Tim Bunce wrote:
> On Fri, Dec 21, 2012 at 11:38:37PM +, Lyle wrote:
> > Hi,
> >   From working through the docs I've hit some issues.
> 
> >   ftp://sqlstandards.org/SC32/SQL_Registry/
> 
> > This FTP link no longer works, looks like it hasn't for some time:
> > http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31292.html
> > 
> > Then surly this link should be updated? Perhaps some guidance to
> > contact Jim?
> 
> I'll do that.
> 
> > I have a copy of the SQL:2011 standard, yet I cannot seem to find a
> > list of numbers correlating to data types? Is this DBI specific? Is
> > it still supported? Am I missing something?
> 
> It's not DBI specific. I've attached the copy I have.

Ooops. Here it is.

Tim.


Registry of Values for the SQL Standard
(ANSI X3.135 and ISO/IEC 9075)
With especial attention to values related to
SQL/CLI ([ANSI/]ISO/IEC 9075-3)
Registry maintained by Jim Melton
(jim.mel...@sybase.com)
1. Introduction
This registry lists the ranges of values that have been reserved for various 
numeric values and strings within SQL/CLI and other parts of the SQL standard.  
Certain ranges are allocated to ISO, to X/Open (and potentially to other 
consortia), and to specific vendors. Adherence to this registry ensures that 
the same value will not be defined by two different sources to represent 
incompatible behaviour.
Each entry in the registry specifies who may define values, not who may use 
them.  Vendors may use a value in any range, provided they uses the value as 
specified by the source to which its range is registered.
For example, values in ranges reserved to ISO are also used in the X/Open 
SQL/CLI specification in cases where the ISO SQL/CLI International Standard 
covers the same material, while ranges registered to X/Open are used for 
material that has not yet been adopted by ISO.
Vendors may well choose to define multiple values for the same feature. Useful 
vendor features may be specified in future versions of SQL/CLI to promote 
application portability, and promote standardisation of the specifications.  At 
such time as a feature is adopted for the SQL/CLI standards, this registry will 
define relevant values occupying the appropriate range.  In these cases, this 
registry may change the definition of the ranges in future editions, as an 
alternative less intrusive to existing applications than redefining specific 
values.
All ranges include both endpoints.
The legend "In Use" indicates ranges that are already in use by various 
implementations.  Vendors should not request values within these ranges.  
However, there may be unused values within those ranges.
Portable applications should use only the values defined in this document, and 
should refer to them using symbolic names wherever possible.
This registry is available on the SQL Standards Archives at the following URL:
ftp://jerry.ece.umassd.edu/isowg3/dbl/SQL_Registry

2. 
Function Return Values
Each SQL/CLI function returns an SQL SMALLINT indicating the function result.  
This table lists portable return values from functions and specifies the 
meaning of each.

Lower Value Upper Value Registered
User
-1001   ISO
2   99  Microsoft
100 199 X/Open
3. Handle Types
The AllocHandle() and FreeHandle() functions take as the HandleType argument an 
SQL SMALLINT that specifies the type of handle to be allocated or freed.

Lower Value Upper Value Registered
User
1   100 ISO
4. Attributes
An SQL INTEGER value as an argument to an SQL/CLI function that sets or gets an 
attribute specifies the attribute in question.
On some implementations, there is a single number space for attributes of all 
types of handle.  For instance, in some implementations, an INTEGER value 
defined as a statement attribute can also be used as a connection attribute; 
doing so may let the application specify a default value for the statement 
attribute whenever it creates a statement handle within that connection. This 
registry intends to never specify the same value as attributes of different 
handle types, and advises vendors to adhere to this rule.
4.1. Environment, Connection, and Statement Attributes
Lower Value Upper Value Registered
User
All negative values ISO
-150-101ISO/IEC 9579 (Remote Database Access)
0   1000In Use
10011019MicroRim
10201039FFE Software
10401139Intersolv
11401149Rochester Software
11501199Simba Technologies, Inc.
12001249Microsoft Corp.
12501299IBM DB2 CLI
13001399IBI
14001449NCR
14501499Oracle
15001549Fulcrum
15501599Walldata
16001649Showcase AS/400
16501699Visigenic
17001749Progress Software Corp.
17501799Fujitsu Limited
18001849Tandem Computers
18501899NEON Systems
19001949Watcom (a Sybase company)
19501959VMARK Software, Inc.
2001

Re: Errors in DBI docs

[Tim Bunce]

On Fri, Dec 21, 2012 at 11:38:37PM +, Lyle wrote:
> Hi,
>   From working through the docs I've hit some issues.

>   ftp://sqlstandards.org/SC32/SQL_Registry/

> This FTP link no longer works, looks like it hasn't for some time:
> http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31292.html
> 
> Then surly this link should be updated? Perhaps some guidance to
> contact Jim?

I'll do that.

> I have a copy of the SQL:2011 standard, yet I cannot seem to find a
> list of numbers correlating to data types? Is this DBI specific? Is
> it still supported? Am I missing something?

It's not DBI specific. I've attached the copy I have.

> Likewise, here:
> http://search.cpan.org/~timb/DBI-1.622/DBI.pm#Standards_Reference_Information
> The link to:
> http://www.sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_drafts/iso-9075-2-1999.bnf
> Is broken.
> 
> As are:
> http://www.jcc.com/SQLPages/jccs_sql.htm
> http://www.altavista.com/query?q=sql+tutorial
> 
> 
> I'm guessing that might want to be updated to a google link.
> 
> If I can help with this, let me know, I can submit patches.

I'd be delighted to get patches updating those. Thanks!

Tim.

Errors in DBI docs

[Lyle]

Hi,
  From working through the docs I've hit some issues.

=start

TYPE

Type: array-ref, read-only

Returns a reference to an array of integer values for each column. The 
value indicates the data type of the corresponding column.


The values correspond to the international standards (ANSI X3.135 and 
ISO/IEC 9075) which, in general terms, means ODBC. Driver-specific types 
that don't exactly match standard types should generally return the same 
values as an ODBC driver supplied by the makers of the database. That 
might include private type numbers in ranges the vendor has officially 
registered with the ISO working group:


  ftp://sqlstandards.org/SC32/SQL_Registry/

=end

This FTP link no longer works, looks like it hasn't for some time:
http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31292.html

Then surly this link should be updated? Perhaps some guidance to contact 
Jim?


I have a copy of the SQL:2011 standard, yet I cannot seem to find a list 
of numbers correlating to data types? Is this DBI specific? Is it still 
supported? Am I missing something?


Likewise, here:
http://search.cpan.org/~timb/DBI-1.622/DBI.pm#Standards_Reference_Information
The link to:
http://www.sqlstandards.org/SC32/WG3/Progression_Documents/Informal_working_drafts/iso-9075-2-1999.bnf
Is broken.

As are:
http://www.jcc.com/SQLPages/jccs_sql.htm
http://www.altavista.com/query?q=sql+tutorial 



I'm guessing that might want to be updated to a google link.

If I can help with this, let me know, I can submit patches.


Lyle