--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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"?
>

THAT'S the little note: I went looking for a gdscode (as
documentation seems to hint at), but I couldn't see how to get it out.

>
> >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.

>

A non-existant (I checked the permissions - the role adopted by the
user has sufficient privs) permissions problem should *definitely* not
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?
>

"isql-fb -user sysdba -pass lock.smith -i
~/python_daemon/def/WEBSERVICE_STOREDPROC.sql localhost:it320.gdb"
was how it went in - no complaints. Edited it to incorporate new
thing and still works.

> 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.
>

Yes, and it has them - I checked - show grant on service_incidents,
CLI_COMPUTERS, and cli_shipto shows that the role adopted has them.

>
> > > 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...)
>

Dialect is 3, copied database at first, then deleted it (after
shutting down firebird) and used gbak to restore it from old backup.

> Are any events or UDFs involved in the application code?
>
> ./heLen

No events, but thanks for the heads up on the UDF - This isn't my
code, and that was in a trigger - my client used a rather unsafe UDF
that ran whatever command the person liked with the privs of the FB
user - *not* a good thing :) I'm eliminating that with the
introduction of events, tho... :) And I just eliminated it - and the
problem's gone. Thanks lots.


BTW :
libfbclient.so.1 is the ultimate library...

Cheers,

Michael