SQL Function and batch handling

[robert_kratschmann]

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

[VolkerBarth · Accepted solution]

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...

[robert_kratschmann · Accepted solution]

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.