Managing the Database

About this chapter

This chapter describes how to manage a database from within PowerBuilder.

Before you begin

You work with relational databases in PowerBuilder. If you are not familiar with relational databases, you might want to consult an introductory text.

Working with database components

A database is an electronic storage place for data. Databases are designed to ensure that data is valid and consistent and that it can be accessed, modified, and shared.

A database management system (DBMS) governs the activities of a database and enforces rules that ensure data integrity. A relational DBMS stores and organizes data in tables.

How you work with databases in PowerBuilder

You can use PowerBuilder to work with the following database components:

  • Tables and columns

  • Keys

  • Indexes

  • Database views

  • Extended attributes

  • Additional database components

Tables and columns

A database usually has many tables, each of which contains rows and columns of data. Each row in a table has the same columns, but a column's value for a particular row could be empty or NULL if the column's definition allows it.

Tables often have relationships with other tables. For example, in the PB Demo DB included with PowerBuilder, the Department table has a Dept_id column, and the Employee table also has a Dept_id column that identifies the department in which the employee works. When you work with the Department table and the Employee table, the relationship between them is specified by a join of the two tables.

Keys

Relational databases use keys to ensure database integrity.

Primary keysA primary key is a column or set of columns that uniquely identifies each row in a table. For example, two employees may have the same first and last names, but they have unique ID numbers. The Emp_id column in the Employee table is the primary key column.

Foreign keysA foreign key is a column or set of columns that contains primary key values from another table. For example, the Dept_id column is the primary key column in the Department table and a foreign key in the Employee table.

Key iconsIn PowerBuilder, columns defined as keys are displayed with key icons that use different shapes and colors for primary and foreign. PowerBuilder automatically joins tables that have a primary/foreign key relationship, with the join on the key columns.

In the following illustration there is a join on the dept_id column, which is a primary key for the department table and a foreign key for the employee table:


For more information, see Working with keys.

Indexes

An index is a column or set of columns you identify to improve database performance when searching for data specified by the index. You index a column that contains information you will need frequently. Primary and foreign keys are special examples of indexes.

You specify a column or set of columns with unique values as a unique index, represented by an icon with a single key.

You specify a column or set of columns that has values that are not unique as a duplicate index, represented by an icon with two file cabinets.

For more information, see Working with indexes.

Database views

If you often select data from the same tables and columns, you can create a database view of the tables. You give the database view a name, and each time you refer to it the associated SELECT command executes to find the data.

Database views are listed in the Objects view of the Database painter and can be displayed in the Object Layout view, but a database view does not physically exist in the database in the same way that a table does. Only its definition is stored in the database, and the view is re-created whenever the definition is used.

Database administrators often create database views for security purposes. For example, a database view of an Employee table that is available to users who are not in Human Resources might show all columns except Salary.

For more information, see Working with database views.

Extended attributes

Extended attributes enable you to store information about a table's columns in special system tables. Unlike tables, keys, indexes, and database views (which are DBMS-specific), extended attributes are PowerBuilder-specific. The most powerful extended attributes determine the edit style, display format, and validation rules for the column.

For more information about extended attributes, see Specifying column extended attributes. For more information about the extended attribute system tables, see Appendix A, The Extended Attribute System Tables.

Additional database components

Depending on the database to which you are connected and on your user privileges, you may be able to view or work with a variety of additional database components through PowerBuilder. These components might include:

  • Driver information

  • Groups

  • Metadata types

  • Procedures and functions

  • Users

  • Logins

  • Triggers

  • Events

  • Web services

For example, driver information is relevant to ODBC connections. It lists all the ODBC options associated with the ODBC driver, allowing you to determine how the ODBC interface will behave for a given connection. Login information is listed for Adaptive Server® Enterprise database connections. Information about groups and users is listed for several of the databases and allows you to add new users and groups and maintain passwords for existing users.

You can drag most items in these folders to the Object Details view to display their properties. You can also drag procedures, functions, triggers, and events to the ISQL view.

Trigger information is listed for Adaptive Server Enterprise and SQL Anywhere tables. A trigger is a special form of stored procedure that is associated with a specific database table. Triggers fire automatically whenever someone inserts, updates or deletes rows of the associated table. Triggers can call procedures and fire other triggers, but they have no parameters and cannot be invoked by a CALL statement. You use triggers when referential integrity and other declarative constraints are insufficient.

Events can be used in a SQL Anywhere database to automate database administration tasks, such as sending a message when disk space is low. Event handlers are activated when a provided trigger condition is met. If any events are defined for a SQL Anywhere connection, they display in the Events folder for the connection in the Objects view.

Managing databases

PowerBuilder supports many database management systems (DBMSs). For the most part, you work the same way in PowerBuilder for each DBMS, but because each DBMS provides some unique features (which PowerBuilder makes use of), there are some issues that are specific to a particular DBMS. For complete information about using your DBMS, see Connecting to Your Database.

What you can do

Using the Database painter, you can do the following in any DBMS to which you have been given access by the database administrator:

  • Modify local table and column properties

  • Retrieve, change, and insert data

  • Create new local tables or modify existing tables

Setting the database connection

When you open a painter that communicates with the database (such as the Database painter or DataWindow painter), PowerBuilder connects you to the database you used last if you are not already connected. If the connection to the default database fails, the painter still opens.

If you do not want to connect to the database you used last, you can deselect the Connect to Default Profile option in the Database Preferences dialog box.

Changing the database connection

You can change to a different database at any time. You can have several database connections open at a time, although only one connection can be active. The database components for each open connection are listed in the Objects view.

The Database painter title bar displays the number of open connections and which is active. The title bar for each view displays the connection with which it is currently associated. You can change the connection associated with a view by dragging the profile name for a different connection onto the view.

For more about changing the database you are connected to, see Working with Database Connections in Connecting to Your Database.

Creating and deleting databases

When you are connected to SQL Anywhere, you can create a new database or delete an existing database using the Database painter.

For all other DBMSs, creating and deleting a database is an administrative task that you cannot do within PowerBuilder.

Using the Database painter

To open the Database painter, click the Database button in the PowerBar.

About the painter

Like the other PowerBuilder painters, the Database painter contains a menu bar, customizable PainterBars, and several views. All database-related tasks that you can do in PowerBuilder can be done in the Database painter.


Views in the Database painter

The following table lists the views available in the Database painter.

View

Description

Activity Log

Displays the SQL syntax generated by the actions you execute.

Columns

Used to create and/or modify a table's columns.

Extended Attributes

Lists the display formats, edit styles, and validation rules defined for the selected database connection.

