The warning "CURSOR OPTION VALUES CHANGED" (SQLCODE +121) is not an
error, and I am confident that it has nothing to do with being unable to
find table TEMP, which should give you a different error (SQLCODE -141,
Table Not Found).

Please post your exact build of 902, your complete repro, and the error
message that you get.

One thing that you can try is to not use the T-SQL INTO syntax, but
instead use an explicit DECLARE TEMPORARY TABLE and see if that works as
you expect.

Glenn

Mark wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

I am using ASA 9.01 and 9.02 with a Delphi 7 client/server
application, ASTA middleware, and ODBCExpress dataset components. In
ASA 9.01, the following code is executed through one dataset:

SELECT id, internal_id INTO #Temp1 FROM Clients
WHERE shelter_date >= '2007-1-1' AND shelter_date <= '2007-6-30'
AND c_agy_id = 4;

SELECT MAX(id) as id INTO #Temp FROM #Temp1
WHERE internal_id IS NOT NULL
GROUP BY internal_id;

CREATE INDEX UniqueIndex ON #Temp (id);

SELECT COUNT(*) as CX FROM Clients
JOIN #Temp T ON T.id = Clients.id
<various WHERE clauses>

Then a second dataset issues another set of queries that begin as
follows:

'SELECT COUNT(*) as CX FROM Clients
JOIN #Temp T ON T.id = Clients.id
<various WHERE clauses>

In ASA 9.01, this works perfectly. In ASA 9.02, it fails with the
message that table Temp cannot be found. The specific error in the log
is: code: 121 "Cursor option values changed".

The first dataset component has a cursor type of Forward Only and a
concurrency type of Values (Cursor uses optimistic concurrency
control, comparing values). I have tried many different combinations
of cursor type and concurrency to try to overcome this behavior change
in ASA 9.02. At this point, I'm stuck. Any suggestions would be very
much appreciated.

Mark

Glenn,

Thank you for your response. The ASA version is 9.02.3557. Shown below
is the log that provides the code and error messages under ASA 9.02:

