At 09:12 PM 12/09/2007, you wrote:

>--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> > 1. Put a monitor on your application and see what exact values are
> > being passed for each one, using your query as it stands.
>
>There is a monitor output after I call IBOQuery1.Refresh:

Ok, sorry for not picking up the syntax errors in your SELECT
statement. IBO 4.8 now requires all multi-table selects to be
properly qualified to avoid alias problems with Firebird 2 and
above. (Actually, properly qualified statements were expected in Fb
1.5 as well but there the rough syntax returned warnings, rather than
exceptions.)

So, start with the syntax. For future-proofing use either all
relation aliases or all table names for identifiers, since Fb 2 and
above won't allow them to be intermixed.

e.g., in this statement you need to qualify all of the fields:
select
TAB1.ID,
TAB1.ZKR,
TAB1.DATUM,
(select TAB2.NAZ from TAB2
where TAB2.ID = TAB1.ID and TAB2.TYP = :typ)
as NAZ
from TAB1
WHERE TAB1.ID= :id

Make sure your server is properly configured with
OldParameterOrdering commented out. Don't forget to stop and restart
the server when you set a parameter in firebird.conf. To eliminate
all possibility that the chain of exceptions you are getting is in
any way related to scrambled parameters, set your ib_connection
ParameterOrder property explicitly to poNew. (You can reset it to
poAuto later).

Now, double-check your metadata in TAB2, to make sure that TYP is
actually an integer. If it is a string, then your assignment code is
causing the type mismatch for that parameter. There's no way a bona
fide date or date literal could be interpreted as strings unless you
actually pass strings to them.

>/*---
>EXECUTE STATEMENT
>TR_HANDLE = 3830508
>STMT_HANDLE = 3830660
>PARAMS = [ Version 1 SQLd 2 SQLn 2
> [TYP] = 2
> [DATUM] = '12 IX 2007' ] <---- W H A T I S T H I S ? At
> best, it is a string (not a date, not a date literal).


>----*/
>/*---
>PREPARE STATEMENT
>TR_HANDLE = 3830508
>STMT_HANDLE = 3830320
>
>select
> ID,
> ZKR,
> DATUM,
> (select NAZ from TAB2 where ID = TAB1.ID and TYP = ? /* TYP */ ) as
>NAZ
>from TAB1
>WHERE TAB1.ID=? /* BIND_0 */ <-- this is the binding parameter for
>the values in the subquery expression
>
>PLAN (TAB2 INDEX (PK_TAB2))
>PLAN (TAB1 INDEX (PK_TAB1))
>
>FIELDS = [ Version 1 SQLd 4 SQLn 4
> TAB1.ID = <n> 0
> TAB1.ZKR = <NULL>
> TAB1.DATUM = <NULL>
> [NAZ] = <NULL> ]

Notice above that IBO has correctly guessed the relation qualifiers
for the fields.

>----*/
>/*---
>PREPARE STATEMENT
>TR_HANDLE = 3830508
>STMT_HANDLE = 3830076
>
>SELECT TAB1.ID
>from TAB1
>WHERE TAB1.ID=?
> AND DATUM = ? /* DATUM */
>
>PLAN (TAB1 INDEX (PK_TAB1))

Here it is loading the search keys for the dataset.

>FIELDS = [ Version 1 SQLd 1 SQLn 1
> TAB1.ID = 1 ]
>----*/
>/*---
>EXECUTE STATEMENT
>TR_HANDLE = 3830508
>STMT_HANDLE = 3830076
>PARAMS = [ Version 1 SQLd 2 SQLn 2
> TAB1.ID = 1
> [TYP] = 2 ]
>
>ERRCODE = 335544569
>----*/
>/*---
>INTERPRET BUFFER =
>ERRCODE = 17
>----*/
>/*---
>INTERPRET BUFFER = Dynamic SQL Error
>ERRCODE = 21
>----*/
>/*---
>INTERPRET BUFFER = SQL error code = -303
>ERRCODE = 32
>----*/
>/*---
>INTERPRET BUFFER = conversion error from string "2"
>ERRCODE = -1
>----*/
>
>Note, that the statement with STMT_HANDLE = 3830076 has params
>TAB1.ID and DATUM (in Prepare phase} and it receives params TAB1.ID
>and TYP (in Execute phase).

That's ok. But the problem here really does look like the [TYP]
parameter in the subquery expression has the wrong data type.

> > 2. Change this parameter assignment:
> >
> > IBOQuery1.ParamByName('DATUM').asDateTime := StrToDate('1.1.2007');
> >
> > to (first test):
> >
> > a) IBOQuery1.ParamByName('DATUM').asDateTime := StrToDate
>('1/1/2007');
>
>It doesn't work, because I have set DateSeparator to '.', not to '/'

That's OK, I only suggested it because you had not mentioned it.

> > b) (second test):
> >
> > IBOQuery1.ParamByName('DATUM').AsRawString := '''1.1.2007''';
>
>It doesn't work, because ParamByName is TParam, not TIB_Column. There
>is no property AsRawString.

Sorry, that's quite correct. Would you please test it with the same
triple-quoted value in the right side of the assignment, but use
AsString instead? We want to arrive at a situation where a date
literal is being passed to the parameter without getting the
literalising quotes stripped out. I don't know whether the TDataset
implementation of AsString will behave nicely in this regard, or not.

>I tried also
> IBOQuery1.ParamByName('DATUM').AsDateTime := Date;
>without success.

Try it with the full function syntax, Date(). And please describe
what "without success" means.

Helen