Re: PROBLEMS ON STORED PROCEDURES (Procedure not found)
}..
}> I am using Informix5.03 running on SunOS UNIX. I use stored
}procedures
}> in my 4gl modules. Everything was ok until I got the following error
}message:
}>
}> --------------------------------------------------
}> Program stopped at "obs.4gl", line number 722.
}> SQL statement error number -674.
}> Procedure (oidb.getprogsofstudent) not found.
}> SYSTEM error number -111.
}> ISAM error: no record found.
}> --------------------------------------------------
}>
}> The interesting thing is that the owner of the procedure
}> "getprogsofstudent" is "omer", not "oidb". The procedure exists,
}> and when I execute it from dbaccess, it works properly. Moreover,
}> there are some other procedures whose owner are "oidb" and programme
}> gives an error "procedure (omer.######) not found". By the way,
}> my 4gl module does not crash in the same procedure when I run
}> it at the different times. Today's "menu" is <getprogsofstudent>,
}> "chief's advise" in tomorrow may be <defineStudentId> :-)
}..
}
}Dear Omer
}
}We encountered similar problems while using stored procedure from Visual
}Basic. You can try to grant execute permission for the individual user to
}enable them to execute the procedure.
}
}It was a bug on our version of Informix OnLine. We found out that the owner,
}Informix did not encounter the error but other users, james, etc did. We had
}to grant execute permission to james for the specific stored procedure. The
}user public did have the execute permission but it did not help.
}
}Good luck.
}
}Best Regards
}Jessica
Well, since Omer is out for holiday I will try to explain how we
cured the problem. Omer had reported the problem to Informix Tech.Supp.
over here who, after not being able to solve it, handed it over to Tech.
Supp. at England. However 3 weeks of waiting was more than I could afford
so I decided to have a look.
The stored procedures seemed to work fine from dbaccess and from a
simple 4GL that did nothing more than calling a single stored procedure.
Omer's code had 15-20 cursors declared and I observed that the problem
disappeared when several of them were commented out. Also, when I moved the
cursor declaration of an EXECUTE PROCEDURE statement to a point just before
its use, that stored procedure executed without any problem (however it
would then fail on another). We were not able to locate the limit for the
number of cursor declarations in a 4GL module in the manuals. Even if we
did, we would prefer to get some "Too many cursor declarations" error
rather than "Procedure ... not found".
Now, Omer is declaring most of the cursors prior to their use and
FREEing afterwards.
======================================================================
Ayhan Ergul Internet: <ergul@rorqual.cc.metu.edu.tr>
ROAS Project Manager at METU Computer Center
Research Assistant at President's Office Computer Center
Middle East Technical University Ankara, Turkiye
______________________________________________________________________
A hundred years in the sunshine hasn't taught me all there is to know.
-- Wishbone Ash