Nothing is ever easy, but what about this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roy Hann - 07 Jul 2008 16:17 GMT

For about the 200th time I've just had someone ask me if there's a way an
ABF application can tell if a session global temporary table already (or
still) exists. It's an old chestnut and I always feel a twinge when I give
the bad news.

I know nothing is ever easy, but is it feasible to think of adding a way to
check? It seems to me that it should be done with a query on a new or
existing SCI catalogue because it would be consistent with the way you'd
test for the existence of a real table, but just how that could be made to
work is beyond me.

Comments?

Roy

Signature

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Reply to this Message

Karl & Betty Schendel - 07 Jul 2008 16:42 GMT

> For about the 200th time I've just had someone ask me if there's a
> way an
[quoted text clipped - 3 lines]
> when I give
> the bad news.

I suppose that one could invent a new built-in ABF runtime function to
check. It's easy to do, just hard to do outside of embedded SQL (or
API,
or similar). You just construct a select _bogus_ from session.tablename
query and inspect the error returned. Obviously you have to choose the
column name to be something "impossible" that nobody would ever
actually use.
If you try this within ABF you have to jump thru all sorts of hoops to
suppress the abf-generated error displays, which is why I always do it
from a little esqlc subroutine.

It might be possible to enhance the built-in resolve_table
(owner,tabname)
function to accept something like '$session' as an owner, or to include
session tables in the search path when a blank owner is given.

Karl

Reply to this Message

On Web - 07 Jul 2008 17:33 GMT

> For about the 200th time I've just had someone ask me if there's a way an
> ABF application can tell if a session global temporary table already (or
[quoted text clipped - 8 lines]
>
> Comments?

<evil programmer>

Tell them to drop it - if they don't get an error the table was there,
otherwise it wasn't.. ;-)

</evil programmer>

( the actual approach I used with OpenROAD with the errors suppressed)

Paul

> Roy

Reply to this Message

Roy Hann - 07 Jul 2008 22:28 GMT

>> For about the 200th time I've just had someone ask me if there's a way an
>> ABF application can tell if a session global temporary table already (or
[quoted text clipped - 17 lines]
>
> ( the actual approach I used with OpenROAD with the errors suppressed)

That is pretty much the standard workaround alright, although I prefer a
SELECT with a trivially false where-clause; if the table still exists
perhaps I'd like to keep it.

But what I was really trying ask was what would the ideal solution look
like? I suggested something that behaves like an SCI table. Can anyone
think of a nicer solution?

Roy

Signature

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Reply to this Message

On Web - 11 Jul 2008 16:52 GMT

>>> For about the 200th time I've just had someone ask me if there's a way
>>> an ABF application can tell if a session global temporary table already
[quoted text clipped - 25 lines]
> like? I suggested something that behaves like an SCI table. Can anyone
> think of a nicer solution?

A 'nicer' solution would be for the ABF developer to create the session
tables tables at a known point in the code before they need them, so that
way they know they will be there when they have to be populated. In
practice, my use of session tables always involved populating them from
complicated queries and they were re-used many times during a single
session, so it's quicker to drop and recreate them than clear them out.

It's no big deal for any developer to log the creation of a session table in
a lookup table and use that to get around the ingres deficiency, though I
admit it's an annoying nuisance.

Paul

> Roy

Reply to this Message

Roy Hann - 11 Jul 2008 17:05 GMT

> It's no big deal for any developer to log the creation of a session table in
> a lookup table and use that to get around the ingres deficiency [snip]

Right up until he gets an error in a transaction that updated a
temporary table, at which point the table vanishes.

There really is no good solution except to have a way for us to ask the
server if a table (still) exists, and what I am curious to know is what
that way should look like.

Signature

Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Reply to this Message

On Web - 11 Jul 2008 17:21 GMT

>> It's no big deal for any developer to log the creation of a session table
>> in
>> a lookup table and use that to get around the ingres deficiency [snip]
>
> Right up until he gets an error in a transaction that updated a
> temporary table, at which point the table vanishes.

Now I've learnt something new. Seems a little drastic to remove the whole
table if an error occurs?

Makes me think we wrote some code that is running 'but for the grace of
god'.

Paul

> There really is no good solution except to have a way for us to ask the
> server if a table (still) exists, and what I am curious to know is what
> that way should look like.

Reply to this Message

Roy Hann - 11 Jul 2008 17:52 GMT

>> Right up until he gets an error in a transaction that updated a
>> temporary table, at which point the table vanishes.
>
> Now I've learnt something new. Seems a little drastic to remove the whole
> table if an error occurs?

There's not much else that it can do if it's not transaction logging.
I guess maybe it could truncate the table instead. I haven't really
thought through the implications if that though. That might be a really
silly thing to do. Or the behaviour might be dictated by the ISO/ANSI
standard. Don't know.

> Makes me think we wrote some code that is running 'but for the grace of
> god'.

Ah.

Signature

Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Reply to this Message

Karl & Betty Schendel - 11 Jul 2008 21:26 GMT

>>> Right up until he gets an error in a transaction that updated a
>>> temporary table, at which point the table vanishes.
[quoted text clipped - 9 lines]
> silly thing to do. Or the behaviour might be dictated by the ISO/ANSI
> standard. Don't know.

I don't believe the standard says anything on the topic.

I've occasionally wanted a "keep on error" style of gtt, where
instead of dropping the table, the contents are simply retained
in whatever state they happen to be in. I haven't wanted it
often enough to actually do it, though.

If we had table-producing database procedures (ahem cough cough),
then my suggestion would be some sort of new built-in DB procedure
that produces iitables-like rows for transient objects like
temporary tables. Lacking that, I'll stick to my hack of a
trial select on a bogus column and check the error code for
nonexistent column vs nonexistent table.

Karl

Reply to this Message

Roy Hann - 11 Jul 2008 23:49 GMT

Or the behaviour might be dictated by the ISO/ANSI

>> standard. Don't know.
>
> I don't believe the standard says anything on the topic.

I was pretty sure it didn't, but I'm often surprised by it.

> If we had table-producing database procedures (ahem cough cough),

Doug was showing his progress with them at the IUA conference. His
slides aren't publicly available yet though.

Signature

Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Reply to this Message