10/15 19:28:55.775 ** REQUEST conn: 4 STMT_PREPARE
[large string unavailable]
10/15 19:28:55.775 ** EXTRA conn: 4 SELECT id, ssn INTO #Temp1
FROM Clients WHERE shelter_date >= '2006-1-1' AND shelter_date <=
'2007-1-31' AND c_agy_id = 26 ; SELECT MAX(id) as id INTO #Temp FROM
#Temp1 WHERE ssn IS NOT NULL GROUP BY ssn; CREATE INDEX UniqueIndex ON
#Temp (id); SELECT COUNT(*) as CX FROM Clients JOIN #Temp T ON T.id =
Clients.id WHERE shelter_date >= '2006-1-1' AND shelter_date <=
'2007-1-31' AND (transferred IS NULL OR transferred = 'N') AND
c_agy_id = 26 AND (client_age > 21 )
10/15 19:28:55.785 ** DONE conn: 4 STMT_PREPARE
Stmt=131138
10/15 19:28:55.785 ** REQUEST conn: 4 STMT_DESCRIBE_OUTPUT
Stmt=-1
10/15 19:28:55.785 ** DONE conn: 4 STMT_DESCRIBE_OUTPUT
10/15 19:28:55.785 ** REQUEST conn: 4 STMT_DESCRIBE_INPUT
Stmt=-1
10/15 19:28:55.785 ** DONE conn: 4 STMT_DESCRIBE_INPUT
10/15 19:28:55.785 ** REQUEST conn: 4 COMMIT
10/15 19:28:55.785 ** DONE conn: 4 COMMIT
10/15 19:28:55.786 ** REQUEST conn: 4 CURSOR_OPEN
Stmt=131138
10/15 19:28:56.160 ** WARNING conn: 4 code: 121 "Cursor option
values changed"
10/15 19:28:56.160 ** DONE conn: 4 CURSOR_OPEN
Crsr=65603
10/15 19:28:56.160 ** REQUEST conn: 4 CURSOR_DESCRIBE_OUTPUT
Crsr=65603
10/15 19:28:56.160 ** DONE conn: 4 CURSOR_DESCRIBE_OUTPUT
10/15 19:28:56.160 ** REQUEST conn: 4 CURSOR_DESCRIBE_INPUT
Crsr=65603
10/15 19:28:56.160 ** DONE conn: 4 CURSOR_DESCRIBE_INPUT
10/15 19:28:56.161 ** REQUEST conn: 4 ROW_DESCRIPTOR
Crsr=65603
10/15 19:28:56.161 ** DONE conn: 4 ROW_DESCRIPTOR
10/15 19:28:56.161 ** REQUEST conn: 4 CURSOR_FETCH
Crsr=65603, Count=1, Offset=1 (relative)
10/15 19:28:56.198 ** DONE conn: 4 CURSOR_FETCH
10/15 19:28:56.198 ** REQUEST conn: 4 CURSOR_PREFETCH
Crsr=65603
10/15 19:28:56.198 ** WARNING conn: 4 code: 100 "Row not found"
10/15 19:28:56.198 ** DONE conn: 4 CURSOR_PREFETCH
10/15 19:28:56.198 ** REQUEST conn: 4 COMMIT
10/15 19:28:56.198 ** DONE conn: 4 COMMIT
10/15 19:28:56.198 ** REQUEST conn: 4 COMMIT
10/15 19:28:56.198 ** DONE conn: 4 COMMIT
10/15 19:28:56.199 ** REQUEST conn: 4 COMMIT
10/15 19:28:56.199 ** DONE conn: 4 COMMIT
10/15 19:28:56.199 ** REQUEST conn: 4 COMMIT
10/15 19:28:56.199 ** DONE conn: 4 COMMIT
10/15 19:28:56.199 ** REQUEST conn: 4 STMT_PREPARE
"SELECT COUNT(*) as CX FROM Clients JOIN #Temp T ON T.id = Clients.id
WHERE shelter_date >= '2006-1-1' AND shelter_date <= '2007-1-31' AND
(transferred IS NULL OR transferred = 'N') AND c_agy_id = 26 AND
(client_age IS NOT NULL ) "
10/15 19:28:56.199 ** DONE conn: 4 STMT_PREPARE
Stmt=196676
10/15 19:28:56.199 ** REQUEST conn: 4 STMT_DESCRIBE_OUTPUT
Stmt=-1
10/15 19:28:56.199 ** ERROR conn: 4 code: -141 "Table '#Temp'
not found"
10/15 19:28:56.199 ** DONE conn: 4 STMT_DESCRIBE_OUTPUT
10/15 19:28:56.199 ** REQUEST conn: 4 STMT_DROP
Stmt=196676
10/15 19:28:56.199 ** DONE conn: 4 STMT_DROP
10/15 19:28:56.199 ** REQUEST conn: 4 COMMIT
10/15 19:28:56.199 ** DONE conn: 4 COMMIT
10/15 19:28:56.199 ** REQUEST conn: 4 CURSOR_CLOSE
Crsr=65603
10/15 19:28:56.199 ** DONE conn: 4 CURSOR_CLOSE

Here is the log for the same process that succeeds under ASA 9.01:

