You can create and edit temporary tables in the Database painter, SQL Select painter, or DataWindow painter when you use the ASE or SYC native driver to connect to an Adaptive Server database, or the SNC native driver to connect to a Microsoft SQL Server 2005 database. Temporary tables persist for the duration of a database connection, residing in a special database called “tempdb”.
You add a temporary table to the tempdb database by right-clicking the Temporary Tables icon in the Objects view and selecting New. The table is designated as a temporary table by assigning a name that starts with the # character. When you save the table, the Create New Temporary Table dialog box displays. The # character is added automatically.
If there is no Temporary Tables icon in the Objects view, right-click the Tables icon and select New. Assign a table name prefaced with the # character.
For SNC, use # for a local temporary table or ## for a global temporary table. Temporary tables must start with the # character. Local temporary tables are visible only in the user’s current connection and are deleted when the user disconnects. Global temporary tables are visible to any user connected to the instance of SQL Server, and they are deleted when all users referencing the table disconnect.
After you create a temporary table, you can create indexes and a primary key for the table from the pop-up menu for the table in the Object Layout view. If you define a unique index or primary key, you can perform insert, update, and delete operations in DataWindow objects.
Selecting Edit Data from the pop-up menu of a temporary table retrieves data that you store in that table. You can also select Drop Table, Add to Layout, Export Syntax, and properties from the pop-up menu in the Objects view.
You can create DataWindow objects that access temporary tables in a PowerBuilder runtime application, but your application must first explicitly create the temporary tables, along with the appropriate keys and indexes, using the same database transaction object used by the DataWindow.
You can use the EXECUTE IMMEDIATE PowerScript syntax to create temporary tables at runtime:
string s1, s2, s3, s4
s1 = 'create table dbo.#temptab1 (id int not null, ' &
+ 'lname char(20) not null) '
s2 = 'alter table dbo.#temptab1 add constraint idkey' &
+ ' primary key clustered (id) '
s3 = 'create nonclustered index nameidx on ' &
+ 'dbo.#temptab1 (lname ) '
s4 = 'insert into #temptab1 select emp_id, ' &
+ 'emp_lname from qadb_emp'
execute immediate :s1 using sqlca;
if sqlca.sqlcode = 0 then
execute immediate :s2 using sqlca;
execute immediate :s3 using sqlca;
execute immediate :s4 using sqlca;
else
messagebox("Create error", sqlca.sqlerrtext)
end if
