Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cursor not open error on executeQuery

19 posts in General Discussion Last posting was on 2009-04-10 14:53:32.0Z
Bill Williams Posted on 2009-04-01 14:14:55.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
Subject: Cursor not open error on executeQuery
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d376df$1@forums-1-dub>
Date: 1 Apr 2009 06:14:55 -0800
X-Trace: forums-1-dub 1238595295 10.22.241.152 (1 Apr 2009 06:14:55 -0800)
X-Original-Trace: 1 Apr 2009 06:14:55 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7457
Article PK: 5870

I am running ASA 10.0.1 build 3835.

I am trying to use the iAnywhere JDBC driver to call a stored procedure that
has a loop within it that has a commit that is fired inside the loop.

When I call the executeQuery method for the CallableStatement, I get a
"cursor not open" error; however, the stored procedure did run because my
database changes are performed.

If I remove the commit, I don't get the error but I really need the commit
inside the loop.

If I use the jConnect, I do not get the error with the exact same procedure
and java code.

Any ideas?

Bill


Jeff Albion [Sybase iAnywhere] Posted on 2009-04-01 14:41:25.0Z
From: "Jeff Albion [Sybase iAnywhere]" <firstname.lastname@ianywhere.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub>
In-Reply-To: <49d376df$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d37d15$1@forums-1-dub>
Date: 1 Apr 2009 06:41:25 -0800
X-Trace: forums-1-dub 1238596885 10.22.241.152 (1 Apr 2009 06:41:25 -0800)
X-Original-Trace: 1 Apr 2009 06:41:25 -0800, vip152.sybase.com
Lines: 47
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7458
Article PK: 5872

Bill,

Bill Williams wrote:
> I am running ASA 10.0.1 build 3835.
>
> I am trying to use the iAnywhere JDBC driver to call a stored procedure that
> has a loop within it that has a commit that is fired inside the loop.

Just to make sure it's crystal clear - the COMMIT is inside the SP? Can
we see the code for the SP / Java call?

> When I call the executeQuery method for the CallableStatement, I get a
> "cursor not open" error; however, the stored procedure did run because my
> database changes are performed.

"Cursor not open" usually means you either:

1) Tried to open a cursor on a result set that doesn't exist (i.e. the
SP doesn't return any results, but you tried to open a cursor on this
anyway).

2) Opened a cursor without the "WITH HOLD" clause and are issuing an
implicit (via DDL or otherwise) or explicit COMMIT.

> If I remove the commit, I don't get the error but I really need the commit
> inside the loop.
>
> If I use the jConnect, I do not get the error with the exact same procedure
> and java code.

My guess is on the way you tried to open the cursor. See the "OPEN"
statement and note the "WITH HOLD" clause:
http://dcx.sybase.com/html/dbrfen10/rf-open-statement.html

*Remember to close your cursor when you're done with it!

Regards,

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/


Bill Williams Posted on 2009-04-01 15:04:19.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d38273$1@forums-1-dub>
Date: 1 Apr 2009 07:04:19 -0800
X-Trace: forums-1-dub 1238598259 10.22.241.152 (1 Apr 2009 07:04:19 -0800)
X-Original-Trace: 1 Apr 2009 07:04:19 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7459
Article PK: 5871

I am using a JDBC CallableStatement. The name of the procedure is
InductTote. The statement is formed with the following string: {call
InductTote('12345')}. The InductTote stored procedure does return a result
set. Like I said, it does have an embedded commit within the procedure, so
like you said, it sounds as if I need a WITH HOLD statement but I don't know
how to do that with a CallableStatement object. Within the stored procedure
I have cursors that are being opened and I am using the WITH HOLD statement
because the commit is within a loop the cursor is running.

"Jeff Albion [Sybase iAnywhere]" <firstname.lastname@ianywhere.com> wrote in
message news:49d37d15$1@forums-1-dub...
> Bill,
>
> Bill Williams wrote:
>> I am running ASA 10.0.1 build 3835.
>>
>> I am trying to use the iAnywhere JDBC driver to call a stored procedure
>> that has a loop within it that has a commit that is fired inside the
>> loop.
>
> Just to make sure it's crystal clear - the COMMIT is inside the SP? Can we
> see the code for the SP / Java call?
>
>> When I call the executeQuery method for the CallableStatement, I get a
>> "cursor not open" error; however, the stored procedure did run because my
>> database changes are performed.
>
> "Cursor not open" usually means you either:
>
> 1) Tried to open a cursor on a result set that doesn't exist (i.e. the SP
> doesn't return any results, but you tried to open a cursor on this
> anyway).
>
> 2) Opened a cursor without the "WITH HOLD" clause and are issuing an
> implicit (via DDL or otherwise) or explicit COMMIT.
>
>> If I remove the commit, I don't get the error but I really need the
>> commit inside the loop.
>>
>> If I use the jConnect, I do not get the error with the exact same
>> procedure and java code.
>
> My guess is on the way you tried to open the cursor. See the "OPEN"
> statement and note the "WITH HOLD" clause:
> http://dcx.sybase.com/html/dbrfen10/rf-open-statement.html
>
> *Remember to close your cursor when you're done with it!
>
> Regards,
>
> --
> Jeff Albion, Sybase iAnywhere
>
> iAnywhere Developer Community :
> http://www.sybase.com/developer/library/sql-anywhere-techcorner
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> SQL Anywhere Patches and EBFs :
> http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
> Report a Bug/Open a Case : http://case-express.sybase.com/cx/


Jeff Albion [Sybase iAnywhere] Posted on 2009-04-01 15:36:16.0Z
From: "Jeff Albion [Sybase iAnywhere]" <firstname.lastname@ianywhere.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub>
In-Reply-To: <49d38273$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d389f0$1@forums-1-dub>
Date: 1 Apr 2009 07:36:16 -0800
X-Trace: forums-1-dub 1238600176 10.22.241.152 (1 Apr 2009 07:36:16 -0800)
X-Original-Trace: 1 Apr 2009 07:36:16 -0800, vip152.sybase.com
Lines: 27
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7461
Article PK: 5876

Bill,

Bill Williams wrote:
> I am using a JDBC CallableStatement. The name of the procedure is
> InductTote. The statement is formed with the following string: {call
> InductTote('12345')}. The InductTote stored procedure does return a result
> set. Like I said, it does have an embedded commit within the procedure, so
> like you said, it sounds as if I need a WITH HOLD statement but I don't know
> how to do that with a CallableStatement object. Within the stored procedure
> I have cursors that are being opened and I am using the WITH HOLD statement
> because the commit is within a loop the cursor is running.

Without more information, my only request is:

Can we see the code for the SP / Java call?

Regards,

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-02 14:29:31.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub>
In-Reply-To: <49d389f0$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d4cbcb$1@forums-1-dub>
Date: 2 Apr 2009 06:29:31 -0800
X-Trace: forums-1-dub 1238682571 10.22.241.152 (2 Apr 2009 06:29:31 -0800)
X-Original-Trace: 2 Apr 2009 06:29:31 -0800, vip152.sybase.com
Lines: 76
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7468
Article PK: 5884

Bill,

Jeff is correct, we need more information to diagnose the problem. Here
is what I tried...

1) I created the procedure foo as follows:

create procedure foo(p char(128))
begin
select * from systable;
commit;
select * from syscolumn;
end

2) I then built a JDBC app using the iAnywhere JDBC driver with the
following code snippet:

try {
CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
boolean has_result = stmt.execute();
int rows_affected = 0;
do {
if( has_result ) {
ResultSet rs = stmt.getResultSet();
int count = 0;
while( rs.next() ) {
++count;
}
System.out.println( "Num rows in result set is " + count );
} else {
rows_affected = stmt.getUpdateCount();
if( rows_affected >= 0 ) {
System.out.println( "Rows affected is " + rows_affected );
}
}

has_result = stmt.getMoreResults();
} while( has_result || rows_affected != -1 );
stmt.close();
con.close();
System.out.println( "Disconnected" );
} catch (SQLException sqe) {
...
}

3) When I run my JDBC app I get the output:

Num rows in result set is 312
Num rows in result set is 2109
Disconnected

So, try and see if your JDBC code is missing something based on the
above snippet and then post both the source for your stored procedure
and your JDBC app.

Karim

Jeff Albion [Sybase iAnywhere] wrote:
> Bill,
>
> Bill Williams wrote:
>> I am using a JDBC CallableStatement. The name of the procedure is
>> InductTote. The statement is formed with the following string: {call
>> InductTote('12345')}. The InductTote stored procedure does return a
>> result set. Like I said, it does have an embedded commit within the
>> procedure, so like you said, it sounds as if I need a WITH HOLD
>> statement but I don't know how to do that with a CallableStatement
>> object. Within the stored procedure I have cursors that are being
>> opened and I am using the WITH HOLD statement because the commit is
>> within a loop the cursor is running.
>
> Without more information, my only request is:
>
> Can we see the code for the SP / Java call?
>
> Regards,
>


Bill Williams Posted on 2009-04-03 12:52:15.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 669
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d6067f@forums-1-dub>
Date: 3 Apr 2009 04:52:15 -0800
X-Trace: forums-1-dub 1238763135 10.22.241.152 (3 Apr 2009 04:52:15 -0800)
X-Original-Trace: 3 Apr 2009 04:52:15 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7472
Article PK: 5888

Attached are the two main stored procedures and the snipet of code where the
problem occurs. The commit is within the balance.stp file. If I comment
out that commit, the problem goes away.

I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.

The exception occurs on the line "rs = call.executeQuery()".

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:49d4cbcb$1@forums-1-dub...
> Bill,
>
> Jeff is correct, we need more information to diagnose the problem. Here
> is what I tried...
>
> 1) I created the procedure foo as follows:
>
> create procedure foo(p char(128))
> begin
> select * from systable;
> commit;
> select * from syscolumn;
> end
>
> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
> following code snippet:
>
> try {
> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
> boolean has_result = stmt.execute();
> int rows_affected = 0;
> do {
> if( has_result ) {
> ResultSet rs = stmt.getResultSet();
> int count = 0;
> while( rs.next() ) {
> ++count;
> }
> System.out.println( "Num rows in result set is " + count );
> } else {
> rows_affected = stmt.getUpdateCount();
> if( rows_affected >= 0 ) {
> System.out.println( "Rows affected is " + rows_affected );
> }
> }
>
> has_result = stmt.getMoreResults();
> } while( has_result || rows_affected != -1 );
> stmt.close();
> con.close();
> System.out.println( "Disconnected" );
> } catch (SQLException sqe) {
> ...
> }
>
> 3) When I run my JDBC app I get the output:
>
> Num rows in result set is 312
> Num rows in result set is 2109
> Disconnected
>
> So, try and see if your JDBC code is missing something based on the
> above snippet and then post both the source for your stored procedure
> and your JDBC app.
>
> Karim
> Jeff Albion [Sybase iAnywhere] wrote:
>> Bill,
>>
>> Bill Williams wrote:
>>> I am using a JDBC CallableStatement. The name of the procedure is
>>> InductTote. The statement is formed with the following string: {call
>>> InductTote('12345')}. The InductTote stored procedure does return a
>>> result set. Like I said, it does have an embedded commit within the
>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>> statement but I don't know how to do that with a CallableStatement
>>> object. Within the stored procedure I have cursors that are being
>>> opened and I am using the WITH HOLD statement because the commit is
>>> within a loop the cursor is running.
>>
>> Without more information, my only request is:
>>
>> Can we see the code for the SP / Java call?
>>
>> Regards,
>>


