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.

SQLJ Problem with resultset

6 posts in General Discussion Last posting was on 2012-04-23 18:32:08.0Z
Ihor Haydaychuk Posted on 2012-04-16 17:53:48.0Z
Sender: b79.4f8c5b55.1804289383@sybase.com
From: Ihor Haydaychuk
Newsgroups: sybase.public.ase.general
Subject: SQLJ Problem with resultset
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8c5cac.ba5.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 16 Apr 2012 10:53:48 -0700
X-Trace: forums-1-dub 1334598828 172.20.134.41 (16 Apr 2012 10:53:48 -0700)
X-Original-Trace: 16 Apr 2012 10:53:48 -0700, 172.20.134.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31029
Article PK: 73919

Hi,

I have the SQLJ stored procedure that calls another stored
procedure. If calling procedure has just input and output
parameters - everything works fine. But if calling stored
procedure returns result set, SQLJ throws exception:

Could not execute statement
JZ006:Caught IOException:
com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:Connection
is already closed
Sybase errorcode=0,SQLState="JZ006"


In my understanding this is the problem of ASEDriver used
for server side connection. If I switch to jConnect driver
and execute the same code from the client side - problem is
gone.

Any ideas?


J Posted on 2012-04-17 16:08:05.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: SQLJ Problem with resultset
Reply-To: J@bogusemailAddress.com
Message-ID: <4f8d9403.1299414171@forums.sybase.com>
References: <4f8c5cac.ba5.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 17 Apr 2012 09:08:05 -0700
X-Trace: forums-1-dub 1334678885 10.22.241.152 (17 Apr 2012 09:08:05 -0700)
X-Original-Trace: 17 Apr 2012 09:08:05 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31032
Article PK: 73922

On 16 Apr 2012 10:53:48 -0700, Ihor Haydaychuk wrote:

I would need more details to be able to answer more completely. When
a stored procedure returns resultsets those results flow back to the
client and not back to the invoking sproc.

client ---> sproc1 ----> sproc2 sproc2 returns a resultset
the result set will flow back to the client, not to sproc1

return parameters from sproc2 are returned to sproc1.

Could you post simplified SQLJ and sproc code?

Jay

>Hi,
>
>I have the SQLJ stored procedure that calls another stored
>procedure. If calling procedure has just input and output
>parameters - everything works fine. But if calling stored
>procedure returns result set, SQLJ throws exception:
>
>Could not execute statement
>JZ006:Caught IOException:
>com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:Connection
>is already closed
>Sybase errorcode=0,SQLState="JZ006"
>
>
>In my understanding this is the problem of ASEDriver used
>for server side connection. If I switch to jConnect driver
>and execute the same code from the client side - problem is
>gone.
>
>Any ideas?


Ihor Haydaychuk Posted on 2012-04-17 17:45:24.0Z
Sender: 3d7a.4f8da947.1804289383@sybase.com
From: Ihor Haydaychuk
Newsgroups: sybase.public.ase.general
Subject: Re: SQLJ Problem with resultset
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8dac34.3deb.1681692777@sybase.com>
References: <4f8d9403.1299414171@forums.sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Apr 2012 10:45:24 -0700
X-Trace: forums-1-dub 1334684724 172.20.134.41 (17 Apr 2012 10:45:24 -0700)
X-Original-Trace: 17 Apr 2012 10:45:24 -0700, 172.20.134.41
Lines: 133
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31033
Article PK: 73923

Thank you, Jay.
Code below includes the following sections:

- original SP;
- SQLJ definition of SP;
- Java code behind SQLJ: I execute original SP and do some
processing of it result set;
- how I execute SQLJ SP.

In your diagram: is there any way to capture result set in
sproc1 for processing, instead of passing to the client?

Regards,

Ihor


-----------------------------------------------------------------

create procedure dbo.SelectTestData
@a int
as
begin
select a,b,c from table1 where a=@a
end