Interactive SQL

Used to build, execute, or explain SQL.

Object Details

Displays an object's properties. For some objects, its properties are read-only; for others, properties can be modified. This view is analogous to the Properties view in other painters.

Object Layout

Displays a graphical representation of tables and their relationships.

Objects

Lists database interfaces and profiles. For an active database connection, might also list all or some of the following objects associated with that database: groups, metadata types, procedures and functions, tables, columns, primary and foreign keys, indexes, users, views, driver information, events, triggers, and utilities (the database components listed depend on the database and your user privileges).

Results

Displays data in a grid, table, or freeform format.


Dragging and dropping

You can select certain database objects from the Objects view and drag them to the Object Details, Object Layout, Columns, and/or ISQL views. Position the pointer on the database object's icon and drag it to the appropriate view.

Object

Can be dragged to

Driver, group, metadata type, procedure or function, table, column, user, primary or foreign key, index, event trigger

Object Details view

Table or view

Object Layout view

Table or column

Columns view

Procedure or view

ISQL view


Database painter tasks

The following table describes how to do some basic tasks in the Database painter. Most of these tasks begin in the Objects view. Many can be accomplished by dragging and dropping objects into different views. If you prefer, you can use buttons or menu selections from the menu bar or from pop-up menus.

To

Do this

Modify a database profile

Highlight a database profile and select Properties from the Object or pop-up menu or use the Properties button.

You can use the Import and Export Profiles menu selections to copy profiles. For more information, see the section called “Importing and exporting database profiles” in Connecting to Your Database.

Connect to a database

Highlight a database profile and then select Connect from the File or pop-up menu or use the Connect button. With File>Recent Connections, you can review and return to earlier connections. You can also make database connections using the Database Profile button.

Create new profiles, tables, views, columns, keys, indexes, or groups

Highlight the database object and select New from the Object or pop-up menu or use the Create button.

Modify database objects

Drag the object to the Object Details view.

Graphically display tables

Drag the table icon from the list in the Objects view to the Object Layout view, or highlight the table and select Add To Layout from the Object or pop-up menu.

Manipulate data

Highlight the table and select Grid, Tabular, or Freeform from the Object>Data menu or the pop-up menu Edit Data item, or use the appropriate Data Manipulation button.

Build, execute or explain SQL

Use the ISQL view to build SQL statements. Use the Paste SQL button to paste SELECT, INSERT, UPDATE, and DELETE statements or type them directly into the view's workspace. To execute or explain SQL, select Execute SQL and Explain SQL from the Design or pop-up menu. (Explain SQL functionality is available for Sybase databases only.)

Define or modify extended attributes

Select from the Object>Insert menu the type of extended attribute you want to define or modify, or highlight the extended attribute from the list in the Extended Attributes view and select New or Properties from the pop-up menu.

Specify extended attributes for a column

Drag the column to the Object Details view and select the Extended Attributes tab.

Access database utilities

Double-click a utility in the Objects view to launch it.

Log your work

Select Design>Start Log from the menu bar. To see the SQL syntax generated, display the Activity Log view.


Modifying database preferences

To modify database preferences, select Design>Options from the menu bar. Some preferences are specific to the database connection; others are specific to the Database painter.

Preferences on the General property page

The Connect To Default Profile, Shared Database Profiles, Keep Connection Open, Use Extended Attributes, and Read Only preferences are specific to the database connection.

The remaining preferences are specific to the Database painter. For information about modifying these preferences, see the section called “Setting database preferences” in Connecting to Your Database.

Database preference

What PowerBuilder does with the specified preference

Columns in the Table List

When PowerBuilder displays tables graphically, eight table columns display unless you change the number of columns.

SQL Terminator Character

PowerBuilder uses the semicolon as the SQL statement terminator unless you enter a different terminator character in the box. Make sure that the character you choose is not reserved for another use by your database vendor. For example, using the slash character (/) causes compilation errors with some DBMSs.

Refresh Table List

When PowerBuilder first displays a table list, PowerBuilder retrieves the table list from the database and displays it. To save time, PowerBuilder saves this list internally for reuse to avoid regeneration of very large table lists. The table list is refreshed every 30 minutes (1800 seconds) unless you specify a different refresh rate.


Preferences on the Object Colors property page

You can set colors separately for each component of the Database painter's graphical table representation: the table header, columns, indexes, primary key, foreign keys, and joins. Set a color preference by selecting a color from a drop-down list.

You can design custom colors that you can use when you select color preferences. To design custom colors, select Design>Custom Colors from the menu bar and work in the Custom Colors dialog box.

Logging your work

As you work with your database, you generate SQL statements. As you define a new table, for example, PowerBuilder builds a SQL CREATE TABLE statement internally. When you save the table, PowerBuilder sends the SQL statement to the DBMS to create the table. Similarly, when you add an index, PowerBuilder builds a CREATE INDEX statement.

You can see all SQL generated in a Database painter session in the Activity Log view. You can also save this information to a file. This allows you to have a record of your work and makes it easy to duplicate the work if you need to create the same or similar tables in another database.

To start logging your work

  1. Open the Database painter.

  2. Select Start Log from the Design menu or the pop-up menu in the Activity Log view.

    PowerBuilder begins sending all generated syntax to the Activity Log view.

To stop the log

  • Select Stop Log from the Design menu or the pop-up menu in the Activity Log view.

    PowerBuilder stops sending the generated syntax to the Activity Log view. Your work is no longer logged.

To save the log to a permanent text file

  1. Select Save or Save As from the File menu.

  2. Name the file and click Save. The default file extension is SQL, but you can change that if you want to.

Submitting the log to your DBMS

You can open a saved log file and submit it to your DBMS in the ISQL view. For more information, see Building and executing SQL statements.

Creating and deleting a SQL Anywhere database

In PowerBuilder you work within an existing database. With one exception, creating or deleting a database is an administrative task that is not performed directly in PowerBuilder. The one exception is that you can create and delete a local SQL Anywhere database from within PowerBuilder.

For information about creating and deleting other databases, see your DBMS documentation.

To create a local SQL Anywhere database:

  1. From the Objects view, launch the Create SA Database utility included with the ODBC interface.

    The Create SQL Anywhere Database dialog box displays.

  2. In the Database Name box, specify the file name and path of the database you are creating.

    If you do not provide a file extension, the database file name is given the extension DB.

  3. Define other properties of the database as needed.

    If you are using a non-English database, you can specify a code page in the Collation Sequence box.

  4. For complete information about filling in the dialog box, click the Help button in the dialog box.

  5. Click OK.

    When you click OK, PowerBuilder does the following:

    • Creates a database with the specified name in the specified directory or folder. If a database with the same name exists, you are asked whether you want to replace it.

    • Adds a data source to the ODBC.INI key in the registry. The data source has the same name as the database unless one with the same name already exists, in which case a suffix is appended.

    • Creates a database profile and adds it to the registry. The profile has the same name as the database unless one with the same name already exists, in which case a suffix is appended.

    • Connects to the new database.

