On 2016-03-18 9:10, Joje joje@... [firebird-support]
wrote:

> I am executing this query using my .net application and it is not
> throwing any error. If there was a error then it would log that error
> and will display it to the user.

If it is not throwing an error, then I would expect that it just
executed fine. I am a little confused about the nature of your problem:
Is the problem with executing from your .NET application, or is it with
executing as shown below? Or do you think it isn't working in .NET,
because it doesn't work below?

If the problem is with executing from .NET, how did you verify that it
didn't have the intended effect?

> I executed this query in my third party SQL Manager and it throws an
> error
> _Invalid token._
> _Dynamic SQL Error._
> _SQL error code = -104._
> _Unexpected end of command - line 30, column 36._
>
> line number 30 has this statement "DECLARE VARIABLE URL_COUNT
> INTEGER;"

This problem is not with Firebird itself, but with the tool you use to
execute the statement. Firebird executes queries one at a time (trying
to execute multiple statements would result in syntax errors), while a
lot of query tools allow you to execute multiple statements. They
usually do this by splitting on semicolons (;) and sending the
individual statements to Firebird one by one. This works fine for normal
statements, but doesn't work for PSQL, as inside PSQL the semicolons are
statement terminators. Splitting on the semicolon here would lead to an
incomplete statement (hence the "Unexepcted end of command" error).

In a lot of tools (like ISQL and Flamerobin), you need to explicitly
switch the statement terminator to something else (using SET TERM), but
it looks like your tool has some heuristics to recognize statements with
PSQL blocks. It seems to work for ALTER PROCEDURE, but apparently it
doesn't know RECREATE PROCEDURE, and therefor it just splits on the
semicolon and sends an incomplete statement. Check the documentation if
it supports explicitly switching the statement terminator (eg using SET
TERM).

> But the thing to note is that executing same query with ALTER will
> work. As this query was big and important in order to avoid any
> mistake i thought it was better to use RECREATE rather than ALTER. As
> recreate drops previous procedure and creates a new one.
>
> If there is an dependency then firebird should have sent an error to
> application. Here there was no error as such.

Dropping is not possible if there is a dependency, as far as I know
altering is possible when there are dependencies (I might be wrong on
this point though).

Mark