Item 'xx' already exists (Execute immediate -- checking syntax too strictly?)

[Former Member]

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

call testscript()

[VolkerBarth]

Even adding a "DROP PROCEDURE foo;" between the two CREATE PROCEDURE statements doesn't solve the issue. Looks like a bug, methinks...

(I've tested with SA 12.0.1.3389.)

[MCMartin]

I would agree, obviously the interpreter is not honoring the end of the procdure declaration. Anyway Rune you have a simple solution just don't reuse any object names 😉

[VolkerBarth]

...or put each statement in a separate EXECUTE IMMEDIATE call:)

[Former Member]

That would require some deep modifications within our update-regime.

We typically send an update script of half a MB (on average) to each of our databases.

My predecessors spawned ISQL to execute these... However, that approach have some headaches of its own (especially when upgrading from Anywhere v10 to v12 -- %SQLANY10% suddenly points nowhere). We never got good error handling out of that solution.

EXEC IMM seemed like the proper way (no need to mess with external files), but this puts some serious limits on the way we write our code! (I have a related question about that... Might start a new thread)

If something could be done in an EBF it would be most helpful to us.

[johnsmirnios]

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.