Re: SQLFetchScroll with SQL_ATTR_ROWS_FETCHED_PTR closing statement.

Hi,

I have encountered some problems with SQLFetchScroll when
SQL_ATTR_ROWS_FETCHED_PTR attribute is set. The statement is closed
after first call to SQLFetchScroll. This first call is successful the
numbers of fetched rows correct, but the statement handle, is after the
call, invalid and can not be used in any other SQLFetchScroll calls.

// table test with 'id' integer column and 20 rows.
The code looks like that:
alloc_stmt(hdbc, &hstmt);
SQLPrepare(hstmt, (SQLCHAR *)"select * from test", SQL_NTS);
SQLExecute(hstmt);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, 1, 0);
SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, rowStatus, 0);
SQLBindCol(hstmt, 1, SQL_C_LONG, idout, sizeof(SQLINTEGER), indicator);
SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
// all above is success and rowStatus and idout arrays are correctly
// updated.

SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
// fails with error INVALID HANDLE

Is my usage of the fetch scroll function incorrect?

I am using unixODBC version: 2.3.1
with psqlodbc-09.02.0100 driver
and Postgresql 9.3.1 server.

Kind regards,
Przemyslaw

On 11/26/2013 10:13 AM, Przemyslaw Rzepecki wrote:
> Hi,
>
> I have encountered some problems with SQLFetchScroll when
> SQL_ATTR_ROWS_FETCHED_PTR attribute is set. The statement is closed
> after first call to SQLFetchScroll. This first call is successful the
> numbers of fetched rows correct, but the statement handle, is after the
> call, invalid and can not be used in any other SQLFetchScroll calls.
>
> // table test with 'id' integer column and 20 rows.
> The code looks like that:
> alloc_stmt(hdbc, &hstmt);
> SQLPrepare(hstmt, (SQLCHAR *)"select * from test", SQL_NTS);
> SQLExecute(hstmt);
> SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
> SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, 1, 0);
> SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, rowStatus, 0);
> SQLBindCol(hstmt, 1, SQL_C_LONG, idout, sizeof(SQLINTEGER), indicator);
> SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
> // all above is success and rowStatus and idout arrays are correctly
> // updated.
>
> SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
> // fails with error INVALID HANDLE
>
> Is my usage of the fetch scroll function incorrect?

Looks correct to me. I tried to reproduce this with the attached test
program (modified from the regression tests), and it worked fine.

- Heikki

Hi,

Thanks for the answer! I have compare my program with Yours didn't
noticed any logical difference. Then tried rerun the tests on two
different systems, any my tests ware passing on one but the errors ware
still present on the other.

Tried to step by step eliminate all the differences and I believe its
the libodbcinst.so.2 vs libodbcinst.so.1 which cause the difference. It
works fine with unixodbc 1 but when I use a driver recompiled against
unixodbc 2 then the error happens.

Could You let me know which odbc libraries You use on your system? Is it
possible for someone else to retest with odbc2 libraries?

Best regards,
Przemek

On Tue, 2014-03-04 at 16:21 +0200, Heikki Linnakangas wrote:
> usage of the fetch scroll function incorrect?
>
> Looks correct to me. I tried to reproduce this with the attached test
> program (modified from the regression tests), and it worked fine.
>
> - Heikki

On 03/06/2014 07:05 PM, Przemyslaw Rzepecki wrote:
> Hi,
>
> Thanks for the answer! I have compare my program with Yours didn't
> noticed any logical difference. Then tried rerun the tests on two
> different systems, any my tests ware passing on one but the errors ware
> still present on the other.
>
> Tried to step by step eliminate all the differences and I believe its
> the libodbcinst.so.2 vs libodbcinst.so.1 which cause the difference. It
> works fine with unixodbc 1 but when I use a driver recompiled against
> unixodbc 2 then the error happens.
>
> Could You let me know which odbc libraries You use on your system? Is it
> possible for someone else to retest with odbc2 libraries?

I use what ships with Debian. Seems to be unixodbc 2.3.1.

Seems odd. I don't think the driver depends on libodbcinst.so. Could it
be caused by some kind of confusion between 32-bit and 64-bit versions
of libraries? Where did those libraries come from?

- Heikki

