?
Solved

Want to call stored procedure synchronously, instead of asynchrounously.

Posted on 2005-04-11
5
Medium Priority
?
666 Views
Last Modified: 2013-12-26
in Powerbuilder, how can I call a databse stored procedure and wait until it finishes before executing next statement after calling db stored procedure. It fires stored procedure and without waiting executes the next statement. In other word it calling the stored procedure asynchronously. I want it to call the stored procedure synchronously.
0
Comment
Question by:DGSWEBMASTER
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
diasroshan earned 2000 total points
ID: 13752740
hi,

try,

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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13752773
hi,
also refer this link for useful tips...

http://www.woodger.ca/pb_tipgn.htm#Gen_Tip_4


Cheers,
Rosh
0
 
LVL 8

Expert Comment

by:gajender_99
ID: 13752915
hi

you have to check your procedure because it might be that your procedure is calling another procedure and that is running in the background.

thanks
Gajender
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13752954
hi,

heres an example of SP with arguments...
Create procedure sales_by_quarter
   (year_in IN number, quarter_in IN number, quarter_sales OUT number)
   IS
   BEGIN
       SELECT total_sales
       INTO quarter_sales
       FROM quarterly_sales_info
       WHERE year = year_in AND
       quarter = quarter_in;
   END;

In Powerscript your code would look like the following:

   int year, quarter, quarter_sales

   year = <input year here>
   quarter = <input quarter here>

   DECLARE sales_by_quarter_proc
   PROCEDURE FOR sales_by_quarter(:year,:quarter) USING SQLCA;

   execute sales_by_quarter_proc;

   IF SQLCA.SQLCODE < 0 THEN
       messagebox("Procedure Failed!", string(sqlca.sqlcode))
       messagebox("Procedure Failed!", sqlca.sqlerrtext)
       Return
   Else
       Fetch sales_by_quarter_proc INTO :quarter_sales;
   END IF

refer...http://www.sybase.com/detail?id=44426


if u still have problems with calling SP synchronously then paste ur code... might help in pointin out the bug...

ok... also note...
SQLCA.dbParm = "Async = 1" // this parameter allows  to perform asynchronous operations on your database

so might need to set it as...
SQLCA.dbParm = "Async = 0"  //default


Cheers,
Rosh
0
 
LVL 1

Expert Comment

by:AxfordA
ID: 13772100
I usually run stored procedures from a pb function passing across arguments that might include the sp its self. So the code looks like this:-

//This calls a stored procedure called up_getbroker
//It is held in a pb function that is called with the arguments
//as_brokercode --- String and as_brokertype  -- String


long       ll_rc

string ls_brokercode, ls_Sqlstatement
decimal      ld_buyamount, ld_sellamount

SQLCA.autocommit = true
ls_Sqlstatement = "execute " + "up_getbroker '" + as_brokercode + "','" + as_brokertype + "'"

PREPARE SQLSA FROM :ls_Sqlstatement USING SQLCA;
DESCRIBE SQLSA INTO SQLDA ;

DECLARE sp_procedure DYNAMIC PROCEDURE FOR SQLSA ;
EXECUTE DYNAMIC sp_procedure USING DESCRIPTOR SQLDA ;

FETCH sp_procedure USING DESCRIPTOR SQLDA ;
// If the FETCH is successful, the output descriptor array will contain returned
// values from the first row of the result set.

ll_rc = SQLCA.sqlnrows
if SQLCA.sqlcode = 0 then
      ls_brokercode = GetDynamicString(SQLDA, 1)
else
      ls_brokercode = as_brokercode
end if

CLOSE sp_procedure ;
SQLCA.autocommit = false

Return ls_brokercode


//Good Luck
Alan
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question