- Creating a new table from scratch
- Creating a new table from an existing table
- Specifying column definitions
- Specifying table and column properties
- Altering a table
- Cutting, copying, and pasting columns
- Closing a table
- Dropping a table
- Viewing pending SQL changes
- Printing the table definition
- Exporting table syntax
- About system tables
- Creating and editing temporary tables
When you open the Database painter, the Object view lists all tables in the current database that you have access to (including tables that were not created using PowerBuilder). You can create a new table or alter an existing table. You can also modify table properties and work with indexes and keys.
In PowerBuilder, you can create a new table in any database to which PowerBuilder is connected.
To create a table in the current database
-
Do one of the following:
-
Click the Create Table button.
-
Right-click in the Columns view and select New Table from the pop-up menu.
-
Right-click Tables in the Objects view and select New Table from the pop-up menu.
-
Select Insert>Table from the Object menu.
The new table template displays in the Columns view. What you see in the view is DBMS-dependent. You use this template to specify each column in the table. The insertion point is in the Column Name box for the first column.
-
-
Enter the required information for this column.
For what to enter in each field, see Specifying column definitions.
As you enter information, use the Tab key to move from place to place in the column definition. After defining the last item in the column definition, press the Tab key to display the work area for the next column.
-
Repeat step 2 for each additional column in your table.
-
(Optional) Select Object>Pending Syntax from the menu bar or select Pending Syntax from the pop-up menu to see the pending SQL syntax.
If you have not already named the table, you must provide a name in the dialog box that displays. To hide the SQL syntax and return to the table columns, select Object>Pending Syntax from the menu bar.
-
Click the Save button or select Save from the File or pop-up menu, then enter a name for the table in the Create New Table dialog box.
PowerBuilder submits the pending SQL syntax statements it generated to the DBMS, and the table is created. The new table is displayed in the Object Layout view.
About saving the table
If you make changes after you save the table and before you close it, you see the pending changes when you select Pending SQL again. When you click Save again, PowerBuilder submits a DROP TABLE statement to the DBMS, recreates the table, and applies all changes that are pending. Clicking Save many times can be time consuming when you are working with large tables, so you might want to save only when you have finished.
-
Specify extended attributes for the columns.
For what to enter in each field, see Specifying column extended attributes.
You can create a new table that is similar to an existing table very quickly by using the Save Table As menu option.
To create a new table from an existing table
-
Open the existing table in the Columns view by dragging and dropping it or selecting Alter Table from the pop-up menu.
-
Right-click in the Columns view and select Save Table As from the pop-up menu.
-
Enter a name for the new table and then the owner's name, and click OK.
The new table appears in the Object Layout view and the Columns view.
-
Make whatever changes you want to the table definition.
-
Save the table.
-
Make changes to the table's properties in the Object Details view.
For more information about modifying table properties, see Specifying table and column properties.
When you create a new table, you must specify a definition for each column. The fields that display for each column in the Columns view depend on your DBMS. You might not see all of the following fields, and the values that you can enter are dependent on the DBMS.
For more information, see your DBMS documentation.
|
Field |
What you enter |
|---|---|
|
Column Name |
(Required) The name by which the column will be identified. |
|
Data Type |
(Required) Select a datatype from the drop-down list. All datatypes supported by the current DBMS are displayed in the list. |
|
Width |
For datatypes with variable widths, the number of characters in the field. |
|
Dec |
For numeric datatypes, the number of decimal places to display. |
|
Null |
Select Yes or No from the Null drop-down list to specify whether NULLs are allowed in the column. Specifying No means the column cannot have null values; users must supply a value. No is the default in a new table. |
|
Default |
The value that will be placed in a column in a row that you insert into a DataWindow object. The drop-down list has built-in choices, but you can type any other value. For an explanation of the built-in choices, see your DBMS documentation. |
After you create and save a table, you can specify the properties of the table and of any or its columns. Table properties include the fonts used for headers, labels, and data, and a comment that you can associate with the table. Column properties include the text used for headers and labels, display formats, validation rules, and edit styles used for data (also known as a column's extended attributes), and a comment you can associate with the column.
In addition to adding a comment to associate with the table, you can choose the fonts that will be used to display information from the table in a DataWindow object. You can specify the font, point size, color, and style.
To specify table properties
-
Do one of the following:
-
Highlight the table in either the Objects view or the Object Layout view and select Properties from the Object or pop-up menu.
-
Click the Properties button.
-
Drag and drop the table to the Object Details view.
The properties for the table display in the Object Details view.
-
-
Select a tab and specify properties:
Select this tab
To modify this property
General
Comments associated with the table
Data Font
Font for data retrieved from the database and displayed in the Results view by clicking a Data Manipulation button
Heading Font
Font for column identifiers used in grid, tabular, and n-up DataWindow objects displayed in the Results view by clicking a Data Manipulation button
Label Font
Font for column identifiers used in freeform DataWindow objects displayed in the Results view by clicking a Data Manipulation button
-
Right-click on the Object Details view and select Save Changes from the pop-up menu.
Any changes you made in the Object Details view are immediately saved to the table definition.
In addition to adding a comment to associate with a column, you can specify extended attributes for each column. An extended attribute is information specific to PowerBuilder that enhances the definition of the column.
To specify extended attributes
-
Do one of the following:
-
Highlight the column in either the Objects view or the Object Layout view and select Properties from the Object or pop-up menu.
-
Click the Properties button.
-
Drag and drop the column to the Object Details view.
-
-
Select a tab and specify extended attribute values:
Select this tab
To modify these extended attributes
General
Column comments.
Headers
Label text used in free-form DataWindow objects.
Header text used in tabular, grid, or n-up DataWindow objects.
Display
How the data is formatted in a DataWindow object as well as display height, width, and position. For example, you can associate a display format with a Revenue column so that its data displays with a leading dollar sign and negative numbers display in parentheses.
Validation
Criteria that a value must pass to be accepted in a DataWindow object. For example, you can associate a validation rule with a Salary column so that you can enter a value only within a particular range.
The initial value for the column. You can select a value from the drop-down list. The initial value must be the same datatype as the column, must pass validation, and can be NULL only if NULL is allowed for the column.
Edit Style
How the column is presented in a DataWindow object. For example, you can display column values as radio buttons or in a drop-down list.
-
Right-click on the Column property sheet and select Save Changes from the pop-up menu.
Any changes you made in the property sheet are immediately saved to the table definition.
Overriding definitions
In the DataWindow painter, you can override the extended attributes specified in the Database painter for a particular DataWindow object.
How the information is stored
Extended attributes are stored in the PowerBuilder system tables in the database. PowerBuilder uses the information to display, present, and validate data in the Database painter and in DataWindow objects. When you create a view in the Database painter, the extended attributes of the table columns used in the view are used by default.
About display formats, edit styles, and validation rules
In the Database painter, you create display formats, edit styles, and validation rules. Whatever you create is then available for use with columns in tables in the database. You can see all the display formats, edit styles, and validation rules defined for the database in the Extended Attributes view.
For more information about defining, maintaining, and using these extended attributes, see Displaying and Validating Data.
About headings and labels
By default, PowerBuilder uses the column names as labels and headings, replacing any underscore characters with spaces and capitalizing each word in the name. For example, the default heading for the column Dept_name is Dept Name. To define multiple-line headings, press Ctrl+Enter to begin a new line.
You can also set two additional properties for character columns on the Display property page: Case and Picture.
Specifying the displayed case
You can specify whether PowerBuilder converts the case of characters for a column in a DataWindow object.
Specifying a column as a picture
You can specify that a character column can contain names of picture files.
To specify that column values are names of picture files
-
On the Display property page, select the Picture check box.
When the Picture check box is selected, PowerBuilder expects to find picture file names in the column and displays the contents of the picture file—not the name of the file—in reports and DataWindow objects.
Because PowerBuilder cannot determine the size of the image until runtime, it sets both display height and display width to 0 when you select the Picture check box.
-
Enter the size and the justification for the picture (optional).
After a table is created, how you can alter the table depends on your DBMS.
You can always:
-
Add or modify PowerBuilder-specific extended attributes for columns
-
Delete an index and create a new index
You can never:
-
Insert a column between two existing columns
-
Prohibit null values for an appended column
-
Alter an existing index
Some DBMSs let you do the following, but others do not:
-
Append columns that allow null values
-
Increase or decrease the number of characters allowed for data in an existing column
-
Allow null values
-
Prohibit null values in a column that allowed null values
Database painter is DBMS aware
The Database painter grays out or notifies you about actions that your DBMS prohibits.
For complete information about what you can and cannot do when you modify a table in your DBMS, see your DBMS documentation.
To alter a table
-
Highlight the table and select Alter Table from the pop-up menu.
Opening multiple instances of tables
You can open another instance of a table by selecting Columns from the View menu. Doing this is helpful when you want to use the Database painter's cut, copy, and paste features to cut or copy and paste between tables.
The table definition displays in the Columns view (this screen shows the Employee table).
-
Make the changes you want in the Columns view or in the Object Details view.
-
Select Save Table or Save Changes.
PowerBuilder submits the pending SQL syntax statements it generated to the DBMS, and the table is modified.
In the Database painter, you can use the Cut, Copy, and Paste buttons in the PainterBar (or Cut, Copy, and Paste from the Edit or pop-up menu) to cut, copy, and paste one column at a time within a table or between tables.
To cut or copy a column within a table
-
Put the insertion point anywhere in the column you want to cut or copy.
-
Click the Cut or Copy button in the PainterBar.
To paste a column within a table
-
Put the insertion point in the column you want to paste to.
If you are changing an existing table, put the insertion point in the last column of the table. If you try to insert a column between two columns, you get an error message. To an existing table, you can only append a column. If you are defining a new table, you can paste a column anywhere.
-
Click the Paste button in the PainterBar.
You can remove a table from a view by selecting Close or Reset View from its pop-up menu. This action only removes the table from the Database painter view. It does not drop (remove) the table from the database.
Dropping removes the table from the database.
To drop a table
-
Select Drop Table from the table's pop-up menu or select Object>Delete from the menu bar.
-
Click Yes.
Deleting orphaned table information
If you drop a table outside PowerBuilder, information remains in the system tables about the table, including extended attributes for the columns.
As you create or alter a table definition, you can view the pending SQL syntax changes that will be made when you save the table definition.
To view pending SQL syntax changes
-
Right-click the table definition in the Columns view and select Pending Syntax from the pop-up menu.
PowerBuilder displays the pending changes to the table definition in SQL syntax:
The SQL statements execute only when you save the table definition or reset the view and then tell PowerBuilder to save changes.
Copying, saving, and printing pending SQL changes
When you are viewing pending SQL changes, you can:
-
Copy pending changes to the clipboard
-
Save pending changes to a file
-
Print pending changes
To copy, save, or print only part of the SQL syntax
Select the part of the SQL syntax you want before you copy, save, or print.
To copy the SQL syntax to the clipboard
-
In the Pending Syntax view, click the Copy button or select Select All and then Copy from the pop-up menu.
To save SQL syntax for execution at a later time
-
In the Pending Syntax view, Select File>Save As.
The Save Syntax to File dialog box displays.
-
Navigate to the folder where you want to save SQL, name the file, and then click the Save button.
At a later time, you can import the SQL file into the Database painter and execute it.
You can print a report of the table's definition at any time, whether or not the table has been saved. The Table Definition Report contains information about the table and each column in the table, including the extended attributes for each column.
You can export the syntax for a table to the log. This feature is useful when you want to create a backup definition of the table before you alter it or when you want to create the same table in another DBMS.
To export to another DBMS, you must have the PowerBuilder interface for that DBMS.
To export the syntax of an existing table to a log
-
Select the table in the Objects or Object Layout view.
-
Select Export Syntax from the Object menu or the pop-up menu.
If you selected a table and have more than one DBMS interface installed, the DBMS dialog box displays. If you selected a view, PowerBuilder immediately exports the syntax to the log.
-
Select the DBMS to which you want to export the syntax.
If you selected ODBC, specify a data source in the Data Sources dialog box.
-
Supply any information you are prompted for.
PowerBuilder exports the syntax to the log. Extended attribute information (such as validation rules used) for the selected table is also exported. The syntax is in the format required by the DBMS you selected.
For more information about the log, see Logging your work.
Two kinds of system tables exist in the database:
-
System tables provided by your DBMS (for more information, see your DBMS documentation)
-
PowerBuilder extended attribute system tables
About PowerBuilder system tables
PowerBuilder stores extended attribute information you provide when you create or modify a table (such as the text to use for labels and headings for the columns, validation rules, display formats, and edit styles) in system tables. These system tables contain information about database tables and columns. Extended attribute information extends database definitions.
In the Employee table, for example, one column name is Emp_lname. A label and a heading for the column are defined for PowerBuilder to use in DataWindow objects. The column label is defined as Last Name:. The column heading is defined as Last Name. The label and heading are stored in the PBCatCol table in the extended attribute system tables.
The extended attribute system tables are maintained by PowerBuilder and only PowerBuilder users can enter information into them. The following table lists the extended attribute system tables. For more information, see Appendix A, The Extended Attribute System Tables.
|
This system table |
Stores this extended attribute information |
|---|---|
|
PBCatCol |
Column data such as name, header and label for reports and DataWindow objects, and header and label positions |
|
PBCatEdt |
Edit style names and definitions |
|
PBCatFmt |
Display format names and definitions |
|
PBCatTbl |
Table data such as name, fonts, and comments |
|
PBCatVld |
Validation rule names and definitions |
Opening and displaying system tables
You can open system tables like other tables in the Database painter.
By default, PowerBuilder shows only user-created tables in the Objects view. If you highlight Tables and select Show System Tables from the pop-up menu, PowerBuilder also displays system 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:
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