Bill Williams Posted on 2009-04-06 17:50:20.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 97
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da40dc$1@forums-1-dub>
Date: 6 Apr 2009 10:50:20 -0700
X-Trace: forums-1-dub 1239040220 10.22.241.152 (6 Apr 2009 10:50:20 -0700)
X-Original-Trace: 6 Apr 2009 10:50:20 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7473
Article PK: 5887

Did any of this help or is more info needed?

Thanks

"Bill Williams" <billwilliams@qcsoftware.com> wrote in message
news:49d6067f@forums-1-dub...
> Attached are the two main stored procedures and the snipet of code where
> the problem occurs. The commit is within the balance.stp file. If I
> comment out that commit, the problem goes away.
>
> I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.
>
> The exception occurs on the line "rs = call.executeQuery()".
>
> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
> news:49d4cbcb$1@forums-1-dub...
>> Bill,
>>
>> Jeff is correct, we need more information to diagnose the problem. Here
>> is what I tried...
>>
>> 1) I created the procedure foo as follows:
>>
>> create procedure foo(p char(128))
>> begin
>> select * from systable;
>> commit;
>> select * from syscolumn;
>> end
>>
>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>> following code snippet:
>>
>> try {
>> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
>> boolean has_result = stmt.execute();
>> int rows_affected = 0;
>> do {
>> if( has_result ) {
>> ResultSet rs = stmt.getResultSet();
>> int count = 0;
>> while( rs.next() ) {
>> ++count;
>> }
>> System.out.println( "Num rows in result set is " + count );
>> } else {
>> rows_affected = stmt.getUpdateCount();
>> if( rows_affected >= 0 ) {
>> System.out.println( "Rows affected is " + rows_affected );
>> }
>> }
>>
>> has_result = stmt.getMoreResults();
>> } while( has_result || rows_affected != -1 );
>> stmt.close();
>> con.close();
>> System.out.println( "Disconnected" );
>> } catch (SQLException sqe) {
>> ...
>> }
>>
>> 3) When I run my JDBC app I get the output:
>>
>> Num rows in result set is 312
>> Num rows in result set is 2109
>> Disconnected
>>
>> So, try and see if your JDBC code is missing something based on the
>> above snippet and then post both the source for your stored procedure
>> and your JDBC app.
>>
>> Karim
>> Jeff Albion [Sybase iAnywhere] wrote:
>>> Bill,
>>>
>>> Bill Williams wrote:
>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>> InductTote. The statement is formed with the following string: {call
>>>> InductTote('12345')}. The InductTote stored procedure does return a
>>>> result set. Like I said, it does have an embedded commit within the
>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>> statement but I don't know how to do that with a CallableStatement
>>>> object. Within the stored procedure I have cursors that are being
>>>> opened and I am using the WITH HOLD statement because the commit is
>>>> within a loop the cursor is running.
>>>
>>> Without more information, my only request is:
>>>
>>> Can we see the code for the SP / Java call?
>>>
>>> Regards,
>>>
>
>
>


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-06 18:17:47.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub>
In-Reply-To: <49da40dc$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da474b$1@forums-1-dub>
Date: 6 Apr 2009 11:17:47 -0700
X-Trace: forums-1-dub 1239041867 10.22.241.152 (6 Apr 2009 11:17:47 -0700)
X-Original-Trace: 6 Apr 2009 11:17:47 -0700, vip152.sybase.com
Lines: 103
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7474
Article PK: 5886

Sorry Bill,

I have not had a chance to look at the files you posted. I will try and
see if I can glean anything useful and get back to you over the next day
or so.

Karim

Bill Williams wrote:
> Did any of this help or is more info needed?
>
> Thanks
>
> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
> news:49d6067f@forums-1-dub...
>> Attached are the two main stored procedures and the snipet of code where
>> the problem occurs. The commit is within the balance.stp file. If I
>> comment out that commit, the problem goes away.
>>
>> I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.
>>
>> The exception occurs on the line "rs = call.executeQuery()".
>>
>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>> news:49d4cbcb$1@forums-1-dub...
>>> Bill,
>>>
>>> Jeff is correct, we need more information to diagnose the problem. Here
>>> is what I tried...
>>>
>>> 1) I created the procedure foo as follows:
>>>
>>> create procedure foo(p char(128))
>>> begin
>>> select * from systable;
>>> commit;
>>> select * from syscolumn;
>>> end
>>>
>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>> following code snippet:
>>>
>>> try {
>>> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
>>> boolean has_result = stmt.execute();
>>> int rows_affected = 0;
>>> do {
>>> if( has_result ) {
>>> ResultSet rs = stmt.getResultSet();
>>> int count = 0;
>>> while( rs.next() ) {
>>> ++count;
>>> }
>>> System.out.println( "Num rows in result set is " + count );
>>> } else {
>>> rows_affected = stmt.getUpdateCount();
>>> if( rows_affected >= 0 ) {
>>> System.out.println( "Rows affected is " + rows_affected );
>>> }
>>> }
>>>
>>> has_result = stmt.getMoreResults();
>>> } while( has_result || rows_affected != -1 );
>>> stmt.close();
>>> con.close();
>>> System.out.println( "Disconnected" );
>>> } catch (SQLException sqe) {
>>> ...
>>> }
>>>
>>> 3) When I run my JDBC app I get the output:
>>>
>>> Num rows in result set is 312
>>> Num rows in result set is 2109
>>> Disconnected
>>>
>>> So, try and see if your JDBC code is missing something based on the
>>> above snippet and then post both the source for your stored procedure
>>> and your JDBC app.
>>>
>>> Karim
>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>> Bill,
>>>>
>>>> Bill Williams wrote:
>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>> InductTote. The statement is formed with the following string: {call
>>>>> InductTote('12345')}. The InductTote stored procedure does return a
>>>>> result set. Like I said, it does have an embedded commit within the
>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>> statement but I don't know how to do that with a CallableStatement
>>>>> object. Within the stored procedure I have cursors that are being
>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>> within a loop the cursor is running.
>>>> Without more information, my only request is:
>>>>
>>>> Can we see the code for the SP / Java call?
>>>>
>>>> Regards,
>>>>
>>
>>
>
>


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-06 18:35:29.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub>
In-Reply-To: <49da40dc$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da4b71$1@forums-1-dub>
Date: 6 Apr 2009 11:35:29 -0700
X-Trace: forums-1-dub 1239042929 10.22.241.152 (6 Apr 2009 11:35:29 -0700)
X-Original-Trace: 6 Apr 2009 11:35:29 -0700, vip152.sybase.com
Lines: 109
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7475
Article PK: 5889

Bill,

I am not seeing what might be going wrong. Can someone else have a look
at the procedure definitions in the file that Bill posted and see if
there is something obvious that would be causing the cursor not open error?

In the meantime, Bill, what happens if you try executing the
InductCartonEx procedure in dbisqlc? Note, I want you to specifically
use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
JDBC driver. Also, is it possible that the cBalancePicks cursor needs to
be opened using with hold?

Karim

Bill Williams wrote:
> Did any of this help or is more info needed?
>
> Thanks
>
> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
> news:49d6067f@forums-1-dub...
>> Attached are the two main stored procedures and the snipet of code where
>> the problem occurs. The commit is within the balance.stp file. If I
>> comment out that commit, the problem goes away.
>>
>> I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.
>>
>> The exception occurs on the line "rs = call.executeQuery()".
>>
>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>> news:49d4cbcb$1@forums-1-dub...
>>> Bill,
>>>
>>> Jeff is correct, we need more information to diagnose the problem. Here
>>> is what I tried...
>>>
>>> 1) I created the procedure foo as follows:
>>>
>>> create procedure foo(p char(128))
>>> begin
>>> select * from systable;
>>> commit;
>>> select * from syscolumn;
>>> end
>>>
>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>> following code snippet:
>>>
>>> try {
>>> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
>>> boolean has_result = stmt.execute();
>>> int rows_affected = 0;
>>> do {
>>> if( has_result ) {
>>> ResultSet rs = stmt.getResultSet();
>>> int count = 0;
>>> while( rs.next() ) {
>>> ++count;
>>> }
>>> System.out.println( "Num rows in result set is " + count );
>>> } else {
>>> rows_affected = stmt.getUpdateCount();
>>> if( rows_affected >= 0 ) {
>>> System.out.println( "Rows affected is " + rows_affected );
>>> }
>>> }
>>>
>>> has_result = stmt.getMoreResults();
>>> } while( has_result || rows_affected != -1 );
>>> stmt.close();
>>> con.close();
>>> System.out.println( "Disconnected" );
>>> } catch (SQLException sqe) {
>>> ...
>>> }
>>>
>>> 3) When I run my JDBC app I get the output:
>>>
>>> Num rows in result set is 312
>>> Num rows in result set is 2109
>>> Disconnected
>>>
>>> So, try and see if your JDBC code is missing something based on the
>>> above snippet and then post both the source for your stored procedure
>>> and your JDBC app.
>>>
>>> Karim
>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>> Bill,
>>>>
>>>> Bill Williams wrote:
>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>> InductTote. The statement is formed with the following string: {call
>>>>> InductTote('12345')}. The InductTote stored procedure does return a
>>>>> result set. Like I said, it does have an embedded commit within the
>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>> statement but I don't know how to do that with a CallableStatement
>>>>> object. Within the stored procedure I have cursors that are being
>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>> within a loop the cursor is running.
>>>> Without more information, my only request is:
>>>>
>>>> Can we see the code for the SP / Java call?
>>>>
>>>> Regards,
>>>>
>>
>>
>
>


Bill Williams Posted on 2009-04-06 18:53:58.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 118
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da4fc6$1@forums-1-dub>
Date: 6 Apr 2009 11:53:58 -0700
X-Trace: forums-1-dub 1239044038 10.22.241.152 (6 Apr 2009 11:53:58 -0700)
X-Original-Trace: 6 Apr 2009 11:53:58 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7476
Article PK: 5892

I just tried dbisqlc and got the "cursor not open" error when I run the
InductCartonEx.

