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

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

>

I understand perfectly well what you're saying. Macro
substitution *must* be done prior to statement prep., so
anything that gets plugged in by the substituion is static
until and unless the statement gets re-prepared.

In the meantime, I've just taken it out altogether for
testing, because the SESSION_ID is only critical if there
are multiple users. IOW there's no WHERE clause and no macro
stuff on the select at all. This has caused no change in
behavior.

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

Oh. Well, then there's no timing issue here and no possibility
of one. Macro substitution clearly has to happen prior to
statement prep. At that point, the substituted text is
*static* and won't be changed (unless it involved
introducing a parameter which would then get parsed...).
Did you think I supposed that I could changed the value
which gets substituted for <<SESSIONID>> after preparation?
Does anybody actually ever think that?

The only question is whether IBO is doing an "unnecessary"
re-prepare because it considers the existence of an assigned
OnMacroSubstitute procedure to be the same as an SQL change.

> From the monitor output, at

the

> point where the InsertSQL was being executed, the "SESSION_ID"
> parameter of the Insert statement was zero.

Not relevant. The InsertSQL *uses* a parameter, not a
macro. Even *I* thought a macro here was a bad idea,
especially when I could just do a straight assignment
(FieldByName('SESSION_ID').AsInteger := x;) and the
parameter part would get done without further intervention
on my part.

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

Ouch!

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

No argument from me about things like that, and especially
not with you. But I added a primary key to the table (and
removed the macro stuff) and still get the null rows, so
evidently, I'm doing something else wrong. I'll keep
working to isolate things further. Something will turn up
eventually.


Michael D. Spence
Mockingbird Data Systems, Inc.