Unexpected ISQL behavior (calling a function and commiting)

[VolkerBarth]

Hello,

I've noticed strange behavior using Interactive SQL. I've made a sample script to reproduce the issue. Firstly, we have to create a table and a function that updates that table:

CREATE TABLE IF NOT EXISTS _tmp_t (
    t_id INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,
    t_time TIMESTAMP
);

CREATE OR REPLACE FUNCTION _sp_update_tmp_t()
RETURNS INTEGER
NOT DETERMINISTIC
BEGIN
    UPDATE _tmp_t SET t_time = CURRENT TIMESTAMP;
    RETURN 1;
END;

Then insert one record:

INSERT INTO _tmp_t (t_time) VALUES (CURRENT TIMESTAMP);
COMMIT;

Then check and remember the newly inserted value:

SELECT * FROM _tmp_t;

Next, try to update the value by calling the function that we created above:

SELECT _sp_update_tmp_t();
COMMIT;

Surprisingly (at least, to me), the value is still the same. But if I try to call the function without a COMMIT and then COMMIT separately, the value changes as expected. Also, the value changes if I remove the semicolon after the function call (before the COMMIT):

SELECT _sp_update_tmp_t()
COMMIT;

Can such behavior be explained somehow or is it a bug?

Server (and client): SA16 latest EBF (1691), the same behavior with SA12 and SA11 (not latest EBFs). Platform: Windows.

Thanks.

[VolkerBarth]

How do you check the table's contents? IMHO the "select * from _tmp_t" queries are missing in your test scenario...

You are using DBISQL, not dbisqlc, right?

[former_SQLA_member1694875]

I didn't write the same query again, but of course, I use the same simple query for checking table contents:
SELECT * FROM _tmp_t;
Yes, I'm using DBISQL (not DBISQLC).

[Former Member]

I can reproduce this. The lack of update appears to be a bug. It seems that removing the semi-colon causes the statements to be interpreted as a TSQL batch which exhibits the behaviour you're expecting.

[VolkerBarth]

May this be due to any DBISQL option with influence on the result set display (say, the auto_refetch option)?

[Former Member]

I've actually been able to reproduce the problem outside of Interactive SQL. I'll open a CR to get this fixed.

[Former Member]

Actually. One can recreate this if you start with a newly created database using dbinit (not the create database wizard in SCJView).

It seems unlikely this will be experienced by many customers (given a number of observed factors) and you are likley going to get the server to behave correctly after restarting with the database after the creation of the UDF and table ... but it is still odd ... and we are looking into it more now ...

[Breck_Carter]

I used dbinit, and it did NOT occur.

[Former Member]

Did you autostart the database file?

[Breck_Carter]

No .........

[former_SQLA_member1694875]

I used Create Database Wizard in Sybase Central. I also restarted the service. And I still can reproduce that behavior (same times).

[Breck_Carter]

Can you put a MESSAGE statement in the function to display what CURRENT TIMESTAMP displayed? (in case it is returning the same value, thus causing the symptom).

[former_SQLA_member1694875]

The MESSAGE statement is NOT called as the whole function is NOT. But when I change DBISQL settings about Results processing, it starts to work as expected (see my comment below Nick's answer).

[VolkerBarth]

The moral of the story:

In case of unexpected behaviour with your stored function/procedure/trigger/event , use a MESSAGE statement within the body to make sure your code is called at all...