Re: An extra null row is returned if rowset size is a multiple

** Reply to message from "Wayne Armstrong" <wdarmst@bacchus.com.au> on Fri, 18
Apr 2003 20:04:02 +1000
Hmm,
 Talking to myself again :)
 Ok the problem seems to be in qresult.c
 QR_next_tuple when it gets an end_of_tupple message from the backend - setups
a pointer to the first cached row and returns true if any rows hae been
retrieved for the query as a whole (rather than for this fetch)
The offending bit of code looks like:-
                               if (self->num_total_rows > 0)

                                {
                                        qlog("    [ fetched %d rows ]\n",
num_rows);
                                        mylog("_next_tuple: 'C' fetch_max &&
fcount = %d\n", self->num_total_rows);

                                        /* set to first row */
                                        self->tupleField =
self->backend_tuples+ (offset * self->num_fields);
                                        return TRUE;
                                }
                                else
                                {
I have changed that to look at if or not it retrieved any rows on this fetch
attempt and the problem goes away.

Regards,
Wayne

> Hi,
>  The odbc driver is returning an extra null filled row when the number of rows
> to be retrieved is a multiple of the row cache size set in the odbc driver. If
> this occurs when issuing a SQLColumns or SQLtables request, the driver will
> return an error trying to convert the null filled row into column or table row
> values.
>  In normal usage, my smalltalk based app, can abend because the final row
> returned has nulls in columns that cannot possibly be null.
>
>  I have had a look at the current cvs source, and cannot determine where the
> problem is (I am not a c progremmer - but can maintain c (with difficulty and
> under protest  :).)
>
> This is a serious problem for us which we are hitting fairly often and need to
> fix real soon. I am continuing to read the odbc driver code - but any pointers
> for what to try/where to look  would be real usefull.
>
>
> My odbc settings are :-
> Recognize unique indexes,
> Use Declare fetch
> Parse statements
> Unknown sizes - longest
> Text a long varchar
> unknown as long varchar
> bools as char
> cache size 25
> updateable cursors
> dissalow premature.
>
> Here is the bit of the odbc log where it goes wrong (I think).
> This is retrieving 50 rows with a cache size of 25.
>
> [848]next_tuple: fetch_count < fcount: returning tuple 24, fcount = 25
> [848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658672
> [848]type = 23
> [848]value = '0'
> [848]copy_and_convert: field_type = 23, fctype = 4, value = '0', cbValueMax=4
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658680
> [848]type = 1043
> [848]value = 'WB-OST'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WB-OST',
> cbValueMax=16
> [848]DEFAULT: len = 6, ptr = 'WB-OST'
> [848]    SQL_C_CHAR, default: len = 6, cbValueMax = 16, rgbValueBindRow =
> 'WB-OST'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658700
> [848]type = 1043
> [848]value = 'WHEEL'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'WHEEL',
> cbValueMax=16
> [848]DEFAULT: len = 5, ptr = 'WHEEL'
> [848]    SQL_C_CHAR, default: len = 5, cbValueMax = 16, rgbValueBindRow =
> 'WHEEL'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658720
> [848]type = 1043
> [848]value = 'Wheel Bearing Repack - O/S/T'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Wheel Bearing
> Repack - O/S/T', cbValueMax=31
> [848]DEFAULT: len = 28, ptr = 'Wheel Bearing Repack - O/S/T'
> [848]    SQL_C_CHAR, default: len = 28, cbValueMax = 31, rgbValueBindRow =
> 'Wheel Bearing Repack - O/S/T'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658755
> [848]type = 1043
> [848]value = 'No'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'No',
> cbValueMax=11
> [848]DEFAULT: len = 2, ptr = 'No'
> [848]    SQL_C_CHAR, default: len = 2, cbValueMax = 11, rgbValueBindRow = 'No'
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658770
> [848]type = 1043
> [848]value = 'Service'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = 'Service',
> cbValueMax=16
> [848]DEFAULT: len = 7, ptr = 'Service'
> [848]    SQL_C_CHAR, default: len = 7, cbValueMax = 16, rgbValueBindRow =
> 'Service'
> [848]copy_and_convert: retval = 0
> [848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
> [848]SQL_FETCH_NEXT: num_tuples=50, currtuple=25
> [848]PGAPI_ExtendedFetch: new currTuple = 49
> [848]manual_result = 0, use_declarefetch = 1
> [848]clear obsolete 25 tuples
> [848]next_tuple: sending actual fetch (25) query 'fetch 25 in SQL_CUR04193E38'
> [848]send_query(): conn=18913808, query='fetch 25 in SQL_CUR04193E38'
> [848]send_query: done sending query
> [848]send_query: got id = 'Z'
> [848]read 175, global_socket_buffersize=4096
> [848]send_query: got id = 'P'
> [848]send_query: got id = 'T'
> [848]num_fields = 6
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='company_id', adtid=23, adtsize=4, atttypmod=-1
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_code', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_type_code', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='service_desc', adtid=1043, adtsize=-1,
> atttypmod=30
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='print_jobcard', adtid=1043, adtsize=-1,
> atttypmod=10
> [848]READING ATTTYPMOD
> [848]CI_read_fields: fieldname='sheet_type_flag', adtid=1043, adtsize=-1,
> atttypmod=15
> [848]end of tuple list -- setting inUse to false: this = 18913232
> [848]_next_tuple: 'C' fetch_max && fcount = 50
> [848]fetch: cols=6, lf=0, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658672
> [848]type = 23
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 23, fctype = 4, value = '<NULL>',
> cbValueMax=4
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=1, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658680
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=2, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658700
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=3, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658720
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=31
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=4, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658755
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=11
> [848]copy_and_convert: retval = 0
> [848]fetch: cols=6, lf=5, opts = 68763272, opts->bindings = 68751064, buffer[]
> = 1658770
> [848]type = 1043
> [848]value = '<NULL>'
> [848]copy_and_convert: field_type = 1043, fctype = 1, value = '<NULL>',
> cbValueMax=16
> [848]copy_and_convert: retval = 0
> [848]manual_result = 0, use_declarefetch = 1
> [848]next_tuple: fcount < CACHE_SIZE: fcount = 0, fetch_count = 1
> [848]**** SC_fetch: end_tuples
> [848][SQLExtendedFetch][848]PGAPI_ExtendedFetch: stmt=68763192
> [848]SQL_FETCH_NEXT: num_tuples=50, currtuple=50
> [848][SQLFreeStmt][848]PGAPI_FreeStmt: entering...hstmt=68763192, fOption=0
>
>
>
> Regards,
> Wayne Armstrong
> Bacchus Management Systems
> http://www.bacchus.com.au
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org