--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 01:12 AM 27/09/2006, you wrote:
> >I've found another instance where this runs aground.
> >
> >I have a table named SCHED$ which has a number of fields, but two

of

> >them are "Acc ID" and "Serv ID". This table has a master table
> >which contributes values for these two fields so that the
> >corresponding data can be located. This seems to be handled by
> >adding a WHERE clause with appropriate params. But the SQL
> >generated doesn't account for the quoting on the fields, so I get
> >this error:
> >
> >ISC ERROR CODE:335544569
> >
> >ISC ERROR MESSAGE:
> >Dynamic SQL Error
> >SQL error code = -104
> >Token unknown - line 3, char 42
> >ID
> >
> >and this SQL:
> >
> >SELECT ALL SCHED$.*
> >FROM SCHED$
> >WHERE SCHED$."Acc ID" = ? /* Acc */ ID
> > AND SCHED$."Serv ID" = ? /* Serv */ ID
> >ORDER BY SCHED$."Acc ID" ASC
> > , SCHED$."Serv ID" ASC
> >
> >I'm going to try to work around this, but I thought you'd like to
> >know.
>
> OK, you seem to have two problems here.
>
> 1. For masterlinking a query to work automatically in the

TDataset

> architecture, the fieldnames of the primary key in the master set
> must have exactly the same names as the linking foreign key fields

in

> the detail; otherwise you must write the WHERE clause for the

detail

> set yourself and supply your own valid parameter names.

Indeed. And they are. Unchanged, from when they were working with
the BDE.

>
> 2. The other half of the problem is that parameter names are
> actually Delphi identifiers. For that reason, you cannot have

quotes

> or spaces in the identifiers.
>
> So - even if the names of the linking keys match, you don't have a
> way to use those fieldnames the way Delphi uses them for forming
> parameter names - as you can see from the generated SQL.

Because there's a bug in the code that generates the SQL.

>
> In short, you need to provide the WHERE clause for the detail set
> yourself, using for the parameter name any string that is a valid
> Delphi identifier. Here's an example of what you could supply:
>
> SELECT SCHED$.*
> FROM SCHED$
> WHERE SCHED$."Acc ID" = :Acc_ID
> AND SCHED$."Serv ID" = :Serv_ID
> ORDER BY SCHED$."Acc ID" ASC
> , SCHED$."Serv ID" ASC
>
> Provided you have the Datasource property properly set on the

detail

> query AND the Fieldnames of the key fields are identical, the

object

> will take care of assigning values to the parameters. If the
> Fieldnames of the key fields are different, e.g. unquoted, or
> different names, then you will need to do the assignment in the
> master set's AfterScroll event.
>
> Helen
>

The SQL was generated by the TIBODataset code, not me. It pretty
much matches yours exactly. The problem is clearly that the closing
remark text ('*/') gets inserted too soon because the param name is
a quoted string (which, by the way, upsets Delphi and IBO not at
all -- I've used it previously) and the '*/' is getting shoved in
after the first token of the field name.

I only posted the original message in keeping with Jason's request
that inconsistencies in IBOjects be brought to his attention. There
are a great many issues concerning quoted field names in IBObjects,
of which this is just one more.

I realize that it would be better for us all if I just changed the
field names, but there are hundreds and it's not an option at this
time.

Michael D. Spence
Mockingbird Data Systems, Inc.