I tried the with hold statement on the cBalancePicks cursor (which I thought
I had tried before) and still get the error in dbisqlc.

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:49da4b71$1@forums-1-dub...
> Bill,
>
> I am not seeing what might be going wrong. Can someone else have a look at
> the procedure definitions in the file that Bill posted and see if there is
> something obvious that would be causing the cursor not open error?
>
> In the meantime, Bill, what happens if you try executing the
> InductCartonEx procedure in dbisqlc? Note, I want you to specifically use
> dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere JDBC
> driver. Also, is it possible that the cBalancePicks cursor needs to be
> opened using with hold?
>
> Karim
> Bill Williams wrote:
>> Did any of this help or is more info needed?
>>
>> Thanks
>>
>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>> news:49d6067f@forums-1-dub...
>>> Attached are the two main stored procedures and the snipet of code where
>>> the problem occurs. The commit is within the balance.stp file. If I
>>> comment out that commit, the problem goes away.
>>>
>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.
>>>
>>> The exception occurs on the line "rs = call.executeQuery()".
>>>
>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>> news:49d4cbcb$1@forums-1-dub...
>>>> Bill,
>>>>
>>>> Jeff is correct, we need more information to diagnose the problem. Here
>>>> is what I tried...
>>>>
>>>> 1) I created the procedure foo as follows:
>>>>
>>>> create procedure foo(p char(128))
>>>> begin
>>>> select * from systable;
>>>> commit;
>>>> select * from syscolumn;
>>>> end
>>>>
>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>> following code snippet:
>>>>
>>>> try {
>>>> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
>>>> boolean has_result = stmt.execute();
>>>> int rows_affected = 0;
>>>> do {
>>>> if( has_result ) {
>>>> ResultSet rs = stmt.getResultSet();
>>>> int count = 0;
>>>> while( rs.next() ) {
>>>> ++count;
>>>> }
>>>> System.out.println( "Num rows in result set is " + count );
>>>> } else {
>>>> rows_affected = stmt.getUpdateCount();
>>>> if( rows_affected >= 0 ) {
>>>> System.out.println( "Rows affected is " + rows_affected );
>>>> }
>>>> }
>>>>
>>>> has_result = stmt.getMoreResults();
>>>> } while( has_result || rows_affected != -1 );
>>>> stmt.close();
>>>> con.close();
>>>> System.out.println( "Disconnected" );
>>>> } catch (SQLException sqe) {
>>>> ...
>>>> }
>>>>
>>>> 3) When I run my JDBC app I get the output:
>>>>
>>>> Num rows in result set is 312
>>>> Num rows in result set is 2109
>>>> Disconnected
>>>>
>>>> So, try and see if your JDBC code is missing something based on the
>>>> above snippet and then post both the source for your stored procedure
>>>> and your JDBC app.
>>>>
>>>> Karim
>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>> Bill,
>>>>>
>>>>> Bill Williams wrote:
>>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>>> InductTote. The statement is formed with the following string:
>>>>>> {call
>>>>>> InductTote('12345')}. The InductTote stored procedure does return a
>>>>>> result set. Like I said, it does have an embedded commit within the
>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>> statement but I don't know how to do that with a CallableStatement
>>>>>> object. Within the stored procedure I have cursors that are being
>>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>>> within a loop the cursor is running.
>>>>> Without more information, my only request is:
>>>>>
>>>>> Can we see the code for the SP / Java call?
>>>>>
>>>>> Regards,
>>>>>
>>>
>>>
>>


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-06 19:32:00.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub>
In-Reply-To: <49da4fc6$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da58b0$1@forums-1-dub>
Date: 6 Apr 2009 12:32:00 -0700
X-Trace: forums-1-dub 1239046320 10.22.241.152 (6 Apr 2009 12:32:00 -0700)
X-Original-Trace: 6 Apr 2009 12:32:00 -0700, vip152.sybase.com
Lines: 128
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7477
Article PK: 5890

Okay, that's good news (sort of). It means we can rule JDBC out. So now,
we need to figure out what is going wrong with the stored procs. Given
that you can reproduce the problem with dbisqlc, I assume it would be
best if you ran the test using the stored procedure debugger and see
exactly where the cursor not open error is being thrown. In the
meantime, I hope somebody else with a bit more knowledge regarding
stored procs and sql can have a look at your stored proc definitions and
see if they can spot where or why the error gets thrown with the commit
but not without the commit.

Karim

Bill Williams wrote:
> I just tried dbisqlc and got the "cursor not open" error when I run the
> InductCartonEx.
>
> I tried the with hold statement on the cBalancePicks cursor (which I thought
> I had tried before) and still get the error in dbisqlc.
>
> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
> news:49da4b71$1@forums-1-dub...
>> Bill,
>>
>> I am not seeing what might be going wrong. Can someone else have a look at
>> the procedure definitions in the file that Bill posted and see if there is
>> something obvious that would be causing the cursor not open error?
>>
>> In the meantime, Bill, what happens if you try executing the
>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically use
>> dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere JDBC
>> driver. Also, is it possible that the cBalancePicks cursor needs to be
>> opened using with hold?
>>
>> Karim
>> Bill Williams wrote:
>>> Did any of this help or is more info needed?
>>>
>>> Thanks
>>>
>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>> news:49d6067f@forums-1-dub...
>>>> Attached are the two main stored procedures and the snipet of code where
>>>> the problem occurs. The commit is within the balance.stp file. If I
>>>> comment out that commit, the problem goes away.
>>>>
>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3 modes.
>>>>
>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>
>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>> news:49d4cbcb$1@forums-1-dub...
>>>>> Bill,
>>>>>
>>>>> Jeff is correct, we need more information to diagnose the problem. Here
>>>>> is what I tried...
>>>>>
>>>>> 1) I created the procedure foo as follows:
>>>>>
>>>>> create procedure foo(p char(128))
>>>>> begin
>>>>> select * from systable;
>>>>> commit;
>>>>> select * from syscolumn;
>>>>> end
>>>>>
>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>> following code snippet:
>>>>>
>>>>> try {
>>>>> CallableStatement stmt = con.prepareCall( "{call foo('12345')}" );
>>>>> boolean has_result = stmt.execute();
>>>>> int rows_affected = 0;
>>>>> do {
>>>>> if( has_result ) {
>>>>> ResultSet rs = stmt.getResultSet();
>>>>> int count = 0;
>>>>> while( rs.next() ) {
>>>>> ++count;
>>>>> }
>>>>> System.out.println( "Num rows in result set is " + count );
>>>>> } else {
>>>>> rows_affected = stmt.getUpdateCount();
>>>>> if( rows_affected >= 0 ) {
>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>> }
>>>>> }
>>>>>
>>>>> has_result = stmt.getMoreResults();
>>>>> } while( has_result || rows_affected != -1 );
>>>>> stmt.close();
>>>>> con.close();
>>>>> System.out.println( "Disconnected" );
>>>>> } catch (SQLException sqe) {
>>>>> ...
>>>>> }
>>>>>
>>>>> 3) When I run my JDBC app I get the output:
>>>>>
>>>>> Num rows in result set is 312
>>>>> Num rows in result set is 2109
>>>>> Disconnected
>>>>>
>>>>> So, try and see if your JDBC code is missing something based on the
>>>>> above snippet and then post both the source for your stored procedure
>>>>> and your JDBC app.
>>>>>
>>>>> Karim
>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>> Bill,
>>>>>>
>>>>>> Bill Williams wrote:
>>>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>> {call
>>>>>>> InductTote('12345')}. The InductTote stored procedure does return a
>>>>>>> result set. Like I said, it does have an embedded commit within the
>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>> statement but I don't know how to do that with a CallableStatement
>>>>>>> object. Within the stored procedure I have cursors that are being
>>>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>>>> within a loop the cursor is running.
>>>>>> Without more information, my only request is:
>>>>>>
>>>>>> Can we see the code for the SP / Java call?
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>
>


Bill Williams Posted on 2009-04-06 19:41:20.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 140
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da5ae0$1@forums-1-dub>
Date: 6 Apr 2009 12:41:20 -0700
X-Trace: forums-1-dub 1239046880 10.22.241.152 (6 Apr 2009 12:41:20 -0700)
X-Original-Trace: 6 Apr 2009 12:41:20 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7478
Article PK: 5891

