Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

  declare lScript long varchar;
  set lScript = '
      declare MyCursor dynamic scroll cursor for SELECT 1;
      open MyCursor; 
      close MyCursor

      declare MyCursor dynamic scroll cursor for SELECT 2;
      open MyCursor; 
      close MyCursor  
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

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

(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

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
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 18 Oct '11, 08:33

question was seen: 4,785 times

last updated: 19 Oct '11, 09:40