(2014/03/07 2:05), Przemyslaw Rzepecki wrote:
> Hi,
>
> Thanks for the answer! I have compare my program with Yours didn't
> noticed any logical difference. Then tried rerun the tests on two
> different systems, any my tests ware passing on one but the errors ware
> still present on the other.
>
> Tried to step by step eliminate all the differences and I believe its
> the libodbcinst.so.2 vs libodbcinst.so.1 which cause the difference. It
> works fine with unixodbc 1 but when I use a driver recompiled against
> unixodbc 2 then the error happens.

Functions used in lib(i)odbcinst are SQLGetPrivateProfileString()
and SQLWritePrivateProfileString().
Could you please take the Mylog output of your failure case
by setting Debug=1 in your odbc.ini?

regards,
Hiroshi Inoue

--
I am using the free version of SPAMfighter.
SPAMfighter has removed 5505 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

On 07/03/14 08:09, Inoue, Hiroshi wrote:
> (2014/03/07 2:05), Przemyslaw Rzepecki wrote:
>> Hi,
>>
>> Thanks for the answer! I have compare my program with Yours didn't
>> noticed any logical difference. Then tried rerun the tests on two
>> different systems, any my tests ware passing on one but the errors ware
>> still present on the other.
>>
>> Tried to step by step eliminate all the differences and I believe its
>> the libodbcinst.so.2 vs libodbcinst.so.1 which cause the difference. It
>> works fine with unixodbc 1 but when I use a driver recompiled against
>> unixodbc 2 then the error happens.
>
> Functions used in lib(i)odbcinst are SQLGetPrivateProfileString()
> and SQLWritePrivateProfileString().
> Could you please take the Mylog output of your failure case
> by setting Debug=1 in your odbc.ini?
>
> regards,
> Hiroshi Inoue
>
>
Hi,

I hope you don't mind me posting to your list as a observer, but I
noticed the following in the original code

SQLINTEGER int_array[ARRAY_SIZE];

/* Bind */
rc = SQLBindCol(hstmt, 1, SQL_INTEGER, int_array, sizeof(SQLLEN),
ind_array);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

I would have thought

/* Bind */
rc = SQLBindCol(hstmt, 1, SQL_INTEGER, int_array,
sizeof(SQLINTEGER), ind_array);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);

Was what it should have been. The driver will have written past the end
of int_array, and overwritten the handle (I guess).

--
Nick

On 03/07/2014 11:48 AM, Nick Gorham wrote:
> I hope you don't mind me posting to your list as a observer, but I
> noticed the following in the original code
>
> SQLINTEGER int_array[ARRAY_SIZE];
>
> /* Bind */
> rc = SQLBindCol(hstmt, 1, SQL_INTEGER, int_array, sizeof(SQLLEN),
> ind_array);
> CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
>
> I would have thought
>
> /* Bind */
> rc = SQLBindCol(hstmt, 1, SQL_INTEGER, int_array,
> sizeof(SQLINTEGER), ind_array);
> CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
>
> Was what it should have been. The driver will have written past the end
> of int_array, and overwritten the handle (I guess).

The buffer-length argument is ignored with fixed-length data. Although I
agree on readability grounds that it should be sizeof(SQLINTEGER), to
match SQL_INTEGER, or 0 to make it clear that it's ignored.

- Heikki

Hi,

The my logs attached. The test case pass when using: unixODBC 2.2.12
and fails with unixODBC 2.3.1

Thanks for Your help!
Przemek

On Fri, 2014-03-07 at 17:09 +0900, Inoue, Hiroshi wrote:
> (2014/03/07 2:05), Przemyslaw Rzepecki wrote:
> > Hi,
> >
> > Thanks for the answer! I have compare my program with Yours didn't
> > noticed any logical difference. Then tried rerun the tests on two
> > different systems, any my tests ware passing on one but the errors ware
> > still present on the other.
> >
> > Tried to step by step eliminate all the differences and I believe its
> > the libodbcinst.so.2 vs libodbcinst.so.1 which cause the difference. It
> > works fine with unixodbc 1 but when I use a driver recompiled against
> > unixodbc 2 then the error happens.
>
> Functions used in lib(i)odbcinst are SQLGetPrivateProfileString()
> and SQLWritePrivateProfileString().
> Could you please take the Mylog output of your failure case
> by setting Debug=1 in your odbc.ini?
>
> regards,
> Hiroshi Inoue
>
>
> --
> I am using the free version of SPAMfighter.
> SPAMfighter has removed 5505 of my spam emails to date.
> Get the free SPAMfighter here: http://www.spamfighter.com/len
>
> Do you have a slow PC? Try a Free scan
> http://www.spamfighter.com/SLOW-PCfighter?cid=sigen
>
>
>