Cursors and ExecuteNonQuery

  • Thread starter Thread starter Matt Houseman
  • Start date Start date
M

Matt Houseman

All,

Is it possible for ExecuteNonQuery to open a cursor? The
reason I ask is that I'm debugging a client's C# service
which is leaking cursors and eventually Oracle throws a
MAX CURSOR EXCEEDED exception and the service dies a
horrible death. It appears that the method that is the
main culprit calls several stored procedures which do
something like:

INSERT INTO FOO ( ... ) RETURNING FOO_ID;

The FOO_ID is setup in the C# as an output parameter and
additionally, the client never calls the Dispose method on
the Command object.

Additionally, the Command objects are used like so:
cmd = new OleDbCommand("sp1", cnx);
....
cmd.ExecuteNonQuery();
....
cmd = new OleDbCommand("sp2", cnx);
....
cmd.ExecuteNonQuery();
....

Would this technique cause cursor leaks? Thanks...

Matt
 
I've used ExecuteNonQuery to open and manage SQL Server server-side cursors
using ANSI SQL syntax so I know it can be done, but I doubt if ADO.NET is
creating cursors in your behalf. I'm not familiar enough with Oracle to give
a good suggestion for your problem.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Matt,

The ExecuteNonQuery method might open cursors on the server-side. This
depends on the stored procedure you're excuting.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| Content-Class: urn:content-classes:message
| From: "Matt Houseman" <[email protected]>
| Sender: "Matt Houseman" <[email protected]>
| Subject: Cursors and ExecuteNonQuery
| Date: Sun, 28 Sep 2003 16:46:07 -0700
| Lines: 30
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOGGq/WEPtINAoCQRybEG6gkPRUcg==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62329
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| All,
|
| Is it possible for ExecuteNonQuery to open a cursor? The
| reason I ask is that I'm debugging a client's C# service
| which is leaking cursors and eventually Oracle throws a
| MAX CURSOR EXCEEDED exception and the service dies a
| horrible death. It appears that the method that is the
| main culprit calls several stored procedures which do
| something like:
|
| INSERT INTO FOO ( ... ) RETURNING FOO_ID;
|
| The FOO_ID is setup in the C# as an output parameter and
| additionally, the client never calls the Dispose method on
| the Command object.
|
| Additionally, the Command objects are used like so:
| cmd = new OleDbCommand("sp1", cnx);
| ...
| cmd.ExecuteNonQuery();
| ...
| cmd = new OleDbCommand("sp2", cnx);
| ...
| cmd.ExecuteNonQuery();
| ...
|
| Would this technique cause cursor leaks? Thanks...
|
| Matt
|
|
 
So how do I close this cursor if one does get opened? Is
the Dispose method enough on the OleDbCommand object?
 
Hi Matt,

I'm not quite familiar with Oracle database. In SQL Server stored
procedure, we often use close and deallocate after using a cursor to
release it. Please try to check your stored procedure and refer to Oracle
help.

HTH

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| Content-Class: urn:content-classes:message
| From: "Matt Houseman" <[email protected]>
| Sender: "Matt Houseman" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: Cursors and ExecuteNonQuery
| Date: Sun, 28 Sep 2003 20:57:05 -0700
| Lines: 76
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcOGPb9NTq7dzTX+TiOaStObHMJ7tA==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62339
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| So how do I close this cursor if one does get opened? Is
| the Dispose method enough on the OleDbCommand object?
|
| >-----Original Message-----
| >Hi Matt,
| >
| >The ExecuteNonQuery method might open cursors on the
| server-side. This
| >depends on the stored procedure you're excuting.
| >
| >Kevin Yu
| >=======
| >"This posting is provided "AS IS" with no warranties, and
| confers no
| >rights."
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Matt Houseman" <[email protected]>
| >| Sender: "Matt Houseman" <[email protected]>
| >| Subject: Cursors and ExecuteNonQuery
| >| Date: Sun, 28 Sep 2003 16:46:07 -0700
| >| Lines: 30
| >| Message-ID: <[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| Thread-Index: AcOGGq/WEPtINAoCQRybEG6gkPRUcg==
| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Newsgroups: microsoft.public.dotnet.framework.adonet
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.framework.adonet:62329
| >| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| >| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
| >|
| >| All,
| >|
| >| Is it possible for ExecuteNonQuery to open a cursor?
| The
| >| reason I ask is that I'm debugging a client's C#
| service
| >| which is leaking cursors and eventually Oracle throws a
| >| MAX CURSOR EXCEEDED exception and the service dies a
| >| horrible death. It appears that the method that is the
| >| main culprit calls several stored procedures which do
| >| something like:
| >|
| >| INSERT INTO FOO ( ... ) RETURNING FOO_ID;
| >|
| >| The FOO_ID is setup in the C# as an output parameter
| and
| >| additionally, the client never calls the Dispose method
| on
| >| the Command object.
| >|
| >| Additionally, the Command objects are used like so:
| >| cmd = new OleDbCommand("sp1", cnx);
| >| ...
| >| cmd.ExecuteNonQuery();
| >| ...
| >| cmd = new OleDbCommand("sp2", cnx);
| >| ...
| >| cmd.ExecuteNonQuery();
| >| ...
| >|
| >| Would this technique cause cursor leaks? Thanks...
| >|
| >| Matt
| >|
| >|
| >
| >.
| >
|
 
Back
Top