Creating and editing temporary tables
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”.
Creating temporary
tables
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.
Working with temporary
tables
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.
Accessing temporary tables at
runtime
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |