Hi,

I have a problem with batch handling and functions with direct Data Manipulation Statements (in SQL Anywhere 17.0.11.7312):

1) Select the complete code and execute it in dbisql in one block (F9):

    CREATE OR REPLACE TABLE t1 (c1 integer, c2 varchar(10));
    INSERT INTO t1 (c1,c2) VALUES (1,'AAAA'), (2,'BBBB'), (3,'CCCC');
    COMMIT;
    CREATE OR REPLACE FUNCTION f1 (IN rowin integer, IN updatevalnew varchar(10))
    RETURNS varchar(10)
    NOT DETERMINISTIC
    BEGIN
    DECLARE updatevalold varchar(10);
    SELECT c2 INTO updatevalold FROM t1 WHERE c1 = rowin;
    UPDATE t1 SET c2 = updatevalnew WHERE c1 = rowin;
    RETURN updatevalold
    END;
    SELECT * FROM t1;
    SELECT f1(1,'DDDD');
    SELECT * FROM t1;
    SELECT f1(1,'DDDD');
    UPDATE t1 SET c2 = f1(1,'EEEE') WHERE c1 = 2;
    SELECT * FROM t1;

Here ist the result of the complete code execution:

/*
3 row(s) inserted
         c1 c2         
----------- ---------- 
          1 AAAA       
          2 BBBB       
          3 CCCC       
(3 rows)

f1(1,'DDDD') 
------------ 
AAAA         
(1 rows)

         c1 c2         
----------- ---------- 
          1 DDDD       
          2 BBBB       
          3 CCCC       
(3 rows)

f1(1,'DDDD') 
------------ 
DDDD         
(1 rows)

1 row(s) updated
         c1 c2         
----------- ---------- 
          1 EEEE       
          2 DDDD       
          3 CCCC       
(3 rows)
*/

DROP FUNCTION f1;
DROP TABLE t1;

2) Now execute the same code statement by statement, starting from the first select (the output is allways in the comment after the statement):

CREATE OR REPLACE TABLE t1 (c1 integer, c2 varchar(10));
INSERT INTO t1 (c1,c2) VALUES (1,'AAAA'), (2,'BBBB'), (3,'CCCC');
COMMIT;
CREATE OR REPLACE FUNCTION f1 (IN rowin integer, IN updatevalnew varchar(10))
RETURNS varchar(10)
NOT DETERMINISTIC
BEGIN
DECLARE updatevalold varchar(10);
SELECT c2 INTO updatevalold FROM t1 WHERE c1 = rowin;
UPDATE t1 SET c2 = updatevalnew WHERE c1 = rowin;
RETURN updatevalold
END;
SELECT * FROM t1;
/*
3 row(s) inserted
         c1 c2         
----------- ---------- 
          1 AAAA       
          2 BBBB       
          3 CCCC       
(3 rows)
*/

SELECT f1(1,'DDDD');
/*
f1(1,'DDDD') 
------------ 
AAAA         
(1 rows)
*/

SELECT * FROM t1;
/*
         c1 c2         
----------- ---------- 
          1 DDDD       
          2 BBBB       
          3 CCCC       
(3 rows)
*/

SELECT f1(1,'DDDD');
/*
f1(1,'DDDD') 
------------ 
DDDD         
(1 rows)
*/

UPDATE t1 SET c2 = f1(1,'EEEE') WHERE c1 = 2;
/*
1 row(s) updated
f1(1,'DDDD') 
------------ 
EEEE         
(1 rows)
*/

SELECT * FROM t1;
/*
         c1 c2         
----------- ---------- 
          1 DDDD       
          2 DDDD       
          3 CCCC       
(3 rows)
*/

DROP FUNCTION f1;
DROP TABLE t1;

After the different executions I have two different results in table t1:

1)
         c1 c2         
----------- ---------- 
          1 EEEE       
          2 DDDD       
          3 CCCC

2)
         c1 c2         
----------- ---------- 
          1 DDDD       
          2 DDDD       
          3 CCCC

My first idea was that it has to do with the execution in dbisql. But I tried different settings ("Commit after every statement" or "Commit on exit or disconnect") without getting other results. Now I think this is because of the different scope of a batch in both situations.

It makes it hardly to test! How can I test it to get identical results?

Thanks

asked 09 May, 04:37

Robert%20Kratschmann's gravatar image

Robert Krats...
1165715
accept rate: 0%

edited 09 May, 05:06

Volker%20Barth's gravatar image

Volker Barth
39.9k360547816


Hm, I can verify the results with v17.0.1.7312.

I very wildly guess or suspect the difference might have to do with DBISQL's behaviour to sometimes "double" its query executions in order to describe result sets accordingly or to update their display...

If I uncheck the option "Automatically refetch results" in the "Options/SQL Anywhere/Results" pane, both ways to execute the queries return the same final result set with "1 EEEE" as first row.

I guess a request level log would show possible differences during query execution. AFAIK, DBISQL is not necessarily handling queries the same way an ordinary application would do - it has its pecularities based on its usage a generic SQL tool...

permanent link

answered 09 May, 04:59

Volker%20Barth's gravatar image

Volker Barth
39.9k360547816
accept rate: 34%

edited 09 May, 05:06

Hi Volker,

thanks!

You are right the differences are gone when I use the "Scrollable Table" result diaply. I used the "Text" setting, where the differences exists.

(09 May, 05:21) Robert Krats...
Your answer
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:

×105
×24
×8

question asked: 09 May, 04:37

question was seen: 283 times

last updated: 09 May, 05:21