SQL Editor

The SQL Editor is used to edit individual SQL statements that will be run in the database configuration. It enables you to write SQL statements, insert field paths, and access results from previous statements. In addition, it enables you to place SQL comments within the SQL code itself, making it easier to document the exact behavior of a particular query.

Launching the SQL Editor

To open the SQL Editor, double-click on the statement you want to maintain from the Database Configuration Editor. Alternatively, select the statement and then select the Edit Statement icon from the Statement Toolbar. The SQL Editor is then displayed.

The SQL Editor consists of the following key sections:

Section	Description
Statement	The name of the SQL Statement. Click the Rename link to display the Rename Statement screen, which enables you to enter a new name for the statement. By default, when the statement is created, the statement is assigned a name `New Statement`.
Comments	Enter any comments for this statement. Comments are shown on the Database Configuration Editor and enable users to quickly identify the purpose of the statement.
SQL Text Editor Pane	The SQL Text Editor is a color-coded text editor that supports SQL. It allows the SQL statements to be written, field paths to be easily inserted, and results from previous statements to be accessed. In addition, it allows SQL comments to be placed within the SQL code itself, making it easier to document the exact behavior of a particular query. Refer to SQL Editor for details. Database components can only process one result set from a `SELECT` query. Therefore you should provide one `SELECT` query per statement.
Result Substitution Panel	Once queries have been executed, the results can be inserted into either message properties, fields, or both. Result substitution is primarily used within a Database Lookup filter and the Database communication point in Out->In mode, where a message is being updated with some values retrieved via one or more database queries. Refer to Result Substitution for details.
Server Explorer	Displays the database tables, views, and stored procedures. This allows easy reference to the database.
Message Structure	Displays the message structure. This allows easy reference to the message definition and enables you to drag and drop message properties into your query.

SQL Generation

While SQL statements may always be manually entered and edited, the Rhapsody Database Configuration Editor can automatically generate the basis of some commonly used queries. This is done by selecting a table in the Server Explorer, then selecting the Generate SQL button as shown in the following diagram:

This enables you to generate an SQL SELECT, INSERT, or UPDATE query from the database structure. The base query can then be modified as desired.

Alternatively, you can automatically generate an SQL query, by right-clicking and dragging a database table from the server explorer onto the SQL text editor. A context menu is then displayed, which enables you to select the type of SQL query to be generated:

In Rhapsody, repeating SQL spaces are trimmed. The only exceptions are SQL strings with quotes, inside which spaces are not removed during parsing. For example, the three spaces in the quotes are preserved in the following query: SELECT PatientID FROM patient WHERE SpecialField = 'Part1 Part2'.

Parameterizing SQL Statements

SQL statements can be parameterized using values retrieved from the incoming message:

To use a...	Usage	Example
Message Property	Insert a dollar sign (`$`) in front of the message property name. Dynamic table and column names may be specified in a database SQL statement using message properties. Refer to Using Dynamic Table and Column Names for details.	`$myProperty`
Field Within an EDI Message	Insert an at sign (`@`) in front of the full path to the message field.	`@PID.PatientName[0].FamilyName.Surname`
Field Within an XML Message	Surround the XPath with backquotes (`).	`/ADT_A01/NK1[1]/NK1.1`
Column From a Previous Resultset	Insert an at sign (`@`) in front of the column name.	`@PatientName`
Stored Procedure Output Parameter	Insert a hash sign (`#`) in front of the parameter name.	`#newPatientId`
Message Body as a String	Insert `$messageContent`.	`$messageContent`

Inserting Message Fields

While it is possible to type in message field paths manually, it is recommended that instead message paths be inserted automatically. This reduces the chance of an incorrect path being used and allows the Rhapsody™ Database Configuration Editor to automatically take account of statement iteration when generating message paths.

Message paths can be inserted into the SQL in two ways. Firstly, the Insert Field button can be pressed to launch a field selector dialog, which allows a specific field to be selected and inserted at the current cursor location.

Secondly, fields can be selected by dragging the appropriate field from the Message Structure toolbar onto the desired location in the text editor. This generates the appropriate field path and inserts it. This is shown in the following screenshot:

Inserting Prior Results

Complex database configurations frequently perform a number of related queries. The parameters of some queries may at times depend on the results of previous queries. The Database Configuration Editor provides the ability to insert results generated from previous queries to make it easier to access these values. These are accessed from the Insert Prior Result toolbar button as shown in the following screenshot:

Selecting a prior result automatically inserts the appropriate value (with the correct prefix depending on its type) at the current cursor location in the text editor. When the SQL Editor is opened, available prior results for all previous statements are located and added to the menu.

At present, the only types of prior results found are declared output parameters for stored procedure calls, and any columns whose values are saved using result substitution. However, this will be extended in future to allow access to all columns returned via any result set.

Statement Options

The Statement Options dialog enables you to configure how a statement is executed:

Field	Description
Statement Execution Method	This option determines which JDBC execution method is used to run the statement. The available options of `execute`, `executeQuery`, and `executeUpdate` correspond to the methods of the same name on the `java.sql.Statement` interface. This option should only be changed from the default if the database driver being used requires updates and queries to be explicitly signaled by calling the correct method. Neither the standard SQL Server or Oracle® drivers require this. This option is not available for stored procedure calls.
Maximum Result Rows	This option limits the number of rows returned in a result set. This option can be used to protect against unexpected overflow from a `SELECT` statement, and can be set to any positive value. The default of `0` (zero) indicates that there is no maximum number of result rows.
Allow Compound Statements	This option determines whether the Database Configuration Editor allows Compound Statements separated by semicolons for this particular statement. Compound statements are used to define two (or more) closely related statements together, where they are tightly coupled. For example, if some values are being inserted into a SQL Server table with an identity column, the `INSERT` statement to insert the values, and the `SELECT` statement to retrieve the new identifier, can be defined together. If this option is disabled, the Database Configuration Editor will ignore semicolons and treat them as part of a single statement. This option should generally be left on, but is unavailable for stored procedure calls.
Raise an error if the query returns no results	This option is used when using a Database Lookup filter to perform result substitution (inserting values returned from a query into the message) when a query unexpectedly returns no results. If this field is selected (the default), an error is raised and execution of the remaining statements is canceled. If this field is not selected, the default values (if available) for result substitution are used instead, and no error is raised.
On Multiple Results	This option is used when using a Database Lookup filter to perform result substitution (inserting values returned from a query into the message) in the event a query returns multiple results. You can select from one of the following modes: `Throw Error` (default) - an error is raised if a result substitution query returns multiple results. `Read First Result` - the first row is read, and any subsequent rows are ignored. `Read All Results` - all rows are read, and the `<resultSet>ReadAllLines</resultSet`> element is added to the `<statement>` element. To enable or disable the populating of repeating fields using all results, check or uncheck, respectively, the Append checkbox which is provided in the Result Substitution table for this mode.