RE: Stored Proc. What's missing
Yes, he was using ISQL rather than DB-Access.
ISQL doesn't handle CREATE PROCEDURE statements because it was originally
written long before CP statements came on the scene. Until CP statements
arrived, you could tell when you reached the end of a statement because you
either reached a semi-colon (not embedded in a string or comment, of
course) or the end of the 'file'. This made life relatively easy. Then
the CP statement arrived, and SPL being a completely botched language
requires semi-colons to mark the ends of intermediate statements as well as
keywords to introduce the start of the next statement - redundancy. And,
because those semi-colons occur part way through the statement, ISQL's
simple statement matching scheme fails. Obviously, DB-Access got updated
to recognize the CP statement (so did SQLCMD, and it was a real pain to
have to do it!), but the ISQL maintenance team never got the mandate to
update ISQL to match.
If you use ISQL to execute a CP statement, the error always occurs
immediately after the first semi-colon in the SP - either at the end of the
RETURNING clause or, typically, after the first DEFINE.
Yours,
Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>
----------
From: Don Simpson[SMTP:dons@alldata.com]
Sent: Monday, April 20, 1998 1:19 PM
To: informix-list@iiug.org
Subject: Re: Stored Proc. What's missing
Are you using isql? I have run into problems before using isql to create
SPs. When I use dbaccess it works just fine. I have no idea why isql
does not work when dbaccess does. Anyone?
BTW, I used dbacccess to create this SP and it works. I used isql and got
the same error you did.
Don.
Robert Ogren wrote:
> Question:
> This is more than likely a simple one but I am not seeing what would be
> causing this not to work.
>
> create procedure er_raise_error(laerrId int, laerrMsg char(255))
> raise exception laerrId,0,laerrMsg;> 201: A Syntax error occurred ^> end procedure;
>
> In a trigger I call the above Procedure.
> Example:
> execute procedure er_raise_error(-20012,'tblInvDelTrig: Cannot delete
> because foreign keys still exist in tblRacktblInv ')>
> I have permission to create procedures in the DB.
>
> Any help is appreciated.
>
> Thanks
>
> Rob
> rmogren@gte.net