checking for existence of temp table
Paul wanted to detect whether a 4GL program had already created a temp table, without using WHENEVER ERROR CONTINUE around DROP TABLE (because his Aubit4GL setup logs continued errors and auto-raises Mantis bugs). His workaround was a complex sysmaster query joining syslcktab/systabnames/sysptnhdr etc., which was unreliable as the 'created' value varied. Replies recommended simpler approaches: just use DROP TABLE with WHENEVER ERROR, prepare a SELECT against the table, or (David's suggestion, which Paul already largely used) track table existence in a program/module variable so no DB call is needed; Jonathan also suggested making Aubit's continued-error logging switchable. No single definitive fix was agreed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm trying to figure out if a program has already created a temporary
table or not. Normaly in 4GL you'd do a
whenever error continue
drop table foowhenever error stop
create table foo
( col1 char(10)
) with no log
I'm trying to avoid the whenever error continue bit. I came up with
this query it works most of the time except some times the created
column changes value. I know there must be a better way because
onstat can tell about the temporary tables. Any ideas?
-Paul
my query
select count(*)
from sysmaster:syslcktab a, sysmaster:systabnames t,
sysmaster:systxptab c, sysmaster:sysrstcb d,
sysmaster:syssessions g,
sysmaster:sysptnhdr p
where a.partnum = t.partnum
and a.owner = c.address
and c.owner = d.address
and d.sid = g.sid
and t.partnum = p.partnum
and a.grtime in (select created
from sysmaster:systabnames t,sysmaster:sysptnhdr p
where t.partnum = p.partnum
and sysmaster:bitval(p.flags,32) = 1
and t.tabname = ?
and owner = ?)
and pid = ?
On Oct 1, 1:33 pm, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
> I'm trying to figure out if a program has already created a temporary
> table or not. Normaly in 4GL you'd do a
>
> whenever error continue
> drop table foo> whenever error stop
>
> create table foo
> ( col1 char(10)
> ) with no log>
> I'm trying to avoid the whenever error continue bit. I came up with
> this query it works most of the time except some times the created
> column changes value. I know there must be a better way because
> onstat can tell about the temporary tables. Any ideas?
>
> -Paul
>
> my query
>
> select count(*)
> from sysmaster:syslcktab a, sysmaster:systabnames t,
> sysmaster:systxptab c, sysmaster:sysrstcb d,
> sysmaster:syssessions g,> sysmaster:sysptnhdr p
> where a.partnum = t.partnum
> and a.owner = c.address
> and c.owner = d.address
> and d.sid = g.sid
> and t.partnum = p.partnum
> and a.grtime in (select created
> from sysmaster:systabnames t,
> sysmaster:sysptnhdr p
> where t.partnum = p.partnum
> and sysmaster:bitval(p.flags,32) = 1
> and t.tabname = ?
> and owner = ?)
> and pid = ?
The 'cure' looks dramatically more complex, time-consuming, and vastly
less understandable than the 'disease'.
Another way to find out whether the temp table exists is to prepare a
"SELECT * FROM temptable" statement, but frankly, the DROP TABLE
solution is still simpler (not least because you'd need WHENEVER
statements around that, too).
And you need WHENEVER ERROR around the SELECT from sysmaster; the
server may have crashed between the last operation and this one, or
the network may have failed, or other problems may occur that will be
reported by an error - which will be fatal if you have WHENEVER ERROR
STOP in effect normally (which is, in my estimation, good practice).
Why do you think the DROP solution is so bad?
-=JL=-
On 1 Oct, 21:33, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
> I'm trying to figure out if a program has already created a temporary
> table or not. Normaly in 4GL you'd do a
>
> whenever error continue
> drop table foo> whenever error stop
>
> create table foo
> ( col1 char(10)
> ) with no log>
no! do you
function init_tables()
let module_var_table_foo_exists = false
.. repeat for group of tables
end function
function create_table_foo()
if module_var_table_foo_exists = false
create table foo
if error
return false
endif
endif
return true
end function
function drop_table_foo()
drop table foo
if error
return false
endif
let module_var_table_foo_exists = false
return true
end function
That way you do not even talk to the database server if the table
already exists and avoid the round trip communication and putting load
on the database server!
Whippersnappers these days still need to learn to code efficiently...
> I'm trying to avoid the whenever error continue bit. I came up with
> this query it works most of the time except some times the created
> column changes value. I know there must be a better way because
> onstat can tell about the temporary tables. Any ideas?
>
> -Paul
>
> my query
>
> select count(*)
> from sysmaster:syslcktab a, sysmaster:systabnames t,
> sysmaster:systxptab c, sysmaster:sysrstcb d,
> sysmaster:syssessions g,> sysmaster:sysptnhdr p
> where a.partnum = t.partnum
> and a.owner = c.address
> and c.owner = d.address
> and d.sid = g.sid
> and t.partnum = p.partnum
> and a.grtime in (select created
> from sysmaster:systabnames t,
> sysmaster:sysptnhdr p
> where t.partnum = p.partnum
> and sysmaster:bitval(p.flags,32) = 1
> and t.tabname = ?
> and owner = ?)
> and pid = ?
On Oct 2, 9:52 am, Jonathan Leffler <jonathan.leff...@gmail.com>
wrote:
> On Oct 1, 1:33 pm, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
>
>
>
> > I'm trying to figure out if a program has already created a temporary
> > table or not. Normaly in 4GL you'd do a
>
> > whenever error continue
> > drop table foo> > whenever error stop
>
> > create table foo
> > ( col1 char(10)
> > ) with no log>
> > I'm trying to avoid the whenever error continue bit. I came up with
> > this query it works most of the time except some times the created
> > column changes value. I know there must be a better way because
> > onstat can tell about the temporary tables. Any ideas?
>
> > -Paul
>
> > my query
>
> > select count(*)
> > from sysmaster:syslcktab a, sysmaster:systabnames t,
> > sysmaster:systxptab c, sysmaster:sysrstcb d,
> > sysmaster:syssessions g,
> > sysmaster:sysptnhdr p
> > where a.partnum = t.partnum
> > and a.owner = c.address
> > and c.owner = d.address
> > and d.sid = g.sid
> > and t.partnum = p.partnum
> > and a.grtime in (select created
> > from sysmaster:systabnames t,> > sysmaster:sysptnhdr p
> > where t.partnum = p.partnum
> > and sysmaster:bitval(p.flags,32) = 1
> > and t.tabname = ?
> > and owner = ?)
> > and pid = ?
>
> The 'cure' looks dramatically more complex, time-consuming, and vastly
> less understandable than the 'disease'.
>
> Another way to find out whether the temp table exists is to prepare a
> "SELECT * FROM temptable" statement, but frankly, the DROP TABLE
> solution is still simpler (not least because you'd need WHENEVER
> statements around that, too).
>
> And you need WHENEVER ERROR around the SELECT from sysmaster; the
> server may have crashed between the last operation and this one, or
> the network may have failed, or other problems may occur that will be
> reported by an error - which will be fatal if you have WHENEVER ERROR
> STOP in effect normally (which is, in my estimation, good practice).
>
> Why do you think the DROP solution is so bad?
>
> -=JL=-
First the actual code I have is more like what David suggested kept it
brief for example sake. The reason
I'm going through lots of hoops to try and avoid the whenever error
method is.
1. I'm using Aubit4GL and I talked Mike into reporting errors which
occur during "whenever error continue"
to the errorlog. It's a great feature you'd be amazed at what errors
show up when you do it.
2. Because of 1 and because have several hundred programs pointing to
same errorlog it can get messy with
false errors like continuing from a drop table.
3. I got Mike to add a hook in Aubit's error processing that when
errorlog is written to log the error in a bug
tracking system. The one we used is Mantis. Instead of making
exclusions in that process I'd rather avoid
the problem...
Just for reference here's what Aubit's extended error reporting
shows. The bug created in Mantis would
actually include userid and a screen dump if applicable.
Date: 2009-10-02 Time: 11:12:58
Program foo.4ge CONTINUEd after error at 'foo.4gl', line number 3141.
Error status number -206.
The specified table (foo_needed) is not in the database..
4gl function call stack :
foo.4gl (Line 749) calls crt_invoice()
foo.4gl (Line 179) calls main_menu()
MAIN
On 5 Oct, 13:06, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
> On Oct 2, 9:52 am, Jonathan Leffler <jonathan.leff...@gmail.com>
> wrote:
>
>
>
> > On Oct 1, 1:33 pm, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
>
> > > I'm trying to figure out if a program has already created a temporary
> > > table or not. Normaly in 4GL you'd do a
>
> > > whenever error continue
> > > drop table foo> > > whenever error stop
>
> > > create table foo
> > > ( col1 char(10)
> > > ) with no log>
> > > I'm trying to avoid the whenever error continue bit. I came up with
> > > this query it works most of the time except some times the created
> > > column changes value. I know there must be a better way because
> > > onstat can tell about the temporary tables. Any ideas?
>
> > > -Paul
>
> > > my query
>
> > > select count(*)
> > > from sysmaster:syslcktab a, sysmaster:systabnames t,
> > > sysmaster:systxptab c, sysmaster:sysrstcb d,
> > > sysmaster:syssessions g,> > > sysmaster:sysptnhdr p
> > > where a.partnum = t.partnum
> > > and a.owner = c.address
> > > and c.owner = d.address
> > > and d.sid = g.sid
> > > and t.partnum = p.partnum
> > > and a.grtime in (select created
> > > from sysmaster:systabnames t,
> > > sysmaster:sysptnhdr p
> > > where t.partnum = p.partnum
> > > and sysmaster:bitval(p.flags,32) = 1
> > > and t.tabname = ?
> > > and owner = ?)
> > > and pid = ?
>
> > The 'cure' looks dramatically more complex, time-consuming, and vastly
> > less understandable than the 'disease'.
>
> > Another way to find out whether the temp table exists is to prepare a
> > "SELECT * FROM temptable" statement, but frankly, the DROP TABLE
> > solution is still simpler (not least because you'd need WHENEVER
> > statements around that, too).
>
> > And you need WHENEVER ERROR around the SELECT from sysmaster; the
> > server may have crashed between the last operation and this one, or
> > the network may have failed, or other problems may occur that will be
> > reported by an error - which will be fatal if you have WHENEVER ERROR
> > STOP in effect normally (which is, in my estimation, good practice).
>
> > Why do you think the DROP solution is so bad?
>
> > -=JL=-
>
> First the actual code I have is more like what David suggested kept it
> brief for example sake. The reason
> I'm going through lots of hoops to try and avoid the whenever error
> method is.
>
> 1. I'm using Aubit4GL and I talked Mike into reporting errors which
> occur during "whenever error continue"
> to the errorlog. It's a great feature you'd be amazed at what errors
> show up when you do it.
>
> 2. Because of 1 and because have several hundred programs pointing to
> same errorlog it can get messy with
> false errors like continuing from a drop table.
>
> 3. I got Mike to add a hook in Aubit's error processing that when
> errorlog is written to log the error in a bug
> tracking system. The one we used is Mantis. Instead of making
> exclusions in that process I'd rather avoid
> the problem...
>
> Just for reference here's what Aubit's extended error reporting
> shows. The bug created in Mantis would
> actually include userid and a screen dump if applicable.
>
> Date: 2009-10-02 Time: 11:12:58
> Program foo.4ge CONTINUEd after error at 'foo.4gl', line number 3141.
> Error status number -206.
> The specified table (foo_needed) is not in the database..
>
> 4gl function call stack :
> foo.4gl (Line 749) calls crt_invoice()
> foo.4gl (Line 179) calls main_menu()
> MAIN
So do what my code would do, do NOT call DROP but use a variable to
maintain state instead.
On Oct 5, 12:40 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> On 5 Oct, 13:06, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
>
>
>
> > On Oct 2, 9:52 am, Jonathan Leffler <jonathan.leff...@gmail.com>
> > wrote:
>
> > > On Oct 1, 1:33 pm, "pjfa...@gmail.com" <pjfa...@gmail.com> wrote:
>
> > > > I'm trying to figure out if a program has already created a temporary
> > > > table or not. Normaly in 4GL you'd do a
>
> > > > whenever error continue
> > > > drop table foo> > > > whenever error stop
>
> > > > create table foo
> > > > ( col1 char(10)
> > > > ) with no log>
> > > > I'm trying to avoid the whenever error continue bit. I came up with
> > > > this query it works most of the time except some times the created
> > > > column changes value. I know there must be a better way because
> > > > onstat can tell about the temporary tables. Any ideas?
>
> > > > -Paul
>
> > > > my query
>
> > > > select count(*)
> > > > from sysmaster:syslcktab a, sysmaster:systabnames t,
> > > > sysmaster:systxptab c, sysmaster:sysrstcb d,
> > > > sysmaster:syssessions g,> > > > sysmaster:sysptnhdr p
> > > > where a.partnum = t.partnum
> > > > and a.owner = c.address
> > > > and c.owner = d.address
> > > > and d.sid = g.sid
> > > > and t.partnum = p.partnum
> > > > and a.grtime in (select created
> > > > from sysmaster:systabnames t,
> > > > sysmaster:sysptnhdr p
> > > > where t.partnum = p.partnum
> > > > and sysmaster:bitval(p.flags,32) = 1
> > > > and t.tabname = ?
> > > > and owner = ?)
> > > > and pid = ?
>
> > > The 'cure' looks dramatically more complex, time-consuming, and vastly
> > > less understandable than the 'disease'.
>
> > > Another way to find out whether the temp table exists is to prepare a
> > > "SELECT * FROM temptable" statement, but frankly, the DROP TABLE
> > > solution is still simpler (not least because you'd need WHENEVER
> > > statements around that, too).
>
> > > And you need WHENEVER ERROR around the SELECT from sysmaster; the
> > > server may have crashed between the last operation and this one, or
> > > the network may have failed, or other problems may occur that will be
> > > reported by an error - which will be fatal if you have WHENEVER ERROR
> > > STOP in effect normally (which is, in my estimation, good practice).
>
> > > Why do you think the DROP solution is so bad?
>
> > > -=JL=-
>
> > First the actual code I have is more like what David suggested kept it
> > brief for example sake. The reason
> > I'm going through lots of hoops to try and avoid the whenever error
> > method is.
>
> > 1. I'm using Aubit4GL and I talked Mike into reporting errors which
> > occur during "whenever error continue"
> > to the errorlog. It's a great feature you'd be amazed at what errors
> > show up when you do it.
>
> > 2. Because of 1 and because have several hundred programs pointing to
> > same errorlog it can get messy with
> > false errors like continuing from a drop table.
>
> > 3. I got Mike to add a hook in Aubit's error processing that when
> > errorlog is written to log the error in a bug
> > tracking system. The one we used is Mantis. Instead of making
> > exclusions in that process I'd rather avoid
> > the problem...
>
> > Just for reference here's what Aubit's extended error reporting
> > shows. The bug created in Mantis would
> > actually include userid and a screen dump if applicable.
>
> > Date: 2009-10-02 Time: 11:12:58
> > Program foo.4ge CONTINUEd after error at 'foo.4gl', line number 3141.
> > Error status number -206.
> > The specified table (foo_needed) is not in the database..
>
> > 4gl function call stack :
> > foo.4gl (Line 749) calls crt_invoice()
> > foo.4gl (Line 179) calls main_menu()
> > MAIN
>
> So do what my code would do, do NOT call DROP but use a variable to
> maintain state instead.
Or persuade Mike that the continued-errors-to-log feature should be
switchable.
I can see some merit to the reporting - when someone else wrote the
code.
It would bug the hell out of me to have deliberately "I don't care
whether it fails because the table isn't there DROP {temp} TABLE
statements" logged in the log.
I guess it is in part a question of you getting your cake and eating
it.
As long as only one bit of code creates the temp table, the monitor
whether it was created or not mechanism works.
But be aware that if two pieces of code use the same temp table name,
then you might run into situations where the recorder does not
understand all that is going on. Of course, then you'll get the nice
error in your error log saying "table <temptablename> already
exists"...
Anyway - do as you will. I still think that DROP TABLE is the easiest
way to deal with it, but I'm thinking in terms of real 4GL :D
-=JL=-
pjfalbe@gmail.com wrote: > 1. I'm using Aubit4GL and I talked Mike into reporting errors which > occur during "whenever error continue" > to the errorlog. It's a great feature you'd be amazed at what errors > show up when you do it. It just shows the wisdom of the old saying "be careful of what you wish for; you might get it". Maybe you need a cron job to cat from /dev/null to the log file. -- Ian Hotmail is for spammers. Real mail address is igoddard at nildram co uk