At 11:37 AM 30/10/2003 +0100, you wrote:

>Hi all,
>
>we are experiencing a very strange situation where the firebird server
>crashes with an ISC ERROR CODE of 335544741. We have tried almost
>everything, and with every FB recent release (ie, 1.0.3 thru 1.5RC4),
>but without luck. We are still wondering if it's a FB related bug

No

>or if it's raised by a client app with a bad behaviour.

Bad SQL syntax..

>The context: the client app is very complicated, but we were able to
>isolate the query that apparently trash the server:

The problems:
First, lack of column identifiers:

> SELECT IDOperazione, IDOperazioneCiclo, IDTipologia, IDFamiglia
> FROM AM$Operazioni o

Replace with
SELECT o.IDOperazione, o.IDOperazioneCiclo, o.IDTipologia, o.IDFamiglia
/* plus the output columns from the stored procedure */
FROM AM$Operazioni o

Next, a join with no join conditions. You have to join one set ON another
set by way of a linking key and you should usually have columns from both
sets in the SELECT list -- otherwise there's not (usually) a reason to have
a join at all (how can the optimizer make a river out of one stream?).

You can't join a table to a stored procedure that doesn't generate any
output. It has to be a selectable SP with its output row specified in the
RETURNS clause of the SP and its rows being output by means of a loop that
calls SUSPEND.

If any of the meantioned features aren't present in your SP, then you are
in server-crash country. If they are, then press on...

> LEFT JOIN AM$CicloVariante(:IDVARMOD, o.idoperazione, null) on 1=1
> WHERE o.zona=:CODZONA and o.operazione=:CODOPZ
>
>AM$CicloVariante is a selectable SP, that basically expand a tree of
>linked objects into a tabular data set.

I have no idea what you wanted to get from this query so I can't even
suggest what the replacement join condition should be, but it will need to
be something like
LEFT JOIN AM$CicloVariante(:IDVARMOD, o.idoperazione, null) a
ON a.SomeOutputField = o.SomeMatchingKey
WHERE ...

>The client app loops over that result, with this kind of code:

followed by a whole heap of stuff that ought to be in a stored procedure,
not walking all over a whole heap of derived data that's sitting there in
an uncommitted state.

What I'm getting at is - whilst you're fixing up your SP, why not move all
that client-side cursor stuff over to the server, where it belongs? (Yes,
I'm guessing that your Delphi module is a server-side service module of
some kind - I mean, *inside* the database, where it belongs..)

Helen