List:MySQL ODBC« Previous MessageNext Message »
From:M.-A. Lemburg Date:April 29 1999 7:47am
Subject:Re: Problem with MyODBC
View as plain text  
Michael Widenius wrote:
> 
> >>>>> "Christian" == Christian Kirsch <ck@stripped> writes:
> 
> Christian> SQLDescribeCol is supposed to return the precision of a
> Christian> column in the result set. The precision is defined as "The
> Christian> maximum number of digits used by the data type of the column
> Christian> or parameter." The following table states clearly that the
> Christian> precision for a CHAR(10) column must be 10. However, MyODBC
> Christian> "optimizes" this into returning the maximum number of
> Christian> characters _used_ for a column in the current statement. If,
> Christian> for example, I do a "SELECT name FROM addresses where
> Christian> name='Miller'", SQLDescribeCol would return 6 as precision
> Christian> for column 1. To me, this seems to be a violation of the
> Christian> ODBC requirements. I'm aware that one can set OPTION to 1 in
> Christian> the SQL connect string to get the correct behavior. However,
> Christian> I suggest that the OPTION parameter should rather allow to
> Christian> _deviate_ from the correct behavior, not _enforce_ it.
> 
> I have checked this up a couple of times;  According to Microsoft's
> ODBC specification, SQLDescribeCol returns:
> 
> 'The column name, type and length generated by the sql statement'.

This is the definition according to ODBC 2.0 for the pcbColDef
parameter in SQLDescribeCol():

The precision of the column
on the data source. If the
precision cannot be
determined, the driver
returns 0. For more
information on precision, see
"Precision, Scale, Length,
and Display Size" in
Appendix D, "Data Types."
 
> I can't find anything that says that it's not allowed to create a
> temporary set of the SELECT statement with lower CHAR() bounds!
>
> For columns that includes expressions, this is definitely allowed!

Well, I guess you both are right: for expressions the column
is temporary and generated by the database, so there's no
reference as to what its precision should be. Yet for raw
database table columns the Appendix D above clearly states
that precision for a CHAR(10) column is 10. For SQL_LONGVARCHAR
and other huge data containers its the maximum length available
(used or not).

> ODBC provides the SQLColumns() call if you want to get information about
> a column in a table.  SQLDescribeCol() is used to get a result of
> a result column.  The only reason to provide both functions is to
> allow optimization like the one MySQL does.
> 
> The big problem is that the ODBC specification is not the least clear on this
> point (this is just one of many unclear points!)
> 
> Where did you find the above information?  I am using 'Microsoft ODBC
> 3.0, Programmer's Reference, Volume 2' !
> 
> The problem is that the current optimization helps you save a lot of
> memory in your client, if your client is correctly coded.
> For example, TEXT types will be very hard to handle (read almost
> impossible) without this optimization!

Note that a properly coded client will use SQLGetData() to fetch
SQL with precision beyond a certain limit; they will not simply
try a malloc(precision) to obtain buffer space for the column.
 
> The major problem is that if I change the default, its very likely
> that I break a lot of ODBC clients that are using the TEXT or BLOB column !

Actually, I don't think so. TEXT and BLOB column are passed to
the client as SQL_LONGVARCHAR or SQL_LONGVARBINARY types and these
are certainly transferred using SQLGetData() rather than via a
malloced buffer and SQLBindCol().

Cheers,
-- 
Marc-Andre Lemburg                               Y2000: 246 days left
---------------------------------------------------------------------
          : Python Pages >>> http://starship.skyport.net/~lemburg/  :
           ---------------------------------------------------------

Thread
Problem with MyODBCChristian Kirsch28 Apr
  • Problem with MyODBCMichael Widenius29 Apr
  • Re: Problem with MyODBCPeter Harvey29 Apr
  • Re: Problem with MyODBCM.-A. Lemburg29 Apr
    • Re: Problem with MyODBCMichael Widenius29 Apr
  • Re: Problem with MyODBCM.-A. Lemburg29 Apr