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.

Return @@rowcount from an SP causes Unexpected Result Type exception

7 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-06-29 02:09:28.0Z
Jon Bradbury Posted on 1997-05-28 14:11:46.0Z
From: "Jon Bradbury" <101527.3722@compuserve.com>
Subject: Return @@rowcount from an SP causes Unexpected Result Type exception
Message-ID: <01bc6b81$c83b1640$1fc1dec2@isdn-pc>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.jdbcconnect
Date: Wed, 28 May 1997 10:11:46 -0400
Lines: 14
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:757
Article PK: 252509

We have a come accross a problem performing simple updates or inserts via
stored procedures. If the query is executed directly (using INSERT, or
UPDATE) everything works fine. However, if a Stored Procedure is written to
perform exactly the same insert or update, and then return @@rowcount, the
query succeeds (!) but an SQLException is raised, with the text "Unexpected
result type".

This is the result regardless of whether the executeQuery(),
executeUpdate() or execute() methods are used.

Any comments / advice greatly appreciated.


Lance Andersen Posted on 1997-06-02 16:24:10.0Z
Message-ID: <3392F3AA.C3D@sybase.com>
Date: Mon, 02 Jun 1997 12:24:10 -0400
From: Lance Andersen <lancea@sybase.com>
X-Mailer: Mozilla 3.01Gold (X11; I; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Jon Bradbury <101527.3722@compuserve.com>
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 28
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:745
Article PK: 252496

Jon,

could you provide a working example of your problem.

thanks

lance

Jon Bradbury wrote:
>
> We have a come accross a problem performing simple updates or inserts via
> stored procedures. If the query is executed directly (using INSERT, or
> UPDATE) everything works fine. However, if a Stored Procedure is written to
> perform exactly the same insert or update, and then return @@rowcount, the
> query succeeds (!) but an SQLException is raised, with the text "Unexpected
> result type".
>
> This is the result regardless of whether the executeQuery(),
> executeUpdate() or execute() methods are used.
>
> Any comments / advice greatly appreciated.

--
===============================================================================
Lance J. Andersen Email: lancea@sybase.com
Sybase Technical Support Phone:(617) 564-6336
77 South Bedford Street Fax: (617) 564-6148
Burlington, MA 01803

The Dark Knight Returns!!! Let's Go Penguins!!!
===============================================================================


Jon Bradbury Posted on 1997-06-03 13:49:31.0Z
From: "Jon Bradbury" <101527.3722@compuserve.com>
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception (the solution!)
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc> <3392F3AA.C3D@sybase.com>
Message-ID: <01bc7035$f39f1d00$1fc1dec2@ISDNPC>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.jdbcconnect
Date: Tue, 03 Jun 1997 09:49:31 -0400
Lines: 65
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:732
Article PK: 252486

This query has now been resolved with some help from the Sybase Technical
Support team, so I'll answer my own question, for anyone else out there
that's interested!

The "official" way to execute stored procedures is using the
platform-independent (but somewhat cumbersome) Escape Sequence syntax (see
the documentation for the CallableStatement class). This allows you to
execute stored procedures independent of the syntax for SP calls used by
any one database.

We were using the easier (but cruder) method of constructing a String of
the actual SP call *specific to Sybase* such as "EXECUTE OrderUpdate
'Smith', 200", and then executing that String with the usual exectue()
method on the Statement class. Confusingly, this direct approach works fine
as long as the stored procedure is a SELECT, but falls flat on its face
(with an insoluble Unexpected Result Type exception) if the SP happens to
return a specific value (e.g. an INSERT or UPDATE that returns the number
of rows affected using 'return @@rowcount').

So, the moral of the story is to be "well behaved", and use the proper
Escape Sequence syntax. As well as being database-independent, it functions
correctly for all SP's, regardless of the return type.

Hope that helps anyone else who is similarly confused!

Jon Bradbury

Lance Andersen <lancea@sybase.com> wrote in article
<3392F3AA.C3D@sybase.com>...
> Jon,
>
> could you provide a working example of your problem.
>
> thanks
>
> lance
>
> Jon Bradbury wrote:
> >
> > We have a come accross a problem performing simple updates or inserts
via
> > stored procedures. If the query is executed directly (using INSERT, or
> > UPDATE) everything works fine. However, if a Stored Procedure is
written to
> > perform exactly the same insert or update, and then return @@rowcount,
the
> > query succeeds (!) but an SQLException is raised, with the text
"Unexpected
> > result type".
> >
> > This is the result regardless of whether the executeQuery(),
> > executeUpdate() or execute() methods are used.
> >
> > Any comments / advice greatly appreciated.
>
> --
>

============================================================================
===
> Lance J. Andersen Email: lancea@sybase.com
> Sybase Technical Support Phone:(617) 564-6336
> 77 South Bedford Street Fax: (617) 564-6148
> Burlington, MA 01803
>
> The Dark Knight Returns!!! Let's Go Penguins!!!
>
============================================================================
===
>


Andrew Schonberger Posted on 1997-06-29 02:09:28.0Z
From: Andrew.Schonberger@btal.com.au (Andrew Schonberger)
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception (the solution!)
Date: Sun, 29 Jun 1997 02:09:28 GMT
Message-ID: <33b5c1ce.13189713@forums.sybase.com>
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc> <3392F3AA.C3D@sybase.com> <01bc7035$f39f1d00$1fc1dec2@ISDNPC>
X-Newsreader: Forte Free Agent 1.1/32.230
Newsgroups: sybase.public.jdbcconnect
Lines: 88
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:622
Article PK: 252374

Pardon me for jumping in. What you describe is one of the bugs which
have been fixed in the latest release. Unfortunateky, the bug still
persists in the version we purchased. We tried this again on Friday: a
dynamic "EXEC proc" works fine with the trial version (dated 19 June),
but fails as you describe with the Production version (dated 13
March).

Interestingly, I noticed exactly the same bug in another Sybase
product some time ago. Back in November'95, Powerbuilder 4.0 had a
problem calling an SP through CtLib with a dynamic string like
"EXECUTE proc". Strangely, it worked with the shorter form "EXEC
proc". I know jConnect is a brand new implementation, so it might be a
coincidence.

Andrew Schonberger
andrew_sc@compuserve.com

On Tue, 03 Jun 1997 09:49:31 -0400, "Jon Bradbury"

<101527.3722@compuserve.com> wrote:

>This query has now been resolved with some help from the Sybase Technical
>Support team, so I'll answer my own question, for anyone else out there
>that's interested!
>
>The "official" way to execute stored procedures is using the
>platform-independent (but somewhat cumbersome) Escape Sequence syntax (see
>the documentation for the CallableStatement class). This allows you to
>execute stored procedures independent of the syntax for SP calls used by
>any one database.
>
>We were using the easier (but cruder) method of constructing a String of
>the actual SP call *specific to Sybase* such as "EXECUTE OrderUpdate
>'Smith', 200", and then executing that String with the usual exectue()
>method on the Statement class. Confusingly, this direct approach works fine
>as long as the stored procedure is a SELECT, but falls flat on its face
>(with an insoluble Unexpected Result Type exception) if the SP happens to
>return a specific value (e.g. an INSERT or UPDATE that returns the number
>of rows affected using 'return @@rowcount').
>
>So, the moral of the story is to be "well behaved", and use the proper
>Escape Sequence syntax. As well as being database-independent, it functions
>correctly for all SP's, regardless of the return type.
>
>Hope that helps anyone else who is similarly confused!
>
>Jon Bradbury
>
>
>Lance Andersen <lancea@sybase.com> wrote in article
><3392F3AA.C3D@sybase.com>...
>> Jon,
>>
>> could you provide a working example of your problem.
>>
>> thanks
>>
>> lance
>>
>> Jon Bradbury wrote:
>> >
>> > We have a come accross a problem performing simple updates or inserts
>via
>> > stored procedures. If the query is executed directly (using INSERT, or
>> > UPDATE) everything works fine. However, if a Stored Procedure is
>written to
>> > perform exactly the same insert or update, and then return @@rowcount,
>the
>> > query succeeds (!) but an SQLException is raised, with the text
>"Unexpected
>> > result type".
>> >
>> > This is the result regardless of whether the executeQuery(),
>> > executeUpdate() or execute() methods are used.
>> >
>> > Any comments / advice greatly appreciated.
>>
>> --
>>
>============================================================================
>===
>> Lance J. Andersen Email: lancea@sybase.com
>> Sybase Technical Support Phone:(617) 564-6336
>> 77 South Bedford Street Fax: (617) 564-6148
>> Burlington, MA 01803
>>
>> The Dark Knight Returns!!! Let's Go Penguins!!!
>>
>============================================================================
>===
>>


Paul Mapstone Posted on 1997-06-12 15:31:07.0Z
Message-ID: <33A0163B.9FE@t-mi.com>
Date: Thu, 12 Jun 1997 16:31:07 +0100
From: Paul Mapstone <paul.mapstone@t-mi.com>
Organization: Tokyo-Mitsubishi International
X-Mailer: Mozilla 3.0C-NSCP (WinNT; I)
MIME-Version: 1.0
To: Jon Bradbury <101527.3722@compuserve.com>
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception (the solution!)
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc> <3392F3AA.C3D@sybase.com> <01bc7035$f39f1d00$1fc1dec2@ISDNPC>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 80
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:697
Article PK: 252449

Strange! This works ok when I do it, using Statement.execute(). The
only problem I get is an irritating SQLWarning "An unexpected status
value was received and ignored."

It is extremely inconvenient if Sybase will not support the use of
arbitrary SQL batches (including sprocs) with Statement.execute() - I
thought this was the whole point of the method!

I am using the 30May trial download with Sybase 11.0.2.1.

Jon Bradbury wrote:
>
> This query has now been resolved with some help from the Sybase Technical
> Support team, so I'll answer my own question, for anyone else out there
> that's interested!
>
> The "official" way to execute stored procedures is using the
> platform-independent (but somewhat cumbersome) Escape Sequence syntax (see
> the documentation for the CallableStatement class). This allows you to
> execute stored procedures independent of the syntax for SP calls used by
> any one database.
>
> We were using the easier (but cruder) method of constructing a String of
> the actual SP call *specific to Sybase* such as "EXECUTE OrderUpdate
> 'Smith', 200", and then executing that String with the usual exectue()
> method on the Statement class. Confusingly, this direct approach works fine
> as long as the stored procedure is a SELECT, but falls flat on its face
> (with an insoluble Unexpected Result Type exception) if the SP happens to
> return a specific value (e.g. an INSERT or UPDATE that returns the number
> of rows affected using 'return @@rowcount').
>
> So, the moral of the story is to be "well behaved", and use the proper
> Escape Sequence syntax. As well as being database-independent, it functions
> correctly for all SP's, regardless of the return type.
>
> Hope that helps anyone else who is similarly confused!
>
> Jon Bradbury
>
> Lance Andersen <lancea@sybase.com> wrote in article
> <3392F3AA.C3D@sybase.com>...
> > Jon,
> >
> > could you provide a working example of your problem.
> >
> > thanks
> >
> > lance
> >
> > Jon Bradbury wrote:
> > >
> > > We have a come accross a problem performing simple updates or inserts
> via
> > > stored procedures. If the query is executed directly (using INSERT, or
> > > UPDATE) everything works fine. However, if a Stored Procedure is
> written to
> > > perform exactly the same insert or update, and then return @@rowcount,
> the
> > > query succeeds (!) but an SQLException is raised, with the text
> "Unexpected
> > > result type".
> > >
> > > This is the result regardless of whether the executeQuery(),
> > > executeUpdate() or execute() methods are used.
> > >
> > > Any comments / advice greatly appreciated.
> >
> > --
> >
> ============================================================================
> ===
> > Lance J. Andersen Email: lancea@sybase.com
> > Sybase Technical Support Phone:(617) 564-6336
> > 77 South Bedford Street Fax: (617) 564-6148
> > Burlington, MA 01803
> >
> > The Dark Knight Returns!!! Let's Go Penguins!!!
> >
> ============================================================================
> ===
> >


Lance Andersen Posted on 1997-06-23 15:27:23.0Z
Message-ID: <33AE95DB.5463@ysbase.com>
Date: Mon, 23 Jun 1997 11:27:23 -0400
From: Lance Andersen <lancea@ysbase.com>
X-Mailer: Mozilla 3.0 (Win95; I)
MIME-Version: 1.0
To: Paul Mapstone <paul.mapstone@t-mi.com>
CC: 101527.3722@compuserve.com
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception (the solution!)
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc> <3392F3AA.C3D@sybase.com> <01bc7035$f39f1d00$1fc1dec2@ISDNPC> <33A0163B.9FE@t-mi.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 97
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:651
Article PK: 252402

Hi Paul and Jon,

I wanted to get back to you on this. We do support the execution
of sprocs without using a CallableStatement. However, we will
throw the SQLWarning because of the return code not being handled.

You can have a method which processes your SQLWarnings and ignores
this.

At the present time, we do not have any plans to suppress this
warning, but as you are aware you have to poll for the warning
and you may ignore it if you like.

Regards,

-Lance

Paul Mapstone wrote:
>
> Strange! This works ok when I do it, using Statement.execute(). The
> only problem I get is an irritating SQLWarning "An unexpected status
> value was received and ignored."
>
> It is extremely inconvenient if Sybase will not support the use of
> arbitrary SQL batches (including sprocs) with Statement.execute() - I
> thought this was the whole point of the method!
>
> I am using the 30May trial download with Sybase 11.0.2.1.
>
> Jon Bradbury wrote:
> >
> > This query has now been resolved with some help from the Sybase Technical
> > Support team, so I'll answer my own question, for anyone else out there
> > that's interested!
> >
> > The "official" way to execute stored procedures is using the
> > platform-independent (but somewhat cumbersome) Escape Sequence syntax (see
> > the documentation for the CallableStatement class). This allows you to
> > execute stored procedures independent of the syntax for SP calls used by
> > any one database.
> >
> > We were using the easier (but cruder) method of constructing a String of
> > the actual SP call *specific to Sybase* such as "EXECUTE OrderUpdate
> > 'Smith', 200", and then executing that String with the usual exectue()
> > method on the Statement class. Confusingly, this direct approach works fine
> > as long as the stored procedure is a SELECT, but falls flat on its face
> > (with an insoluble Unexpected Result Type exception) if the SP happens to
> > return a specific value (e.g. an INSERT or UPDATE that returns the number
> > of rows affected using 'return @@rowcount').
> >
> > So, the moral of the story is to be "well behaved", and use the proper
> > Escape Sequence syntax. As well as being database-independent, it functions
> > correctly for all SP's, regardless of the return type.
> >
> > Hope that helps anyone else who is similarly confused!
> >
> > Jon Bradbury
> >
> > Lance Andersen <lancea@sybase.com> wrote in article
> > <3392F3AA.C3D@sybase.com>...
> > > Jon,
> > >
> > > could you provide a working example of your problem.
> > >
> > > thanks
> > >
> > > lance
> > >
> > > Jon Bradbury wrote:
> > > >
> > > > We have a come accross a problem performing simple updates or inserts
> > via
> > > > stored procedures. If the query is executed directly (using INSERT, or
> > > > UPDATE) everything works fine. However, if a Stored Procedure is
> > written to
> > > > perform exactly the same insert or update, and then return @@rowcount,
> > the
> > > > query succeeds (!) but an SQLException is raised, with the text
> > "Unexpected
> > > > result type".
> > > >
> > > > This is the result regardless of whether the executeQuery(),
> > > > executeUpdate() or execute() methods are used.
> > > >
> > > > Any comments / advice greatly appreciated.
> > >
> > > --
> > >
> > ============================================================================
> > ===
> > > Lance J. Andersen Email: lancea@sybase.com
> > > Sybase Technical Support Phone:(617) 564-6336
> > > 77 South Bedford Street Fax: (617) 564-6148
> > > Burlington, MA 01803
> > >
> > > The Dark Knight Returns!!! Let's Go Penguins!!!
> > >
> > ============================================================================
> > ===
> > >


Paul Mapstone Posted on 1997-06-23 16:37:00.0Z
Message-ID: <33AEA62C.3659@t-mi.com>
Date: Mon, 23 Jun 1997 17:37:00 +0100
From: Paul Mapstone <paul.mapstone@t-mi.com>
Organization: Tokyo-Mitsubishi International
X-Mailer: Mozilla 3.0C-NSCP (WinNT; I)
MIME-Version: 1.0
To: Lance Andersen <lancea@ysbase.com>
Subject: Re: Return @@rowcount from an SP causes Unexpected Result Type exception (the solution!)
References: <01bc6b81$c83b1640$1fc1dec2@isdn-pc> <3392F3AA.C3D@sybase.com> <01bc7035$f39f1d00$1fc1dec2@ISDNPC> <33A0163B.9FE@t-mi.com> <33AE95DB.5463@ysbase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 111
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:650
Article PK: 252403

Lance,

Thank you for your reply, that makes things much clearer. The only
question I have is precisely what is the warning for? It does not
appear to be the stored procedure return code. Is it a warning that no
rows were returned? I notice that the warning is not returned if the
stored procedure returns at least one result set.

Lance Andersen wrote:
>
> Hi Paul and Jon,
>
> I wanted to get back to you on this. We do support the execution
> of sprocs without using a CallableStatement. However, we will
> throw the SQLWarning because of the return code not being handled.
>
> You can have a method which processes your SQLWarnings and ignores
> this.
>
> At the present time, we do not have any plans to suppress this
> warning, but as you are aware you have to poll for the warning
> and you may ignore it if you like.
>
> Regards,
>
> -Lance
>
> Paul Mapstone wrote:
> >
> > Strange! This works ok when I do it, using Statement.execute(). The
> > only problem I get is an irritating SQLWarning "An unexpected status
> > value was received and ignored."
> >
> > It is extremely inconvenient if Sybase will not support the use of
> > arbitrary SQL batches (including sprocs) with Statement.execute() - I
> > thought this was the whole point of the method!
> >
> > I am using the 30May trial download with Sybase 11.0.2.1.
> >
> > Jon Bradbury wrote:
> > >
> > > This query has now been resolved with some help from the Sybase Technical
> > > Support team, so I'll answer my own question, for anyone else out there
> > > that's interested!
> > >
> > > The "official" way to execute stored procedures is using the
> > > platform-independent (but somewhat cumbersome) Escape Sequence syntax (see
> > > the documentation for the CallableStatement class). This allows you to
> > > execute stored procedures independent of the syntax for SP calls used by
> > > any one database.
> > >
> > > We were using the easier (but cruder) method of constructing a String of
> > > the actual SP call *specific to Sybase* such as "EXECUTE OrderUpdate
> > > 'Smith', 200", and then executing that String with the usual exectue()
> > > method on the Statement class. Confusingly, this direct approach works fine
> > > as long as the stored procedure is a SELECT, but falls flat on its face
> > > (with an insoluble Unexpected Result Type exception) if the SP happens to
> > > return a specific value (e.g. an INSERT or UPDATE that returns the number
> > > of rows affected using 'return @@rowcount').
> > >
> > > So, the moral of the story is to be "well behaved", and use the proper
> > > Escape Sequence syntax. As well as being database-independent, it functions
> > > correctly for all SP's, regardless of the return type.
> > >
> > > Hope that helps anyone else who is similarly confused!
> > >
> > > Jon Bradbury
> > >
> > > Lance Andersen <lancea@sybase.com> wrote in article
> > > <3392F3AA.C3D@sybase.com>...
> > > > Jon,
> > > >
> > > > could you provide a working example of your problem.
> > > >
> > > > thanks
> > > >
> > > > lance
> > > >
> > > > Jon Bradbury wrote:
> > > > >
> > > > > We have a come accross a problem performing simple updates or inserts
> > > via
> > > > > stored procedures. If the query is executed directly (using INSERT, or
> > > > > UPDATE) everything works fine. However, if a Stored Procedure is
> > > written to
> > > > > perform exactly the same insert or update, and then return @@rowcount,
> > > the
> > > > > query succeeds (!) but an SQLException is raised, with the text
> > > "Unexpected
> > > > > result type".
> > > > >
> > > > > This is the result regardless of whether the executeQuery(),
> > > > > executeUpdate() or execute() methods are used.
> > > > >
> > > > > Any comments / advice greatly appreciated.
> > > >
> > > > --
> > > >
> > > ============================================================================
> > > ===
> > > > Lance J. Andersen Email: lancea@sybase.com
> > > > Sybase Technical Support Phone:(617) 564-6336
> > > > 77 South Bedford Street Fax: (617) 564-6148
> > > > Burlington, MA 01803
> > > >
> > > > The Dark Knight Returns!!! Let's Go Penguins!!!
> > > >
> > > ============================================================================
> > > ===
> > > >