One curiosity has been bothering me for a while now. We first noticed it with Anywhere version 10.1, and we continue seeing this after our recent upgrade to 12.1.
Background: Every now and then we download a few dozen scripts and use EXECUTE IMMEDIATE to execute the lot of them in one go. If one fails, the 'upgrade' is deemed a failure and human intervention is required.
To the point: The following snippet won't run. Take the embedded code out, and ISQL will execute it just fine. EXECUTE IMMEDIATE does not allow us to use the same cursor name within two completely different procedures...
CREATE OR REPLACE PROCEDURE testscript() begin declare lScript long varchar; set lScript = ' CREATE OR REPLACE PROCEDURE foo () begin declare MyCursor dynamic scroll cursor for SELECT 1; open MyCursor; close MyCursor end; CREATE OR REPLACE PROCEDURE bar () begin declare MyCursor dynamic scroll cursor for SELECT 2; open MyCursor; close MyCursor end;'; EXECUTE IMMEDIATE lscript; end; call testscript();
Result: Could not execute statement. Item 'MyCursor' already exists SQLCODE=-110, ODBC 3 State="42S01" Line 20, column 1
asked 18 Oct '11, 08:33
I can at least explain the difference between your EXECUTE IMMEDIATE statement and invocation in dbisql.
In the case of your EXECUTE IMMEDIATE statement, the pair of statements is being sent to the server as one single block rather than two separate ones.
When you execute that pair of statements in dbisql, dbisql parses them and sends each one individually to the server.
You can get the same error to be reported in dbisql by putting a begin/end around the pair of statements because that will force dbisql to send a single compound statement to the server with both CREATE statements in it.
When both CREATE statements are executed as a single unit by the server, the server is clearly maintaining state from one CREATE statement to the next. To me, that doesn't sound right but perhaps someone more knowledgeable about scoping rules in SQL can chime in.
answered 18 Oct '11, 10:01