I will take a look at try to cause it to happen with the debugger; however,
why doesn't this problem occur with dbisql or jConnect JDBC?

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:49da58b0$1@forums-1-dub...
> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
> we need to figure out what is going wrong with the stored procs. Given
> that you can reproduce the problem with dbisqlc, I assume it would be best
> if you ran the test using the stored procedure debugger and see exactly
> where the cursor not open error is being thrown. In the meantime, I hope
> somebody else with a bit more knowledge regarding stored procs and sql can
> have a look at your stored proc definitions and see if they can spot where
> or why the error gets thrown with the commit but not without the commit.
>
> Karim
> Bill Williams wrote:
>> I just tried dbisqlc and got the "cursor not open" error when I run the
>> InductCartonEx.
>>
>> I tried the with hold statement on the cBalancePicks cursor (which I
>> thought I had tried before) and still get the error in dbisqlc.
>>
>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>> news:49da4b71$1@forums-1-dub...
>>> Bill,
>>>
>>> I am not seeing what might be going wrong. Can someone else have a look
>>> at the procedure definitions in the file that Bill posted and see if
>>> there is something obvious that would be causing the cursor not open
>>> error?
>>>
>>> In the meantime, Bill, what happens if you try executing the
>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs to
>>> be opened using with hold?
>>>
>>> Karim
>>> Bill Williams wrote:
>>>> Did any of this help or is more info needed?
>>>>
>>>> Thanks
>>>>
>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>> news:49d6067f@forums-1-dub...
>>>>> Attached are the two main stored procedures and the snipet of code
>>>>> where the problem occurs. The commit is within the balance.stp file.
>>>>> If I comment out that commit, the problem goes away.
>>>>>
>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>> modes.
>>>>>
>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>
>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>> Bill,
>>>>>>
>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>> Here
>>>>>> is what I tried...
>>>>>>
>>>>>> 1) I created the procedure foo as follows:
>>>>>>
>>>>>> create procedure foo(p char(128))
>>>>>> begin
>>>>>> select * from systable;
>>>>>> commit;
>>>>>> select * from syscolumn;
>>>>>> end
>>>>>>
>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>> following code snippet:
>>>>>>
>>>>>> try {
>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>> foo('12345')}" );
>>>>>> boolean has_result = stmt.execute();
>>>>>> int rows_affected = 0;
>>>>>> do {
>>>>>> if( has_result ) {
>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>> int count = 0;
>>>>>> while( rs.next() ) {
>>>>>> ++count;
>>>>>> }
>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>> } else {
>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>> if( rows_affected >= 0 ) {
>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> has_result = stmt.getMoreResults();
>>>>>> } while( has_result || rows_affected != -1 );
>>>>>> stmt.close();
>>>>>> con.close();
>>>>>> System.out.println( "Disconnected" );
>>>>>> } catch (SQLException sqe) {
>>>>>> ...
>>>>>> }
>>>>>>
>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>
>>>>>> Num rows in result set is 312
>>>>>> Num rows in result set is 2109
>>>>>> Disconnected
>>>>>>
>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>> above snippet and then post both the source for your stored procedure
>>>>>> and your JDBC app.
>>>>>>
>>>>>> Karim
>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>> Bill,
>>>>>>>
>>>>>>> Bill Williams wrote:
>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>> {call
>>>>>>>> InductTote('12345')}. The InductTote stored procedure does return
>>>>>>>> a
>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>> the
>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>> statement but I don't know how to do that with a CallableStatement
>>>>>>>> object. Within the stored procedure I have cursors that are being
>>>>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>>>>> within a loop the cursor is running.
>>>>>>> Without more information, my only request is:
>>>>>>>
>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>
>>>>>>> Regards,
>>>>>>>
>>>>>
>>


Bill Williams Posted on 2009-04-06 19:50:01.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 147
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da5ce9$1@forums-1-dub>
Date: 6 Apr 2009 12:50:01 -0700
X-Trace: forums-1-dub 1239047401 10.22.241.152 (6 Apr 2009 12:50:01 -0700)
X-Original-Trace: 6 Apr 2009 12:50:01 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7479
Article PK: 5895

UGH! I'm an idiot. The cIdentifyBox cursor at the top of InductCartonEx
needed the "with hold" statement. As soon as I put that on it, it worked
(at least in dbisqlc). I still have to test it from my app but I'll bet it
will work now.

Is there a reason anyone can think of why I did not get the error when I was
in dbisql or using jConnect for my database connection from my app?

Thanks.

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:49da58b0$1@forums-1-dub...
> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
> we need to figure out what is going wrong with the stored procs. Given
> that you can reproduce the problem with dbisqlc, I assume it would be best
> if you ran the test using the stored procedure debugger and see exactly
> where the cursor not open error is being thrown. In the meantime, I hope
> somebody else with a bit more knowledge regarding stored procs and sql can
> have a look at your stored proc definitions and see if they can spot where
> or why the error gets thrown with the commit but not without the commit.
>
> Karim
> Bill Williams wrote:
>> I just tried dbisqlc and got the "cursor not open" error when I run the
>> InductCartonEx.
>>
>> I tried the with hold statement on the cBalancePicks cursor (which I
>> thought I had tried before) and still get the error in dbisqlc.
>>
>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>> news:49da4b71$1@forums-1-dub...
>>> Bill,
>>>
>>> I am not seeing what might be going wrong. Can someone else have a look
>>> at the procedure definitions in the file that Bill posted and see if
>>> there is something obvious that would be causing the cursor not open
>>> error?
>>>
>>> In the meantime, Bill, what happens if you try executing the
>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs to
>>> be opened using with hold?
>>>
>>> Karim
>>> Bill Williams wrote:
>>>> Did any of this help or is more info needed?
>>>>
>>>> Thanks
>>>>
>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>> news:49d6067f@forums-1-dub...
>>>>> Attached are the two main stored procedures and the snipet of code
>>>>> where the problem occurs. The commit is within the balance.stp file.
>>>>> If I comment out that commit, the problem goes away.
>>>>>
>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>> modes.
>>>>>
>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>
>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>> Bill,
>>>>>>
>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>> Here
>>>>>> is what I tried...
>>>>>>
>>>>>> 1) I created the procedure foo as follows:
>>>>>>
>>>>>> create procedure foo(p char(128))
>>>>>> begin
>>>>>> select * from systable;
>>>>>> commit;
>>>>>> select * from syscolumn;
>>>>>> end
>>>>>>
>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>> following code snippet:
>>>>>>
>>>>>> try {
>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>> foo('12345')}" );
>>>>>> boolean has_result = stmt.execute();
>>>>>> int rows_affected = 0;
>>>>>> do {
>>>>>> if( has_result ) {
>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>> int count = 0;
>>>>>> while( rs.next() ) {
>>>>>> ++count;
>>>>>> }
>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>> } else {
>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>> if( rows_affected >= 0 ) {
>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> has_result = stmt.getMoreResults();
>>>>>> } while( has_result || rows_affected != -1 );
>>>>>> stmt.close();
>>>>>> con.close();
>>>>>> System.out.println( "Disconnected" );
>>>>>> } catch (SQLException sqe) {
>>>>>> ...
>>>>>> }
>>>>>>
>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>
>>>>>> Num rows in result set is 312
>>>>>> Num rows in result set is 2109
>>>>>> Disconnected
>>>>>>
>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>> above snippet and then post both the source for your stored procedure
>>>>>> and your JDBC app.
>>>>>>
>>>>>> Karim
>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>> Bill,
>>>>>>>
>>>>>>> Bill Williams wrote:
>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>> {call
>>>>>>>> InductTote('12345')}. The InductTote stored procedure does return
>>>>>>>> a
>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>> the
>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>> statement but I don't know how to do that with a CallableStatement
>>>>>>>> object. Within the stored procedure I have cursors that are being
>>>>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>>>>> within a loop the cursor is running.
>>>>>>> Without more information, my only request is:
>>>>>>>
>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>
>>>>>>> Regards,
>>>>>>>
>>>>>
>>


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-06 19:57:59.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub>
In-Reply-To: <49da5ce9$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49da5ec7@forums-1-dub>
Date: 6 Apr 2009 12:57:59 -0700
X-Trace: forums-1-dub 1239047879 10.22.241.152 (6 Apr 2009 12:57:59 -0700)
X-Original-Trace: 6 Apr 2009 12:57:59 -0700, vip152.sybase.com
Lines: 160
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7480
Article PK: 5894

There are several things that are different when jConnect is used. For
one thing, the chained option is automatically turned off and for
another, jConnect would require that the entire procedure be run to
completion and have all the results and update counts sent in one go.
So, there could be any number of reasons why you would not see the
problem with jConnect.

DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
driver. However, DBISQL does set a few options and do a few clever
things up front which may explain why the problem was not showing up.
Nevertheless, I'm glad the problem seems to be solved and you can
continue to use the iAnywhere JDBC driver instead of jConnect.

Karim

Bill Williams wrote:
> UGH! I'm an idiot. The cIdentifyBox cursor at the top of InductCartonEx
> needed the "with hold" statement. As soon as I put that on it, it worked
> (at least in dbisqlc). I still have to test it from my app but I'll bet it
> will work now.
>
> Is there a reason anyone can think of why I did not get the error when I was
> in dbisql or using jConnect for my database connection from my app?
>
> Thanks.
>
> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
> news:49da58b0$1@forums-1-dub...
>> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
>> we need to figure out what is going wrong with the stored procs. Given
>> that you can reproduce the problem with dbisqlc, I assume it would be best
>> if you ran the test using the stored procedure debugger and see exactly
>> where the cursor not open error is being thrown. In the meantime, I hope
>> somebody else with a bit more knowledge regarding stored procs and sql can
>> have a look at your stored proc definitions and see if they can spot where
>> or why the error gets thrown with the commit but not without the commit.
>>
>> Karim
>> Bill Williams wrote:
>>> I just tried dbisqlc and got the "cursor not open" error when I run the
>>> InductCartonEx.
>>>
>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>> thought I had tried before) and still get the error in dbisqlc.
>>>
>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>> news:49da4b71$1@forums-1-dub...
>>>> Bill,
>>>>
>>>> I am not seeing what might be going wrong. Can someone else have a look
>>>> at the procedure definitions in the file that Bill posted and see if
>>>> there is something obvious that would be causing the cursor not open
>>>> error?
>>>>
>>>> In the meantime, Bill, what happens if you try executing the
>>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs to
>>>> be opened using with hold?
>>>>
>>>> Karim
>>>> Bill Williams wrote:
>>>>> Did any of this help or is more info needed?
>>>>>
>>>>> Thanks
>>>>>
>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>> news:49d6067f@forums-1-dub...
>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>> where the problem occurs. The commit is within the balance.stp file.
>>>>>> If I comment out that commit, the problem goes away.
>>>>>>
>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>> modes.
>>>>>>
>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>
>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>> Bill,
>>>>>>>
>>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>>> Here
>>>>>>> is what I tried...
>>>>>>>
>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>
>>>>>>> create procedure foo(p char(128))
>>>>>>> begin
>>>>>>> select * from systable;
>>>>>>> commit;
>>>>>>> select * from syscolumn;
>>>>>>> end
>>>>>>>
>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>>> following code snippet:
>>>>>>>
>>>>>>> try {
>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>> foo('12345')}" );
>>>>>>> boolean has_result = stmt.execute();
>>>>>>> int rows_affected = 0;
>>>>>>> do {
>>>>>>> if( has_result ) {
>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>> int count = 0;
>>>>>>> while( rs.next() ) {
>>>>>>> ++count;
>>>>>>> }
>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>> } else {
>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>> if( rows_affected >= 0 ) {
>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>> }
>>>>>>> }
>>>>>>>
>>>>>>> has_result = stmt.getMoreResults();
>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>> stmt.close();
>>>>>>> con.close();
>>>>>>> System.out.println( "Disconnected" );
>>>>>>> } catch (SQLException sqe) {
>>>>>>> ...
>>>>>>> }
>>>>>>>
>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>
>>>>>>> Num rows in result set is 312
>>>>>>> Num rows in result set is 2109
>>>>>>> Disconnected
>>>>>>>
>>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>>> above snippet and then post both the source for your stored procedure
>>>>>>> and your JDBC app.
>>>>>>>
>>>>>>> Karim
>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>> Bill,
>>>>>>>>
>>>>>>>> Bill Williams wrote:
>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure is
>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>> {call
>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does return
>>>>>>>>> a
>>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>>> the
>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>> statement but I don't know how to do that with a CallableStatement
>>>>>>>>> object. Within the stored procedure I have cursors that are being
>>>>>>>>> opened and I am using the WITH HOLD statement because the commit is
>>>>>>>>> within a loop the cursor is running.
>>>>>>>> Without more information, my only request is:
>>>>>>>>
>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>>
>
>


Bill Williams Posted on 2009-04-07 11:57:07.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub> <49da5ec7@forums-1-dub>
Subject: Re: Cursor not open error on executeQuery
Lines: 176
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49db3f93$1@forums-1-dub>
Date: 7 Apr 2009 04:57:07 -0700
X-Trace: forums-1-dub 1239105427 10.22.241.152 (7 Apr 2009 04:57:07 -0700)
X-Original-Trace: 7 Apr 2009 04:57:07 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7482
Article PK: 5896

I have never heard of "chained mode". After looking through the docs, it
appears it simply means autocommit is turned on when chained mode is off.

We always turn auto commit off no matter how we are connecting to the
database; therefore, wouldn't that mean chained mode was turned on? Not
that it really matters now that I have it working, I was just curious.

Thanks

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:49da5ec7@forums-1-dub...
> There are several things that are different when jConnect is used. For one
> thing, the chained option is automatically turned off and for another,
> jConnect would require that the entire procedure be run to completion and
> have all the results and update counts sent in one go. So, there could be
> any number of reasons why you would not see the problem with jConnect.
>
> DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
> driver. However, DBISQL does set a few options and do a few clever things
> up front which may explain why the problem was not showing up.
> Nevertheless, I'm glad the problem seems to be solved and you can continue
> to use the iAnywhere JDBC driver instead of jConnect.
>
> Karim
> Bill Williams wrote:
>> UGH! I'm an idiot. The cIdentifyBox cursor at the top of InductCartonEx
>> needed the "with hold" statement. As soon as I put that on it, it worked
>> (at least in dbisqlc). I still have to test it from my app but I'll bet
>> it will work now.
>>
>> Is there a reason anyone can think of why I did not get the error when I
>> was in dbisql or using jConnect for my database connection from my app?
>>
>> Thanks.
>>
>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>> news:49da58b0$1@forums-1-dub...
>>> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
>>> we need to figure out what is going wrong with the stored procs. Given
>>> that you can reproduce the problem with dbisqlc, I assume it would be
>>> best if you ran the test using the stored procedure debugger and see
>>> exactly where the cursor not open error is being thrown. In the
>>> meantime, I hope somebody else with a bit more knowledge regarding
>>> stored procs and sql can have a look at your stored proc definitions and
>>> see if they can spot where or why the error gets thrown with the commit
>>> but not without the commit.
>>>
>>> Karim
>>> Bill Williams wrote:
>>>> I just tried dbisqlc and got the "cursor not open" error when I run the
>>>> InductCartonEx.
>>>>
>>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>>> thought I had tried before) and still get the error in dbisqlc.
>>>>
>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>> news:49da4b71$1@forums-1-dub...
>>>>> Bill,
>>>>>
>>>>> I am not seeing what might be going wrong. Can someone else have a
>>>>> look at the procedure definitions in the file that Bill posted and see
>>>>> if there is something obvious that would be causing the cursor not
>>>>> open error?
>>>>>
>>>>> In the meantime, Bill, what happens if you try executing the
>>>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs
>>>>> to be opened using with hold?
>>>>>
>>>>> Karim
>>>>> Bill Williams wrote:
>>>>>> Did any of this help or is more info needed?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>>> news:49d6067f@forums-1-dub...
>>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>>> where the problem occurs. The commit is within the balance.stp
>>>>>>> file. If I comment out that commit, the problem goes away.
>>>>>>>
>>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>>> modes.
>>>>>>>
>>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>>
>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>>> Bill,
>>>>>>>>
>>>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>>>> Here
>>>>>>>> is what I tried...
>>>>>>>>
>>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>>
>>>>>>>> create procedure foo(p char(128))
>>>>>>>> begin
>>>>>>>> select * from systable;
>>>>>>>> commit;
>>>>>>>> select * from syscolumn;
>>>>>>>> end
>>>>>>>>
>>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>>>> following code snippet:
>>>>>>>>
>>>>>>>> try {
>>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>>> foo('12345')}" );
>>>>>>>> boolean has_result = stmt.execute();
>>>>>>>> int rows_affected = 0;
>>>>>>>> do {
>>>>>>>> if( has_result ) {
>>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>>> int count = 0;
>>>>>>>> while( rs.next() ) {
>>>>>>>> ++count;
>>>>>>>> }
>>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>>> } else {
>>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>>> if( rows_affected >= 0 ) {
>>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>>> }
>>>>>>>> }
>>>>>>>>
>>>>>>>> has_result = stmt.getMoreResults();
>>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>>> stmt.close();
>>>>>>>> con.close();
>>>>>>>> System.out.println( "Disconnected" );
>>>>>>>> } catch (SQLException sqe) {
>>>>>>>> ...
>>>>>>>> }
>>>>>>>>
>>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>>
>>>>>>>> Num rows in result set is 312
>>>>>>>> Num rows in result set is 2109
>>>>>>>> Disconnected
>>>>>>>>
>>>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>>>> above snippet and then post both the source for your stored
>>>>>>>> procedure
>>>>>>>> and your JDBC app.
>>>>>>>>
>>>>>>>> Karim
>>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>>> Bill,
>>>>>>>>>
>>>>>>>>> Bill Williams wrote:
>>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure
>>>>>>>>>> is
>>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>>> {call
>>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does
>>>>>>>>>> return a
>>>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>>>> the
>>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>>> statement but I don't know how to do that with a
>>>>>>>>>> CallableStatement
>>>>>>>>>> object. Within the stored procedure I have cursors that are
>>>>>>>>>> being
>>>>>>>>>> opened and I am using the WITH HOLD statement because the commit
>>>>>>>>>> is
>>>>>>>>>> within a loop the cursor is running.
>>>>>>>>> Without more information, my only request is:
>>>>>>>>>
>>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>>
>>


Karim Khamis [Sybase iAnywhere] Posted on 2009-04-07 12:45:04.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub> <49da5ec7@forums-1-dub> <49db3f93$1@forums-1-dub>
In-Reply-To: <49db3f93$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49db4ad0$1@forums-1-dub>
Date: 7 Apr 2009 05:45:04 -0700
X-Trace: forums-1-dub 1239108304 10.22.241.152 (7 Apr 2009 05:45:04 -0700)
X-Original-Trace: 7 Apr 2009 05:45:04 -0700, vip152.sybase.com
Lines: 188
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7483
Article PK: 5898

Chained mode was just one (and usually the most obvious) difference
between jConnect and the iAnywhere JDBC driver so that's why I mentioned
it. Now that I think of it though, another fairly big difference between
using jConect and the iAnywhere JDBC driver is the default isolation
level. If you are using the iAnywhere JDBC driver, the isolation level
by default is 0 while the default isolation level when using jConnect is
1. That might be the more relevant difference in this case.

Nevertheless, you are correct, it does not really matter now since
you've got things working.

Karim

Bill Williams wrote:
> I have never heard of "chained mode". After looking through the docs, it
> appears it simply means autocommit is turned on when chained mode is off.
>
> We always turn auto commit off no matter how we are connecting to the
> database; therefore, wouldn't that mean chained mode was turned on? Not
> that it really matters now that I have it working, I was just curious.
>
> Thanks
>
> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
> news:49da5ec7@forums-1-dub...
>> There are several things that are different when jConnect is used. For one
>> thing, the chained option is automatically turned off and for another,
>> jConnect would require that the entire procedure be run to completion and
>> have all the results and update counts sent in one go. So, there could be
>> any number of reasons why you would not see the problem with jConnect.
>>
>> DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
>> driver. However, DBISQL does set a few options and do a few clever things
>> up front which may explain why the problem was not showing up.
>> Nevertheless, I'm glad the problem seems to be solved and you can continue
>> to use the iAnywhere JDBC driver instead of jConnect.
>>
>> Karim
>> Bill Williams wrote:
>>> UGH! I'm an idiot. The cIdentifyBox cursor at the top of InductCartonEx
>>> needed the "with hold" statement. As soon as I put that on it, it worked
>>> (at least in dbisqlc). I still have to test it from my app but I'll bet
>>> it will work now.
>>>
>>> Is there a reason anyone can think of why I did not get the error when I
>>> was in dbisql or using jConnect for my database connection from my app?
>>>
>>> Thanks.
>>>
>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>> news:49da58b0$1@forums-1-dub...
>>>> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
>>>> we need to figure out what is going wrong with the stored procs. Given
>>>> that you can reproduce the problem with dbisqlc, I assume it would be
>>>> best if you ran the test using the stored procedure debugger and see
>>>> exactly where the cursor not open error is being thrown. In the
>>>> meantime, I hope somebody else with a bit more knowledge regarding
>>>> stored procs and sql can have a look at your stored proc definitions and
>>>> see if they can spot where or why the error gets thrown with the commit
>>>> but not without the commit.
>>>>
>>>> Karim
>>>> Bill Williams wrote:
>>>>> I just tried dbisqlc and got the "cursor not open" error when I run the
>>>>> InductCartonEx.
>>>>>
>>>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>>>> thought I had tried before) and still get the error in dbisqlc.
>>>>>
>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>>> news:49da4b71$1@forums-1-dub...
>>>>>> Bill,
>>>>>>
>>>>>> I am not seeing what might be going wrong. Can someone else have a
>>>>>> look at the procedure definitions in the file that Bill posted and see
>>>>>> if there is something obvious that would be causing the cursor not
>>>>>> open error?
>>>>>>
>>>>>> In the meantime, Bill, what happens if you try executing the
>>>>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>>>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs
>>>>>> to be opened using with hold?
>>>>>>
>>>>>> Karim
>>>>>> Bill Williams wrote:
>>>>>>> Did any of this help or is more info needed?
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>>>> news:49d6067f@forums-1-dub...
>>>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>>>> where the problem occurs. The commit is within the balance.stp
>>>>>>>> file. If I comment out that commit, the problem goes away.
>>>>>>>>
>>>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>>>> modes.
>>>>>>>>
>>>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>>>
>>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>>>> Bill,
>>>>>>>>>
>>>>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>>>>> Here
>>>>>>>>> is what I tried...
>>>>>>>>>
>>>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>>>
>>>>>>>>> create procedure foo(p char(128))
>>>>>>>>> begin
>>>>>>>>> select * from systable;
>>>>>>>>> commit;
>>>>>>>>> select * from syscolumn;
>>>>>>>>> end
>>>>>>>>>
>>>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>>>>> following code snippet:
>>>>>>>>>
>>>>>>>>> try {
>>>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>>>> foo('12345')}" );
>>>>>>>>> boolean has_result = stmt.execute();
>>>>>>>>> int rows_affected = 0;
>>>>>>>>> do {
>>>>>>>>> if( has_result ) {
>>>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>>>> int count = 0;
>>>>>>>>> while( rs.next() ) {
>>>>>>>>> ++count;
>>>>>>>>> }
>>>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>>>> } else {
>>>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>>>> if( rows_affected >= 0 ) {
>>>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>>>> }
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> has_result = stmt.getMoreResults();
>>>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>>>> stmt.close();
>>>>>>>>> con.close();
>>>>>>>>> System.out.println( "Disconnected" );
>>>>>>>>> } catch (SQLException sqe) {
>>>>>>>>> ...
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>>>
>>>>>>>>> Num rows in result set is 312
>>>>>>>>> Num rows in result set is 2109
>>>>>>>>> Disconnected
>>>>>>>>>
>>>>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>>>>> above snippet and then post both the source for your stored
>>>>>>>>> procedure
>>>>>>>>> and your JDBC app.
>>>>>>>>>
>>>>>>>>> Karim
>>>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>>>> Bill,
>>>>>>>>>>
>>>>>>>>>> Bill Williams wrote:
>>>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure
>>>>>>>>>>> is
>>>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>>>> {call
>>>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does
>>>>>>>>>>> return a
>>>>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>>>>> the
>>>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>>>> statement but I don't know how to do that with a
>>>>>>>>>>> CallableStatement
>>>>>>>>>>> object. Within the stored procedure I have cursors that are
>>>>>>>>>>> being
>>>>>>>>>>> opened and I am using the WITH HOLD statement because the commit
>>>>>>>>>>> is
>>>>>>>>>>> within a loop the cursor is running.
>>>>>>>>>> Without more information, my only request is:
>>>>>>>>>>
>>>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>


Breck Carter [TeamSybase] Posted on 2009-04-08 11:58:56.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <7f2pt49q02g94rb43o7sbcf4dipu11hnlu@4ax.com>
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub> <49da5ec7@forums-1-dub> <49db3f93$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Apr 2009 04:58:56 -0700
X-Trace: forums-1-dub 1239191936 10.22.241.152 (8 Apr 2009 04:58:56 -0700)
X-Original-Trace: 8 Apr 2009 04:58:56 -0700, vip152.sybase.com
Lines: 235
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7491
Article PK: 5906

There are two kinds of "auto commit": server-side auto commit (chained
mode off) means that each SQL statement executed by the server is
committed individually, internally, and client-side auto commit
(called "autocommit" by the client-side interface software) means that
each request sent to the server is followed by an actual, explicit (to
the server) COMMIT request. So, if you turn client-side auto commit
off, but server-side auto commit is on (i.e., chained mode is off),
then in effect you still have auto commit on... the end result remains
the same.

In general, for multi-connection databases (multi-user, or complex
single-user apps), both kinds of auto commit should be turned off and
your application should take control of transaction design by issuing
explicit COMMIT and ROLLBACK statements when appropriate. Turning auto
commit off can be problematic with interfaces like jConnect because by
default SQL Anywhere turns chained mode off (server-side auto commit
on) in the sp_tsql_environment stored procedure. Here's the whole
story:

By default the login_procedure option point to the
sp_login_environment procedure, and when the connection uses the TDS
protocol (e.g., jConnect) that procedure calls sp_tsql_environment,
and that procedure does several curious things, including...

set temporary option chained = 'OFF';

You can edit either or both procedures in Sybase Central, or change
login_procedure to point to another procedure, or set it to '',
whatever you need to do.

Read the Help about login_procedure, and ignore the exhortation "You
should not edit either sp_login_environment or sp_tsql_environment."
The reason I say that is there is nothing magic about TDS connections
per se; if you have TDS connections that want chained mode on and
others (old-style Transact SQL applications that futz around
with/without BEGIN TRANSACTION statements) that need chained mode off,
then you MUST do something special. My advice is to drop both sp's,
code your own... get rid of all vestiges of twentieth century junk...
take control of your connections.

Another subtle point: client-side auto commit affects stored procedure
differently than server-side. With server-side auto commit each
statement inside the procedure is followed by an internal commit. With
client-side auto-commit, the interface software sends a COMMIT after
the CALL returns, so the statements inside the procedure are not
auto-committed until after the procedure ends.

Bottom line: In English, "auto commit" means "never use".

Breck

On 7 Apr 2009 04:57:07 -0700, "Bill Williams"

<billwilliams@qcsoftware.com> wrote:

>I have never heard of "chained mode". After looking through the docs, it
>appears it simply means autocommit is turned on when chained mode is off.
>
>We always turn auto commit off no matter how we are connecting to the
>database; therefore, wouldn't that mean chained mode was turned on? Not
>that it really matters now that I have it working, I was just curious.
>
>Thanks
>
>"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>news:49da5ec7@forums-1-dub...
>> There are several things that are different when jConnect is used. For one
>> thing, the chained option is automatically turned off and for another,
>> jConnect would require that the entire procedure be run to completion and
>> have all the results and update counts sent in one go. So, there could be
>> any number of reasons why you would not see the problem with jConnect.
>>
>> DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
>> driver. However, DBISQL does set a few options and do a few clever things
>> up front which may explain why the problem was not showing up.
>> Nevertheless, I'm glad the problem seems to be solved and you can continue
>> to use the iAnywhere JDBC driver instead of jConnect.
>>
>> Karim
>> Bill Williams wrote:
>>> UGH! I'm an idiot. The cIdentifyBox cursor at the top of InductCartonEx
>>> needed the "with hold" statement. As soon as I put that on it, it worked
>>> (at least in dbisqlc). I still have to test it from my app but I'll bet
>>> it will work now.
>>>
>>> Is there a reason anyone can think of why I did not get the error when I
>>> was in dbisql or using jConnect for my database connection from my app?
>>>
>>> Thanks.
>>>
>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>> news:49da58b0$1@forums-1-dub...
>>>> Okay, that's good news (sort of). It means we can rule JDBC out. So now,
>>>> we need to figure out what is going wrong with the stored procs. Given
>>>> that you can reproduce the problem with dbisqlc, I assume it would be
>>>> best if you ran the test using the stored procedure debugger and see
>>>> exactly where the cursor not open error is being thrown. In the
>>>> meantime, I hope somebody else with a bit more knowledge regarding
>>>> stored procs and sql can have a look at your stored proc definitions and
>>>> see if they can spot where or why the error gets thrown with the commit
>>>> but not without the commit.
>>>>
>>>> Karim
>>>> Bill Williams wrote:
>>>>> I just tried dbisqlc and got the "cursor not open" error when I run the
>>>>> InductCartonEx.
>>>>>
>>>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>>>> thought I had tried before) and still get the error in dbisqlc.
>>>>>
>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>>> news:49da4b71$1@forums-1-dub...
>>>>>> Bill,
>>>>>>
>>>>>> I am not seeing what might be going wrong. Can someone else have a
>>>>>> look at the procedure definitions in the file that Bill posted and see
>>>>>> if there is something obvious that would be causing the cursor not
>>>>>> open error?
>>>>>>
>>>>>> In the meantime, Bill, what happens if you try executing the
>>>>>> InductCartonEx procedure in dbisqlc? Note, I want you to specifically
>>>>>> use dbisqlc instead of dbisql since dbisqlc does not use the iAnywhere
>>>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor needs
>>>>>> to be opened using with hold?
>>>>>>
>>>>>> Karim
>>>>>> Bill Williams wrote:
>>>>>>> Did any of this help or is more info needed?
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>>>> news:49d6067f@forums-1-dub...
>>>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>>>> where the problem occurs. The commit is within the balance.stp
>>>>>>>> file. If I comment out that commit, the problem goes away.
>>>>>>>>
>>>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>>>> modes.
>>>>>>>>
>>>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>>>
>>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>>>> Bill,
>>>>>>>>>
>>>>>>>>> Jeff is correct, we need more information to diagnose the problem.
>>>>>>>>> Here
>>>>>>>>> is what I tried...
>>>>>>>>>
>>>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>>>
>>>>>>>>> create procedure foo(p char(128))
>>>>>>>>> begin
>>>>>>>>> select * from systable;
>>>>>>>>> commit;
>>>>>>>>> select * from syscolumn;
>>>>>>>>> end
>>>>>>>>>
>>>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with the
>>>>>>>>> following code snippet:
>>>>>>>>>
>>>>>>>>> try {
>>>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>>>> foo('12345')}" );
>>>>>>>>> boolean has_result = stmt.execute();
>>>>>>>>> int rows_affected = 0;
>>>>>>>>> do {
>>>>>>>>> if( has_result ) {
>>>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>>>> int count = 0;
>>>>>>>>> while( rs.next() ) {
>>>>>>>>> ++count;
>>>>>>>>> }
>>>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>>>> } else {
>>>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>>>> if( rows_affected >= 0 ) {
>>>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>>>> }
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> has_result = stmt.getMoreResults();
>>>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>>>> stmt.close();
>>>>>>>>> con.close();
>>>>>>>>> System.out.println( "Disconnected" );
>>>>>>>>> } catch (SQLException sqe) {
>>>>>>>>> ...
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>>>
>>>>>>>>> Num rows in result set is 312
>>>>>>>>> Num rows in result set is 2109
>>>>>>>>> Disconnected
>>>>>>>>>
>>>>>>>>> So, try and see if your JDBC code is missing something based on the
>>>>>>>>> above snippet and then post both the source for your stored
>>>>>>>>> procedure
>>>>>>>>> and your JDBC app.
>>>>>>>>>
>>>>>>>>> Karim
>>>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>>>> Bill,
>>>>>>>>>>
>>>>>>>>>> Bill Williams wrote:
>>>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure
>>>>>>>>>>> is
>>>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>>>> {call
>>>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does
>>>>>>>>>>> return a
>>>>>>>>>>> result set. Like I said, it does have an embedded commit within
>>>>>>>>>>> the
>>>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>>>> statement but I don't know how to do that with a
>>>>>>>>>>> CallableStatement
>>>>>>>>>>> object. Within the stored procedure I have cursors that are
>>>>>>>>>>> being
>>>>>>>>>>> opened and I am using the WITH HOLD statement because the commit
>>>>>>>>>>> is
>>>>>>>>>>> within a loop the cursor is running.
>>>>>>>>>> Without more information, my only request is:
>>>>>>>>>>
>>>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Bill Williams Posted on 2009-04-08 14:28:47.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub> <49da5ec7@forums-1-dub> <49db3f93$1@forums-1-dub> <7f2pt49q02g94rb43o7sbcf4dipu11hnlu@4ax.com>
Subject: Re: Cursor not open error on executeQuery
Lines: 273
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49dcb49f$1@forums-1-dub>
Date: 8 Apr 2009 07:28:47 -0700
X-Trace: forums-1-dub 1239200927 10.22.241.152 (8 Apr 2009 07:28:47 -0700)
X-Original-Trace: 8 Apr 2009 07:28:47 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7493
Article PK: 5907

Thanks for the info.

We've always had in our code to turn auto commit off as soon as we create a
connection regardless of the type of JDBC software we are using. Like you
said, we like to have control when the commits occur. It seems by doing
that, we've never had an issue with being able to issue a rollback or commit
to control our transaction.

Based on what you are saying, there is still something on the server side
that could bite us with an auto commit when we use jConnect?

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
message news:7f2pt49q02g94rb43o7sbcf4dipu11hnlu@4ax.com...
> There are two kinds of "auto commit": server-side auto commit (chained
> mode off) means that each SQL statement executed by the server is
> committed individually, internally, and client-side auto commit
> (called "autocommit" by the client-side interface software) means that
> each request sent to the server is followed by an actual, explicit (to
> the server) COMMIT request. So, if you turn client-side auto commit
> off, but server-side auto commit is on (i.e., chained mode is off),
> then in effect you still have auto commit on... the end result remains
> the same.
>
> In general, for multi-connection databases (multi-user, or complex
> single-user apps), both kinds of auto commit should be turned off and
> your application should take control of transaction design by issuing
> explicit COMMIT and ROLLBACK statements when appropriate. Turning auto
> commit off can be problematic with interfaces like jConnect because by
> default SQL Anywhere turns chained mode off (server-side auto commit
> on) in the sp_tsql_environment stored procedure. Here's the whole
> story:
>
> By default the login_procedure option point to the
> sp_login_environment procedure, and when the connection uses the TDS
> protocol (e.g., jConnect) that procedure calls sp_tsql_environment,
> and that procedure does several curious things, including...
>
> set temporary option chained = 'OFF';
>
> You can edit either or both procedures in Sybase Central, or change
> login_procedure to point to another procedure, or set it to '',
> whatever you need to do.
>
> Read the Help about login_procedure, and ignore the exhortation "You
> should not edit either sp_login_environment or sp_tsql_environment."
> The reason I say that is there is nothing magic about TDS connections
> per se; if you have TDS connections that want chained mode on and
> others (old-style Transact SQL applications that futz around
> with/without BEGIN TRANSACTION statements) that need chained mode off,
> then you MUST do something special. My advice is to drop both sp's,
> code your own... get rid of all vestiges of twentieth century junk...
> take control of your connections.
>
> Another subtle point: client-side auto commit affects stored procedure
> differently than server-side. With server-side auto commit each
> statement inside the procedure is followed by an internal commit. With
> client-side auto-commit, the interface software sends a COMMIT after
> the CALL returns, so the statements inside the procedure are not
> auto-committed until after the procedure ends.
>
> Bottom line: In English, "auto commit" means "never use".
>
> Breck
>
> On 7 Apr 2009 04:57:07 -0700, "Bill Williams"
> <billwilliams@qcsoftware.com> wrote:
>
>>I have never heard of "chained mode". After looking through the docs, it
>>appears it simply means autocommit is turned on when chained mode is off.
>>
>>We always turn auto commit off no matter how we are connecting to the
>>database; therefore, wouldn't that mean chained mode was turned on? Not
>>that it really matters now that I have it working, I was just curious.
>>
>>Thanks
>>
>>"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>news:49da5ec7@forums-1-dub...
>>> There are several things that are different when jConnect is used. For
>>> one
>>> thing, the chained option is automatically turned off and for another,
>>> jConnect would require that the entire procedure be run to completion
>>> and
>>> have all the results and update counts sent in one go. So, there could
>>> be
>>> any number of reasons why you would not see the problem with jConnect.
>>>
>>> DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
>>> driver. However, DBISQL does set a few options and do a few clever
>>> things
>>> up front which may explain why the problem was not showing up.
>>> Nevertheless, I'm glad the problem seems to be solved and you can
>>> continue
>>> to use the iAnywhere JDBC driver instead of jConnect.
>>>
>>> Karim
>>> Bill Williams wrote:
>>>> UGH! I'm an idiot. The cIdentifyBox cursor at the top of
>>>> InductCartonEx
>>>> needed the "with hold" statement. As soon as I put that on it, it
>>>> worked
>>>> (at least in dbisqlc). I still have to test it from my app but I'll
>>>> bet
>>>> it will work now.
>>>>
>>>> Is there a reason anyone can think of why I did not get the error when
>>>> I
>>>> was in dbisql or using jConnect for my database connection from my app?
>>>>
>>>> Thanks.
>>>>
>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>> news:49da58b0$1@forums-1-dub...
>>>>> Okay, that's good news (sort of). It means we can rule JDBC out. So
>>>>> now,
>>>>> we need to figure out what is going wrong with the stored procs. Given
>>>>> that you can reproduce the problem with dbisqlc, I assume it would be
>>>>> best if you ran the test using the stored procedure debugger and see
>>>>> exactly where the cursor not open error is being thrown. In the
>>>>> meantime, I hope somebody else with a bit more knowledge regarding
>>>>> stored procs and sql can have a look at your stored proc definitions
>>>>> and
>>>>> see if they can spot where or why the error gets thrown with the
>>>>> commit
>>>>> but not without the commit.
>>>>>
>>>>> Karim
>>>>> Bill Williams wrote:
>>>>>> I just tried dbisqlc and got the "cursor not open" error when I run
>>>>>> the
>>>>>> InductCartonEx.
>>>>>>
>>>>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>>>>> thought I had tried before) and still get the error in dbisqlc.
>>>>>>
>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>> message
>>>>>> news:49da4b71$1@forums-1-dub...
>>>>>>> Bill,
>>>>>>>
>>>>>>> I am not seeing what might be going wrong. Can someone else have a
>>>>>>> look at the procedure definitions in the file that Bill posted and
>>>>>>> see
>>>>>>> if there is something obvious that would be causing the cursor not
>>>>>>> open error?
>>>>>>>
>>>>>>> In the meantime, Bill, what happens if you try executing the
>>>>>>> InductCartonEx procedure in dbisqlc? Note, I want you to
>>>>>>> specifically
>>>>>>> use dbisqlc instead of dbisql since dbisqlc does not use the
>>>>>>> iAnywhere
>>>>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor
>>>>>>> needs
>>>>>>> to be opened using with hold?
>>>>>>>
>>>>>>> Karim
>>>>>>> Bill Williams wrote:
>>>>>>>> Did any of this help or is more info needed?
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>>>>> news:49d6067f@forums-1-dub...
>>>>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>>>>> where the problem occurs. The commit is within the balance.stp
>>>>>>>>> file. If I comment out that commit, the problem goes away.
>>>>>>>>>
>>>>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>>>>> modes.
>>>>>>>>>
>>>>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>>>>
>>>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>>>>> Bill,
>>>>>>>>>>
>>>>>>>>>> Jeff is correct, we need more information to diagnose the
>>>>>>>>>> problem.
>>>>>>>>>> Here
>>>>>>>>>> is what I tried...
>>>>>>>>>>
>>>>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>>>>
>>>>>>>>>> create procedure foo(p char(128))
>>>>>>>>>> begin
>>>>>>>>>> select * from systable;
>>>>>>>>>> commit;
>>>>>>>>>> select * from syscolumn;
>>>>>>>>>> end
>>>>>>>>>>
>>>>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with
>>>>>>>>>> the
>>>>>>>>>> following code snippet:
>>>>>>>>>>
>>>>>>>>>> try {
>>>>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>>>>> foo('12345')}" );
>>>>>>>>>> boolean has_result = stmt.execute();
>>>>>>>>>> int rows_affected = 0;
>>>>>>>>>> do {
>>>>>>>>>> if( has_result ) {
>>>>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>>>>> int count = 0;
>>>>>>>>>> while( rs.next() ) {
>>>>>>>>>> ++count;
>>>>>>>>>> }
>>>>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>>>>> } else {
>>>>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>>>>> if( rows_affected >= 0 ) {
>>>>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>>>>> }
>>>>>>>>>> }
>>>>>>>>>>
>>>>>>>>>> has_result = stmt.getMoreResults();
>>>>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>>>>> stmt.close();
>>>>>>>>>> con.close();
>>>>>>>>>> System.out.println( "Disconnected" );
>>>>>>>>>> } catch (SQLException sqe) {
>>>>>>>>>> ...
>>>>>>>>>> }
>>>>>>>>>>
>>>>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>>>>
>>>>>>>>>> Num rows in result set is 312
>>>>>>>>>> Num rows in result set is 2109
>>>>>>>>>> Disconnected
>>>>>>>>>>
>>>>>>>>>> So, try and see if your JDBC code is missing something based on
>>>>>>>>>> the
>>>>>>>>>> above snippet and then post both the source for your stored
>>>>>>>>>> procedure
>>>>>>>>>> and your JDBC app.
>>>>>>>>>>
>>>>>>>>>> Karim
>>>>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>>>>> Bill,
>>>>>>>>>>>
>>>>>>>>>>> Bill Williams wrote:
>>>>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure
>>>>>>>>>>>> is
>>>>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>>>>> {call
>>>>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does
>>>>>>>>>>>> return a
>>>>>>>>>>>> result set. Like I said, it does have an embedded commit
>>>>>>>>>>>> within
>>>>>>>>>>>> the
>>>>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>>>>> statement but I don't know how to do that with a
>>>>>>>>>>>> CallableStatement
>>>>>>>>>>>> object. Within the stored procedure I have cursors that are
>>>>>>>>>>>> being
>>>>>>>>>>>> opened and I am using the WITH HOLD statement because the
>>>>>>>>>>>> commit
>>>>>>>>>>>> is
>>>>>>>>>>>> within a loop the cursor is running.
>>>>>>>>>>> Without more information, my only request is:
>>>>>>>>>>>
>>>>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>>>>
>>>>>>>>>>> Regards,
>>>>>>>>>>>
>>>>
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com


Breck Carter [TeamSybase] Posted on 2009-04-10 14:53:32.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: Cursor not open error on executeQuery
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <0umut45t2vd4h7pkffiveom6mq49dl8u11@4ax.com>
References: <49d376df$1@forums-1-dub> <49d37d15$1@forums-1-dub> <49d38273$1@forums-1-dub> <49d389f0$1@forums-1-dub> <49d4cbcb$1@forums-1-dub> <49d6067f@forums-1-dub> <49da40dc$1@forums-1-dub> <49da4b71$1@forums-1-dub> <49da4fc6$1@forums-1-dub> <49da58b0$1@forums-1-dub> <49da5ce9$1@forums-1-dub> <49da5ec7@forums-1-dub> <49db3f93$1@forums-1-dub> <7f2pt49q02g94rb43o7sbcf4dipu11hnlu@4ax.com> <49dcb49f$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 10 Apr 2009 07:53:32 -0700
X-Trace: forums-1-dub 1239375212 10.22.241.152 (10 Apr 2009 07:53:32 -0700)
X-Original-Trace: 10 Apr 2009 07:53:32 -0700, vip152.sybase.com
Lines: 299
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7507
Article PK: 5920

Yes, it is possible to be bitten by chained=off, as described in my
earlier response. To be completely safe, put some temporary diagnostic
code in your application, right after it makes a successful
connection, to display the CHAINED option. E.g., do some kind of
execute immediate / execte query on this...

MESSAGE STRING ( 'Connection ', @@spid, ': CHAINED = ',
CONNECTION_PROPERTY ( 'CHAINED' ) ) TO CONSOLE;

then look at the engine console window or console log (dbeng -o
option) for this:

Connection 1: CHAINED = On

If it says Off then you've been bitten.

Breck

On 8 Apr 2009 07:28:47 -0700, "Bill Williams"

<billwilliams@qcsoftware.com> wrote:

>Thanks for the info.
>
>We've always had in our code to turn auto commit off as soon as we create a
>connection regardless of the type of JDBC software we are using. Like you
>said, we like to have control when the commits occur. It seems by doing
>that, we've never had an issue with being able to issue a rollback or commit
>to control our transaction.
>
>Based on what you are saying, there is still something on the server side
>that could bite us with an auto commit when we use jConnect?
>
>"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
>message news:7f2pt49q02g94rb43o7sbcf4dipu11hnlu@4ax.com...
>> There are two kinds of "auto commit": server-side auto commit (chained
>> mode off) means that each SQL statement executed by the server is
>> committed individually, internally, and client-side auto commit
>> (called "autocommit" by the client-side interface software) means that
>> each request sent to the server is followed by an actual, explicit (to
>> the server) COMMIT request. So, if you turn client-side auto commit
>> off, but server-side auto commit is on (i.e., chained mode is off),
>> then in effect you still have auto commit on... the end result remains
>> the same.
>>
>> In general, for multi-connection databases (multi-user, or complex
>> single-user apps), both kinds of auto commit should be turned off and
>> your application should take control of transaction design by issuing
>> explicit COMMIT and ROLLBACK statements when appropriate. Turning auto
>> commit off can be problematic with interfaces like jConnect because by
>> default SQL Anywhere turns chained mode off (server-side auto commit
>> on) in the sp_tsql_environment stored procedure. Here's the whole
>> story:
>>
>> By default the login_procedure option point to the
>> sp_login_environment procedure, and when the connection uses the TDS
>> protocol (e.g., jConnect) that procedure calls sp_tsql_environment,
>> and that procedure does several curious things, including...
>>
>> set temporary option chained = 'OFF';
>>
>> You can edit either or both procedures in Sybase Central, or change
>> login_procedure to point to another procedure, or set it to '',
>> whatever you need to do.
>>
>> Read the Help about login_procedure, and ignore the exhortation "You
>> should not edit either sp_login_environment or sp_tsql_environment."
>> The reason I say that is there is nothing magic about TDS connections
>> per se; if you have TDS connections that want chained mode on and
>> others (old-style Transact SQL applications that futz around
>> with/without BEGIN TRANSACTION statements) that need chained mode off,
>> then you MUST do something special. My advice is to drop both sp's,
>> code your own... get rid of all vestiges of twentieth century junk...
>> take control of your connections.
>>
>> Another subtle point: client-side auto commit affects stored procedure
>> differently than server-side. With server-side auto commit each
>> statement inside the procedure is followed by an internal commit. With
>> client-side auto-commit, the interface software sends a COMMIT after
>> the CALL returns, so the statements inside the procedure are not
>> auto-committed until after the procedure ends.
>>
>> Bottom line: In English, "auto commit" means "never use".
>>
>> Breck
>>
>> On 7 Apr 2009 04:57:07 -0700, "Bill Williams"
>> <billwilliams@qcsoftware.com> wrote:
>>
>>>I have never heard of "chained mode". After looking through the docs, it
>>>appears it simply means autocommit is turned on when chained mode is off.
>>>
>>>We always turn auto commit off no matter how we are connecting to the
>>>database; therefore, wouldn't that mean chained mode was turned on? Not
>>>that it really matters now that I have it working, I was just curious.
>>>
>>>Thanks
>>>
>>>"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>news:49da5ec7@forums-1-dub...
>>>> There are several things that are different when jConnect is used. For
>>>> one
>>>> thing, the chained option is automatically turned off and for another,
>>>> jConnect would require that the entire procedure be run to completion
>>>> and
>>>> have all the results and update counts sent in one go. So, there could
>>>> be
>>>> any number of reasons why you would not see the problem with jConnect.
>>>>
>>>> DBISQL is a bit of a mystery though since it uses the iAnywhere JDBC
>>>> driver. However, DBISQL does set a few options and do a few clever
>>>> things
>>>> up front which may explain why the problem was not showing up.
>>>> Nevertheless, I'm glad the problem seems to be solved and you can
>>>> continue
>>>> to use the iAnywhere JDBC driver instead of jConnect.
>>>>
>>>> Karim
>>>> Bill Williams wrote:
>>>>> UGH! I'm an idiot. The cIdentifyBox cursor at the top of
>>>>> InductCartonEx
>>>>> needed the "with hold" statement. As soon as I put that on it, it
>>>>> worked
>>>>> (at least in dbisqlc). I still have to test it from my app but I'll
>>>>> bet
>>>>> it will work now.
>>>>>
>>>>> Is there a reason anyone can think of why I did not get the error when
>>>>> I
>>>>> was in dbisql or using jConnect for my database connection from my app?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
>>>>> news:49da58b0$1@forums-1-dub...
>>>>>> Okay, that's good news (sort of). It means we can rule JDBC out. So
>>>>>> now,
>>>>>> we need to figure out what is going wrong with the stored procs. Given
>>>>>> that you can reproduce the problem with dbisqlc, I assume it would be
>>>>>> best if you ran the test using the stored procedure debugger and see
>>>>>> exactly where the cursor not open error is being thrown. In the
>>>>>> meantime, I hope somebody else with a bit more knowledge regarding
>>>>>> stored procs and sql can have a look at your stored proc definitions
>>>>>> and
>>>>>> see if they can spot where or why the error gets thrown with the
>>>>>> commit
>>>>>> but not without the commit.
>>>>>>
>>>>>> Karim
>>>>>> Bill Williams wrote:
>>>>>>> I just tried dbisqlc and got the "cursor not open" error when I run
>>>>>>> the
>>>>>>> InductCartonEx.
>>>>>>>
>>>>>>> I tried the with hold statement on the cBalancePicks cursor (which I
>>>>>>> thought I had tried before) and still get the error in dbisqlc.
>>>>>>>
>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>> message
>>>>>>> news:49da4b71$1@forums-1-dub...
>>>>>>>> Bill,
>>>>>>>>
>>>>>>>> I am not seeing what might be going wrong. Can someone else have a
>>>>>>>> look at the procedure definitions in the file that Bill posted and
>>>>>>>> see
>>>>>>>> if there is something obvious that would be causing the cursor not
>>>>>>>> open error?
>>>>>>>>
>>>>>>>> In the meantime, Bill, what happens if you try executing the
>>>>>>>> InductCartonEx procedure in dbisqlc? Note, I want you to
>>>>>>>> specifically
>>>>>>>> use dbisqlc instead of dbisql since dbisqlc does not use the
>>>>>>>> iAnywhere
>>>>>>>> JDBC driver. Also, is it possible that the cBalancePicks cursor
>>>>>>>> needs
>>>>>>>> to be opened using with hold?
>>>>>>>>
>>>>>>>> Karim
>>>>>>>> Bill Williams wrote:
>>>>>>>>> Did any of this help or is more info needed?
>>>>>>>>>
>>>>>>>>> Thanks
>>>>>>>>>
>>>>>>>>> "Bill Williams" <billwilliams@qcsoftware.com> wrote in message
>>>>>>>>> news:49d6067f@forums-1-dub...
>>>>>>>>>> Attached are the two main stored procedures and the snipet of code
>>>>>>>>>> where the problem occurs. The commit is within the balance.stp
>>>>>>>>>> file. If I comment out that commit, the problem goes away.
>>>>>>>>>>
>>>>>>>>>> I have tried this with the iAnywhere driver in both JDBC 2 and 3
>>>>>>>>>> modes.
>>>>>>>>>>
>>>>>>>>>> The exception occurs on the line "rs = call.executeQuery()".
>>>>>>>>>>
>>>>>>>>>> "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in
>>>>>>>>>> message news:49d4cbcb$1@forums-1-dub...
>>>>>>>>>>> Bill,
>>>>>>>>>>>
>>>>>>>>>>> Jeff is correct, we need more information to diagnose the
>>>>>>>>>>> problem.
>>>>>>>>>>> Here
>>>>>>>>>>> is what I tried...
>>>>>>>>>>>
>>>>>>>>>>> 1) I created the procedure foo as follows:
>>>>>>>>>>>
>>>>>>>>>>> create procedure foo(p char(128))
>>>>>>>>>>> begin
>>>>>>>>>>> select * from systable;
>>>>>>>>>>> commit;
>>>>>>>>>>> select * from syscolumn;
>>>>>>>>>>> end
>>>>>>>>>>>
>>>>>>>>>>> 2) I then built a JDBC app using the iAnywhere JDBC driver with
>>>>>>>>>>> the
>>>>>>>>>>> following code snippet:
>>>>>>>>>>>
>>>>>>>>>>> try {
>>>>>>>>>>> CallableStatement stmt = con.prepareCall( "{call
>>>>>>>>>>> foo('12345')}" );
>>>>>>>>>>> boolean has_result = stmt.execute();
>>>>>>>>>>> int rows_affected = 0;
>>>>>>>>>>> do {
>>>>>>>>>>> if( has_result ) {
>>>>>>>>>>> ResultSet rs = stmt.getResultSet();
>>>>>>>>>>> int count = 0;
>>>>>>>>>>> while( rs.next() ) {
>>>>>>>>>>> ++count;
>>>>>>>>>>> }
>>>>>>>>>>> System.out.println( "Num rows in result set is " + count );
>>>>>>>>>>> } else {
>>>>>>>>>>> rows_affected = stmt.getUpdateCount();
>>>>>>>>>>> if( rows_affected >= 0 ) {
>>>>>>>>>>> System.out.println( "Rows affected is " + rows_affected );
>>>>>>>>>>> }
>>>>>>>>>>> }
>>>>>>>>>>>
>>>>>>>>>>> has_result = stmt.getMoreResults();
>>>>>>>>>>> } while( has_result || rows_affected != -1 );
>>>>>>>>>>> stmt.close();
>>>>>>>>>>> con.close();
>>>>>>>>>>> System.out.println( "Disconnected" );
>>>>>>>>>>> } catch (SQLException sqe) {
>>>>>>>>>>> ...
>>>>>>>>>>> }
>>>>>>>>>>>
>>>>>>>>>>> 3) When I run my JDBC app I get the output:
>>>>>>>>>>>
>>>>>>>>>>> Num rows in result set is 312
>>>>>>>>>>> Num rows in result set is 2109
>>>>>>>>>>> Disconnected
>>>>>>>>>>>
>>>>>>>>>>> So, try and see if your JDBC code is missing something based on
>>>>>>>>>>> the
>>>>>>>>>>> above snippet and then post both the source for your stored
>>>>>>>>>>> procedure
>>>>>>>>>>> and your JDBC app.
>>>>>>>>>>>
>>>>>>>>>>> Karim
>>>>>>>>>>> Jeff Albion [Sybase iAnywhere] wrote:
>>>>>>>>>>>> Bill,
>>>>>>>>>>>>
>>>>>>>>>>>> Bill Williams wrote:
>>>>>>>>>>>>> I am using a JDBC CallableStatement. The name of the procedure
>>>>>>>>>>>>> is
>>>>>>>>>>>>> InductTote. The statement is formed with the following string:
>>>>>>>>>>>>> {call
>>>>>>>>>>>>> InductTote('12345')}. The InductTote stored procedure does
>>>>>>>>>>>>> return a
>>>>>>>>>>>>> result set. Like I said, it does have an embedded commit
>>>>>>>>>>>>> within
>>>>>>>>>>>>> the
>>>>>>>>>>>>> procedure, so like you said, it sounds as if I need a WITH HOLD
>>>>>>>>>>>>> statement but I don't know how to do that with a
>>>>>>>>>>>>> CallableStatement
>>>>>>>>>>>>> object. Within the stored procedure I have cursors that are
>>>>>>>>>>>>> being
>>>>>>>>>>>>> opened and I am using the WITH HOLD statement because the
>>>>>>>>>>>>> commit
>>>>>>>>>>>>> is
>>>>>>>>>>>>> within a loop the cursor is running.
>>>>>>>>>>>> Without more information, my only request is:
>>>>>>>>>>>>
>>>>>>>>>>>> Can we see the code for the SP / Java call?
>>>>>>>>>>>>
>>>>>>>>>>>> Regards,
>>>>>>>>>>>>
>>>>>
>>
>> --
>> Breck Carter http://sqlanywhere.blogspot.com/
>>
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> breck.carter@risingroad.com
>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com