Stored Procedure OUTPUT

[Michael]

Hi,

I'm currently using PB 6.5.1 with MS SQL Server 7.02 using Microsoft's 3.7
ODBC driver. I currently converting our connection type for the direct
connect drivers to ODBC drivers since OLEDB to sql server doesn't work in
powerbuilder yet.

The statement below had worked fine using the direct connect method however
the odbc driver returns back SQLSTATE 37000; Cannot use the OUTPUT option
when passing a constant to a stored procedure.

DECLARE fs_post_ar PROCEDURE FOR dbo.fs_post_ar
@user = :gs_user,
@process_ctrl_num = :batch,
@err = :err OUT;

EXECUTE fs_post_ar ;

I have read a blurb about declaring an external function using a nvo of type
sqlca, but I haven't been able to get that to work. If someone as done this
can you please provide a detailed example. Our work around so far as been
to wrap the stored procedure with another stored procedure then using a
datastore to retrieve the select on the output argument that we did in the
wrap sp. I said it was a workaround not a good workaround.

Thanks in advance......

Michael Bokhari

[Jim O'Neil [Sybase]]

The code you have here will not work prior to PB 7.0 anyway, since you
could not obtain output parameters from embedded SQL.

On Thu, 22 Jun 2000 18:55:41 -0700, "Michael" <mbok...@hotmail.com>
wrote:

[Michael Bokhari]

Jim,

Thanks for your quick response. The statement below has worked fine using
the dblib connect, but we are required by market to switch to OLEdb, but
since we are using pb we have to settle on odbc. Is there any way that we
can get this to work using pb 6.5.1. If this can be done in 7.02 (no ebf)
then can you give me an example. We have quite abit of stored procedure
that utilize this the output argument.

"Jim O'Neil [Sybase]" <jon...@sybase.com> wrote in message
news:395369be...@forums.powersoft.com...

[Jim O'Neil [Sybase]]

With 7.0.2, you can do this with embedded SQL using the same syntax as
you did for the Native driver - although I believe you will have to
delete the OUT tag from the DECLARE syntax. If you are only returning
output parameters (and not result sets) then using the RPC FUNC method
is cleaner and more efficient from an execution standpoint. To do so,
you'll want to inherit a new standard class user object from the base
transaction object and then define a local external function on that
object. When you do that, the local external function will really
point to the stored procedure you want to execute. In execution you
will invoke the stored proc by calling the local external function you
just defined on the transaction object.

Jim

[Michael Bokhari]

Do you happen to have an example of an RPC Func using the OUT syntax? We
found example in the pb online books but it didn't have the OUT syntax.

"Jim O'Neil [Sybase]" <jon...@sybase.com> wrote in message

news:39539559...@forums.powersoft.com...

[David Rawheiser]

If you use the procedure picklist window from within the transaction object
decendent, it should paste the correct syntax for the proc.

I believe the RPC declaration in PowerScript does not say OUT but rather
says REF.

See Chap 12 of Application Techniques - Using Transaction objects to call
stored procedures.

"Michael Bokhari" <mbok...@hotmail.com> wrote in message
news:n66oaMV3$GA...@forums.sybase.com...

[Vladimir Gendler]

Hi Michael,

I have read a response from Jim O'Neil [Sybase] about impossibility
to obtain output parameters from stored procedures. It is not correct.
I did it some time ago (it means I do not have examples) in Sybase (so
it should work with MS SQL Server). According to PB documentation
(PB7 and prior!) you can use key word OUT with SQL Server. It does
not say specifically which SQL Server so we should assume both - MS and Sybase.
You should consider ODBC as a separate type of database regardless
of what is actual database you are eventually connected. So, according
the PB documentation you SQL statement will not work
In spite of the fact staying in the PB documentation about about the
possibility to put a modifier OUT it does not say that you will be able
to get the value in this parameter from the server as you might think
based on common sense. They do not say in the documentation what
do you need this modifier for. Anyway, your statement will execute
without error but you will not see the parameter value as you
expect. There are good reasons do not have them and the most
important is the fact that stored procedure and your program should
work in different address spaces, even on different machines.
But you are able to get these parameters using FETCH (!). There are
three type of things which can be returned from SQL Server - returned
value from stored procedure, output parameters and result set(s).
It is documented how to get result set(s) but it is not documented
how to get the first two entities. As I wrote here you have to fetch
them too. There is a predefined order, which I do not remember,
who come first when you fetch. It is either [return], out parameters,
result set(s) or out parameters, [return], result set(s). You can check
it by running any example. Of course, if any entity does not exist then
you should eliminate this item from the order.
I have checked the PB7 documentation and do not see any new words
about OUT modifier for stored procedures so I doubt if it will work
as we expect in this version too.
You might ask how it works for RPC. They hide the fetch mechanism
which is activated when you call the function.
Anyway, you can try to return parameters using ODBC even without the OUT
modifier by trying to fetch them. I do not guarantee success but
it is easy to check. They have to be placed in the same order as
parameters go and they should correspond the number of intended
"OUT" parameters.

Vladimir.

[Vladimir Gendler]

It is possible, read my post to the same question.

xxx wrote:

> Even I have tried my best to get output from MSS 7.0 in pB 7.02. It was not
> possible.
>
> Thanks.

[xxx]