At 07:12 PM 23/01/2005 +0000, you wrote:

>Sorry, just tested a little additional thing - I executed isql with
>same user (unpriveleged) and I get the same message - but as SYSDBA
>I do not - and I get a lovely little note, but still same thing in DB:
>"Statement failed, SQLCODE = -902".

No gdscode? (9 digits). SQLCODE -902 is very broad-reaching - it
encompasses filesystem permissions problems as well as db user problems and
various sorts of I/O error, including corruption...however, at the end of
the day, it probably only reflects the fact that the server crashed.

What's the "lovely little note"?

>In other words, it works as priveleged user - but not unprivileged.
>This worked fine on Interbase 6.0.

The trouble is, a permissions problem shouldn't cause the server to crash.

>If anyone has any hints, I'm happy to hear them - I couldn't see
>anything from a google of this...
>
>--- In firebird-support@yahoogroups.com, "vandy899" <vandy899@y...> wrote:
> >
> >
> > I'm having trouble with some code that worked on Interbase 6.0.
> >
> > The procedure is as follows:
> > SET TERM ## ;
> > CREATE PROCEDURE WEBSERVICE_CLIENT_INCIDENTS_I (
> > CLIENTID INTEGER,
> > MACHINENAME VARCHAR(20),
> > DELIVERYID INTEGER,
> > LOCATIONID INTEGER,
> > CONTACT VARCHAR(40),
> > PHONE VARCHAR(15),
> > MOBILE VARCHAR(15),
> > ADDR1 VARCHAR(35),
> > CITY VARCHAR(15),
> > STATE VARCHAR(15),
> > POSTCODE VARCHAR(10)
> > ) RETURNS (
> > INCIDENTID INTEGER
> > ) AS
> > DECLARE VARIABLE employeeid integer;
> > DECLARE VARIABLE machineid integer;
> > DECLARE VARIABLE location varchar (60);
> > BEGIN
> > BEGIN
> > location =(ADDR1 ||' '||CITY||' '||STATE||' '||POSTCODE);
> > incidentid = GEN_ID(GEN_SERVICE_INCIDENT, 0)+1;/* DONT INCR */
> > if (machinename <> '') then
> > select machineid from CLI_COMPUTERS
> > where (machine_name=:machinename) and clientid= :clientid


> > into machineid;

should be into :machineid. Fb would have prevented you from compiling this
source. So - the next question is: how and where did you achieve that?

Also, there are accesses in the SP that would require a non-sysdba user
(other than the owner of the accessed objects), and/or the procedure
itself, to require SQL privileges.

> > if (machineid is null) then machineid = 0;
> > select employeeid from cli_shipto S where S.LOCATIONID =
> > :locationid into employeeid;

Another illegal query, not accepted by Fb 1.5. Two corrections: should be:

if (machineid is null) then machineid = 0;
select S.employeeid from cli_shipto S
where S.LOCATIONID = :locationid
into :employeeid;

> > END
> >
> > BEGIN
> > insert into
> >
>service_incidents(incidentid,startedby,clientid,employeeid,machineid,deliveryid,contact,started,
> > mobile,phone,location)
> >
> >
>values(:incidentid,user,:clientid,:EMPLOYEEID,:MACHINEID,:DELIVERYID,:contact,'Now',:MOBILE,
> > :PHONE,:LOCATION);
> > END
> > suspend;
> > END##
> >
> > Upon "select * from WEBSERVICE_CLIENT_INCIDENTS_I ('xxx', 'xxx', 'x',
> > 'xxxx', 'xxxx', 'xx', 'xx', 'xxxxx', 'xxx', 'xxx', 'xxx')" (xs are
> > valid data in app with PHP 4.3.10 (debian) or kinterbasdb (a python
> > firebird interface). Using EXECUTE PROCEDURE doesn't help either.
> > However, if I use isql it works fine.

This is a puzzle and means it's not easy to suppose anything about what's
going on.

> >
> > Is it something I did? The log just said "terminated abnormally",
> > which doesn't tell me much... :(

Well, it tells you that your request crashed the server - which shouldn't
happen at all. You do seem to have filesystem permissions and/or user
privileges involved here at some point, but something is structurally out
of joint too, apparently.

What means did you use to port the database from IB 6? And what version of
Firebird is it, actually? And is the DB dialect 1 or 3? What's the
(ultimate) name of the client library? Is it possible your app is loading
the wrong one? (There are several possible bad scenarios here, depending on
Firebird version and where the client app is connecting from...)

Are any events or UDFs involved in the application code?

./heLen