• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3246
  • Last Modified:

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.


0
rakesh_rl
Asked:
rakesh_rl
  • 5
  • 3
1 Solution
 
diasroshanCommented:
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
0
 
rakesh_rlAuthor Commented:
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.
0
 
rakesh_rlAuthor Commented:
Is value3 a PB variable ? If yes, I can return value3 after the end of your code right ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rakesh_rlAuthor Commented:
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 ?
0
 
diasroshanCommented:
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
0
 
rakesh_rlAuthor Commented:
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 ?
0
 
rakesh_rlAuthor Commented:
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
0
 
diasroshanCommented:
Hi,

im sorry... i missed the FETCH command in the above code... it shud be as follows...

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;

DO WHILE SQLCA.SQLCODE = 0
FETCH ap_proc_pb
      INTO :ll_check
LOOP

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

Return ll_check


Cheers,
Rosh
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now