I understand that the CREATE FUNCTION statement implies an "Automatic commit", but I have a situation where simply calling the function automatically commits the transaction.

It seems to be related to the fact that the function calls the Java EXTERNAL ENVIRONMENT. For example, with the Java code as:

public class Test {
    public static String jtest() {
        return "jTEST";
    }
    public static void main(String[] args) {
        System.out.println( jtest() );
    }
}
and the SQL code as:
INSTALL JAVA UPDATE FROM FILE 'D:/eclipse_workspace/TEST/bin/Test.class';

CREATE OR REPLACE FUNCTION jtest() RETURNS LONG VARCHAR EXTERNAL NAME 'Test.jtest()Ljava/lang.String;' LANGUAGE JAVA;

you can run the test:
CREATE OR REPLACE TABLE TEST(
    VAL LONG VARCHAR
);
SELECT * FROM TEST;
INSERT INTO TEST VALUES(123);
SELECT * FROM TEST;
SELECT jtest();
ROLLBACK;
SELECT * FROM TEST;
I would expect the last SELECT to return no rows; however, the inserted row has been committed.

If you run the test without the calling the external environment function (i.e., comment out the SELECT jtest(); statement), the transaction is rolled back as expected.

I'm running SQL Anywhere 17.0.4.2053 on Windows 7.

Can anyone explain this behaviour? What am I missing?

Thanks,

asked 05 Aug '16, 11:32

Terry%20Wilkinson's gravatar image

Terry Wilkinson
496162136
accept rate: 40%

I can reproduce this ... and will look into it more ...

I suspect this was also possible in 17.0.0 GA ... can you confirm what you're experience there was?

(05 Aug '16, 13:19) Nick Elson S...

Sorry Nick - don't have that version available.

However, I just installed SA 16.0.0.2043 and tried it there and it works properly, i.e., the ROLLBACK in fact rolls back the transaction even when doing the call to jtest(). Seems the behaviour change happened after that version.

(05 Aug '16, 13:48) Terry Wilkinson

Nick, I realized I had a linux version of 17.0.0.1358 available under virtualbox and tried it there - it worked properly there too ( i.e., the ROLLBACK in fact rolls back the transaction even when doing the call to jtest().)

(07 Aug '16, 23:25) Terry Wilkinson

Thanks for letting us know ... With the limited cycles here, having fewer things to narrow down goes a log way ...

(08 Aug '16, 18:04) Nick Elson S...
2

This looks like this is most likely tied to the server-side "auto_commit" option; a new feature of version 17

I'm still researching how that is actually working under the covers and will cycle back around when I find out more.

(09 Aug '16, 10:25) Nick Elson S...
Replies hidden
1

> ...server-side "auto_commit"; a new feature...

At least The Watcom Rule is in force (the default is 'Off') but seriously?

Does the world really need another way to autocommit?

(09 Aug '16, 13:50) Breck Carter
showing 5 of 6 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×80
×64
×47
×22
×6

question asked: 05 Aug '16, 11:32

question was seen: 187 times

last updated: 09 Aug '16, 14:02