SQL Anywhere database - ASA8: When the stored procedure finished??

Author

ASA8: When the stored procedure finished??

Sue Yu

2005-10-27, 7:40 am

Hi there;

I have a few stored procedures to transfer data from one
table to another one. I used ISQL in Sybase Central to run
it as following:
=========
call dt.my_procedure()
go
commit work
go
==========

ALTER procedure dt.my_procedure()
--- load tissCode to tableB from tableA
begin
declare v_hs_num integer;
declare v_tissCode char(6);
declare curList dynamic scroll cursor for
select A.TissCode,B.hs#
from tableA as A,tableB as B where A.HS# =
B.HS#;
open curList;
listLoop: loop
fetch next curList into v_tissCode,v_hs_num;

update cgs.nucleicacid set TissCode = v_tissCode where
HS# = v_hs_num
end loop listLoop;
close curList
end
====================
======

The process going on and on, never came out the finishing
message, even though the procedure already finished by
checking the new data transfered. I have to guess the
finishing time and then click the square button to stop it.

What did I miss to bring up the finishing message or the
finishing state in ISQL? Is

Thanks
Sue Yu

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

The procedure may not be finished; the UPDATE may be bocked by another
connection.

Or the COMMIT may take a while to run.

Put a MESSAGE statement at the end of the procedure...

....
end loop listLoop;
close curList;
MESSAGE 'Done' TO CLIENT;
end

Breck

On 21 Oct 2005 09:59:37 -0700, Sue Yu wrote:

>Hi there;
>
>I have a few stored procedures to transfer data from one
>table to another one. I used ISQL in Sybase Central to run
>it as following:
>=========
>call dt.my_procedure()
>go
>commit work
>go
>==========
>
>ALTER procedure dt.my_procedure()
>--- load tissCode to tableB from tableA
>begin
> declare v_hs_num integer;
> declare v_tissCode char(6);
> declare curList dynamic scroll cursor for
> select A.TissCode,B.hs#
> from tableA as A,tableB as B where A.HS# =
>B.HS#;
> open curList;
> listLoop: loop
> fetch next curList into v_tissCode,v_hs_num;

> update cgs.nucleicacid set TissCode = v_tissCode where
>HS# = v_hs_num
> end loop listLoop;
> close curList
>end
> ====================
======
>
>The process going on and on, never came out the finishing
>message, even though the procedure already finished by
>checking the new data transfered. I have to guess the
>finishing time and then click the square button to stop it.
>
> What did I miss to bring up the finishing message or the
>finishing state in ISQL? Is
>
>Thanks
>Sue Yu

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

Ooops, I meant "the UPDATE may be blocked by another connection".

On 21 Oct 2005 10:20:49 -0700, "Breck Carter [TeamSybase]"
< NOSPAM__bcarter@risi
ngroad.com> wrote:
[color=darkred]
>The procedure may not be finished; the UPDATE may be bocked by another
>connection.
>
>Or the COMMIT may take a while to run.
>
>Put a MESSAGE statement at the end of the procedure...
>
>...
> end loop listLoop;
> close curList;
> MESSAGE 'Done' TO CLIENT;
>end
>
>Breck
>
>
>On 21 Oct 2005 09:59:37 -0700, Sue Yu wrote:
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Rudy Sujanto

2005-10-27, 7:40 am

Hi Sue,

Don't you think you should put an "end of file" checking like:

if sqlstate=err_notfoun
d then
leave <loop_label>
end if

immediately under the FETCH?

I always follow this template when writing a cursor-based stored-procedure:

declare err_notfound exception for sqlstate value '02000';
declare <cursor temp variables>;
declare <cursor_name> cursor for select <select stmt>;
...
open <cursor_name>;
<loop_label>: loop
fetch next <cursor_name> into <cursor temp vars>;
if sqlstate=err_notfoun
d then
leave <loop_label>
end if
;
...
<cursor actions>
...
end loop <loop_label>
;
close <cursor_name>

Regards,
Rudy Sujanto

Sue Yu wrote:
> Hi there;
>
> I have a few stored procedures to transfer data from one
> table to another one. I used ISQL in Sybase Central to run
> it as following:
> =========
> call dt.my_procedure()
> go
> commit work
> go
> ==========
>
> ALTER procedure dt.my_procedure()
> --- load tissCode to tableB from tableA
> begin
> declare v_hs_num integer;
> declare v_tissCode char(6);
> declare curList dynamic scroll cursor for
> select A.TissCode,B.hs#
> from tableA as A,tableB as B where A.HS# =
> B.HS#;
> open curList;
> listLoop: loop
> fetch next curList into v_tissCode,v_hs_num;

> update cgs.nucleicacid set TissCode = v_tissCode where
> HS# = v_hs_num
> end loop listLoop;
> close curList
> end
> ====================
======
>
> The process going on and on, never came out the finishing
> message, even though the procedure already finished by
> checking the new data transfered. I have to guess the
> finishing time and then click the square button to stop it.
>
> What did I miss to bring up the finishing message or the
> finishing state in ISQL? Is
>
> Thanks
> Sue Yu

Sponsored Links