Hi,

While trying to convert a reasonably big VMS application (mainly COBOL)
from SYBASE to RDB, I do encounter problems dealing with SYBASE
hash-tables (aka #tables).

Styding the doc for RDB suggest using "DECLARE LOCAL TEMPORARY TABLE
MODULE.tablename". This works well in straightforward SQL (tables are
realy scratch-mode, no two processes can see each other tables,
automatic drop after disconnect, etc.).

But alas, as the doc says, it is not usuable in precompiled programs
(Cobol). I tried using a CONTEXT file ( $SQLPRE blablabla.sco
context_file /whatever), and this basically returns :

declare local temporary TABLE module.SUPP_SERV_STATUS
1
%SQL-F-NO_DMLPLAN, (1) You can only use DECLARE statements in a context
file.
declare local temporary TABLE module.SUPP_SERV_STATUS
2
%SQL-E-INV_TBL_DCL, (2) Invalid use of declared local temporary table
SUPP_SERV_STATUS

(declare statement comes the from context files, not from the .SCO)

Although the message NO_DMLPLAN is slightly confusing (other DECLAREs
are accepted), this is really the same error I get when trying to put
the DECLARE inside the .SCO

Using CREATE LOCAL TEMPORARY TABLE ... from inside the .SCO is not good
enough, because two processes can see each other (temporary) tables,
and I already experienced some nasty locking when two temp tables are
about to be created/used by separated processes.

Any takers ?

advTHANKSance,
f.

Hi Filip,

Do the "GLOBAL" temporary table creations as an off(ish)-line database
maintenance procedure. That is, just a one-off table creation CREAT TEMPORY
TABLE blah (Just drop the "LOCAL").

All this means is that the table definition is nolonger local to the SQL
procedure that created it. But the DATA *is* still local to each attach and
will not conflict with other users.

The metadata will be visible from everyone, but the contents will be attach
(and process) private. I'm guessing that the locking you were getting was to
do with metadata consistency locks.

Regards Richard Maher

PS. Great choice of database?

PPS. Looking for additional resources :-)

<filip.de.block (AT) proximus (DOT) net> wrote

Opps! meant to be an exclamation mark!!!

"Richard Maher" <maher_rj (AT) hotspamnotmail (DOT) com> wrote

Good idea,
but I am afraid it cannot be used.
Several program might do "CREATE TABLE 'name' at abut the same time,
for which the second one will result in error (metadata is already
existing). In this context it is almost impossible to find out in the
zillion lines of existing code whether or not tebale is dropped.

I also already observed that the name "TEMP" (in existing code) for a
temporary table is very popular, each TEMP table having different
layouts.

Maybe change the creation to dynamic SQL, having table_name including
PID and force a drop in an exit_handler ?


I know. Having been using the crown jewels (with RDML) for twenty years
as a DECCIE, before they were sold out.

Any way, THX for the input.