Skip to Content.
Sympa Menu

freetds - Re: [freetds] Multi-record SQLFetch in ODBC layer?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Multi-record SQLFetch in ODBC layer?
  • Date: 13 Feb 2004 20:17:03 +0100

Il ven, 2004-02-13 alle 14:11, Patrick Surry ha scritto:
> Now, I'm no expert on ODBC but have been trying to use FreeTDS ODBC driver
> on
> HPUX to allow some inhouse ODBC-aware data-analysis s/w to pull data from
> MSSQL.
>
> I've successfully installed and tested the driver with iODBC driver-manager
> (eg. freetds tests, iodbc tests and perl DBI:ODBC interface all work fine).
>
> However, our s/w tries to use multi-record fetch for performance reasons
> and
> gets confused about the warning (rather than error) generated by FreeTDS
> which
> apparently only supports single-record fetch at the ODBC layer (one of our
> developers has explained with reference to the FreeTDS source code below).
> Our
> engineers will make a patch to adapt to the FreeTDS ODBC driver, but I have
> a
> couple of questions that I thought someone on this list might be able to
> answer:
>
> - is our use of multi-record fetch via ODBC a 'non-standard' practice (and
> hence why it's not currently supported)?
>
> - does FreeTDS ODBC support of only single-record fetch have a performance
> impact? Or is the underlying TDS layer doing and caching a multi-record
> fetch
> and feeding single records to the ODBC layer, so that performance is not
> much
> different?
>
> Regards,
> Patrick
>
> >> Well, here is the culprit, the code in FreeTds src/odbc/odbc.c
> >> for SQLSetStmtAttr when passing in our request to block fetch
> >> at 24756 records looks like the following:
> >>
> >> case SQL_ATTR_ROW_ARRAY_SIZE:
> >> assert(stmt->ard->header.sql_desc_array_size == 1);
> >> if (stmt->ard->header.sql_desc_array_size != ui) {
> >> odbc_errs_add(&stmt->errs, "01S02", NULL, NULL);
> >> ODBC_RETURN(stmt, SQL_SUCCESS_WITH_INFO);
> >> }
> >> stmt->ard->header.sql_desc_array_size = ui;
> >> break;
> >>
> >> The value we are passing in is mapped to 'ui'.
> >> As you can see their implementation hardwires the fetch to be just
> >> s single record at a time. Because their code returns a warning and
> >> not an error, our code thinks its able to fetch 24576, atcually
> >> processes just a single record at a time but is under the
> >> misapprehension that its really dealing in blocks of 24576 hence
> >> why only your first record is populated with data, the rest wuth
> >> nulls and why it takes a long time to execute.
>
>
> ...
>
> >> Now I can fix our code to query what the setting of ATTR_ROW_ARRAY_SIZE
> >> is after we set it in order to workaround this, but we will still be
> >> left with this implementation of FreeTDS ODBC driver not supporting
> >> block fetches. The underlying TDS library does support this and I
> >> don't understand yet why the ODBC driver cannot (there is another
> >> setting to do with block inserts which is supported).
>
>
> ...
>
> >> So I could fix our driver to cope with FreeTDS ODBC driver, but
> >> I'd like to see the FreeTDS ODBC driver be block fetch enabled.
>

Previous Bill post is absolutely right. About ODBC our driver as you
noted multi-row fetch is not supported. The reason is quite
complicated... first of all drivers are able to refuse
ATTR_ROW_ARRAY_SIZE change and result from SQLSetStmtAttr is correct so
any portable ODBC application should handle this case. To support
multi-row fetch you need to
1- accept record size (SQL_DESC_BIND_TYPE != SQL_PARAM_BIND_BY_COLUMN)
2- store multiple results (SQL_DESC_ROWS_PROCESSED_PTR and
SQL_DESC_ARRAY_STATUS_PTR)
3- handle SQLGetData case. With unbinded column you stop and leave
client the possibility to read... I don't really know how to handle this
case... stop at first row... discard first rows and stop at last ? Some
tests are needed.
So, current implementation is correct and I don't think that a local
call can decrease performance so much...

freddy77






Archive powered by MHonArc 2.6.24.

Top of Page