Defining type_info Was: DBIstcf_DISCARD_STRING and DBIstcf_STRICT

On 05/02/2013 09:09, Martin J. Evans wrote:
> On 05/02/13 00:46, Lyle wrote:
>> Hi All, I just submitted bug 83132. It's nothing major, but after
>> upgrading to a newer DBI my comparison tool reported SQL_CHAR and
>> SQL_NUMERIC as DBIstcf_DISCARD_STRING and DBIstcf_STRICT. I could be
>> wrong, but it seems the DBI Constants example isn't totally clear on
>> what gets returned.
>>
>>
>> Lyle
>>
>
> As I've commented on the RT that was my fault. If we decide on a tag 
> name I will move it.

I saw in the change log, I didn't want to point fingers :)

While I have you, I'm still messing with type_info_all. Looking at 
SQLGetTypeInfo from ODBC it's defined as:
*SQLGetTypeInfo* returns information about data types supported by the 
data source. The driver returns the information in the form of an SQL 
result set. The data types are intended for use in Data Definition 
Language (DDL) statements.

So as Greg said before, it's a case of the user requesting a data type, 
and being told if it's available (with details of it's local type name 
for DDL). So that said, why not map one type to all that it can match? 
Also, whether this should be taken further, and return type definitions 
that effectively emulate the one that's being asking for. This is what 
happens if you ask Oracle for an INT in your DDL, it just gives you a 
NUMBER(38):
http://www.sysdba.de/oracle-dokumentation/11.1/server.111/b28286/sql_elements001.htm#i54335

Oracle command line output:
SQL> create table test_int ( nummy INT );
Table created.
SQL> desc test_int;
  Name                                      Null?    Type
  ----------------------------------------- -------- 
----------------------------
  NUMMY                                              NUMBER(38)

What I'm having to do is extend the output from type_info_all, to 
include such multiple mappings and emulations. I'm trying to figure how 
much of this I should try to feed back into the DBDs.

It would help a lot, and what I think is needed, is a clear definition 
as to the purpose of type_info_all, and more guidance for DBD developers 
on how mappings should be carried out.

In the DBI docs it states:
Since DBI and ODBC drivers vary in how they map their types into the ISO 
standard types you may need to search for more than one type.

Should this really be something put on the user, as opposed to giving 
them the best mapping for that type if there is one? It seems like it's 
adding extra search effort for them that they shouldn't necessarily need 
to do. As you said, it appears the only guidance DBD authors get is to 
pull the information from the equivalent ODBC driver:

Metadata.pm#Generating_a_TypeInfo_package_for_a_driver

And from DBI::DBD with regards to type_info it states:
Writing DBD::Driver::db::type_info
The guidelines on writing this method are still not really clear. No 
sample implementation is available.

A final bit from the documentation, in DBI.pm's POD with regard to 
type_info_all it suggests:

Drivers are also free to return extra driver-specific columns of 
information - though it's recommended that they start at column index 50 
to leave room for expansion of the DBI/ODBC specification.

Although I've yet to find a driver that has followed this guidance. They 
all appear to have just adding driver specific columns into the next 
available slot (19, 20, etc.).

I think at this point, a big question is: Does the DBI just want to 
emulate ODBC's SQLSetTypeInfo function, or does it want to have 
something that goes a bit further and possibly does it a bit better?


Lyle