The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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()

asked 18 Oct '11, 08:33

Rune's gravatar image

Rune
2365817
accept rate: 50%

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.)

(18 Oct '11, 09:34) Volker Barth
Replies hidden

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 ;-)

(19 Oct '11, 03:25) Martin

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

(19 Oct '11, 03:55) Volker Barth
1

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.

(19 Oct '11, 09:40) Rune

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.

permanent link

answered 18 Oct '11, 10:01

John%20Smirnios's gravatar image

John Smirnios
8.7k377106
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×10

question asked: 18 Oct '11, 08:33

question was seen: 2,908 times

last updated: 19 Oct '11, 09:40