Administering the database
Administering your databases with the Database painter means
having the ability to:
- Create SQL statements to send to the DBMS for immediate execution
- Control access to the current database
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 paint SQL statements. You can also set editing
properties to make reading your SQL files easier.
Building and executing SQLstatements
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 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.
Controlling comments
By default, PowerBuilder strips off comments when it sends SQL
to the DBMS. You can have comments included by clearing the checkmark
next to Design>Strip Comments from the menu bar.
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:
-
Click the Paste SQL button in the PainterBar.
or
Select Paste SQL from the Edit or popup menu.
The SQL Statement Type dialog box displays listing the types
of SQL statements you can use. -
Double-click the appropriate icon to select the
statement type.The Select Table dialog box displays.
-
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 Select painter,
see Defining the data source . -
Follow the procedure for the statement you are
pasting:Type of statement What you do SELECT Define the statement exactly as in the
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 criteriaFor more information, see “Working with database views “INSERT Type the values to insert into each column.
You can insert as many rows as you wantUPDATE First specify the new values for the
columns in the Update Column Values dialog box. Then specify the
WHERE criteria to indicate which rows to updateDELETE Specify the WHERE criteria to indicate
which rows to delete -
When you have completed painting 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.
Sybase SQL server stored procedures When you use the Database painter to execute a Sybase SQL
Server 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:
-
Put the insertion point where you want
to insert the SQL. -
Select Paste Special>From File from the
Edit or popup menu.The File Import dialog box displays.
- Select the file containing the SQL and click OK.
Dragging a procedure or function from the Objects
view You can select from the tree view in the Objects view 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. When this is the case, you can use
Explain SQL on the Design menu to help you select the most efficient
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 a window identical
to a grid Data Manipulation 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
editor’s property sheet. 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 data types 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 checkbox is selected before
you set colors for SQL styles. You can turn off all Coloring properties
by clearing the checkbox.
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.
This part describes how to use PowerBuilder to manage your
database, how to build DataWindow objects to retrieve, present,
and manipulate data in your applications, and how to use the Data
Pipeline painter to copy data from one database to another.