To delete a local SQL Anywhere database:

  1. Open the Database painter.

  2. From the Objects view, launch the Delete SA Database utility included with the ODBC interface.

  3. Select the database you want to delete and select Open.

  4. Click Yes to delete the database.

    When you click Yes, PowerBuilder deletes the specified database.

Working with 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.

Creating a new table from scratch

In PowerBuilder, you can create a new table in any database to which PowerBuilder is connected.

To create a table in the current database

  1. 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.

  2. 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.

  3. Repeat step 2 for each additional column in your table.

  4. (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.

  5. 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.

  6. Specify extended attributes for the columns.

    For what to enter in each field, see Specifying column extended attributes.

Creating a new table from an existing table

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

  1. Open the existing table in the Columns view by dragging and dropping it or selecting Alter Table from the pop-up menu.

  2. Right-click in the Columns view and select Save Table As from the pop-up menu.

  3. 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.

  4. Make whatever changes you want to the table definition.

  5. Save the table.

  6. 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.

Specifying column definitions

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.


Specifying table and column properties

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.

Specifying table properties

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

  1. 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.

  2. 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


  3. 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.

Specifying column extended attributes

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

  1. 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.

  2. 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.


  3. 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.

Specifying additional properties for character columns

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.

To specify how character data should be displayed

  • On the Display property page, select a value in the Case drop-down list:

    Value

    Meaning

    Any

    Characters are displayed as they are entered

    UPPER

    Characters are converted to uppercase

    lower

    Characters are converted to lowercase


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

  1. 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.

  2. Enter the size and the justification for the picture (optional).

Altering a table

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

  1. 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).


  2. Make the changes you want in the Columns view or in the Object Details view.

  3. Select Save Table or Save Changes.

    PowerBuilder submits the pending SQL syntax statements it generated to the DBMS, and the table is modified.

Cutting, copying, and pasting columns

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

  1. Put the insertion point anywhere in the column you want to cut or copy.

  2. Click the Cut or Copy button in the PainterBar.

To paste a column within a table

  1. 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.

  2. Click the Paste button in the PainterBar.

To paste a column to a different table

  1. Open another instance of the Columns view and use Alter Table to display an existing table or click New to create a new table.

  2. Put the insertion point in the column you want to paste to.

  3. Click the Paste button in the PainterBar.

Closing a table

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 a table

Dropping removes the table from the database.

To drop a table

  1. Select Drop Table from the table's pop-up menu or select Object>Delete from the menu bar.

  2. 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.

To delete orphaned table information from the extended attribute system tables

  • Select Design>Synch Extended Attributes from the menu bar and click Yes.

    If you try to delete orphaned table information and there is none, a message tells you that synchronization is not necessary.

Viewing pending SQL changes

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

  1. In the Pending Syntax view, Select File>Save As.

    The Save Syntax to File dialog box displays.

  2. 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.

To print pending table changes

  • While viewing the pending SQL syntax, click the Print button or select Print from the File menu.

To display columns in the Columns view

  • Select Object>Pending Syntax from the menu bar.

Printing the table definition

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.

To print the table definition

  • Select Print or Print Definition from the File or pop-up menu or click the Print button.

Exporting table syntax

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

  1. Select the table in the Objects or Object Layout view.

  2. 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.

  3. 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.

  4. 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.

About system tables

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.

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:

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

Working with keys

If your DBMS supports primary and foreign keys, you can work with the keys in PowerBuilder.

Why you should use keys

If your DBMS supports them, you should use primary and foreign keys to enforce the referential integrity of your database. That way you can rely on the DBMS to make sure that only valid values are entered for certain columns instead of having to write code to enforce valid values.

For example, say you have two tables called Department and Employee. The Department table contains the column Dept_Head_ID, which holds the ID of the department's manager. You want to make sure that only valid employee IDs are entered in this column. The only valid values for Dept_Head_ID in the Department table are values for Emp_ID in the Employee table.

To enforce this kind of relationship, you define a foreign key for Dept_Head_ID that points to the Employee table. With this key in place, the DBMS disallows any value for Dept_Head_ID that does not match an Emp_ID in the Employee table.

For more about primary and foreign keys, consult a book about relational database design or your DBMS documentation.

What you can do in the Database painter

You can work with keys in the following ways:

  • Look at existing primary and foreign keys

  • Open all tables that depend on a particular primary key

  • Open the table containing the primary key used by a particular foreign key

  • Create, alter, and drop keys

For the most part, you work with keys the same way for each DBMS that supports keys, but there are some DBMS-specific issues. For complete information about using keys with your DBMS, see your DBMS documentation.

Viewing keys

Keys can be viewed in several ways:

  • In the expanded tree view of a table in the Objects view

  • As icons connected by lines to a table in the Object Layout view

In the following picture, the sales_order table has three keys:

  • A primary key (on id)

  • Two foreign keys (on cust_id and fin_code_id)


If you cannot see the lines

If the color of your window background makes it difficult to see the lines for the keys and indexes, you can set the colors for each component of the Database painter's graphical table representation, including keys and indexes. For information, see Modifying database preferences.

Opening related tables

When working with tables containing keys, you can easily open related tables.

To open the table that a particular foreign key references:

  1. Display the foreign key pop-up menu.

  2. Select Open Referenced Table.

To open all tables referencing a particular primary key:

  1. Display the primary key pop-up menu.

  2. Select Open Dependent Table(s).

    PowerBuilder opens and expands all tables in the database containing foreign keys that reference the selected primary key.

Defining primary keys

If your DBMS supports primary keys, you can define them in PowerBuilder.

