DB2 and ODBC - Function sequence error

[Szymon Dembek]

Hi

Recently I did some DB2 and ODBC coding in Visual FoxPro. I bumped on a
problem I cannot resolve.

When I issue a delete statement that deletes no rows (no rows qualify
for the WHERE conditions), DB2 raises SQL0100W warning with
SQLSTATE=02000. The same happens when issuing an insert with select
clause (insert into .... select from ...) when no rows are fetched by
the select clause.

It is normal behaviour for DB2, but it causes me problems when
interacting with DB2 through ODBC. When I issue SQLEXEC routine with one
of the previous statements (delete or insert), I get the following error:
"Connectivi ty error: [Microsoft][ODBC Driver Manager] Function sequence
error"

with an SqlState of S1010. I checked this number - it is an internal
error of ODBC service, not a DB2 one. It means that some odbc calls were
made in wrong order, especially asynchronous ones. But I am not using
asynchronous calls.

I tried setting up IgnoreWarnList CLI variable (both in connection
string and in odbc data source control panel) to include 02000 (it
should prevent DB2 to raise warning 02000 to the client) but it did not
help. I also tried IgnoreWarnings= 1 but it did not help either.

Did anybody ever had similar problems and successfully solved them?

Thanks in advance

--
Szymon Dembek

[Knut Stolze]

Szymon Dembek wrote:

Hi

Recently I did some DB2 and ODBC coding in Visual FoxPro. I bumped on a
problem I cannot resolve.

When I issue a delete statement that deletes no rows (no rows qualify
for the WHERE conditions), DB2 raises SQL0100W warning with
SQLSTATE=02000. The same happens when issuing an insert with select
clause (insert into .... select from ...) when no rows are fetched by
the select clause.

It is normal behaviour for DB2, but it causes me problems when
interacting with DB2 through ODBC. When I issue SQLEXEC routine with one
of the previous statements (delete or insert), I get the following error:
"Connectivi ty error: [Microsoft][ODBC Driver Manager] Function sequence
error"

with an SqlState of S1010. I checked this number - it is an internal
error of ODBC service, not a DB2 one. It means that some odbc calls were
made in wrong order, especially asynchronous ones. But I am not using
asynchronous calls.

To me, it looks like a bug in your ODBC driver. Can't you use the CLI
driver that comes with DB2?

I tried setting up IgnoreWarnList CLI variable (both in connection
string and in odbc data source control panel) to include 02000 (it
should prevent DB2 to raise warning 02000 to the client) but it did not
help. I also tried IgnoreWarnings= 1 but it did not help either.

You surely don't want to ignore 02000 because that is the only means to tell
an application that no more rows can be retrieved from a cursor.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Dave Hughes]

Szymon Dembek wrote:

Hi

Recently I did some DB2 and ODBC coding in Visual FoxPro. I bumped on
a problem I cannot resolve.

When I issue a delete statement that deletes no rows (no rows qualify
for the WHERE conditions), DB2 raises SQL0100W warning with
SQLSTATE=02000. The same happens when issuing an insert with select
clause (insert into .... select from ...) when no rows are fetched by
the select clause.

It is normal behaviour for DB2, but it causes me problems when
interacting with DB2 through ODBC. When I issue SQLEXEC routine with
one of the previous statements (delete or insert), I get the
following error:
"Connectivi ty error: [Microsoft][ODBC Driver Manager] Function
sequence error"

with an SqlState of S1010. I checked this number - it is an internal
error of ODBC service, not a DB2 one. It means that some odbc calls
were made in wrong order, especially asynchronous ones. But I am not
using asynchronous calls.

I tried setting up IgnoreWarnList CLI variable (both in connection
string and in odbc data source control panel) to include 02000 (it
should prevent DB2 to raise warning 02000 to the client) but it did
not help. I also tried IgnoreWarnings= 1 but it did not help either.

Did anybody ever had similar problems and successfully solved them?

I've seen this error before, though I suspect under different
circumstances (in my case it was due to a query timeout imposed by the
DB2 governor).

In your case, I believe you need to set the CLI variable PATCH1 to 1024
(PATCH1 is a bit-mask parameter so you'll need to add 1024 to any
existing value). This value represents: "MS Visual Basic fix for empty
searched update/delete". You should find it in the same place as you
found IgnoreWarnList.

Incidentally, there's a ton of other "compatibil ity" patches under the
PATCH1 and PATCH2 settings that are worth exploring.
HTH,

Dave.

--

[Szymon Dembek]

Dave Hughes wrote:

>
In your case, I believe you need to set the CLI variable PATCH1 to 1024
(PATCH1 is a bit-mask parameter so you'll need to add 1024 to any
existing value). This value represents: "MS Visual Basic fix for empty
searched update/delete". You should find it in the same place as you
found IgnoreWarnList.

Incidentally, there's a ton of other "compatibil ity" patches under the
PATCH1 and PATCH2 settings that are worth exploring.
HTH,

Dave.

Thank you very much!!!

I'll try this configuration variable as soon as possible.

Best regards

--
Szymon

[Szymon Dembek]

Knut Stolze wrote:

Szymon Dembek wrote:

>>
It is normal behaviour for DB2, but it causes me problems when
interacting with DB2 through ODBC. When I issue SQLEXEC routine with one
of the previous statements (delete or insert), I get the following error:
"Connectivit y error: [Microsoft][ODBC Driver Manager] Function sequence
error"

with an SqlState of S1010. I checked this number - it is an internal
error of ODBC service, not a DB2 one. It means that some odbc calls were
made in wrong order, especially asynchronous ones. But I am not using
asynchronous calls.

To me, it looks like a bug in your ODBC driver. Can't you use the CLI
driver that comes with DB2?

I'm forced to code in visual fox pro, which means that using odbc is the
easiest way for me.

BTW, I'm using the ODBC driver that came with DB2 client instalation
package. I tried 3 different versions (v8.2, v9)....

>

>I tried setting up IgnoreWarnList CLI variable (both in connection
string and in odbc data source control panel) to include 02000 (it
should prevent DB2 to raise warning 02000 to the client) but it did not
help. I also tried IgnoreWarnings= 1 but it did not help either.

You surely don't want to ignore 02000 because that is the only means to tell
an application that no more rows can be retrieved from a cursor.

You're right, but I tried this only to check if it helps in my case.

Thank you for help.

--
Szymon