Subject Re: [IB-Architect] Messaging API
Author Jason Wharton
>Some questions:
> 1. How do you wait for a message?

Like I said, you prepare a SELECT statement against a message table and
(perhaps optionally) supply an AST to receive notification when a message is
available. This would require an enhanced API call similar to
isc_dsql_prepare() except you would need to pass in a proc address for the

The alternative if an AST wasn't provided is to use an API call or a call
using the statement info call to allow the client to poll a flag that would
be maintained internal to the GDSxx client if there was a message waiting or
not on a per statement handle basis.

Either the main thread in a GUI type app could use Idle events to poll it
(thus it would be synchronized automatically this way) or a consol or
service type of app could have a sub-thread polling it.

In short, is all that needs to happen is something needs to say "go get 'em"
and then message fetches can be performed. Events are excellent for this and
could simply be the underlying vehicle to make this all work. (Which is what
I am doing already.)

> 2. How do you wait for messages from a number of message tables?

You would be limited to use a single statement per each message table. You
would use up one statement handle per each message type you wanted to wait

> 3. What happens to a result set when another message appears?

That's a good question. I suppose it depends upon how and when they are
introduced into the message queue table. If they are not actually introduced
into the message queue until they are committed then it would be a
sequential thing. If they are sitting in the queue table in an uncommitted
state then there wouldn't be a pure sequential series of records.

> 4. Could you explain what the server does in response to an
> insert into a message table?

Well, since I'm not an IB internals guru I'll have to show my ignorance... I
was kind of hoping you would figure it out and tell me! <g>

I'm assuming that when a normal record is inserted that various pages are
touched in the process. The data page to hold the record is populated with
the record's data and then it's DB_KEY and such is written to the various
other pages that maintain the natural "index" that defines the contents of
the table. The record is also stamped with the transaction id for versioning
purposes, etc.

This, if true, would be the same for the message table I presume.

Where things would probably need to differ is to have the ability to set a
column value in the message table at the time it is being committed. I think
a message table should have a system defined column (different than a
DB_KEY) that is populated from a system defined generator at the time the
record is committed.

This way you would have a pure sequence that represented the committed order
of the records in the message queue table. This would allow the client to be
able to hinge on that sequence and "walk" it. Each time it fetched messages
its sequence would be incremented to ignore previous messsages.

During the same process as setting these sequence stamps it could be
initializing the necessary activities to notify the clients that have a
registered interest in the newly committed messages. Each registered message
statement would have its former sequence and it would walk the new messages
looking for a match. If there was a match then it would stop and send
notification. If there was no match it would simply increment the sequence
and move forward.

I have a hunch that this "activity" at the time of committing is quite
foreign to Interbase's optimistic nature. I presume that records written and
then committed don't actually need to be touched in any way. Rollbacks do
seem to need to touch modified records so perhaps there would need to be
some logic from the rollback cycle applied to the commit cycle where the
message tables are concerned. Perhaps whatever structures are "walked"
during a rollback can be maintained to be "walked" just for messages tables
when a commit is performed. How far out is this?

> 5. Can a message table participate in a join?

Should be denied. isc_dsql_error or something like that.

> Can it be ordered?

dito above

> Can it be referenced in a stored procedure?

If so what should it see? Nothing I would presume. Deny it too?

Perhaps the reveals a significant aggrivating factor to this, I don't know.

> Can it have triggers?

That could be useful. The INSERT triggers would be the only ones useful.
The others should probably be denied.

Jason Wharton
InterBase Developer Initiative

InterBase will be the database of the new millennium.