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
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 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.
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”.
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.
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.
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
Sybase Adaptive Server Enterprise stored procedures
When you use the Database painter to execute a Sybase 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.
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 pop-up menu.
Select the file containing the SQL, and click OK.
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.
