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.
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.
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.
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.
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 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
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
-
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.
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.
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.
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.
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 editor, 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.
