calling procedure over webservice

[Baron]

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?

[MarkCulp]

What happens when the service doesn't "work". I.e. do you get an error, or is it slow, or something else?

What do you mean by "too big"? If you are getting an INT overflow error then perhaps change the result type to BIGINT?

[Baron]

I dont get any error from the service, I simply wait forever for the reply of the service, but I get nothing!

Too big I mean > 1000.

Using BIGINT will not solve the problem because as I told, it regards the count of the rows and not the result of sum function.

As I mentioned before, the procedure alone works very good.

call dba.proc1(); -- even if the count is 5000

[Baron]

The complete code looks like this:

create table table1 ("col1" INT PRIMARY KEY DEFAULT AUTOINCREMENT not null, "col2" varchar(10));

begin

declare cntr int;

set cntr=1;

loop1:

while cntr < 1000 loop

insert into table1 (col2) values ('test');

set cntr = cntr+1;

end loop loop1;

end;

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" ();

[Baron]

here even changing the result (total) from INT to BIGINT doesnt change anything!

So, the proc1 works, and the service1 does not work (I dont receive any message, but it stays silent), and once I open the comment (where col1 < 10) then the service works also fine

[Vlad]

Thank you for the example. I have executed it on my SA 17.0.10.5745. I don't see any delay:

Just in case, have you tried the latest version of SA? p.s. this is how I launch the DB server:

dbsrv17 -xs http(port=8080) test

[Baron]

Thanks for the reply, Yes the DB is started with -xs so that the service works fine (where col1 < 10).

Even when I start Sybase Central with break point in proc1 then it stops working (F11, F5 does nothing)!

Could this problem be only in Sql-Anywhere 10?

I tried even to let proc1 call another procedure (proc2) which in turns does the iterative job (sum function), but the result is the same (as long as the call to proc1 is coming from a webservice).

[Baron]

Once I try to use a sub procedure (like proc2 above), then I get an error message (All threads are blocked)!

[VolkerBarth]

Do you happen to call the web service via a SQL Anywhere web client from a database running on the same database server?

[Baron]

No, I am calling the webservice over Internet Explorer (or Curl)

[VolkerBarth]

Well, so you have faced a deadlock situation. Do you call the web service procedure while rows in the according table are inserted or modified (which would lead to a classic blocking situation unless you use isolation_level 0 or snapshot isolation)?

Or are you just running out of worker threads, so the multiprogramming level may need to be increased (via dbsrv10 -gn X)?

[Baron]

The Database does nothing for the time when I call the webservice, and there are no any connections to the database!

I am starting the database with dbsrv10 -gt 8

[VolkerBarth]

I'm relating to the -gn option, not -gt.

[Baron · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[Baron · Accepted solution]

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.