FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

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