To create a primary key:

  1. Do one of the following:

    • Highlight the table for which you want to create a primary key and click the Create Primary Key drop-down toolbar button in PainterBar1.

    • Select Object>Insert>Primary Key from the main menu or New>Primary Key from the pop-up menu.

    • Expand the table's tree view, right-click Primary Key, and select New Primary Key from the pop-up menu.

    The Primary Key properties display in the Object Details view.

  2. Select one or more columns for the primary key.

    Columns that are allowed in a primary key

    Only a column that does not allow null values can be included as a column in a primary key definition. If you choose a column that allows null values, you get a DBMS error when you save the table. In DBMSs that allow rollback for Data Definition Language (DDL), the table definition is rolled back. In DBMSs that do not allow rollback for DDL, the Database painter is refreshed with the current definition of the table.

  3. Specify any information required by your DBMS.

    Naming a primary key

    Some DBMSs allow you to name a primary key and specify whether it is clustered or not clustered. For these DBMSs, the Primary Key property page has a way to specify these properties.

    For DBMS-specific information, see your DBMS documentation.

  4. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you made in the view are immediately saved to the table definition.

    Completing the primary key

    Some DBMSs automatically create a unique index when you define a primary key so that you can immediately begin to add data to the table. Others require you to create a unique index separately to support the primary key before populating the table with data.

    To find out what your DBMS does, see your DBMS documentation.

Defining foreign keys

If your DBMS supports foreign keys, you can define them in PowerBuilder.

To create a foreign key:

  1. Do one of the following:

    • Highlight the table and click the Create Foreign Key drop-down toolbar button in PainterBar1.

    • Select Object>Insert>Foreign Key from the main menu or New>Foreign Key from the pop-up menu.

    • Expand the table's tree view and right-click on Foreign Keys and select New Foreign Key from the pop-up menu.

    The Foreign Key properties display in the Object Details view. Some of the information is DBMS-specific.

  2. Name the foreign key in the Foreign Key Name box.

  3. Select the columns for the foreign key.

  4. On the Primary Key tab page, select the table and column containing the Primary key referenced by the foreign key you are defining.

    Key definitions must match exactly

    The definition of the foreign key columns must match the primary key columns, including datatype, precision (width), and scale (decimal specification).

  5. On the Rules tab page, specify any information required by your DBMS.

    For example, you might need to specify a delete rule by selecting one of the rules listed for On Delete of Primary Table Row.

    For DBMS-specific information, see your DBMS documentation.

  6. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you make in the view are immediately saved to the table definition.

Modifying keys

You can modify a primary key in PowerBuilder.

To modify a primary key:

  1. Do one of the following:

    • Highlight the primary key listed in the table's expanded tree view and click the Properties button.

    • Select Properties from the Object or pop-up menu.

  2. Drag the primary key icon and drop it in the Object Details view.

  3. Select one or more columns for the primary key.

  4. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you make in the view are immediately saved to the table definition.

Dropping a key

You can drop keys (remove them from the database) from within PowerBuilder.

To drop a key:

  1. Highlight the key in the expanded tree view for the table in the Objects view or right-click the key icon for the table in the Object Layout view.

  2. Select Drop Primary Key or Drop Foreign Key from the key's pop-up menu.

  3. Click Yes.

Working with indexes

You can create as many single- or multi-valued indexes for a database table as you need, and you can drop indexes that are no longer needed.

Update limitation

You can update a table in a DataWindow object only if it has a unique index or primary key.

Creating an index

In SQL Anywhere databases

In SQL Anywhere databases, you should not define an index on a column that is defined as a foreign key, because foreign keys are already optimized for quick reference.

To create an index:

  1. Do one of the following:

    • Highlight the table for which you want to create an index and click the Create Index drop-down toolbar button in PainterBar1.

    • Select Object>Insert>Index from the main menu or New>Index from the pop-up menu.

    • Expand the table's tree view, right-click on Indexes, and select New Index from the pop-up menu.

    The Index's properties display in the Object Details view.

  2. Enter a name for the index in the Index box.

  3. Select whether or not to allow duplicate values for the index.

  4. Specify any other information required for your database.

    For example, in Adaptive Server Enterprise specify whether the index is clustered, and in SQL Anywhere specify the order of the index.

  5. Click the names of the columns that make up the index.

  6. Select Save Changes from the pop-up menu.

  7. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you made in the view are immediately saved to the table definition.

Modifying an index

You can modify an index.

To modify an index:

  1. Do one of the following:

    • Highlight the index listed in the table's expanded tree view and click the Properties button.

    • Select Properties from the Object or pop-up menu.

  2. Drag the index icon and drop it in the Object Details view.

  3. In the Object Details view, select or deselect columns as needed.

  4. Right-click on the Object Details view and select Save Changes from the pop-up menu.

    Any changes you made in the view are immediately saved to the table definition.

Dropping an index

Dropping an index removes it from the database.

To drop an index from a table:

  1. In the Database painter workspace, display the pop-up menu for the index you want to drop.

  2. Select Drop Index and click Yes.

Working with database views

A database view gives a different (and usually limited) perspective of the data in one or more tables. Although you see existing database views listed in the Objects view, a database view does not physically exist in the database as a table does. Each time you select a database view and use the view's data, PowerBuilder executes a SQL SELECT statement to retrieve the data and creates the database view.

For more information about using database views, see your DBMS documentation.

Using database views in PowerBuilder

You can define and manipulate database views in PowerBuilder. Typically you use database views for the following reasons:

  • To give names to frequently executed SELECT statements.

  • To limit access to data in a table. For example, you can create a database view of all the columns in the Employee table except Salary. Users of the database view can see and update all information except the employee's salary.

  • To combine information from multiple tables for easy access.

In PowerBuilder, you can create single- or multiple-table database views. You can also use a database view when you define data to create a new database view.

You define, open, and manipulate database views in the View painter, which is similar to the SQL Select painter. For more information about the SQL Select painter, see Selecting a data source.

Updating database views

Some database views are logically updatable and others are not. Some DBMSs do not allow any updating of views. For the rules your DBMS follows, see your DBMS documentation.

To open a database view:

  1. In the Objects view, expand the list of Views for your database.

  2. Highlight the view you want to open and select Add To Layout from the pop-up menu, or drag the view's icon to the Object Layout view.

To create a database view:

  1. Click the Create View button, or select View or New View from the Object>Insert or pop-up menu.

    The Select Tables dialog box displays, listing all tables and views that you can access in the database.

  2. Select the tables and views from which you will create the view by doing one of the following:

    • Click the name of each table or view you want to open in the list displayed in the Select Tables dialog box, then click the Open button to open them. The Select Tables dialog box closes.

    • Double-click the name of each table or view you want to open. Each object is opened immediately. Then click the Cancel button to close the Select Tables dialog box.

    Representations of the selected tables and views display in the View painter workspace:


  3. Select the columns to include in the view and include computed columns as needed.

  4. Join the tables if there is more than one table in the view.

    For information, see Joining tables.

  5. Specify criteria to limit rows retrieved (Where tab), group retrieved rows (Group tab), and limit the retrieved groups (Having tab), if appropriate.

    For information, see the section on using the SQL Select painter in Selecting a data source. The View painter and the SQL Select painter are similar.

  6. When you have completed the view, click the Return button.

  7. Name the view.

  8. Include view or some other identifier in the view's name so that you will be able to distinguish it from a table in the Select Tables dialog box.

  9. Click the Create button.

    PowerBuilder generates a CREATE VIEW statement and submits it to the DBMS. The view definition is created in the database. You return to the Database painter workspace with the new view displayed in the workspace.

