You can create and edit temporary tables in the Database painter, SQL painter, or DataWindow painter when you use the PowerBuilder SYC native driver to connect to an ASE 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 assigning a name that starts with the # character when you create a new table in a PowerBuilder painter. (Temporary tables must start with the # character.)
After you create a temporary table, you can create indexes and a primary key for the table. If you define a unique index or primary key, you can execute INSERT, UPDATE, and DELETE statements for a temporary table. Selecting Edit Data from the pop-up menu of a temporary table retrieves data that you store in that table.
Standard catalog query limitations
When you click Refresh from the pop-up menu for the Tables
node in the Database painter or the Objects view of the DataWindow
painter, the list of tables displays temporary tables even though
they exist only in the tempdb database. However,
once you refresh table definitions from the database, the Objects
view can no longer list the index or primary key information of
the temporary tables, and the Layout view can no longer display
that information graphically.
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
