From: M.-A. Lemburg Date: April 29 1999 11:40am Subject: Re: Problem with MyODBC List-Archive: http://lists.mysql.com/myodbc/182 Message-Id: <37284531.7DFB1B8C@lemburg.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Michael Widenius wrote: > > >>>>> "M" == M -A Lemburg writes: > > > > 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/ : ---------------------------------------------------------