Displaying a database view's SQL statement

You can display the SQL statement that defines a database view. How you do it depends on whether you are creating a new view in the View painter or want to look at the definition of an existing view.

To display the SQL statement from the View painter:

  • Select the Syntax tab in the View painter.

    PowerBuilder displays the SQL it is generating. The display is updated each time you change the view.

To display the SQL statement from the Database painter:

  • Highlight the name of the database view in the Objects view and select Properties from the pop-up menu, or drag the view's icon to the Object Details view.

    The completed SELECT statement used to create the database view displays in the Definition field on the General page:


View dialog box is read-only

You cannot alter the view definition in the Object Details view. To alter a view, drop it and create another view.

Joining tables

If the database view contains more than one table, you should join the tables on their common columns. When the View painter is first opened for a database view containing more than one table, PowerBuilder makes its best guess as to the join columns, as follows:

  • If there is a primary/foreign key relationship between the tables, PowerBuilder automatically joins them.

  • If there are no keys, PowerBuilder tries to join tables based on common column names and types.

To join tables:

  1. Click the Join button.

  2. Click the columns on which you want to join the tables.

    In the following screen, the Employee and Department tables are joined on the dept_id column:


  3. To create a join other than the equality join, click the join representation in the workspace.

    The Join dialog box displays:


  4. Select the join operator you want from the Join dialog box.

    If your DBMS supports outer joins, outer join options also display in the Join dialog box. For example, in the preceding dialog box (which uses the Employee and Department tables), you can choose to include rows from the Employee table where there are no matching departments, or rows from the Department table where there are no matching employees.

    For more about outer joins, see Using ANSI outer joins.

Dropping a database view

Dropping a database view removes its definition from the database.

To drop a view:

  1. In the Objects view, select the database view you want to drop.

  2. Click the Drop Object button or select Drop View from the pop-up menu.

    PowerBuilder prompts you to confirm the drop, then generates a DROP VIEW statement and submits it to the DBMS.

Exporting view syntax

You can export the syntax for a view to the log. This feature is useful when you want to create a backup definition of the view before you alter it or when you want to create the same view in another DBMS.

To export the syntax of an existing view to a log:

  1. Select the view in the painter workspace.

  2. Select Export Syntax from the Object menu or the pop-up menu.

    For more information about the log, see Logging your work.

Manipulating data

As you work on the database, you often want to look at existing data or create some data for testing purposes. You might also want to test display formats, validation rules, and edit styles on real data.

PowerBuilder provides data manipulation for such purposes. With data manipulation, you can:

  • Retrieve and manipulate database information

  • Save the contents of the database in a variety of formats (such as Excel, PDF, or XML)

Retrieving data

To retrieve data

  1. In the Database painter, select the table or database view whose data you want to manipulate.

  2. Do one of the following:

    • Click one of the three Data Manipulation buttons (Grid, Tabular, or Freeform) in the PainterBar.

    • Select Data or Edit Data from the Object or pop-up menu and choose one of the edit options from the cascading menu that displays.

    All rows are retrieved and display in the Results view. As the rows are being retrieved, the Retrieve button changes to a Cancel button. You can click the Cancel button to stop the retrieval.

    Exactly what you see in the Results view depends on the formatting style you picked. What you see is actually a DataWindow object. The formatting style you picked corresponds to a type of DataWindow object (grid, tabular, or freeform). In a grid display, you can drag the mouse on a column's border to resize the column.

    This window is in the grid format:


    Only a few rows of data display at a time. You can use the First, Prior, Next, and Last buttons or the pop-up menu to move from page to page.

Modifying data

You can add, modify, or delete rows. When you have finished manipulating the data, you can apply the changes to the database.

If looking at data from a view

Some views are logically updatable and others are not. Some DBMSs do not allow any updating of views.

For the rules your DBMS follows regarding updating of views, see your DBMS documentation.

To modify data

  1. Do one of the following:

    • To modify existing data, tab to a field and enter a new value.

    • To add a row, click the Insert Row button and enter data in the new row.

    • To delete a row, click the Delete Row button.

    When you add or modify data, the data uses the validation rules, display formats, and edit styles that you or others have defined for the table in the Database painter.

  2. Click the Save Changes button or select Rows>Update to apply changes to the database.

Sorting rows

You can sort the data, but any sort criteria you define are for testing only and are not saved with the table or passed to the DataWindow painter.

To sort the rows

  1. Select Rows > Sort from the menu bar.

    The Specify Sort Columns dialog box displays.

  2. Drag the columns you want to sort on from the Source Data box to the Columns box:


    A check box with a check mark in it displays under the Ascending heading to indicate that the values will be sorted in ascending order. To sort in descending order, clear the check box.

    Precedence of sorting

    The order in which the columns display in the Columns box determines the precedence of the sorting. For example, in the preceding dialog box, rows would be sorted by department ID. Within department ID, rows would be sorted by state.

    To change the precedence order, drag the column names in the Column box into the order you want.

  3. (Optional) Double-click an item in the Columns box to specify an expression to sort on.

    The Modify Expression dialog box displays.

  4. Specify the expression.

    For example, if you have two columns, Revenues and Expenses, you can sort on the expression Revenues – Expenses.

  5. Click OK to return to the Specify Sort Columns dialog box with the expression displayed.

    If you change your mind

    You can remove a column or expression from the sorting specification by simply dragging it and releasing it outside the Columns box.

  6. When you have specified all the sort columns and expressions, click OK.

Filtering rows

You can limit which rows are displayed by defining a filter.

The filters you define are for testing only and are not saved with the table or passed to the DataWindow painter.

To filter the rows

  1. Select Rows>Filter from the menu bar.

    The Specify Filter dialog box displays.

  2. Enter a boolean expression that PowerBuilder will test against each row:


    If the expression evaluates to TRUE, the row is displayed. You can paste functions, columns, and operators in the expression.

  3. Click OK.

    PowerBuilder filters the data. Only rows meeting the filter criteria are displayed.