10/25 20:58:39.507 ** REQUEST conn: 4 STMT_PREPARE
[large string unavailable]
10/25 20:58:39.507 ** EXTRA conn: 4 SELECT id, ssn INTO #Temp1
FROM Clients WHERE shelter_date >= '2005-1-1' AND shelter_date <=
'2007-1-31' AND c_agy_id = 4 ; SELECT MAX(id) as id INTO #Temp FROM
#Temp1 WHERE ssn IS NOT NULL GROUP BY ssn; CREATE INDEX UniqueIndex ON
#Temp (id); SELECT COUNT(*) as CX FROM Clients JOIN #Temp T ON T.id =
Clients.id WHERE shelter_date >= '2005-1-1' AND shelter_date <=
'2007-1-31' AND (transferred IS NULL OR transferred = 'N') AND
c_agy_id = 4 AND (client_age > 21 )
10/25 20:58:39.522 ** DONE conn: 4 STMT_PREPARE
Stmt=131136
10/25 20:58:39.523 ** REQUEST conn: 4 COMMIT
10/25 20:58:39.523 ** DONE conn: 4 COMMIT
10/25 20:58:39.523 ** REQUEST conn: 4 CURSOR_OPEN
Stmt=131136
10/25 20:58:39.577 ** DONE conn: 4 CURSOR_OPEN
Crsr=65601
10/25 20:58:39.586 ** WARNING conn: 4 code: 100 "Row not found"
10/25 20:58:39.586 ** REQUEST conn: 4 COMMIT
10/25 20:58:39.586 ** DONE conn: 4 COMMIT
10/25 20:58:39.586 ** REQUEST conn: 4 STMT_PREPARE
"SELECT COUNT(*) as CX FROM Clients JOIN #Temp T ON T.id = Clients.id
WHERE shelter_date >= '2005-1-1' AND shelter_date <= '2007-1-31' AND
(transferred IS NULL OR transferred = 'N') AND c_agy_id = 4 AND
(client_age IS NOT NULL ) "
10/25 20:58:39.586 ** DONE conn: 4 STMT_PREPARE
Stmt=196674
10/25 20:58:39.587 ** REQUEST conn: 4 COMMIT
10/25 20:58:39.587 ** DONE conn: 4 COMMIT
10/25 20:58:39.587 ** REQUEST conn: 4 CURSOR_OPEN
Stmt=196674
10/25 20:58:39.588 ** DONE conn: 4 CURSOR_OPEN
Crsr=131139
10/25 20:58:39.588 ** WARNING conn: 4 code: 100 "Row not found"
10/25 20:58:39.589 ** REQUEST conn: 4 CURSOR_CLOSE
Crsr=65601
10/25 20:58:39.589 ** DONE conn: 4 CURSOR_CLOSE

Mark

The problem is a result of the temporary table being created within a
"batch", i.e. there are two SELECTs executed together, separated by a
semi-colon. At the end of the batch, the server drops the temporary table.
The following, executed from DBISQL, shows the same behavior:

begin
select 1 as c1,'abc' as c2 into #temp;
select * from #temp
end
go
select * from #temp

I was able to see the same behavior in 9.0.1.1863, so I'm not sure why your
two versions are behaving differently.

The solution is to execute the SELECT which creates the temporary table by
itself. The table will then not be dropped by the server unless you
explicitly drop it or the connection disconnects.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"Mark" <infocus (AT) swbell (DOT) net> wrote

I am reasonably confident that the behaviour change you are running into
is that in 9.0.2 CREATE INDEX on a temporary table performs an automatic
COMMIT, whereas in previous releases that did not occur (though the
documentation said that it should). The reason that the COMMIT is
necessary is to properly handle transactions that perform a ROLLBACK.

With the implicit COMMIT, my guess (without seeing all of your
application logic) is that #TEMP has now gone out of scope when the
second dataset is executed. To test the theory, you can remove the
CREATE INDEX statement from your application, and see if everything
works correctly.

The easiest workaround for you is to explicitly declare the local temp
table, and perform the CREATE INDEX, prior to doing any DML operations.

Glenn

Mark wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

Glenn & Bruce,

Thank you both very much for your suggestions. Here is what I've
learned:

1. Removing the CREATE INDEX statement, by itself, does not solve the
problem.

2. If the batch statement that creates the temporary tables is
deconstructed and each action is executed separately, then the scope
of the Temp temporary table is preserved so that it can be "seen" by
the SQL in the second dataset. Problem solved!

BTW, the ASA 9.01 build where all works fine is 1751.

One final question. How can I check to see if a temporary table exists
and, if so, drop it?

Mark

To drop a temporary table when you don't know whether or not it exists, you
can enclose the DROP within a BEGIN ... END block that has an EXCEPTION
clause, as in:
begin
drop table #temp;
exception when others then
return;
end
The block could also check for the more specific "table not found" error
('42W33').

Usually it's best to avoid this by either creating and using the temp table
within a procedure or BEGIN ... END block such that the table is
automatically dropped, or by creating the table once using CREATE GLOBAL
TEMPORARY TABLE so it's always available. In the latter case, you just need
to truncate the table before populating it.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"Mark" <infocus (AT) swbell (DOT) net> wrote