Temporary table in UniVerse

[rwolancz]

Hi,

I would like to use UniVerse DB as a storage for temporary data in my job.
My job runs in a multi instance mode, therefore each instance should deal with its own "temp" table.

The job should:
- establish temp table name (constructed eg based on InvocationId ... ?). How can I use it later on eg in UV stage, table name propery.
- drop the temp table, if exists.
- create temp table (with its name )
- drop the table when finished.

How do I do this?

I have not found a piece of info in DS docs on how to access UniVerse DB from BASIC.
Can Universe handle temp tables by itself, eg something #temp in SQL Server?

Any hints much appreciated.

Thanks,
Robert.

[ray.wurlod]

Pretty much everything you want to do can be done within the UV stage. The problem is getting the name of the table to be unique. As you suggest, the job invocation ID provides a good mechanism, but how to get it there?
I would suggest that you start your job from a control job, which can obtain the invocation ID from the job name (which it has to provide to DSAttachJob() in any case), so could set the temporary table name in a job parameter.
The UV stage has a create table option, and on the EditDDL tab there is a capability to drop the table first. To drop the table afterwards you could use an after-stage or after-job subroutine ExecUV executing the DROP TABLE #temptablename#; command.

Another possibility, which would need you to create a before/after subroutine, is to have a before-job subroutine create the table (you could perhaps use the UniVerse port number for uniqueness) and store the table name somewhere convenient (perhaps the user status area), and an after-stage/job subroutine to drop the table.

The following sample code may be instructive. It does not include "drop table if it already exists", but this is straight-forward to code. If you prefer to use UniVerse port number rather than invocation ID, use @USERNO rather than InvocationID when generating the table name. The UniVerse CREATE TABLE statement does have syntax options for placing the table anywhere on the file system; I have omitted this for clarity.

SUBROUTINE CreateTempUVTable(InputArg,ErrorCode)
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
TableBaseName = InputArg
If TableBaseName = "" Then TableBaseName = "TempTable"
InvocationID = DSGetJobInfo(DSJ.ME,DSJ.JOBINVOCATIONID)
TableName = TableBaseName : InvocationID
Convert " " To "" In TableName
Shell = (If System(91) Then "NT" Else "UNIX")
Command = "CREATE TABLE " : TableName : ";"
Output = ""
Code = 0
Call DSExecute(Shell,Command,Output,Code)
Message = "Executed " : Quote(Command)
Message<-1> = Output
Call DSLogInfo(Message,"Create Table")
ErrorCode = Code ; * abort on failure
Call DSSetUserStatus(TableName)
RETURN


SUBROUTINE DropTempUVTable(InputArg, ErrorCode)
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
TempTable = DSGetJobInfo(DSJ.ME,DSJ.USERSTATUS)
Shell = (If System(91) Then "NT" Else "UNIX")
Command = "DROP TABLE " : TempTable : ";"
Output = ""
Code = 0
Call DSExecute(Shell, Command, Output, Code)
Message = "Executed " : Quote(Command)
Message<-1> = Output
Call DSLogInfo(Message,"Drop Table")
RETURN

[rwolancz]

Done as you suggested. All works fine.
Many thanks Ray