-----------------------------------------------------------------

create procedure dbo.TestJava(a int) language java
parameter style java external name
'com.test.sp.SybaseSpWrapper.executeSelectTestData(int)'

-----------------------------------------------------------------

package com.test.sp;


import java.sql.*;

public class SybaseSpWrapper {

public static int executeSelectTestData(int a) throws
SQLException {
PreparedStatement cs = null;
ResultSet rs = null;
try {
Class.forName("sybase.asejdbc.ASEDriver");
Connection con =
DriverManager.getConnection("jdbc:default:connection");
String sql = "{call SelectTestData(?)}";
cs = con.prepareCall(sql);
cs.setInt(1, a);
rs = cs.executeQuery(); //!!! this is where exception
happens
// do some processing of the result set
}
catch (SQLException ex) {
ex.printStackTrace();
throw ex;
}
finally {
closeAll(rs, cs);
}
return 0;
}

private static void closeAll(ResultSet rs,
PreparedStatement cs) {
try {
if(rs != null) rs.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
try {
if(cs != null) cs.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}

-----------------------------------------------------------------

create procedure dbo.MyTestJava
as
begin
execute dbo.TestJava 1
end

> On 16 Apr 2012 10:53:48 -0700, Ihor Haydaychuk wrote:
>
> I would need more details to be able to answer more
> completely. When a stored procedure returns resultsets
> those results flow back to the client and not back to the
> invoking sproc.
>
> client ---> sproc1 ----> sproc2 sproc2 returns a
> resultset
> the result set will flow back to the client, not to
> sproc1
>
> return parameters from sproc2 are returned to sproc1.
>
> Could you post simplified SQLJ and sproc code?
>
> Jay
>
> >Hi,
> >
> >I have the SQLJ stored procedure that calls another
> stored >procedure. If calling procedure has just input and
> output >parameters - everything works fine. But if calling
> stored >procedure returns result set, SQLJ throws
> exception: >
> >Could not execute statement
> >JZ006:Caught IOException:
> >com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:C
> onnection >is already closed
> >Sybase errorcode=0,SQLState="JZ006"
> >
> >
> >In my understanding this is the problem of ASEDriver used
> >for server side connection. If I switch to jConnect
> driver >and execute the same code from the client side -
> problem is >gone.
> >
> >Any ideas?
>


J Posted on 2012-04-18 17:21:54.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: SQLJ Problem with resultset
Reply-To: J@bogusemailAddress.com
Message-ID: <4f8ef6f7.1390286296@forums.sybase.com>
References: <4f8d9403.1299414171@forums.sybase.com> <4f8dac34.3deb.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Apr 2012 10:21:54 -0700
X-Trace: forums-1-dub 1334769714 10.22.241.152 (18 Apr 2012 10:21:54 -0700)
X-Original-Trace: 18 Apr 2012 10:21:54 -0700, vip152.sybase.com
Lines: 230
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31042
Article PK: 73932

On 17 Apr 2012 10:45:24 -0700, Ihor Haydaychuk wrote:

Ihor,

Your code sample works ok for me. I did not add processing code for
the results but I get past the error that you are seeing.

I modified the SQLJ as follows:

import java.sql.*;

public class SybaseSpWrapper {

public static int executeSelectTestData(int a) throws
Exception {
PreparedStatement cs = null;
ResultSet rs = null;
try {
Class.forName("sybase.asejdbc.ASEDriver");
Connection con =
DriverManager.getConnection("jdbc:default:connection");
String sql = "{call SelectTestData(?)}";
cs = con.prepareCall(sql);
cs.setInt(1, a);
rs = cs.executeQuery(); //!!! this is where
exception happens
// do some processing of the result set
if (true)
throw new SQLException("jay's
exception");
}
catch (SQLException ex) {
ex.printStackTrace();
throw ex;
}
finally {
closeAll(rs, cs);
}
return 0;
}

private static void closeAll(ResultSet rs,
PreparedStatement cs) {
try {
if(rs != null) rs.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
try {
if(cs != null) cs.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}

I test as follows:

1> exec TestJava 1
2> go
java.sql.SQLException: jay's excecption
at SybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java:18)
Msg 10707, Level 16, State 1:
Server 'JANDERSXP_155', Procedure 'TestJava', Line 3:
Unhandled Java Exception:
java.sql.SQLException: jay's exception
at
.ybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java:18)
(return status = -6)
1> select @@version
2> go





-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------
Adaptive Server Enterprise/15.5/EBF 18157 SMP ESD#2/P/NT
(IX86)/Windows 2003/as

ear155/2514/32-bit/OPT/Wed Aug 25 05:31:40 2010


What version of ASE and java are you using? You might need a Tech
Support case to narrow this down. Using the ASE jdbc driver as you do
returns the results to the sproc.

Jay

>Thank you, Jay.
>Code below includes the following sections:
>
>- original SP;
>- SQLJ definition of SP;
>- Java code behind SQLJ: I execute original SP and do some
>processing of it result set;
>- how I execute SQLJ SP.
>
>In your diagram: is there any way to capture result set in
>sproc1 for processing, instead of passing to the client?
>
>Regards,
>
>Ihor
>
>
>-----------------------------------------------------------------
>
>create procedure dbo.SelectTestData
>@a int
>as
>begin
> select a,b,c from table1 where a=@a
>end
>
>-----------------------------------------------------------------
>
>create procedure dbo.TestJava(a int) language java
>parameter style java external name
>'com.test.sp.SybaseSpWrapper.executeSelectTestData(int)'
>
>-----------------------------------------------------------------
>
>package com.test.sp;
>
>
>import java.sql.*;
>
>public class SybaseSpWrapper {
>
> public static int executeSelectTestData(int a) throws
>SQLException {
> PreparedStatement cs = null;
> ResultSet rs = null;
> try {
> Class.forName("sybase.asejdbc.ASEDriver");
> Connection con =
>DriverManager.getConnection("jdbc:default:connection");
> String sql = "{call SelectTestData(?)}";
> cs = con.prepareCall(sql);
> cs.setInt(1, a);
> rs = cs.executeQuery(); //!!! this is where exception
>happens
> // do some processing of the result set
> }
> catch (SQLException ex) {
> ex.printStackTrace();
> throw ex;
> }
> finally {
> closeAll(rs, cs);
> }
> return 0;
> }
>
> private static void closeAll(ResultSet rs,
>PreparedStatement cs) {
> try {
> if(rs != null) rs.close();
> }
> catch(Exception ex) {
> ex.printStackTrace();
> }
> try {
> if(cs != null) cs.close();
> }
> catch(Exception ex) {
> ex.printStackTrace();
> }
> }
>}
>
>-----------------------------------------------------------------
>
>create procedure dbo.MyTestJava
>as
>begin
> execute dbo.TestJava 1
>end
>
>
>
>
>> On 16 Apr 2012 10:53:48 -0700, Ihor Haydaychuk wrote:
>>
>> I would need more details to be able to answer more
>> completely. When a stored procedure returns resultsets
>> those results flow back to the client and not back to the
>> invoking sproc.
>>
>> client ---> sproc1 ----> sproc2 sproc2 returns a
>> resultset
>> the result set will flow back to the client, not to
>> sproc1
>>
>> return parameters from sproc2 are returned to sproc1.
>>
>> Could you post simplified SQLJ and sproc code?
>>
>> Jay
>>
>> >Hi,
>> >
>> >I have the SQLJ stored procedure that calls another
>> stored >procedure. If calling procedure has just input and
>> output >parameters - everything works fine. But if calling
>> stored >procedure returns result set, SQLJ throws
>> exception: >
>> >Could not execute statement
>> >JZ006:Caught IOException:
>> >com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:C
>> onnection >is already closed
>> >Sybase errorcode=0,SQLState="JZ006"
>> >
>> >
>> >In my understanding this is the problem of ASEDriver used
>> >for server side connection. If I switch to jConnect
>> driver >and execute the same code from the client side -
>> problem is >gone.
>> >
>> >Any ideas?
>>


Ihor Haydaychuk Posted on 2012-04-18 20:20:15.0Z
Sender: 70c7.4f8f215c.1804289383@sybase.com
From: Ihor Haydaychuk
Newsgroups: sybase.public.ase.general
Subject: Re: SQLJ Problem with resultset
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8f21ff.70d8.1681692777@sybase.com>
References: <4f8ef6f7.1390286296@forums.sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 13:20:15 -0700
X-Trace: forums-1-dub 1334780415 172.20.134.41 (18 Apr 2012 13:20:15 -0700)
X-Original-Trace: 18 Apr 2012 13:20:15 -0700, 172.20.134.41
Lines: 254
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31044
Article PK: 73934

Thank you, Jay.
This is great news for me that the exception I experienced
is not the limitation of ASE, but rather the problem of the
old version of ASE. We have

Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT
(IX86)/Windows 2000/ase1502/2486/32-bit/OPT/Thu May 24
04:10:36 2007

I will try to run the code on other database instances.
Thanks again.

Ihor

> On 17 Apr 2012 10:45:24 -0700, Ihor Haydaychuk wrote:
>
> Ihor,
>
> Your code sample works ok for me. I did not add
> processing code for the results but I get past the error
> that you are seeing.
>
> I modified the SQLJ as follows:
>
> import java.sql.*;
>
> public class SybaseSpWrapper {
>
> public static int executeSelectTestData(int a) throws
> Exception {
> PreparedStatement cs = null;
> ResultSet rs = null;
> try {
> Class.forName("sybase.asejdbc.ASEDriver");
> Connection con =
> DriverManager.getConnection("jdbc:default:connection");
> String sql = "{call SelectTestData(?)}";
> cs = con.prepareCall(sql);
> cs.setInt(1, a);
> rs = cs.executeQuery(); //!!! this is where
> exception happens
> // do some processing of the result set
> if (true)
> throw new SQLException("jay's
> exception");
> }
> catch (SQLException ex) {
> ex.printStackTrace();
> throw ex;
> }
> finally {
> closeAll(rs, cs);
> }
> return 0;
> }
>
> private static void closeAll(ResultSet rs,
> PreparedStatement cs) {
> try {
> if(rs != null) rs.close();
> }
> catch(Exception ex) {
> ex.printStackTrace();
> }
> try {
> if(cs != null) cs.close();
> }
> catch(Exception ex) {
> ex.printStackTrace();
> }
> }
> }
>
> I test as follows:
>
> 1> exec TestJava 1
> 2> go
> java.sql.SQLException: jay's excecption
> at
> SybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java
> :18) Msg 10707, Level 16, State 1:
> Server 'JANDERSXP_155', Procedure 'TestJava', Line 3:
> Unhandled Java Exception:
> java.sql.SQLException: jay's exception
> at
> .ybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java
> :18) (return status = -6)
> 1> select @@version
> 2> go
>
>
>
>
>
> ----------------------------------------------------------
> ---------------------
> ----------------------------------------------------------
> ----------------------
> ----------------------------------------------------------
> ---------------------- ----------------
> Adaptive Server Enterprise/15.5/EBF 18157 SMP ESD#2/P/NT
> (IX86)/Windows 2003/as
>
> ear155/2514/32-bit/OPT/Wed Aug 25 05:31:40 2010
>
>
> What version of ASE and java are you using? You might
> need a Tech Support case to narrow this down. Using the
> ASE jdbc driver as you do returns the results to the
> sproc.
>
> Jay
>
>
>
> >Thank you, Jay.
> >Code below includes the following sections:
> >
> >- original SP;
> >- SQLJ definition of SP;
> >- Java code behind SQLJ: I execute original SP and do
> some >processing of it result set;
> >- how I execute SQLJ SP.
> >
> >In your diagram: is there any way to capture result set
> in >sproc1 for processing, instead of passing to the
> client? >
> >Regards,
> >
> >Ihor
> >
> >
> >---------------------------------------------------------
> -------- >
> >create procedure dbo.SelectTestData
> >@a int
> >as
> >begin
> > select a,b,c from table1 where a=@a
> >end
> >
> >---------------------------------------------------------
> -------- >
> >create procedure dbo.TestJava(a int) language java
> >parameter style java external name
> >'com.test.sp.SybaseSpWrapper.executeSelectTestData(int)'
> >
> >---------------------------------------------------------
> -------- >
> >package com.test.sp;
> >
> >
> >import java.sql.*;
> >
> >public class SybaseSpWrapper {
> >
> > public static int executeSelectTestData(int a) throws
> >SQLException {
> > PreparedStatement cs = null;
> > ResultSet rs = null;
> > try {
> > Class.forName("sybase.asejdbc.ASEDriver");
> > Connection con =
> >DriverManager.getConnection("jdbc:default:connection");
> > String sql = "{call SelectTestData(?)}";
> > cs = con.prepareCall(sql);
> > cs.setInt(1, a);
> > rs = cs.executeQuery(); //!!! this is where
> exception >happens
> > // do some processing of the result set
> > }
> > catch (SQLException ex) {
> > ex.printStackTrace();
> > throw ex;
> > }
> > finally {
> > closeAll(rs, cs);
> > }
> > return 0;
> > }
> >
> > private static void closeAll(ResultSet rs,
> >PreparedStatement cs) {
> > try {
> > if(rs != null) rs.close();
> > }
> > catch(Exception ex) {
> > ex.printStackTrace();
> > }
> > try {
> > if(cs != null) cs.close();
> > }
> > catch(Exception ex) {
> > ex.printStackTrace();
> > }
> > }
> >}
> >
> >---------------------------------------------------------
> -------- >
> >create procedure dbo.MyTestJava
> >as
> >begin
> > execute dbo.TestJava 1
> >end
> >
> >
> >
> >
> >> On 16 Apr 2012 10:53:48 -0700, Ihor Haydaychuk wrote:
> >>
> >> I would need more details to be able to answer more
> >> completely. When a stored procedure returns resultsets
> >> those results flow back to the client and not back to
> the >> invoking sproc.
> >>
> >> client ---> sproc1 ----> sproc2 sproc2 returns a
> >> resultset
> >> the result set will flow back to the client, not to
> >> sproc1
> >>
> >> return parameters from sproc2 are returned to sproc1.
> >>
> >> Could you post simplified SQLJ and sproc code?
> >>
> >> Jay
> >>
> >> >Hi,
> >> >
> >> >I have the SQLJ stored procedure that calls another
> >> stored >procedure. If calling procedure has just input
> and >> output >parameters - everything works fine. But if
> calling >> stored >procedure returns result set, SQLJ
> throws >> exception: >
> >> >Could not execute statement
> >> >JZ006:Caught IOException:
> >>
> >com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:C
> >> onnection >is already closed >> >Sybase errorcode=0
> ,SQLState="JZ006" >> >
> >> >
> >> >In my understanding this is the problem of ASEDriver
> used >> >for server side connection. If I switch to
> jConnect >> driver >and execute the same code from the
> client side - >> problem is >gone.
> >> >
> >> >Any ideas?
> >>
>


J Posted on 2012-04-23 18:32:08.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: SQLJ Problem with resultset
Reply-To: J@bogusemailAddress.com
Message-ID: <4f959fd7.1826749250@forums.sybase.com>
References: <4f8ef6f7.1390286296@forums.sybase.com> <4f8f21ff.70d8.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 23 Apr 2012 11:32:08 -0700
X-Trace: forums-1-dub 1335205928 10.22.241.152 (23 Apr 2012 11:32:08 -0700)
X-Original-Trace: 23 Apr 2012 11:32:08 -0700, vip152.sybase.com
Lines: 263
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31053
Article PK: 73944

On 18 Apr 2012 13:20:15 -0700, Ihor Haydaychuk wrote:

The java code usage was significantly upgraded in ASE release 15.0.3
and up. I would suggest that you need to be using at least this
version.

Jay

>
>Thank you, Jay.
>This is great news for me that the exception I experienced
>is not the limitation of ASE, but rather the problem of the
>old version of ASE. We have
>
>Adaptive Server Enterprise/15.0.2/EBF 14332/P/NT
>(IX86)/Windows 2000/ase1502/2486/32-bit/OPT/Thu May 24
>04:10:36 2007
>
>I will try to run the code on other database instances.
>Thanks again.
>
>Ihor
>
>
>
>
>
>> On 17 Apr 2012 10:45:24 -0700, Ihor Haydaychuk wrote:
>>
>> Ihor,
>>
>> Your code sample works ok for me. I did not add
>> processing code for the results but I get past the error
>> that you are seeing.
>>
>> I modified the SQLJ as follows:
>>
>> import java.sql.*;
>>
>> public class SybaseSpWrapper {
>>
>> public static int executeSelectTestData(int a) throws
>> Exception {
>> PreparedStatement cs = null;
>> ResultSet rs = null;
>> try {
>> Class.forName("sybase.asejdbc.ASEDriver");
>> Connection con =
>> DriverManager.getConnection("jdbc:default:connection");
>> String sql = "{call SelectTestData(?)}";
>> cs = con.prepareCall(sql);
>> cs.setInt(1, a);
>> rs = cs.executeQuery(); //!!! this is where
>> exception happens
>> // do some processing of the result set
>> if (true)
>> throw new SQLException("jay's
>> exception");
>> }
>> catch (SQLException ex) {
>> ex.printStackTrace();
>> throw ex;
>> }
>> finally {
>> closeAll(rs, cs);
>> }
>> return 0;
>> }
>>
>> private static void closeAll(ResultSet rs,
>> PreparedStatement cs) {
>> try {
>> if(rs != null) rs.close();
>> }
>> catch(Exception ex) {
>> ex.printStackTrace();
>> }
>> try {
>> if(cs != null) cs.close();
>> }
>> catch(Exception ex) {
>> ex.printStackTrace();
>> }
>> }
>> }
>>
>> I test as follows:
>>
>> 1> exec TestJava 1
>> 2> go
>> java.sql.SQLException: jay's excecption
>> at
>> SybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java
>> :18) Msg 10707, Level 16, State 1:
>> Server 'JANDERSXP_155', Procedure 'TestJava', Line 3:
>> Unhandled Java Exception:
>> java.sql.SQLException: jay's exception
>> at
>> .ybaseSpWrapper.executeSelectTestData(SybaseSpWrapper.java
>> :18) (return status = -6)
>> 1> select @@version
>> 2> go
>>
>>
>>
>>
>>
>> ----------------------------------------------------------
>> ---------------------
>> ----------------------------------------------------------
>> ----------------------
>> ----------------------------------------------------------
>> ---------------------- ----------------
>> Adaptive Server Enterprise/15.5/EBF 18157 SMP ESD#2/P/NT
>> (IX86)/Windows 2003/as
>>
>> ear155/2514/32-bit/OPT/Wed Aug 25 05:31:40 2010
>>
>>
>> What version of ASE and java are you using? You might
>> need a Tech Support case to narrow this down. Using the
>> ASE jdbc driver as you do returns the results to the
>> sproc.
>>
>> Jay
>>
>>
>>
>> >Thank you, Jay.
>> >Code below includes the following sections:
>> >
>> >- original SP;
>> >- SQLJ definition of SP;
>> >- Java code behind SQLJ: I execute original SP and do
>> some >processing of it result set;
>> >- how I execute SQLJ SP.
>> >
>> >In your diagram: is there any way to capture result set
>> in >sproc1 for processing, instead of passing to the
>> client? >
>> >Regards,
>> >
>> >Ihor
>> >
>> >
>> >---------------------------------------------------------
>> -------- >
>> >create procedure dbo.SelectTestData
>> >@a int
>> >as
>> >begin
>> > select a,b,c from table1 where a=@a
>> >end
>> >
>> >---------------------------------------------------------
>> -------- >
>> >create procedure dbo.TestJava(a int) language java
>> >parameter style java external name
>> >'com.test.sp.SybaseSpWrapper.executeSelectTestData(int)'
>> >
>> >---------------------------------------------------------
>> -------- >
>> >package com.test.sp;
>> >
>> >
>> >import java.sql.*;
>> >
>> >public class SybaseSpWrapper {
>> >
>> > public static int executeSelectTestData(int a) throws
>> >SQLException {
>> > PreparedStatement cs = null;
>> > ResultSet rs = null;
>> > try {
>> > Class.forName("sybase.asejdbc.ASEDriver");
>> > Connection con =
>> >DriverManager.getConnection("jdbc:default:connection");
>> > String sql = "{call SelectTestData(?)}";
>> > cs = con.prepareCall(sql);
>> > cs.setInt(1, a);
>> > rs = cs.executeQuery(); //!!! this is where
>> exception >happens
>> > // do some processing of the result set
>> > }
>> > catch (SQLException ex) {
>> > ex.printStackTrace();
>> > throw ex;
>> > }
>> > finally {
>> > closeAll(rs, cs);
>> > }
>> > return 0;
>> > }
>> >
>> > private static void closeAll(ResultSet rs,
>> >PreparedStatement cs) {
>> > try {
>> > if(rs != null) rs.close();
>> > }
>> > catch(Exception ex) {
>> > ex.printStackTrace();
>> > }
>> > try {
>> > if(cs != null) cs.close();
>> > }
>> > catch(Exception ex) {
>> > ex.printStackTrace();
>> > }
>> > }
>> >}
>> >
>> >---------------------------------------------------------
>> -------- >
>> >create procedure dbo.MyTestJava
>> >as
>> >begin
>> > execute dbo.TestJava 1
>> >end
>> >
>> >
>> >
>> >
>> >> On 16 Apr 2012 10:53:48 -0700, Ihor Haydaychuk wrote:
>> >>
>> >> I would need more details to be able to answer more
>> >> completely. When a stored procedure returns resultsets
>> >> those results flow back to the client and not back to
>> the >> invoking sproc.
>> >>
>> >> client ---> sproc1 ----> sproc2 sproc2 returns a
>> >> resultset
>> >> the result set will flow back to the client, not to
>> >> sproc1
>> >>
>> >> return parameters from sproc2 are returned to sproc1.
>> >>
>> >> Could you post simplified SQLJ and sproc code?
>> >>
>> >> Jay
>> >>
>> >> >Hi,
>> >> >
>> >> >I have the SQLJ stored procedure that calls another
>> >> stored >procedure. If calling procedure has just input
>> and >> output >parameters - everything works fine. But if
>> calling >> stored >procedure returns result set, SQLJ
>> throws >> exception: >
>> >> >Could not execute statement
>> >> >JZ006:Caught IOException:
>> >>
>> >com.sysbase.jdbc3.jdbc.SybConnectionDeadException:JZ0C0:C
>> >> onnection >is already closed >> >Sybase errorcode=0
>> ,SQLState="JZ006" >> >
>> >> >
>> >> >In my understanding this is the problem of ASEDriver
>> used >> >for server side connection. If I switch to
>> jConnect >> driver >and execute the same code from the
>> client side - >> problem is >gone.
>> >> >
>> >> >Any ideas?
>> >>
>>