Using OdbcDataAdapter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a scenario in my C#.NET application in which I have to open an ODBC connection to a DB2 database residing on AS/400. The connection needs to be kept open 24/7. I have a simple stored procedure on AS/400 which declares a simple cursor for a simple select statement and opens the cursor

The following steps are done in the application
Connection is opened
An OdbcCommand object is created for calling the stored procedure. The open connection is passed
An OdbcDataAdapter object is created. The above created OdbcCommand is set to the SelectCommand property of the OdbcDataAdapter
A DataSet object is created to store the resulting data
The fill(datasetname) command of the OdbcDataAdapter is used to get the results from the stored procedure
Click to expand...

The above steps work fine. But since these steps are repeated (except the connection opening) every half second in my appliction, after about ~40,000 iterations. The application starts receiving SQL0502 errors (cursor already open) because the cursors opened implicity by the fill method of OdbcDataAdapter were never closed. Is there way for me to explicitly close the cursor which was implicitly opened by the fill method of OdbcDataAdapter without closing the ODBC connection

Your help is appreciated.
 
Hi Sam,

The cursor is closed (well, it should be) within Fill method.
I guess there is something wrong with Odbc provider which doesn't behave as
expected.
For testing purposes:
Execute in a loop:
OdbcDataReader reader = OdbcCommand.ExecuteReader();
reader.Read();
reader.Close();
To see if the problem occurs.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Sam said:
Hi,

I have a scenario in my C#.NET application in which I have to open an ODBC
Click to expand...
connection to a DB2 database residing on AS/400. The connection needs to be
kept open 24/7. I have a simple stored procedure on AS/400 which declares a
simple cursor for a simple select statement and opens the cursor.
The following steps are done in the application.
Click to expand...
results from the stored procedure.
The above steps work fine. But since these steps are repeated (except the
Click to expand...
connection opening) every half second in my appliction, after about ~40,000
iterations. The application starts receiving SQL0502 errors (cursor already
open) because the cursors opened implicity by the fill method of
OdbcDataAdapter were never closed. Is there way for me to explicitly close
the cursor which was implicitly opened by the fill method of OdbcDataAdapter
without closing the ODBC connection.
 
Hi Miha,

Thanks for your reply. I have tried the steps you had suggested, but the results are the same. What baffles me is that the sequence of steps I do are pretty ordinary and common and it is hard to believe that there is major error in the ODBC driver for such a common usage.

Your reply is appreciated all the same.

-Sam.
 
Hi Sam,

I am not saying that odbc driver is buggy.
It can be that .net issues a command that odbc doesn't interpret very well
or doesn't implement at all.
http://support.microsoft.com/default.aspx?scid=kb;en-us;330126&Product=NETFrame

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Sam said:
Hi Miha,

Thanks for your reply. I have tried the steps you had suggested, but the
Click to expand...
results are the same. What baffles me is that the sequence of steps I do are
pretty ordinary and common and it is hard to believe that there is major
error in the ODBC driver for such a common usage.
 
Hi Miha

I agree with you. I am not implying that you said the odbc driver was buggy
I do have some additional information from an ODBC TRACE. A portion of that is included below

****************** ODBC TRACE **********************
TestOdbc c0c-f00 ENTER SQLGetData
HSTMT 070E195
UWORD 9
SWORD -8 <SQL_C_WCHAR
PTR 0x001A97A0
SQLLEN 409
SQLLEN * 0x0012F48

TestOdbc c0c-f00 EXIT SQLGetData with return code 0 (SQL_SUCCESS
HSTMT 070E195
UWORD 9
SWORD -8 <SQL_C_WCHAR
PTR 0x001A97A0 [ 8] "0465
SQLLEN 409
SQLLEN * 0x0012F484 (8

TestOdbc c0c-f00 ENTER SQLFetch
HSTMT 070E195

TestOdbc c0c-f00 EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND
HSTMT 070E195

TestOdbc c0c-f00 ENTER SQLMoreResults
HSTMT 070E195

TestOdbc c0c-f00 EXIT SQLMoreResults with return code 100 (SQL_NO_DATA_FOUND
HSTMT 070E195

TestOdbc c0c-f00 ENTER SQLFreeStmt
HSTMT 070E195
UWORD 0 <SQL_CLOSE

TestOdbc c0c-f00 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS
HSTMT 070E195
UWORD 0 <SQL_CLOSE

******************* ODBC TRACE ***************************************

Of particular note is the call to SQLFreeStmt with <SQL_CLOSE> parameter. According to ODBC API reference this call should close the cursor on the database server (in this case AS/400 DB2.) But apparently the cursor and its related resources are not being freed and after it reaches a limit of 16MB it starts throwing errors.
 
Hi Sam,

Sam said:
Hi Miha,

I agree with you. I am not implying that you said the odbc driver was buggy.

:)

I do have some additional information from an ODBC TRACE. A portion of that is included below:
Click to expand...
Of particular note is the call to SQLFreeStmt with <SQL_CLOSE> parameter.
Click to expand...
According to ODBC API reference this call should close the cursor on the
database server (in this case AS/400 DB2.) But apparently the cursor and its
related resources are not being freed and after it reaches a limit of 16MB
it starts throwing errors.

You might check with IBM for patches/updates or workarounds.
 
Hi Miha

I am in the process of doing just that. Will post information if I come accross a resolution this problem

Thanks again.
 
Miha
Thanks for all of your help. I have a resolution to this problem

It turns out that it is a bug on AS/400 for V5R1. IBM support has verified this and is in the process of creating a patch (PTF in AS/400 terminology) for resolving the problem

-SAM
 
Back
Top