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.

...and the answer is, from Bruce's comment on his original answer: "@Breck: there is currently no mechanism for automatically recompiling events. You can, of course, put the logic for the event in a procedure which the event calls. – Bruce Hay"


Here's the scenario in 11.0.1.2276: An updated stored procedure event failed at runtime with "column not found" because of a FOR loop which referred to the new name of a column in an existing table when, in fact, the table still had the old column name. An ALTER TABLE was run to fix the column name, and the stored procedure event was re-executed, only to fail with "Invalid statement" (see below). The stored procedure event was recompiled and that got rid of the "Invalid statement" error.

If this is expected behavior, the Help should be updated to mention the possibility that a stored procedure an event should be recompiled:

http://dcx.sybase.com/index.html#1101en/saerrors_en11/errm130.html

Invalid statement 
Error constant SQLE_INVALID_STATEMENT 
ODBC 2 State ERROR 
ODBC 3 State ERROR 
Severity 16 
SQLCODE –130 
SQLSTATE 07W02 
Sybase error code 13628

Probable cause 
The statement identifier generated by a PREPARE passed to the database 
for a further operation is invalid.

asked 19 Nov '10, 11:22

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 22 Nov '10, 16:29

According to the discussion on http://sqlanywhere-forum.sap.com/questions/1226, I would have expected that ALTER TABLE causes all STPs to be recompiled - not sure if there's a misunderstanding on my part or why it hasn't happened here...

(19 Nov '10, 12:32) Volker Barth

Having said that, the help for ALTER PROCEDURE (http://dcx.sybase.com/index.html#1200en/dbreference/alter-procedure-statement.html) implies that RECOMPILE "can be useful if a table referenced by the procedure has been altered to add, remove, or rename columns since the procedure was created." - Yes, I'm puzzled.

(19 Nov '10, 12:34) Volker Barth

@Breck: How exactly do you recompile an event handler (as there is no ALTER EVENT RECOMPILE clause, AFAIK)? ALTER EVENT with the original event handler?

(20 Nov '10, 20:40) Volker Barth

@Volker: I'm old school, every [module].SQL file contains a DROP and CREATE. The DROP is wrapped in a BEGIN with an empty EXCEPTION WHEN OTHERS THEN so it doesn't throw an error on first execution.

(21 Nov '10, 10:01) Breck Carter

@Volker: ...so yeah, technically I don't do "recompile" :)

(21 Nov '10, 10:02) Breck Carter

The most likely cause for the problem described here is that the procedure was still being used by at least one connection when the ALTER TABLE was performed. For example, if the procedure returns a result set and the application cursor had not been closed, then the procedure would still be in use. Once all references to the procedure have completed, the next call will cause the procedure to be "recompiled" automatically.

permanent link

answered 19 Nov '10, 19:34

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

W.r.t. my first comment on the question: So my assumption (ALTER TABLE triggers an automatic recompile of all procedures/functions - at least those not currently used) is generally right?

(20 Nov '10, 12:34) Volker Barth
Comment Text Removed

@Bruce: Arrgh! Not only was the procedure not running at the time of the ALTER TABLE, it isn't even a procedure... it is an event! I have fixed the question.

(20 Nov '10, 15:14) Breck Carter
2

@Volker: procedures not currently in use are recompiled immediately when an ALTER TABLE occurs. Those that are in use will be recompiled when they are no longer in use by any connection, and a new call is made.

(22 Nov '10, 15:02) Bruce Hay
2

@Breck: there is currently no mechanism for automatically recompiling events. You can, of course, put the logic for the event in a procedure which the event calls.

(22 Nov '10, 15:04) Bruce Hay
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:

×106

question asked: 19 Nov '10, 11:22

question was seen: 4,803 times

last updated: 22 Nov '10, 16:29