Hi Quentin,
Are you running this procedure from isql as if so autocommit mode by default
and you need to set it to manual commit mode first before running the procedure
with the command:
set AUTOCOMMIT MANUAL;
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 31 Dec 2013, at 03:27, Quentin <quent...@clearbluewater.com.au> wrote:
> Hi,
>
> I'm attempting to implement something using your advice and am encountering a
> problem. I have a function:
> create procedure
> testapp.test.testAbort(IN name VARCHAR, IN pwd VARCHAR, IN cert VARCHAR)
> returns varchar
> {
>
> whenever SQLEXCEPTION goto errorabort;
>
> declare options VECTOR;
> options := vector('SQL_ENABLE', 1);
>
> DB.DBA.USER_CREATE (name, pwd, options);
> DB.DBA.USER_CERT_REGISTER(name, cert);
> exec(concat('GRANT SPARQL_UPDATE TO ',name));
>
> DB.DBA.RDF_DEFAULT_USER_PERMS_SET (name, 0);
> DB.DBA.USER_GRANT_ROLE(name, 'TEST_USER',0);
>
> return 'everything ok';
>
> errorabort:
> rollback work;
> result(__SQL_STATE);
> result(__SQL_MESSAGE);
> return concat('error: ',__SQL_STATE, ' - ',__SQL_MESSAGE);
> }
>
> Then I call "select testapp.test.testAbort('testUser3','xxx', 'xxx');"
>
> Of course this certificate is not quite correct and Virtuoso rightly
> complains causing the outer function to return: "error: 22023 - U....: The
> certificate have been supplied is not valid or corrupted"
>
> But I have three problems here:
> The first is that the exception is not returned to the parent, I can only see
> it via the return value. If I try to signal/resignal it, I trip the
> exception handler and enter an infinite loop.
> The second is that the user "testUser3" has been created and this work is not
> aborted, presumably because it was committed in the USER_CREATE function.
>
> So, how do I remove a declared handler once I want to escalate an exception?
> How can I prevent a function from committing work that I want to occur in a
> transaction with other activity in a parent function?
>
> Oh, and when I kill the DB process to break the exception handling loop, I
> get an error on startup, that's problem three:
> ---------------
> ERROR: Error executing a server init statement : 22023: SR528: Uninitialized
> property qmfOkForAnySqlvalue in JSO instance
> <http://www.openlinksw.com/virtrdf-data-formats#default-iid> of type
> <http://www.openlinksw.com/schemas/virtrdf#QuadMapFormat> --
> DB.DBA.RDF_QUAD_FT_UPGRADE ()
> ---------------
> It might not be related to what I was doing, I'm not sure.
>
>
>
>
> On 16 December 2013 10:48, Hugh Williams <hwilli...@openlinksw.com> wrote:
> Hi Quentin,
>
> The log_enable should be removed, and unless there are no other commits or
> rollbacks the sequence will be executed in one transaction ...
>
> 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 11 Dec 2013, at 05:07, Quentin <quent...@clearbluewater.com.au> wrote:
>
>> Hi,
>>
>> If I'm executing an sql function that does some sparql, deletes some
>> triples and calls a few other functions, can I force this to occur all
>> within the context of one continuous transaction state?
>>
>> So if I have something like the below function and the
>> someOtherFunction() throws an exception (or signal), can I abort the
>> transaction and rollback the sparql insert? Or will I find some
>> results have already been committed?
>>
>> create procedure test.test.oneTransaction();
>> {
>> log_enable (0);
>> exec('sparql insert.....');
>> test.test.someOtherFunction();
>> log_enable (1);
>> commit work;
>> }
>>
>> --
>> 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=84349831&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
>
>
> On 16 December 2013 10:48, Hugh Williams <hwilli...@openlinksw.com> wrote:
> Hi Quentin,
>
> The log_enable should be removed, and unless there are no other commits or
> rollbacks the sequence will be executed in one transaction ...
>
> 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 11 Dec 2013, at 05:07, Quentin <quent...@clearbluewater.com.au> wrote:
>
>> Hi,
>>
>> If I'm executing an sql function that does some sparql, deletes some
>> triples and calls a few other functions, can I force this to occur all
>> within the context of one continuous transaction state?
>>
>> So if I have something like the below function and the
>> someOtherFunction() throws an exception (or signal), can I abort the
>> transaction and rollback the sparql insert? Or will I find some
>> results have already been committed?
>>
>> create procedure test.test.oneTransaction();
>> {
>> log_enable (0);
>> exec('sparql insert.....');
>> test.test.someOtherFunction();
>> log_enable (1);
>> commit work;
>> }
>>
>> --
>> 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=84349831&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=84349831&iu=/4140/ostg.clktrk_______________________________________________
> Virtuoso-users mailing list
> Virtuoso-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
------------------------------------------------------------------------------
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=84349831&iu=/4140/ostg.clktrk
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