To remove the filter

  1. Select Rows>Filter from the menu bar.

    The Specify Filter dialog box displays, showing the current filter.

  2. Delete the filter expression, then click OK.

Filtered rows and updates

Filtered rows are updated when you update the database.

Viewing row information

You can display information about the data you have retrieved.

To display row information

  • Select Rows>Described from the menu bar.

    The Describe Rows dialog box displays showing the number of:

    • Rows that have been deleted in the Database painter but not yet deleted from the database

    • Rows displayed in Preview

    • Rows that have been filtered

    • Rows that have been modified in the Database painter but not yet modified in the database

    All row counts are zero until you retrieve the data from the database or add a new row. The count changes when you modify the displayed data or test filter criteria.

Importing data

You can import data from an external source and then save the imported data in the database.

To import data

  1. Select Rows>Import from the menu bar.

    The Select Import File dialog box displays.

  2. Specify the file from which you want to import the data.

    The types of files you can import into the Database painter are shown in the Files of Type drop-down list.

  3. Click Open.

    PowerBuilder reads the data from the file. You can click the Save Changes button or select Rows>Update to add the new rows to the database.

Printing data

You can print the data displayed by selecting File>Print from the menu bar. Before printing, you can also preview the output on the screen.

To preview printed output before printing

  1. Select File>Print Preview from the menu bar.

  2. Preview displays the data as it will print. To display rulers around the page borders in Print Preview, select File>Print Preview Rulers.

    To change the magnification used in Print Preview, select File>Print Preview Zoom from the menu bar.

    The Zoom dialog box displays.

  3. Select the magnification you want and click OK.

  4. Preview zooms in or out as appropriate.

    When you have finished looking at the print layout, select File>Print Preview from the menu bar again.

Saving data

You can save the displayed data in an external file.

To save the data in an external file

  1. Select File>Save Rows As from the menu bar.

    The Save Rows As dialog box displays.

  2. Choose a format for the file.

    You can select from several formats, including Powersoft report (PSR), XML, PDF, and HTML.

    If you want the column headers saved in the file, select a file format that includes headers, such as Excel With Headers. When you select a with headers format, the names of the database columns (not the column labels) will also be saved in the file.

    For more information, see Saving data in an external file.

    For TEXT, CSV, SQL, HTML, and DIF formats, select an encoding for the file.

    You can select ANSI/DBCS, Unicode LE (Little-Endian), Unicode BE (Big-Endian), or UTF8.

  3. Name the file and save it.

    PowerBuilder saves all displayed rows in the file; all columns in the displayed rows are saved. Filtered rows are not saved.

Creating and executing SQL statements

The Database painter's Interactive SQL view is a SQL editor in which you can enter and execute SQL statements. The view provides all editing capabilities needed for writing and modifying SQL statements. You can cut, copy, and paste text; search for and replace text; and create SQL statements. You can also set editing properties to make reading your SQL files easier.

Building and executing SQL statements

You can use the Interactive SQL view to build SQL statements and execute them immediately. The view acts as a notepad in which you can enter SQL statements.

Creating stored procedures

You can use the Interactive SQL view to create stored procedures or triggers, but make sure that the Database painter's SQL statement terminator character is not the same as the terminator character used in the stored procedure language of your DBMS.

About the statement terminator

By default, PowerBuilder uses the semicolon as the SQL statement terminator. You can override the semicolon by specifying a different terminator character in the Database painter. To change the terminator character, select Design>Options from the Database painter's menu bar.

Make sure that the character you choose is not reserved for another use by your database vendor. For example, using the slash character (/) causes compilation errors with some DBMSs.

Controlling comments

By default, PowerBuilder strips off comments when it sends SQL to the DBMS. You can have comments included by clearing the check mark next to Strip Comments in the pop-up menu of the Interactive SQL view.

Entering SQL

You can enter a SQL statement in four ways:

  • Pasting the statement

  • Typing the statement in the view

  • Opening a text file containing the SQL

  • Dragging a procedure or function from the Objects view

Pasting SQL

You can paste SELECT, INSERT, UPDATE, and DELETE statements to the view. Depending on which kind of statement you want to paste, PowerBuilder displays dialog boxes that guide you through painting the full statement.

To paste a SQL statement to the workspace

  1. Click the Paste SQL button in the PainterBar, or select Paste Special>SQL from the Edit or pop-up menu, then the statement type (Select, Insert, Update, or Delete).

    The Select Table(s) dialog box displays.

  2. Select the table(s) you will reference in the SQL statement.

    You go to the Select, Insert, Update, or Delete painter, depending on the type of SQL statement you are pasting. The Insert, Update, and Delete painters are similar to the Select painter, but only the appropriate tabs display in the SQL toolbox at the bottom of the workspace.

    For more information about the SQL Select painter, see Selecting a data source.

  3. Do one of the following:

    • For a SELECT statement, define the statement exactly as in the SQL Select painter when building a view.

      You choose the columns to select. You can define computed columns, specify sorting and joining criteria, and WHERE, GROUP BY, and HAVING criteria. For more information, see Working with database views.

    • For an INSERT statement, type the values to insert into each column. You can insert as many rows as you want.

    • For an UPDATE statement, specify the new values for the columns in the Update Column Values dialog box. Then specify the WHERE criteria to indicate which rows to update.

    • For a DELETE statement, specify the WHERE criteria to indicate which rows to delete.

  4. When you have finished creating the SQL statement, click the Return button in the PainterBar in the Select, Insert, Update, or Delete painter.

    You return to the Database painter with the SQL statement pasted into the ISQL view.

Typing SQL

Rather than paste, you can simply type one or more SQL statements directly in the ISQL view.

You can enter most statements supported by your DBMS. This includes statements you can paint as well as statements you cannot paint, such as a database stored procedure or CREATE TRIGGER statement.

You cannot enter certain statements that could destabilize the PowerBuilder development environment. These include the SET statement and the USE database statement. However, you might want to use a SET statement to change a default setting in the development environment, such as SET NOCOUNT ON or SET ANSI_WARNINGS OFF. You can enable SET commands in the ISQL view for database interfaces that support them by adding the following line to the [Database] section in your PB.INI file:

EnableSet=1

SAP Adaptive Server Enterprise stored procedures

When you use the Database painter to execute an SAP Adaptive Server Enterprise system stored procedure, you must start the syntax with the keyword EXEC or EXECUTE. For example, enter EXEC SP_LOCK. You cannot execute the stored procedure simply by entering its name.

Importing SQL from a text file

You can import SQL that has been saved in a text file into the Database painter.

