asked on

Syntax for calling a SQLServer procedure from powerbuilder

I have a SQL Server procedure which accepts three params and output is an integer. How do I call (I mean syntax) this from powerbuilder, I want the integer value to be available in powerbuilder into a varaiable. I would like to script it without creating a datawindow/datastore.

is it like
ll_ret_value = EXECUTE procedureName (:param1, :param2, :param3)
??

The procedure in SQL server is like:
CREATE PROCEDURE procedureName (@param1 varchar(11),@param1 varchar(11),@param1 varchar(11), @param4 int OUTPPUT)

This is a very straight forward question I guess.

Thanks.

diasroshan

Hi,

DECLARE logical_procedure_name PROCEDURE FOR
SQL_Server_procedure_name
@Param1 = value1, @Param2 = value2 , @Param3 = value3 OUTPUT,
{USING transaction_object}

simple ex:
DECLARE test_update_procedure PROCEDURE FOR test_update
USING SQLCA;
IF SQLCA.SQLCode <> 0 THEN
MessageBox ( "Error", "DECLARE failed" )
RETURN
END IF

EXECUTE test_update_procedure;

IF ( SQLCA.SQLCode <> 0 ) &
and ( SQLCA.SQLCode <> 100 ) THEN
MessageBox ( "Error", "EXECUTE failed" )
RETURN
END IF

Cheers,
Rosh

rakesh_rl

ASKER

But after EXECUTE test_update_procedure; from powerbuilder, where will I get the output value from procedure ? I need to get an integer value from the procedure.
I am sorry I am a liitle bad in syntax.

rakesh_rl

ASKER

Is value3 a PB variable ? If yes, I can return value3 after the end of your code right ?

rakesh_rl

ASKER

can you please see this code and advise me: This is from powerbuilder:
/**************************************************************/
integer ll_check

DECLARE ap_checking_plan_pb PROCEDURE FOR ap_checking_plan
@fcode = :fcode, @dcode = :dcode ,&
@process_nbr = :as_process, @check = :ll_check OUTPUT
USING SQLCA;

IF SQLCA.SQLCode <> 0 THEN
      MessageBox ( "Error", "DECLARE failed" )
      RETURN -1
END IF

EXECUTE ap_checking_plan_pb;

IF ( SQLCA.SQLCode <> 0 ) and ( SQLCA.SQLCode <> 100 ) THEN
      MessageBox ( "Error", "EXECUTE failed" )
      RETURN -1
END IF

Return ll_check
/**************************************************************/

This code gave me an error with sqlcode 100, but the procedure existed in dbo.
Could you explain whats wrong here ?

diasroshan

Hi,

SQLCODE returns 100 when fetched record not found....

Now,
try executing ur procedure thru query analyzer by passing the same values and see if u get a return value... guess there is an issue with ur procedure...

the powerbuilder syntax posted above looks perfect!

Cheers,
Rosh

rakesh_rl

ASKER

Hi diasroshan, the code has some problem. can you check again ?
This is what happened:
The procedure is executing fine and gave me an output of 2, whereas from PB it gave me 0.
When I executed the procedure one more time, from PB, it gave me sqlcode as -1, and gave me the error
"Execute failed".
Can you help me fixing this ?

rakesh_rl

ASKER

Forgot to add the error: The sqlerrtext says "Procedure has already been executed".
(This happens for the second time)
But for first time, its always giving me 0

ASKER CERTIFIED SOLUTION

diasroshan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial