List:MySQL ODBC« Previous MessageNext Message »
From:M.-A. Lemburg Date:April 29 1999 11:40am
Subject:Re: Problem with MyODBC
View as plain text  
Michael Widenius wrote:
> 
> >>>>> "M" == M -A Lemburg <mal@stripped> writes:
> 
> <cut>
> 
> M> Well, I guess you both are right: for expressions the column
> M> is temporary and generated by the database, so there's no
> M> reference as to what its precision should be. Yet for raw
> M> database table columns the Appendix D above clearly states
> M> that precision for a CHAR(10) column is 10. For SQL_LONGVARCHAR
> M> and other huge data containers its the maximum length available
> M> (used or not).
> 
> Note that we are talking about the precision of a result set, not
> the precision of the original column.

I know, SQLDescribeCol() is usually used for result sets and table
layout information queried via SQLColumns().

> If you make an SELECT statement, the result set doesn't have to have the
> same precision as the original columns.
> (This is actual my major point here;  I would be very interesting to know
> if you can find ant documentation that proves/disapproves the above.
> If we can settle this issue, then the rest is easy!)
> 
> In this case libmysql emulates a temporary set with an optimized
> CHAR() column.  We could easily do this also in the SQL server, and I
> think it would be perfectly legal to do so.
> 
> (Whenever we introduce cursors in MySQL, this will be done in the
> server!)

Well, true of course, but client software could get confused by
not returning the same precision as in the table definition. The
client software might make a query to find information about
a database table (e.g. do a SELECT * FROM table WHERE 1=0)
and use SQLDescribeCol() instead of SQLColumns() to query the
table layout -- which is perfectly legal according to the
ODBC spec. [It should probably use SQLColumns() instead, but that
APi is not necessarily available everywhere, so the above hack
is a portable alternative].

One example where this is done quite often is through interface
APIs that don't provide access to SQLColumns(), e.g. the standard
Python DB API only supports the above hack (since it's the only
portable way of querying the information other than using system
tables directly).

> >> 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!
> 
> M> Note that a properly coded client will use SQLGetData() to fetch
> M> SQL with precision beyond a certain limit; they will not simply
> M> try a malloc(precision) to obtain buffer space for the column.
> 
> With MySQL TEXT/BLOB handling this coding is often unnecessary. If you
> always select short TEXT/BLOB:s I see no reason why you should have to
> use SQLGetData();   SQLGetData() is for big results!

I think you're mixing up targets here: for me as an ODBC client
software designer I can choose whatever means I pefer to get
the information I want and I would probably take the SQLColumns()
approach. But the situation is different for an ODBC user: he
will just want to use software XYZ to access the data and if
that software happens to be using a different approach, then
the server software will have to adapt (or mark XYZ as non compatible).

> >> 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 !
> 
> M> Actually, I don't think so. TEXT and BLOB column are passed to
> M> the client as SQL_LONGVARCHAR or SQL_LONGVARBINARY types and these
> M> are certainly transferred using SQLGetData() rather than via a
> M> malloced buffer and SQLBindCol().
> 
> This depends on the client;  It's perfectly legal to use CHAR
> bindings on short TEXT columns and I would be very surprised if no ODBC
> client would do this;  These will probably crash if the MyODBC reports
> that the precision is 24M!

But it's a perfectly legal output, so it's not the server that's
doing anything wrong here, it's the client.
 
> As the use of TEXT/BLOB is very normal with MySQL (because they are
> very simple to use), you will get problems with simple clients that
> doesn't use (or need) SQLGetData() to handle TEXT.

I would assume that these simple client don't ODBC anyway... it's
just way too complicated for quick simple hacks.

> PS: I agree that I am confused about the ODBC specification;  My only
>     defense is that it's very unlikely that I am the only one :)

You're not alone ;-)

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