To read SQL from a file

  1. Put the insertion point where you want to insert the SQL.

  2. Select Paste Special>From File from the Edit or pop-up menu.

  3. Select the file containing the SQL, and click OK.

Dragging a procedure or function from the Objects view

From the tree view in the Objects view, you can select an existing procedure or function that contains a SQL statement you want to enter, and drag it to the Interactive SQL view.

Explaining SQL

Sometimes there is more than one way to code SQL statements to obtain the results you want. If you connect to an SAP database using an SAP native driver, or to a SQL Anywhere database using the ODBC driver, you can select Explain SQL on the Design menu to help you choose the most efficient coding method. Explain SQL displays information about the path that PowerBuilder will use to execute the statements in the SQL Statement Execution Plan dialog box. This is most useful when you are retrieving or updating data in an indexed column or using multiple tables.

DBMS-specific information

The information displayed in the SQL Statement Execution Plan dialog box depends on your DBMS. For more about the SQL execution plan, see your DBMS documentation.

Executing SQL

When you have the SQL statements you want in the workspace, you can submit them to the DBMS.

To execute the SQL

  • Click the Execute button, or select Design>Execute SQL from the menu bar.

    If the SQL retrieves data, the data appears in grid format in the Results view. If there is a database error, you see a message box describing the problem.

    For a description of what you can do with the data, see Manipulating data.

Customizing the editor

The Interactive SQL view provides the same editing capabilities as the file editor. It also has Script, Font, and Coloring properties that you can change to make SQL files easier to read. With no change in properties, SQL files have black text on a white background and a tab stop setting of 3 for indentation.

Setting Script and Font properties

Select Design>Options from the menu bar to open the Database Preferences dialog box. The Script and Font properties are the same as those you can set for the file editor.

For more information, see Using the file editor.

Editor properties apply elsewhere

When you set Script and Font properties for the Database painter, the settings also apply to the Script view, the file editor, and the Debug window.

Setting Coloring properties

You can set the text color and background color for SQL styles (such as datatypes and keywords) so that the style will stand out and the SQL code will be more readable. You set Coloring properties on the Coloring tab page.

Enabling syntax coloring

Be sure the Enable Syntax Coloring check box is selected before you set colors for SQL styles. You can turn off all Coloring properties by clearing the check box.

Controlling access to the current database

The Database painter's Design menu provides access to a series of dialog boxes you can use to control access to the current database. In some DBMSs, for example, you can assign table access privileges to users and groups.

Which menu items display on the Design menu and which dialog boxes display depend on your DBMS.

For information about support for security options in your DBMS, see Connecting to Your Database and your DBMS documentation.

Using the ASA MobiLink synchronization wizard

About MobiLink

MobiLink™ is a session-based synchronization system that allows two-way synchronization between a main database, called the consolidated database, and multiple remote databases. The ASA MobiLink Synchronization wizard on the Database tab of the New dialog box creates objects that facilitate control of database synchronization from a PowerBuilder application.

This section describes the MobiLink synchronization wizard and the objects it creates. For more detailed information about synchronization from PowerBuilder applications, including information about creating consolidated and remote databases, as well as synchronization objects without using the wizard, see the section called “Using MobiLink Synchronization” in Application Techniques.

What the wizard generates

You use the ASA MobiLink Synchronization wizard to create a nonvisual user object and a global external function that invokes the MobiLink dbmlsync executable. By default, the wizard also adds two windows and a second global function, but these objects are optional.

The wizard-generated objects make it easier to add database synchronization capabilities to a PowerBuilder target. A structure that inherits from the PowerBuilder SyncParm object is also instantiated by default by one of the wizard-generated global functions. The SyncParm structure is used to hold sensitive database connection parameters entered by an end user in the synchronization options window.

The following table shows objects that can be generated by the wizard, listed by their default names, where appname stands for the name of the current application.

Default name

Description

nvo_appname_mlsync

An instance of the MLSync standard class user object that starts synchronization from the remote client.

gf_appname_sync

Global function that instantiates nvo_appname_mlsync to start the synchronization. This function includes the logic to start the synchronization with or without a feedback window.

w_appname_syncprogress

Optional feedback window that can be used to display synchronization status to the client.

gf_appname_configure_sync

Optional global function that calls the w_appname_sync_options window, which allows the user to configure the dbmlsync client before invoking the dbmlsync executable.

w_appname_sync_options

Window that allows the application user to change connection arguments at runtime.


Using a desktop database profile

Some information that you enter in the wizard is optional, but other information is required. The wizard prompts you for a database profile, which it uses to establish a connection to a remote database on the development computer. If you are not testing a connection on the desktop, you can select the option to proceed without a database connection and ignore the database profile field.

A database profile is required for automatic retrieval of publication names in the database. A publication is a database object describing data to be synchronized. A publication, along with a synchronization user name and a synchronization subscription, is required for MobiLink synchronization.

Selecting publication names

The wizard lets you select multiple publication names if they exist in the remote database defined by the connection profile. There must be subscriptions associated with the publication in order for them to display in the publication selection list.

If you selected the option to proceed without a database connection, the wizard prompts you to type a publication name (or a comma-separated list of publication names) in the MobiLink Client Publication wizard page instead of prompting you to select publication names retrieved from the database.

For more information about publications, see MobiLink - Client Administration on the SQL Anywhere online Help.

Overriding registry settings on the client computer

By default, information you enter in the wizard is saved in properties of the nvo_appname_mlsync user object that the wizard generates. This information includes values that you select for MobiLink logging and command line options and the MobiLink server and port. Prior to synchronization, the values of these properties can be modified with values entered by an application user in the w_appname_sync_options Options window.

The first time synchronization is run, user object property values are entered into the client computer registry. The next time the application is run, this information is available for retrieval from the registry.

The ASA MobiLink Synchronization wizard has an optional Override Registry Settings screen that allows you to override client registry settings. When you enable runtime overrides to the client registry settings, you must assign a build number to the objects generated by the wizard.

The build number you assign can be any positive numeric value. To override the registry settings, the build number you assign must be higher than the build number in the registry, if there is one. Registry settings will be used if the build number in the registry is equal to or lower than the build number in the ObjectRevision property of the nvo_appname_mlsync user object that the wizard generates.

Security measure

For security reasons, the MobiLink user name and password, and the authentication parameters and encryption key database settings are never saved to the registry.

The Override Registry Settings page of the wizard displays only if you do not change the radio button option to prompt the application user for password and runtime changes on the previous wizard page (Optional Runtime Configuration Objects). If, however, you change the radio button selection to disallow runtime overrides to the synchronization, the wizard does not display the Override Registry Settings page and does not generate the w_appname_sync_options Options window.

