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.
That seems to be an issue of materialization . . .
I'm betting you are running DBISQL configured to not return multiple result sets, and not to show each result set (if running as a batch). When DBISQL runs that way it
it just fine! BUT Never fetches from it.
Without that fetch, the first row does not materialize and that can result in no call to the function. No call, no updates ... or other side-effects.
So if this is your case, you can configure Tools >> Options >> SQL Anywhere >> Results Processing to "Show results from each statment" and "Show all results" ... and then it should behave the way you are probably expecting.
Let us know if that changes the behaviour issue in your setting.
Despite the fact that Mike (here) was able to recreate the behaviour, I too, like Breck, could not.
I've tried this both ways on 12.0.1 and 16 ... and get different times each time ... The latest script Arthoor provided returns this result (from my latest run): t_number,t_time 1, '2014-01-16 12:18:05.252' 2, '2014-01-16 12:18:10.322'
So that would only seem to leave some machine specific behaviour or some option setting.
answered 16 Jan '14, 12:23
Nick Elson S...
Please doublecheck your testing method... I can't repeat your results with 220.127.116.111 or 18.104.22.16898
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; INSERT INTO _tmp_t (t_time) VALUES (CURRENT TIMESTAMP); COMMIT; SELECT 'first', * FROM _tmp_t; WAITFOR DELAY '00:00:01'; SELECT _sp_update_tmp_t(); COMMIT; SELECT 'second', * FROM _tmp_t; 'first' t_id t_time ------- ----------- ----------------------- first 1 2014-01-16 09:06:18.803 _sp_update_tmp_t() ------------------ 1 'second' t_id t_time -------- ----------- ----------------------- second 1 2014-01-16 09:06:19.896
answered 16 Jan '14, 09:02
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 ...
answered 16 Jan '14, 15:00
Nick Elson S...
If dbisql is configured to display multiple result sets you won't see this behaviour (mostly for Breck's benefit) ... still investigating ...
answered 17 Jan '14, 16:11
Nick Elson S...