At 08:54 PM 4/04/2008, you wrote:

>Dear All,
>
>I have recently been trying to use IBObjects to the create a small
>project. The following query generates a 'Multiple Errors in Singleton
>Fetch' error, using TIBObjects:
>
>select
> fileid,
> refid,
> (select register.fullname from register where register.regid =
>refid) as Client,
> filerefnum,
> (select file_typedesc from file_types where file_types.file_typeid
>= casefiles.filetypeid) as FileType,
> subject,
> casenum,
> bu_by,
> next_actiondate,
> (select tx_reasons.txreasondesc from tx_reasons where
>tx_reasons.txreasonid = casefiles.next_actiontype) as ActionType,
> (select register.fullname from register where register.regid =
>next_actionby) as Action_By,
> 'Case Files' AS Src
>from casefiles
>where f_weekofyear(next_actiondate) = f_weekofyear(current_date) and
>is_active=1
>
>Copy-Pasting the same code into isql or ibExpert executes the query as
>expected and yields no errors, leading me to conclude that there is
>really nothing wrong with the query, except maybe how IBO parses it.
>Any ideas?
>
>Delphi 7, IBO 4.8.4, Firebird 2.0.3.

Some clients built with older versions of IBO might process this statement without exceptions, even though it isn't valid SQL. I dare say the same is true for older versions of FIBPlus, which is the interface layer beneath IBExpert. The whole purpose of nudging Firebird into tighter compliance and more correct logic is to *avoid* these blunders.

Indeed it might well be a genuine singleton error, caused by IBO actually finding a different output set from your subqueries than the one returned by IBExpert. One of the major sources of blunders in SQL is people assuming that, if they don't get exceptions, their results must be right. If you get an exception when you pass a statement through IBO's interface, you can pretty much count on its being an exception!

However ---!! do check that you have the correct firebird.msg file being accessed by (client? server?) as you should be seeing a different "first" exception, most likely the "ambiguous statement" one (isc_code 335544708) or possibly the "context is already in use one" (isc_code 335544425) for the logic error I annotated below.

You *must* study the compatibility section of the Firebird 2 release notes to see why statements like this are no longer tolerated. IBO 4.8 does a lot of stuff underneath to ensure that qualifier associations are right for Firebird 2. You need to be most thorough about using proper qualification from now on. Firebird users have had 4 years to fix up badly qualified statements....so it's not like it fell out of the sky! Firebird 2.1 will give you a new config setting to allow you to revert to "bad old ways" if you must, but I strongly recommend against touching it if you're going to develop your software in parallel with IBO support for the proper rules. Besides, it's a temporary config setting that will be removed after a period of grace.

select
cf.fileid,
cf.refid,
(select r.fullname from register r
where r.regid = cf.refid) as Client,
cf.filerefnum,
(select ft.file_typedesc from file_types ft
where ft.file_typeid = cf.filetypeid) as FileType,
cf.subject,
cf.casenum,
cf.bu_by,
cf.next_actiondate,
(select txr.txreasondesc from tx_reasons txr
where txr.txreasonid = cf.next_actiontype) as ActionType,
/* the original form of the following might have been the error
that caused the singleton exception: it needs a separate cursor */
(select r2.fullname from register r2
where r2.regid = cf.next_actionby) as Action_By,
'Case Files' AS Src
from casefiles cf
where f_weekofyear(cf.next_actiondate) = f_weekofyear(cf.current_date)
and cf.is_active=1

Helen