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
