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

>
> At 04:17 AM 4/09/2006, you wrote:
> >1) The TIBOQuery I'm using has the following items set (I've
> >redacted some of the fields for brevity):
> > (Really long stuff removed)
> >
> >As you can see, Description and for that matter, all of the other
> >fields are now null. What could cause this?
>
> Don't use macros to try to define parameters - use the correct

syntax, viz.

>
> WHERE SESSION_ID = :session_id
>
> Architecturally, it's a bad decision in any case to

provide "static"

> SQL for a statement that has search values that are going to
> change. Even in the best case (where the timing is taken care of)

it

> causes an enormous amount of overhead because the whole query,
> including structures related to the XxxxSQL statements, get
> invalidated and the statement itself has to be unprepared and re-

prepared.

>
> In this case, where you are depending on OnMacroSubstitute to

alter

> the value in SESSION_ID, you're almost certainly encountering a
> timing issue. If you need to apply a pre-execution change to a
> search value, use a parameter and apply the change in the

BeforePost event.


I see what you're saying, but the SESSION_ID *is* static.
It's set to a unique value when the application starts and
never changes (for a given app instance) after that. Here's
the IBMonitor log for that select:

SELECT "Date", "Code", "Serv ID", "Description", "Qty","Amount",
"Base Price", "Tax Code", "Tax", "Extended", "Crew","Zone",
"Route", "Cost", "Scheduled", "Qty Used", "Man Hours","Old
Date", "Old Code", "Old Serv ID", "Old Qty", "Old Amount","Old
Base Price", "Old Tax Code", "Old Tax", "Old Qty Used", "Old Man
Hours", SESSION_ID , EDITINV.RDB$DB_KEY FROM EDITINV WHERE
SESSION_ID = 67

In other words, SESSION_ID will always be 67 until I stop
the app and start it again. Suppose "Code" had values "A"
and "B" and I only wanted to see WHERE "CODE" = A. Are you
saying I should use a parameter for a case like that?

And just out of curiousity, what would the timing issues be
if SESSION_ID changed? (I'm not trying to debate
architecture here, I'm just trying to learn.)

> >3) In the past, I seem to recall being able to treat a straight
> >query like this (e.g., select x,y,z from foo) almost as though it
> >were a table provided I set RequestLive. In other words, I can do
> >Append, FieldByName().AsValue, Post without having to provide any
> >InsertSQL. When I first tried it with this query, it wouldn't
> >automatically produce the InsertSQL so I constructed some. I
> >thought this might have to do with the select including a WHERE
> >clause. Should this have been an issue? Am I misremembering the
> >situation with live queries?
>
> Check your KeyLinks. When you perform DML from a Delphi dataset,
> you're actually doing a kind of emulated positioned update (which

the

> engine itself doesn't allow via DSQL). In order for this

emulation

> to work for updates and deletes, the XxxxxSQL has to target

exactly

> one row. This is done by expressing the uniqueness by the
> KeyFields. IBO surfaces the KeyFields array for you in KeyLinks.
>
> Considering the simple "select x,y,z from foo", if RequestLive is
> true AND x, y and/or z contain the primary key AND

KeyLinksAutoDefine

> is true, then you will transparently get the "hidden" behaviour

that

> you are used to in the VCL. However, because IBO enables you
> participate in determining the KeyLinks, you can (almost always)
> update certain sets that the VCL can't.
>
> I can't see from either of your statements what is the primary key

of

> this table. Possibly you are relying on a trigger, or you have
> GeneratorLinks for it, but it's not evident....
>
> Anyway, after all that didactic, the principal problem here is
> macrotising that search clause. The biggest breakthrough comes

when

> you accept the concept that substitutable (replaceable) parameters
> are *not* variables. There are several ways to come to grief when
> you treat them as though they were....
>

Don't think so, I've used this in several other tables and
it's worked just fine. All of these tables contain a very
small number of rows (like, 12 or maybe 20) and are only in
the database so that VCL controls can get at their data. If
Firebird had per-session temporary tables, then it would've
been much simpler for me to use them.

I'll bet there's some error case in IBObjects that's getting
provoked by the fact that there isn't a primary key at all
on this table. It's just a (very short) list of stuff whose
order is not important. The XxxxxxSQL statements don't get
autogenerated, which is probably also a bad sign. I could
certainly see that this might be related to unsuitable
keylinks.

I considered that this *might* be the problem, but passed on
it thinking that (1) EDITINV.RDB$DBKEY was being provided
because I had provided nothing on my own and (2) because I
thought I would've gotten the error that says multiple rows
selected or something if the keylinks were improper, instead
of getting an actual row added that is compeletely null.
(And now that I think of it, SESSION_ID is not allowed to be
null, but it gets set to a 0, so I guess that's why I didn't
get a constraint violation.) In the morning, I'll try
adding a proper key based on the SESSION_ID and a counter
field.

Thanks again,

Michael D. Spence
Mockingbird Data Systems, Inc.