FreeTDS Development Group

[Patrick Surry <pds AT quadstone.com>]

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.


--
_________________________________________________________________________
Patrick Surry, PhD  Tel 617 457 5200 Fax 617 457 5299   www.quadstone.com


--
_________________________________________________________________________
Patrick Surry, PhD  Tel 617 457 5200 Fax 617 457 5299   www.quadstone.com