Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Patrick Surry <pds AT quadstone.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Multi-record SQLFetch in ODBC layer?
  • Date: Fri, 13 Feb 2004 08:11:19 -0500

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






Archive powered by MHonArc 2.6.24.

Top of Page