exec SQL

Type

Procedural Command

Purpose

The exec SQL command lets you connect to a specified server, embed an SQL statement in a DQL Procedure, and terminate a connection to a specified server. A procedure may contain any number of exec SQL commands. Each SQL statement must be preceded by the exec SQL command and followed by a period or semicolon.

Embedded SQL statements must use the correct syntax for the target server. When processing reaches an exec SQL command, DataEase SQL passes the SQL statement to the current server with no mediation whatsoever (DataEase does not provide any interactive prompts or check the syntax for you).

Syntax

The exec SQL command is divided into three parts:

1. The first part connects to the server using the following syntax:

 exec SQL connect CONNECT_ID to ENGINE_NAME SERVER_NAME

 DATABASE_NAME as USER_NAME PASSWORD .

2. The second part executes a user-defined SQL statement against a previously established connection using the following syntax:

 exec SQL at CONNECT_ID ANY SQL STATEMENT [: VARIABLE NAME] ;

3. The third part disconnects from a server using the following syntax:

 exec SQL DISCONNECT CONNECT_ID .

Usage

To connect to a specific server and database, follow the exec SQL command with:

• CONNECT_ID - a user-defined name for the connection.

• ENGINE_NAME - the text description of an enabled SQL Engine. This description must exactly match the text description in the Engine Type drop-down list in the Database Links dialog (e.g., Oracle, Other Engines via ODBC).

• SERVER_NAME - the name of the server where the database is stored.

• DATABASE_NAME - the name of an existing database on the specified server.

• USER_NAME - the name of a valid user Logon ID for the specified server. DataEase uses this User Name to log on to the server.

• PASSWORD - the password associated with the specified USER_NAME.

Note: For connections that use the Other Engines via ODBC link option, you must ensure that the ODBC Datasource name is a single word without special characters. In this case the ODBC Datasource name is used for both the SERVER_NAME and the DATABASE_NAME.

To send embedded SQL statements to the server, follow the exec SQL at command with any combination of:

Any valid dynamic SQL statement.

Any DataEase variable name(s) (e.g., current date, temp"SQLTEXT").

:VARIABLE NAME - the name of any valid DataEase variable (temp, global, data-entry, or current) or DataEase field, preceded by a colon.

To terminate a connection to an SQL server, follow the exec SQL command with the keyword disconnect and the CONNECT_ID.

When you use exec SQL to insert SQL statements in a DQL Procedure, you must use the names of the SQL tables and columns, not the corresponding DataEase Form and Field Names. An SQL Table Name or Column Name must not include embedded spaces.

You can combine DQL and exec SQL commands in the same script, but when you use the SQL INSERT, DELETE, or UPDATE commands within a DQL for loop, you must be especially careful about how you use the SQL COMMIT command. Incorrect usage may lead to unexpected results (e.g., you may be locked out or you may not be able to list records that were modified by the exec SQL statement).

All DQL variables (including current, data-entry, temp, and global variables) can be used in conjunction with embedded SQL statements to pass values needed for additional processing. These variables are substituted in the SQL statement when the DQL script executes.

If you use a variable or Field Name in an exec SQL statement, you must precede the variable or Field Name with a colon. The example below shows a colon used before the variable TAX RATE:

 exec SQL at connect1 UPDATE RESERVATIONS

 SET TOTAL_DUE = SUBTOTAL + ( SUBTOTAL* : TAX RATE) ;

If you are passing a variable that must normally be enclosed in quotes (for example, a date value used as part of a comparison in SQL), enclose the entire variable (including the colon) within single quotes, as shown:

 exec SQL at connect1 DELETE FROM CATALOG_MEMBERS

 WHERE EXPIRATIONDATE<': current date' ;

Example

This sample script demonstrates the use of the exec SQL command as well as the current SQLCODE, current SQLCOUNT, and current SQLMSGTXT variables.

The exec SQL command must precede each SQL command in a DQL script. Each exec SQL statement must be followed by a period.

exec SQL connect CONN1 to ORACLE t: oraserv default SCOTT TIGER .

exec SQL at CONN1 DELETE FROM MEMBERS WHERE

OVERDUE_90 = "Y" and PAY_PROCESS = "N" ;

if current SQLCODE not = 0 then

exec SQL at CONN1 COMMIT ;

message jointext ( current SQLCOUNT, " Members deleted. " ) window .

else

exec SQL at CONN1 ROLLBACK ;

message " Delete from Members failed; all changes rolled back. " window.

message current SQLMSGTXT window .

end

exec SQL disconnect CONN1 .

The first exec SQL statement.

 exec SQL connect CONN1 to ORACLE t: oraserv default as SCOTT  TIGER.

…logs on to the Oracle server (defined by the Oracle connect string t:oraserv) and connects to the default database via the UserID SCOTT and the Password TIGER. This connection is identified as CONN1.

The second exec SQL command,

 exec SQL at CONN1 DELETE FROM MEMBERS WHERE

 OVERDUE_90 = "Y" and PAY_PROCESS = "N" ;

..uses the SQL DELETE command to delete all records for all members whose payments are more than 90 days in arrears and not currently being processed. The SQL statement is applied to connection CONN1 What follows is a DQL if...then...else statement:

 if current SQLCODE = 0 then

This if statement tells DataEase to check the value in the current SQLCODE variable before continuing to process the script. If the value of the current SQLCODE is zero (indicating no errors have occurred), the script continues processing and executes the third exec SQL command:

 exec SQL at CONN1 COMMIT ;

This command commits the changes to the database permanently. DataEase displays a message on the screen telling the user how many MEMBERS records were actually deleted.

If current SQLCODE returns a value not equal to zero (indicating an SQL error), the fourth exec SQL command is executed:

 exec SQL at CONN1 ROLLBACK ;

The ROLLBACK command cancels the record deletions before they are permanently saved to the database. DQL messages inform the user that the transaction has failed and display the text of the returned SQL error message.

The final exec SQL command:

 exec SQL disconnect CONN1 .

..disconnects DataEase from the server and ends processing of the script.