SQL Anywhere database - FORWARD TO... in Stored procedures

Author

FORWARD TO... in Stored procedures

Hans Groeneveld

2005-05-18, 9:23 am

A customer does a main database and some minor databases. The databases are
linked by remote databases.
The article table in these databases are equal and the articles are
maintained in the main database.

Now they like to delete all non-used articles. Before I can test articles I
have to be sure it is not in use in one of the databases.
I found the command FORWARD TO what seems to be the solution for my problem
but when I use that statement in a SP I got a syntax error. In the Help I
could not find if it is supported in Stored Procedures but I am afraid it is
not...? Because when I use 'FORWARD TO ;' I got a syntax error.

After some more studie I found that when I use EXECUTE IMMEDIATE it works
fine.
I tried the following:

SET ls_sql = 'SELECT Count(*) INTO ll_c FROM table_1 WHERE art_code = ''' +
ls_artcode + '''' ;
SET ls_sql = 'FORWARD TO ' + remote_server + '{' + ls_sql + '}' ;
EXECUTE IMMEDIATE ls_sql ;

That gives an error that ll_c did not exists. It does exists in the SP so I
created a variable ll_c into the remote database also (by using FORWARD TO
and CREATE variable).
That seems to work............ BUT... how do I get the value ll_c into my
SP?

Is there a way to solve this problem by using FORWARD TO or do I have to use
proxy tables?
(I do not prever to create for every tabel and remote database a proxy table
in the main database).

Using ASA 8.03.

Thanks
Hans

Breck Carter [TeamSybase]

2005-05-18, 9:23 am

EXECUTE IMMEDIATE *is* necessary to make FORWARD TO work inside a
stored procedure.

It is difficult to return values via FORWARD TO. It works in ISQL but
not in a stored procedure. One *very crude* workaround is to put a
whole BEGIN block in the FORWARD TO; code inside that block selects a
value into a local variable (local to the begin block), then RAISERROR
puts that value in the error message string. That string can be
captured in an EXCEPTION block by calling ERRORMSG, and parsed, by the
caller (er, the "forward-er" :)

Like I said, crude.

Breck

On 18 May 2005 06:21:59 -0700, "Hans Groeneveld"
<h. groeneNOveld@tsdSPAM
.nl> wrote:

>A customer does a main database and some minor databases. The databases are
>linked by remote databases.
>The article table in these databases are equal and the articles are
>maintained in the main database.
>
>Now they like to delete all non-used articles. Before I can test articles I
>have to be sure it is not in use in one of the databases.
>I found the command FORWARD TO what seems to be the solution for my problem
>but when I use that statement in a SP I got a syntax error. In the Help I
>could not find if it is supported in Stored Procedures but I am afraid it is
>not...? Because when I use 'FORWARD TO ;' I got a syntax error.
>
>After some more studie I found that when I use EXECUTE IMMEDIATE it works
>fine.
>I tried the following:
>
>SET ls_sql = 'SELECT Count(*) INTO ll_c FROM table_1 WHERE art_code = ''' +
>ls_artcode + '''' ;
>SET ls_sql = 'FORWARD TO ' + remote_server + '{' + ls_sql + '}' ;
>EXECUTE IMMEDIATE ls_sql ;
>
>That gives an error that ll_c did not exists. It does exists in the SP so I
>created a variable ll_c into the remote database also (by using FORWARD TO
>and CREATE variable).
>That seems to work............ BUT... how do I get the value ll_c into my
>SP?
>
>Is there a way to solve this problem by using FORWARD TO or do I have to use
>proxy tables?
>(I do not prever to create for every tabel and remote database a proxy table
>in the main database).
>
>
>Using ASA 8.03.
>
>Thanks
>Hans
>
>

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

Hans Groeneveld

2005-05-18, 9:23 am

Thanks Breck, now I am sure that I am at a dead end with FORWARD TO.

I created a new SP to test if a particular article is in use, that SP will
be copied in every database and in the main database I will create remote
procedures.
That works fine, the only disadvantage is that I have to create a (temp)
remote procedure for every remote server.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:k9hm8114best3f8
hkkbq8l9vmfbs5dv4qm@
4ax.com...
> EXECUTE IMMEDIATE *is* necessary to make FORWARD TO work inside a
> stored procedure.
>
> It is difficult to return values via FORWARD TO. It works in ISQL but
> not in a stored procedure. One *very crude* workaround is to put a
> whole BEGIN block in the FORWARD TO; code inside that block selects a
> value into a local variable (local to the begin block), then RAISERROR
> puts that value in the error message string. That string can be
> captured in an EXCEPTION block by calling ERRORMSG, and parsed, by the
> caller (er, the "forward-er" :)
>
> Like I said, crude.
>
> Breck
>
> On 18 May 2005 06:21:59 -0700, "Hans Groeneveld"
> <h. groeneNOveld@tsdSPAM
.nl> wrote:
>
are[color=darkred]
I[color=darkred]
problem[color=darkre
d]
is[color=darkred]
+[color=darkred]
I[color=darkred]
TO[color=darkred]
use[color=darkred]
table[color=darkred]

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

Sponsored Links