cancel
Showing results for 
Search instead for 
Did you mean: 

calling procedure over webservice

Baron
Participant
0 Kudos
1,174

I am facing a weird problem in SQL-Anywhere 10:

CREATE PROCEDURE "dba"."Proc1" ()
RESULT (total int)
BEGIN
  select sum(col1) from Table1;-- where col1 < 10;
END;
CREATE SERVICE Service1
  TYPE 'XML'
  AUTHORIZATION OFF
  USER dba
  AS CALL "dba"."Proc1" ();

The problem is that the Proc1 works always very fast, but the Service1 works only if I restrict the corresponding rows (for example if I open the comment 'where col1 < 10')

The problem looks to be related to the count of the corresponding rows from Table1, and not related to the result of sum(col1).

In other words, even if the value of each Table1.col1 is 0.0001 then I face the same problem if the (select count(col1) from Table1) is too big.

Is there any explanation and/or solution for this problem?

Accepted Solutions (1)

Accepted Solutions (1)

Baron
Participant
0 Kudos

The only solution which I could find is to write the sum(function) inside an event (which in turns calculates the sum and writes the value of sum in a table T2). Now within proc1 I trigger the event and read the value of sum from T2.

With this approach everything works fine (maybe because the sum function is not anymore related to the webservice and has different connection id).

VolkerBarth
Contributor
0 Kudos

Hm, I certainly can't believe that calculating a sum over 1000 int values should anyhow lock the server, I guess there must be another issue here... - just saying:)

Vlad
Product and Topic Expert
Product and Topic Expert
0 Kudos

the records were inserted fast enough on my PC. So I didn't hit the lock situation. I assume maybe the Sako's version of SA is old enough... I don't know. Have you tried reproducing the issue?

VolkerBarth
Contributor
0 Kudos

Well, in my understanding the inserts don't happen during the procedure call but before so the time needed to insert should not matter at all for the calculation of the sum IMVHO... - and Sarkis has stated that there are no other connections at that time.

Baron
Participant
0 Kudos

According to my experiment, the problem arises only if the procedure is called over webservice!

Baron
Participant
0 Kudos

Yes, the insert is not matter at all, I just wrote it to complete the code. The problem is in sum function as long as it is called over a webservice.

Answers (0)