I see.
Well regardless, even using log_enable to disable row-based commits, the
work is being committed halfway through the function and not rolled back
after an exception.
So is it possible to batch everything in a function into an atomic
transaction regardless of where the function is called from?
And is it also possible to remove an exception handler in order to resignal
an exception state?
On 2 January 2014 13:07, Hugh Williams <hwilli...@openlinksw.com> wrote:
> Hi Quentin,
>
> By "isql" I mean't the Virtuoso command line tool, which is where the "set
> AUTOCOMMIT MANUAL;" command works.
>
> 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 1 Jan 2014, at 22:54, Quentin <quent...@clearbluewater.com.au> wrote:
>
> Hi Hugh,
>
> I'm testing in conductor/isql but will be using odbc in implementation.
>
> The suggested instruction gives:
>
>
> SQLState: 37000
>
> Message: SQ074: Line 1: syntax error at 'MANUAL' before ';'
> set AUTOCOMMIT MANUAL
>
> I'm certain that's a correct isql instruction so am a little confused
> about that occurring but regardless, I believe it's functionally equivalent
> to:
> log_enable(0);
> ...
> do work;
> ...
> log_enable(3);
>
> So I tried using log_enable instead and the users were still being
> created/committed despite the exception and rollback.
>
> On 31 December 2013 22:33, Hugh Williams <hwilli...@openlinksw.com> wrote:
>
>> 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
>>
>>
>>
>
>
> --
> 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
