SQL Anywhere Mobile - Trap FK error in SP after download phase

Author

Trap FK error in SP after download phase

Tony Lanzel

2006-02-28, 8:34 pm

ASA 9.0.2.3044

Here's the problem:

1) I perform synchronization and the download phase is
completed (but the sp_hook_dbmlsync_end
procedure has yet to
fire). No errors have occurred at this point which is
correct.
2) I have my sp_hook_dbmlsync_end
stored procedure coded so
it will call another stored procedure called XSP. XSP is a
stored procedure built to insert data from one of the tables
that was just synchronized into another non-synchronized
table. This other table has a foreign key on it. The XSP
procedure also has exception handlers so any error that
occurs inside should be trapped and not raise an error to
mobilink.
3) The XSP procedure fires off and does an insert into this
other table. The problem is that the insert SHOULD raise
(and trap) an error because it violates a FK. But it
doesn't! Instead, it just moves past the insert statement
as if nothing happens. I confirmed this with the debugger
when synchronization occurs.
4) After the XSP procedure is done with its work, logic goes
back to the calling sp_hook_dbmlsync_end
procedure. That
finishes and NOW the FK violation error is reported. The
problem here is I don't want this error reported in mobilink
- I want it handled in my error handler of the XSP procedure
so it would have been logged to a table and mobilink
wouldn't have noticed anything.

If I run the XSP procedure outside of synchronization, the
FK error is raised correctly and handled correctly in my
exception handler.

Is there something in mobilink that turns off FK violation
checks until synchronization is totally completed? Is there
a way to bypass that, at least for the sake of the
sp_hook_dbmlsync_end
procedure? What I will now have to do
is to write a query in my XSP stored procedure to see if
that record will fail the FK and to raise an error myself if
it sees a violation will occur. But I'd rather the built in
FK violation checks raised the error as they normally would.

David Fishburn

2006-02-28, 8:34 pm

Tony Lanzel wrote in news:440399f6.4547.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

It sounds like dbmlsync does this when it connects:
set temporary option wait_for_commit='on'
;

Have a look at that option.
Your stored procedure could set it to off, run your code, and put it
back to it's previous value before it returns.

I would use the EXECUTE IMMEDIATE statement to do that.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Tony Lanzel

2006-02-28, 8:34 pm

That does work, but how do I identify what the existing
value is for a temporary option (so I will know what to set
it back to)? I know how to look for a permanent option in
the sysoption table, but I don't know how to identify
temporary options already in place?

Granted, it's pretty obvious that the temp option for this
is set to 'on' given my problem, but is there a way to query
existing temporary options?

> Tony Lanzel wrote in
> news:440399f6.4547.1681692777@sybase.com of
> sybase.public.sqlanywhere.mobilink:
>
> It sounds like dbmlsync does this when it connects:
> set temporary option wait_for_commit='on'
;
>
> Have a look at that option.
> Your stored procedure could set it to off, run your code,
> and put it back to it's previous value before it returns.
>
> I would use the EXECUTE IMMEDIATE statement to do that.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
>
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

David Fishburn

2006-02-28, 8:34 pm

Tony Lanzel wrote in news:44046caf.4eb0.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

TL> That does work, but how do I identify what the existing
TL> value is for a temporary option (so I will know what to set
TL> it back to)? I know how to look for a permanent option in
TL> the sysoption table, but I don't know how to identify
TL> temporary options already in place?
TL>
TL> Granted, it's pretty obvious that the temp option for this
TL> is set to 'on' given my problem, but is there a way to query
TL> existing temporary options?

DECLARE @prev_wait_for_commi
t_value VARCHAR(255);

SELECT connection_property(
'Wait_for_commit')
INTO @prev_wait_for_commi
t_value;

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Sponsored Links