Calling stored procedures from powerscript

[alexis...@whistlebrook.co.uk]

Hi guys,

I am a bit confussed about how you are ment to call stored procedures
from power script.

This is how I am calling the procedre from SQL Server Management
Studios(SSMS) 2008 and it works fine.

USE [VRS]
GO

DECLARE @return_value int,
@as_result varchar(20)

EXEC @return_value = [dbo].[ssp_remove_unit]
@as_serial = N'TSMMZC11S00516299',
@as_result = @as_result OUTPUT

SELECT @as_result as N'@as_result'

SELECT 'Return Value' = @return_value

I am using the following code to call the stored procedure from within
powerbuilder, this is the best I could do with the powerbuilder help
files.

DECLARE removeunit PROCEDURE FOR
ssp_remove_unit @as_serial = :sSerial, @as_return = :ls_return
OUTPUT
USING (gt_vrs);
EXECUTE removeunit;
FETCH removeunit into :li_return;
CLOSE removeunit;

The syntax for this is as it should be according to the help file
however the @ signs get highlighted in the code and when you validate
the code it gives the following error.

Database c0038:SQLSTATE = 22005 [MICROSOFT][ODBC SQL Server Driver]
Invalid character value for cast specification.

I don't think that this validation error is too important as i have
seen other procedures with this message that work as they should.

However the procedure does not return what I am expecting it too.
With the sSerial value that I am passing it it should be returning a
value of -1 for li_return and a value of 'fail' for ls_return as it
does with I execute this procedure from SSMS.

however li_return is returned as 0 and ls_return just comes back as
''.

Anyone got any ideas where I am going wrong or can point me in the
direction of a good guide on the matter?

Thanks in advance

[Terry Dykstra [TeamSybase]]

I think you are mixing int and string in your return value.

--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

<alexis...@whistlebrook.co.uk> wrote in message
news:7e709ed4-c780-4231...@o18g2000vbi.googlegroups.com...

[alexis...@whistlebrook.co.uk]

Hi Terry,

I don't think that is the problem, I have done some more work on this
this morning and I have simplified things a little.

I am now just tring to get a result back from the output variable
method that is documented in the help file.

I have created the following procedure in SQL server 2008.

CREATE PROCEDURE [dbo].[TestOutputVariable](
@return_value varchar(20) OUTPUT )
AS
SELECT @return_value = 'success'

I am now trying to call this with the following code in PB 11.1.

DECLARE OutputVariable PROCEDURE FOR
dbo.TestOutputVariable @return_value = :ls_output OUTPUT
USING (gt_vrs);
EXECUTE OutputVariable;

messageBox('output', ls_output)

Now I am expecting the messageBox to show me success when I run this
bit of code however it returns nothing, as before this works fine when
calling it from SSMS.

I am wondering weather it is something to do with the ODBC drivers, I
have come accross this artical about a very simular problem...

http://support.microsoft.com/kb/290175

However this is an old fix and as I am running xp pro SP3 my MDAC
version is well above the fix that this artical is suggesting.

I really need to get a stored procedure working for this functionality
as the process is used in many places and is probably going to change
quite a bit in the future.

Please could some one post some code that they know works so I could
test if it something to do with my enviroment.

This is driving me mad!

[Scott Morris]

<alexis...@whistlebrook.co.uk> wrote in message
news:24790f3f-0c01-4c0b...@e21g2000yqb.googlegroups.com...

> Hi Terry,
>
> I don't think that is the problem, I have done some more work on this
> this morning and I have simplified things a little.
>
> I am now just tring to get a result back from the output variable
> method that is documented in the help file.
>
> I have created the following procedure in SQL server 2008.
>
> CREATE PROCEDURE [dbo].[TestOutputVariable](
> @return_value varchar(20) OUTPUT )
> AS
> SELECT @return_value = 'success'

You should really use SET to set scalar values, and use SELECT to generate a
resultset. There is one exception in MS sql server, but that rule should
hold for the vast majority of situations.

>
> I am now trying to call this with the following code in PB 11.1.
>
> DECLARE OutputVariable PROCEDURE FOR
> dbo.TestOutputVariable @return_value = :ls_output OUTPUT
> USING (gt_vrs);
> EXECUTE OutputVariable;

Error checking? I'll assume you left it out for brevity.

>
>
> messageBox('output', ls_output)

You still need to fetch output variables. Below works for various versions
of PB (5 ~ 11, SQL Server (7 ~ 2005), and types of connections (MSS, OLE,
SNC). This was written a long time ago - and if I had to reimplement it I
would do so as a RPC. Using a RPC is much simpler and does all of the
necessary retrievals for the return value and output variables internally.
The only drawback is that you cannot access any resultsets.

--Procedure Signature
CREATE PROCEDURE sp_get_business_date_after
@adt_target datetime,
@ai_days_after integer,
@ai_error_on_null integer,
@adt_result datetime
OUTPUT

-- PB function
public function long of_get_next_business_date1 (datetime adtm_target, ref
datetime adtm_next);
// Return the next business date after the target date argument.
long ll_ret = -1
datetime ldtm_result

DECLARE lp_get_business_date_after PROCEDURE FOR
dbo.sp_get_business_date_after
@adt_target = :adtm_target,
@ai_days_after = 1,
@ai_error_on_null = 0,
@adt_result = :adtm_next OUTPUT
USING SQLCA;

EXECUTE lp_get_business_date_after;

IF SQLCA.SQLCode <> 0 THEN
This.EVENT ue_dberror()
ELSE
FETCH lp_get_business_date_after INTO :adtm_next;
IF SQLCA.SQLCode <> 0 THEN
This.EVENT ue_dberror()
ELSE
// Procedure executed successfully. If adtm_next is NULL
// then return 0 (next business date not determined).
// Otherwise, return 1
IF IsNull(adtm_next) THEN ll_ret = 0 ELSE ll_ret = 1
END IF

CLOSE lp_get_business_date_after;
END IF

RETURN ll_ret

[Paul Horan[Sybase]]

Fetching output variables... UGH.

Try extending the transaction object with the RPCFUNC alias technique. This
creates a method on the transaction object that invokes your stored
procedure. Output variables are immediately available without fetching (you
must pass them in by reference).

So much easier, and much more OO in nature.

--
Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

"Scott Morris" <bo...@bogus.com> wrote in message
news:49e5d75a$1@forums-1-dub...