At 01:24 PM 4/09/2006, you wrote:

>--- 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?

No. But this isn't the same thing. When you use a macro in an SQL
statement, you're saying "Every time you process this statement, run
the macro in place of the macrotised piece". So the actual *string*
that the parser process is changed and, even though *you* know that
it's going to be the same every time, IBO doesn't; as far as it's
concerned, "changing the string" is "changing the specification for
the output". This is different to both a hardcoded string that
doesn't change and to a parameterised statement. Please confirm that
you understand what I'm saying here as it's crucial. Basically,
you're introducing backwaters of logic that IBO has no reason to anticipate.

>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.)

Even your question indicates that you don't understand the difference
between a variable and a replaceable parameter.

You assign a statement to an object - a dataset, in this case. At
various points in the processing of this statement for a myriad of
purposes, IBO passes through a cycle of steps (read, implemented
behaviours for objects) that are sequence dependent. Of course, many
objects are involved, each with its own event cycle arranged in such
a sequence that everything happens at the right time (where "time"
and "timing" refers to milestones in the sequence, not how long it
takes or whether it is lunchtime..)

But you intervene with a macro, for which, at a certain point, IBO
has to process the OnMacroSubstitute event handler. So, for example,
if it finds it had to do that at a point where the statement is
prepared and all it needs is to go and read the Params and Fields
arrays, it's potentially in a place where there's a hole - a state
change that it doesn't know about. From the monitor output, at the
point where the InsertSQL was being executed, the "SESSION_ID"
parameter of the Insert statement was zero.

Now whether that's exactly the cause of the problem, or something
else down track, or because you're referring to this SESSION_ID
thingy (be it variable, parameter or whatever) with and without
double-quotes, I could not guess and I don't have the time or the
inclination to do the forensics. I'd rather see you do it the right
way than waste my effort trying to find how you can make it work
doing it the wrong way.

> > 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.

Fine; then I guess you just need to keep trying to squeeze blood from a stone.

>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.

Sure could. But I guess it's fodder for a whole new round of futile
argument to defend the principle that tables in relational databases
need keys. Please have that argument with someone else....

Helen