SAP SQL Anywhere

Question

Based on Breck's helpful suggestion, I have tried to build a reproducible for that other FAQ:

First, create a fresh v12.0.1 database (I've used 12.0.1.4278) with default settings and launch DBISQL (or dbisqlc):

MD MyTestDb
CD MyTestDb
"%SQLANY12%\bin64\dbinit" MyTest.db
"%SQLANY12%\bin64\dbisql" -c "DBF=.\MyTest;UID=DBA;PWD=sql"

Here's a small code snippet for a stored function that makes dummy entries to a dummy table and uses WAITFOR in order to make it look like "hard word" (likewise, the parameters and the used savepoint logic are just attempts to mimic the real function's inner workings):

drop table if exists dbo.MyTestTable;
create table dbo.MyTestTable
(
   pk            int not null      default autoincrement primary key,
   lastChanged   datetime not null default timestamp
);

create or replace function dbo.MyFctn(nPkTest int, nSecondsToWait int) returns int
not deterministic
begin
    declare bSuccess int;
    declare nResult int = -1;
    savepoint strSavepoint;
    message '';
    message 'MyFctn with pk ' || nPkTest || ' waiting for ' || nSecondsToWait || ' seconds...';

    -- limit timespan to one minute
    set nSecondsToWait = greater(nSecondsToWait, 0);
    set nSecondsToWait = lesser(nSecondsToWait, 60);
    set bSuccess = if nSecondsToWait > 0 then 1 else 0 end if;
    waitfor time dateadd(ss, nSecondsToWait, current time);

    -- dummy DML to make the function really not-deterministic
    insert dbo.MyTestTable values (default, default);

    if bSuccess <> 0 then
        release savepoint strSavepoint;
        set nResult = nSecondsToWait;
    else
        rollback to savepoint strSavepoint;
    end if;

    message 'MyFctn: Result = ' || nResult;

    return nResult;
end;

-- inital test call
select dbo.MyFctn(10, 2);

Here's a second script that is used for a (fake) test: dump the output of the table, make some function calls (with non-identical arguments) and dump again and calculate the elapsed time.

-- Test script as a bunch of SQL statements (no code blocks):

create variable tsStart timestamp;
create variable nDurance bigint;
create variable varSaVersion varchar(4);
create variable nFunctionCallsBefore int;
create variable nFunctionCallsAfter int;
set varSaVersion = 'SA' || left(@@version, 2);

message 'MyFctn - Test starts' to log;

-- Store state before test calls and dump table contents
set tsStart = current timestamp;
set nFunctionCallsBefore = (select count(*) from dbo.MyTestTable);

select * from MyTestTable order by 1;
output to 'LogTableBefore.txt' format text quote ''; 

-- Make several calls with different arguments
select dbo.MyFctn(0, 5);
select dbo.MyFctn(0, 6);
select dbo.MyFctn(0, 7);
select dbo.MyFctn(0, 8);
select dbo.MyFctn(0, 9);
select dbo.MyFctn(0, 10);

-- Store state after test calls and dump table contents
set nFunctionCallsAfter = (select count(*) from dbo.MyTestTable);

select * from MyTestTable order by 1;
output to 'LogTableAfter.txt' format text quote ''; 

-- Calculate delay and message results
set nDurance = datediff(ms, tsStart, current timestamp);
message 'MyFctn with ' || varSaVersion || ' - Durance ' || nDurance || ' ms.' to log;
message 'MyFctn with ' || varSaVersion || ' - Additional number of calls ' || nFunctionCallsAfter - nFunctionCallsBefore || '.' to log;
message 'MyFctn - Test finished' to log;

drop variable tsStart;
drop variable nDurance;
drop variable varSaVersion;
drop variable nFunctionCallsBefore;
drop variable nFunctionCallsAfter;

-- Show results from log
select msg_text, msg_time from sa_server_messages()
where msg_text like 'MyFctn%' order by msg_id;

When running that script, DBISQL will only make the function calls when the following option (on the left) is set to "each statement":

alt text

That's what the result of the console log will look like:

alt text

In contrast, in case the left option is set to "Show results from the last statement", the functions (i.e. the statements "select dbo.MyFctn(0, 5);" and the following lines) do not get called, as the console output clearly shows:

alt text

I would certainly not expect that "not showing a result set" would mean "not calling that statement", particularly as v12 has deprecated the "CALL myFctn" syntax...

Answer 1

I think I can shed some light on this.

When DBISQL 12 runs a statement, it executes it (sends it to the server for compilation and execution), and then it reads the results back (if any). When you execute a batch of statements, DBISQL executes every statement. Whether it reads the results or not is controlled by the option "Show results from the last statement" / "Show results from each statement". If you select the former (which is the default), the results from the last statement are read, but results from the other statements are not read.

For some SELECT statements, the database defers some of the computation until the client actually asks for the results. The rationale is that if the client don't ask for the results, they must not matter, and the server can save itself some work.

In the posted code above, the client is invoking the function MyFctn with a SELECT statement. Suppose DBISQL is configured to show results from only the last statement in a batch. The first SELECT statement will be executed, but its results will not be fetched. The server has determined that it can defer the work for that SELECT statement until the first row is fetched, but DBISQL never fetches that row, so the body of the function is not executed. If you configure DBISQL to show results from every statement, fetching the results will cause the function to be executed.

If the function had been executed using a CALL statement, then it is executed unconditionally.

Consider this code:

create or replace function test( value int ) returns int
begin
    message 'test( ' || value || ' )';
    return 123;
end;

message '-------------';
select test(1);
select test(2);
select test(3);

call test(4);
call test(5);
call test(6);

If you set DBISQL to show only the last result set, you will see the following messages in the database server window:

-------------
test(4)
test(5)
test(6)

Because DBISQL did not fetch the results, the SELECT statements are essentially noops. The three messages were from the CALL statements.

If you set DBISQL to show results from all statements, you will see this:

-------------
test(1)
test(2)
test(3)
test(4)
test(5)
test(6)

This behavior has been simplified in DBISQL 17. It fetches all results from all statements. I think that gives you the results you had initially expected.

Answer 2

I have found that when I have multiple select statements that call functions that return results, I have to use a begin / end and select the results into a variable to get them all to execute.

Your "SET myVar = test();" becomes my:

BEGIN
declare myVar long varchar;
select test(1) into myVar where Column1 = 1;
select test(2) into myVar where Column2 = 2;
select test(3) into myVar where Column1 = 3;
END

I had always suspected it was because DBISQL didn't have a way to handle the multiple result sets, but I never dug into it. Once I had this as the answer I used it and moved on.

Note that I'm not storing myVar and I don't care to return its results. For my case I just needed it to be sure each procedure call executed.

SAP SQL Anywhere

May DBISQL skip SELECT statements in a SQL batch? - Part II

Follow this question

Related questions