The error I got was:
SQLState: 22023
Message: SR169: signal state should be an integer 100 (NO DATA FOUND)
or a string value, not an INTEGER
However it was my fault, I had overlooked the line "state := '00000';" and
wasn't setting the state variable to an initial value as I had assumed the
'0000' was set by exec on a successful compilation/execution. In fact, it
seems to be unchanged and the variable was initialised as integer 0 which
broke signal().
On 2 December 2013 22:45, Hugh Williams <hwilli...@openlinksw.com> wrote:
> Hi Quentin,
>
> The example as defined in the docs link does work (apart from minor error
> in variable declarations which will be corrected) :
>
> SQL> create procedure tb_is_empty (in tb varchar) { declare state, msg,
> descs, rows any; state := '00000'; exec (sprintf ('select 1 from %s',
> tb), state, msg, vector (), 1, descs, rows); if (state <> '00000')
> signal (state, msg); if (length (rows) = 0) return 1; else
> return 0; };
>
> Done. -- 1 msec.
> SQL> create table hugh (c1 integer);
>
>
>
> Done. -- 10 msec.
> SQL> select tb_is_empty ('sys_users');
> tb_is_empty
> VARCHAR
>
> _______________________________________________________________________________
>
> 0
>
> 1 Rows. -- 29 msec.
> SQL> select tb_is_empty ('hugh');
> tb_is_empty
> VARCHAR
>
> _______________________________________________________________________________
>
> 1
>
> 1 Rows. -- 1 msec.
> SQL>
>
> Thus what is the use case in which you are encountering a problem ?
>
> Best Regards
> Hugh Williams
> Professional Services
> OpenLink Software, Inc. // http://www.openlinksw.com/
> Weblog -- http://www.openlinksw.com/blogs/
> LinkedIn -- http://www.linkedin.com/company/openlink-software/
> Twitter -- http://twitter.com/OpenLink
> Google+ -- http://plus.google.com/100570109519069333827/
> Facebook -- http://www.facebook.com/OpenLinkSoftware
> Universal Data Access, Integration, and Management Technology Providers
>
> On 2 Dec 2013, at 06:13, Quentin <quent...@clearbluewater.com.au> wrote:
>
> The documentation is a little outdated here:
> http://docs.openlinksw.com/virtuoso/fn_exec.html
>
> It describes catching exceptions with something like:
>
> if (state <> '00000')
> signal (state, msg);
>
> However this will not work and throws various errors. Something like the
> below will work:
>
> declare errorCode, errorMessage any;
> ...
> if (errorCode <> 0)
> signal (cast(errorCode as VARCHAR), errorMessage);
>
>
> The state/errorCode variable needs to be an integer for comparison and a
> varchar or any in signal().
>
>
> --
> Quentin | Clear Blue Water Pty Ltd
> quent...@clearbluewater.com.au
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
>
> http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk_______________________________________________
> Virtuoso-users mailing list
> Virtuoso-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
>
>
>
--
Quentin | Clear Blue Water Pty Ltd
quent...@clearbluewater.com.au
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
