At 08:28 AM 13/04/2005 +0000, you wrote:

>After upgrading from Firebird 1.0.3 to 1.5 (including backup and
>restore of database file) a long-running query crashed with error
>codes (as shown by IB_SQL from ibobjects):
>SQL ERROR CODE: -901
>ISC ERROR CODE: 335544364 (request synchronization error)

OK, I think you will find this error occurs because the client has sent a
Fetch request for the next row from the SP's output buffer but the
connection to the database has been lost.

>Detail description of situation:
>Old system
>PC P4 3GHz 1 GB RAM OS M$ Windows XP SP2, etc...
>Firebird 1.0.3 WI-V6.2.972
>
>New system
>The same hardware and OS (it is the same machine)
>Firebird 1.5 V.1.5.2.4731
>
>Query type
>
>SELECT *
>FROM SPS_CONTRACT$RESULT_BOOK(:APRODUCT_YEAR_EDITION
>, :APRODUCT_TYPE
>, :ACAMPAIGN_YEAR_EDITION
>, :ACAMPAIGN_REGION_ID
>, :ADATE_TO
>, :ACUSTOMER_REGION_ID)
>
>where SPS_CONTRACT$RESULT_BOOK is selectable stored procedure (with
>recursive calls). The procedure body is too complex and big to show
>it in this message, but it doesn't seem to be syntax or semantic
>problem, because e.g.
>
>SELECT *
>FROM SPS_CONTRACT$RESULT_BOOK(2004
>, `'
>, NULL
>, NULL
>, '31.03.2004'
>, -1)
>
>is running smoothly on "NEW" Firebird and gives all 180
>result rows
>after about 20 minutes, but
>
>SELECT *
>FROM SPS_CONTRACT$RESULT_BOOK(2004
>, `'
>, NULL
>, NULL
>, '31.03.2005'
>, -1)
>
>gives only first 144 result rows after about 40 minutes and crashes
>with error codes from beginning of this message. The only difference
>is in "ADATE_TO" parameter and consequently in the amount of
>data to
>be proceeded (approximately 79.217 rows against 147.211 rows in the
>2nd call). Both calls are running smoothly on "OLD"
>Firebird...
>
>Any suggestion, please?

Right, a few things here:

1. In both cases, this is a heck of a lot of rows to be returning to the
client in a single select....though the size of the dataset probably is for
another subject, probably, since your call is crashing *something* after
only 144 rows have been returned. Do I understand this correctly?

2. If you are using IB_SQL built with a version of IBO that is older than
IBO 4.3Aa, then your server must be set up with OldParameterOrdering (in
firebird.conf) set to 1 (delete the '#' symbol that comments out the
default value of 0). This isn't a nice workaround and I've never been
convinced that it wouldn't have unexpected effects when nesting calls to
executable SPs server-side.) You should get the latest IB_SQL to have it
work reliably with Firebird 1.5. (Wait until I report back - I'll compile
a fresh one now and make sure that I set the version string properly).

3. In the meantime, don't change firebird.conf, but try invoking your SP
using isql, to discover whether anything crashes using the correct client
interface - see (4) below.

4. IB_SQL expects a client named gds32.dll, located in the system32
directory. The standard Firebird 1.5 installs fbclient.dll and locates it
in Firebird's bin directory. So it's important to copy fbclient.dll to the
system32 directory, rename the old gds32.dll to something harmless, and
rename fbclient.dll to gds32.dll, if you are going to use IB_SQL.

That renamed Fb 1.5 client should also be distributed to all remote clients
that are accessing Fb 1.5 from your updated IBO applications. {Sorry this
seems a bit complicated...your pre-IBO 4.3 applications *must* connect to a
server configured with OldParameterOrdering set to 1 AND use the Firebird
1.5 client library}

That will do for a start, I think. I'll now go and make a fresh build of
IB_SQL (for which you will not need to mess about with OldParameterOrdering
- later versions of IBO work with Fb 1.5 out-of-the-box).

./heLen