Re: Can a procedure contain only a SELECT statement?
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 26 Mar 2010 21:10:36 -0000
Message-ID: <QPedneI_BrhXvzDWnZ2dnUVZ8qSdnZ2d_at_bt.com>
"Galen Boyer" <galen_boyer_at_yahoo.com> wrote in message news:uwrwzd92d.fsf_at_www.yahoo.com...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:
>
>> Would anyone care to make a technical comment on
>> my earlier comment
>>
>> I'm still interested to hear how the front-end code can handle the
>> output from a procedure when it doesn't have any information
>> about what that output might look like.
>
> From the java perspective, a procedure executes, and because you called
> it, you know whether it returns a result set or not, so, in java you ask
> the result set object for the result set, so, to the java coder, the API
> works as expected. But, the issue seems to be exposed by the error
> checking that is forced upon the java codebase. Upon execution of a
> procedure within Sybase (and I guess in SQL Server) you have to iterate
> a stack of things. Each thing is tested as to whether it is a return
> code or not, and if it is a return code, then a boolean of yes or no
> tells you whether the procedure has errored or was successful. Within
> the procedure, an error does not actually bubble to the top as it does
> in Oracle, but, instead gets put on this stack of things.
>
> So, on this return stack is, exactly as you are questioning, multiple
> types of things. How is the client supposed to interpret these
> different things. My guess is that the implementation of the jdbc
> drivers iterate the same stack I just described looking for result sets,
> and then, return those things.
>
> That is what I know and am surmising from the java perspective. I would
> guess its the same in other client codebases
>
> --
> Galen Boyer
>
> --- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---
Date: Fri, 26 Mar 2010 21:10:36 -0000
Message-ID: <QPedneI_BrhXvzDWnZ2dnUVZ8qSdnZ2d_at_bt.com>
"Galen Boyer" <galen_boyer_at_yahoo.com> wrote in message news:uwrwzd92d.fsf_at_www.yahoo.com...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:
>
>> Would anyone care to make a technical comment on
>> my earlier comment
>>
>> I'm still interested to hear how the front-end code can handle the
>> output from a procedure when it doesn't have any information
>> about what that output might look like.
>
> From the java perspective, a procedure executes, and because you called
> it, you know whether it returns a result set or not, so, in java you ask
> the result set object for the result set, so, to the java coder, the API
> works as expected. But, the issue seems to be exposed by the error
> checking that is forced upon the java codebase. Upon execution of a
> procedure within Sybase (and I guess in SQL Server) you have to iterate
> a stack of things. Each thing is tested as to whether it is a return
> code or not, and if it is a return code, then a boolean of yes or no
> tells you whether the procedure has errored or was successful. Within
> the procedure, an error does not actually bubble to the top as it does
> in Oracle, but, instead gets put on this stack of things.
>
> So, on this return stack is, exactly as you are questioning, multiple
> types of things. How is the client supposed to interpret these
> different things. My guess is that the implementation of the jdbc
> drivers iterate the same stack I just described looking for result sets,
> and then, return those things.
>
> That is what I know and am surmising from the java perspective. I would
> guess its the same in other client codebases
>
> --
> Galen Boyer
>
> --- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---
Galen
Thanks for that.
I think your comments match the degree of complexity that I had thought might be necessary. I wonder if this means that people tend to write "simple" procedures that only output one type of record set, rather than the multiple types that the OP was asking about.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Fri Mar 26 2010 - 16:10:36 CDT