Wizard options

Except for the object name settings, the following table lists the ASA MobiLink Synchronization wizard options.

Option

Description

Destination library

Lets you select the target PBL file where you want to generate the MobiLink synchronization objects.

Desktop database connection

Lets you select a PowerBuilder database profile or proceed without a database connection.

Publication name

Lets you select a publication (or multiple publications) if you specified a database profile for a desktop database connection. If you did not, you can type the name of a publication you want to synchronize.

Override registry settings

Lets you override client registry settings with values that you (or application users) select for MobiLink logging and command line options, and the MobiLink server and port for the application

Client logging options

Specifies what information gets written to the synchronization log and whether you save the information to a log file.

Additional command line options

Adds the options you specify to the command line for starting the MobiLink synchronization client. You can click the Usage button to see a list of valid options.

Extended options

Adds extended options you specify. You do not need to enter the "-e" switch for extended options in this field. You can click the Usage button to see a list of valid extended options.

Single quotes must be used for any extended option values requiring quotation marks. You must separate multiple options with semicolons; for example:

scn=on;adr='host=localhost;port=2439'

Host

Sets the host information for connecting to the MobiLink synchronization server. If you enter a value for this field, it overrides any value set in synchronization subscriptions and in the Extended Options field.

Port

Sets the port for connecting to the MobiLink synchronization server. The default port for MobiLink is 2439. The value you enter for this field overrides any value set in synchronization subscriptions and in the Extended Options field.


Trying out MobiLink synchronization

This section describes how to try out the ASA MobiLink Synchronization wizard in a sample application. To get started, create a new workspace and a template application. You do not need to create a SQL database connection, but you do need to create a project.

Before you use the wizard to generate objects for the application, you need to set up a remote database and add at least one publication, user, and subscription to it, and create a PowerBuilder database profile for the remote database. To test the synchronization objects from your application, you need to set up a consolidated database. You can create your own remote and consolidated databases, as described in the section called “Using MobiLink Synchronization” in Application Techniques.

To test the synchronization objects, complete the following steps:

Run the wizard

You start the wizard from the Database tab of the New dialog box. The wizard prompts you for a database profile and a publication, although you can enter this information at a later time after you generate synchronization objects.

To run the MobiLink synchronization wizard

  1. Select File>New from the PowerBuilder menu bar.

  2. Click the Database tab, select the ASA MobiLink Synchronization wizard, and click OK.

  3. Follow the instructions in the wizard, providing the information the wizard needs.

    For help using the wizard, place the mouse pointer in any wizard field and press F1.

  4. On the last page of the wizard, make sure the Generate To-Do List check box is selected if you want the wizard to add items to the To-Do List to guide and facilitate your development work.

  5. When you are satisfied with your choices in the wizard, click Finish.

    The wizard generates objects that you can use for database synchronization.

Call synchronization objects from your application

Open a menu for your application in the Menu painter and add two submenu items to the File menu, called Synchronize and Sync Options. Add the following code to the Clicked event of the Synchronize menu item (appname is the name of your application):

syncparm s_opt
gf_appname_sync(s_opt)

Add the following code to the Clicked event of the Sync Options menu item:

gf_appname_configure_sync()

Deploy the application and database files

Use the Project painter to deploy the application to the desktop and copy this to all computers that will be connecting remotely to the MobiLink server. You need to copy the remote database to all end-user computers, and either register the database as an ODBC database or include connection parameters in a data source name (DSN) file.

For information on additional files and registry entries required on end-user computers, see the section called “Using MobiLink Synchronization” in Application Techniques.

Start the MobiLink server

Select MobiLink Synchronization Server from the Utilities folder in the Database painter. Fill in the required information and click OK to start the server.

For more information, see Starting the MobiLink synchronization server.

Run the application

Run the application on the remote computer and select the File>Synchronize and File>Sync Options menu items to test their operation.

Managing MobiLink synchronization on the server

You can start the MobiLink synchronization server and SQL Central (formerly known as Sybase Central) from the PowerBuilder UI.

Starting the MobiLink synchronization server

Before you synchronize remote databases with the consolidated database, you must start the MobiLink synchronization server. You can start the server from the Database or the Database Profile painter in PowerBuilder.

To start the MobiLink synchronization server

  1. From the Objects view of the Database painter or from the Database Profile painter, expand the ODBC Utilities folder and click MobiLink Synchronization server.

    The MobiLink Synchronization Server Options dialog box displays.

  2. Select the MobiLink version and enter the ODBC connection string for your consolidated database.

    The values that populate the MobiLink version drop-down list come from the SQL Anywhere versions listed in the hkey_local_machine\software\odbc\odbcinst.ini registry key.

    The ODBC connection string should not contain any blank spaces that are not part of the data source name. The following is an example of an ODBC connection string for the SQL Anywhere demonstration database:

    DSN=SQL Anywhere 11 Demo;UID=dba;PWD=SQL
  3. Define other options as needed.

    For information about filling in specific fields in the dialog box, click the Help button in the dialog box. The Usage button opens a dialog box with information about command line options.

  4. Click OK.

    When you click OK, PowerBuilder starts the MobiLink Synchronization server.

Using SQL Central

You can use SQL Central (formerly known as Sybase Central) to manage MobiLink synchronization and create synchronization scripts that are held in the consolidated database. You can also use the SQL Anywhere plug-in to SQL Central to add publications, synchronization users, and synchronization subscriptions to remote databases.

To start SQL Central

  • From the Objects view of the Database painter or from the Database Profile painter, expand the ODBC Utilities folder, and click SQL Central.

    SQL Central displays.

To work with the consolidated database in SQL Central

  • Select Connections>Connect with MobiLink 11 from the SQL Central menu, enter connection parameters in the Connect to Consolidated Database dialog box, and click OK.

    You can use SQL Central to add scripts for database tables and select synchronization events that cause the script to be executed.

To work with remote databases in SQL Central

  • Select Connections>Connect with SQL Anywhere 11 from the SQL Central menu, enter connection parameters in the Connect dialog box, and click OK.

    If you open the Publications and MobiLink Users folders in SQL Central, you can add publications and synchronization users for the remote database.

    After you add a publication and a synchronization user, you can create a synchronization subscription by linking a publication to a synchronization user.

    For more information, see the section called “Using MobiLink Synchronization” in Application Techniques and the SQL Anywhere online Help. You can also use the Help menu for the SQL Anywhere and MobiLink plug-ins to SQL Central.