SQL Statements
With the exception of INSERT, DELETE(SEARCHED) and UPDATE(SEARCHED), which are included for your convenience, the embedded SQL statements described here work somewhat differently, or are in addition to, standard SQL statements.
| SQL Statement | Description |
|---|---|
| BEGIN DECLARE SECTION | Signals the beginning of the DECLARE section. |
| BEGIN TRAN | Provides compatibility with Embedded SQL implementations that do not conform to the ANSI SQL standard with respect to transaction management and, in particular, the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server. |
| CALL | Executes a stored procedure. |
| CLOSE | Discards unprocessed rows and frees any locks held by the cursor. |
| COMMIT | Makes any changes made by the current transaction on the current connection permanent in the database. |
| CONNECT | Attaches to a specific database using the supplied username and password. |
| DECLARE CURSOR | Associates the cursor name with the specified SELECT statement and enables you to retrieve rows of data using the FETCH statement. |
| DECLARE DATABASE | Declares the name of a database. |
| DELETE (Positioned) | Deletes the row most recently fetched by using a cursor. |
| DELETE (Searched) | Removes table rows that meet the search criteria. |
| DESCRIBE | Provides information on prepared dynamic SQL statements and describes the result set for an open cursor. |
| DISCONNECT | Closes the connection(s) to a database. In addition, all cursors opened for that connection are automatically closed. |
| END DECLARE SECTION | Terminates a host variable declaration section begun by a BEGIN DECLARE SECTION statement. |
| EXECSP | Executes a stored procedure. |
| EXECUTE | Processes dynamic SQL statements. |
| EXECUTE IMMEDIATE | Immediately executes the SQL statement. |
| FETCH | Retrieves a row from the cursor's results set and writes the values of the columns in that row to the corresponding host variables (or to addresses specified in the SQLDA data structure). |
| GET HDBC | Enables you to use ODBC calls that require you to supply the ODBC connection handle. |
| GET HENV | Enables you to use ODBC calls that require you to supply the ODBC environment handle. |
| GET NEXT RESULT SET | Makes the next result set available to an open cursor. |
| INCLUDE | Includes the definition of the specified SQL data structure or source file in the COBOL program. |
| INSERT | Adds new rows to a table. |
| INTO | Retrieves one row of results and assigns the values of the items returned by an OUTPUT clause in a SQL Server INSERT, UPDATE, or DELETE statement to the host variables specified in the INTO list. |
| OPEN | Runs the SELECT statement specified in the corresponding DECLARE CURSOR statement to produce the results set that is accessed one row at a time by the FETCH statement. |
| PREPARE | Processes dynamic SQL statements. |
| QUERY ODBC | Delivers a results set in the same way as a SELECT statement, and must therefore be associated with a cursor via DECLARE and OPEN, or DECLARE, PREPARE and OPEN. |
| RESET CONNECTION | Closes all open cursors, even if the application has not appropriately closed them. |
| ROLLBACK | Backs out any changes made to the database by the current transaction on the current connection, or partially rolls back changes to a previously set save point. |
| SAVEPOINT SAVE TRANSACTION RELEASE [TO] SAVEPOINT | Sets a transaction save point to which a current transaction can be rolled back, resulting in a partial roll back. |
| SELECT DISTINCT (using DECLARE CURSOR) | Associates the cursor name with the SELECT DISTINCT statement and enables you to retrieve rows of data using the FETCH statement. |
| SELECT INTO | Retrieves one row of results and assigns the values of the items in a specified SELECT list to the host variables specified in the INTO list. |
| SET AUTOCOMMIT | Enables you to control ODBC AUTOCOMMIT mode at run time. |
| SET CONNECTION | Sets the named connection as the current connection. |
| SET ERRORMAP | Changes the SQL error map file for the current connection. |
| SET host_variable | Provides information about CitOESQL connections and databases. |
| SET OPTION | Enables you to set CitOESQL options. |
| SET TRACELEVEL | Enables you to dynamically set or change the reporting level of CitOESQL traces for native applications. |
| SET TRANSACTION ISOLATION | Sets the transaction isolation level for the current connection to one of the isolation level modes specified by ODBC. |
| SYNCPOINT | Closes all open cursors that were not opened using the WITH HOLD clause, even if the application has not appropriately closed them. |
| UPDATE (Positioned) | Updates the rows most recently fetched by using a cursor. |
| UPDATE (Searched) | Updates a table or view based on specified search conditions. |
| WHENEVER | Specifies the default action after running an Embedded SQL statement when a specific condition is met. |
BEGIN DECLARE SECTION
Signals the beginning of the DECLARE section.
Syntax:
\>\>---EXEC SQL---BEGIN DECLARE SECTION---END-EXEC---\>\<
Comments:
The BEGIN DECLARE SECTION statement can be included anywhere where COBOL permits variable declaration. Use END DECLARE SECTION to identify the end of a COBOL declaration section.
- Declare sections cannot be nested.
- Variables must be declared in COBOL, not in SQL.
- To avoid conflict, variables inside a declaration section cannot be the same as any outside the declaration section or in any other declaration section, even in other compilation units.
If data structures are defined within a declaration section, only the bottom-level items (with PIC clauses) can be used as host variables. Two exceptions are arrays specified in FETCH statements and record structures specified in SELECT INTO statements.
Example:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 staff-id pic x(4).
01 last-name pic x(30).
EXEC SQL END DECLARE SECTION END-EXEC
BEGIN TRAN
Provides compatibility with Embedded SQL implementations that do not conform to the ANSI SQL standard with respect to transaction management and the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server.
Syntax Format 1:
\>\>--EXEC SQL--BEGIN TRAN-.------------------.-END-EXEC---\>\<
\+-transaction_name-+
\>\>-EXEC SQL-BEGIN TRANSACTION.----------------.-END-EXEC-\>\<
\+transaction_name+
Transaction name
- An optional identifier that is ignored.
Comments:
Use the BEGIN TRAN statement in AUTOCOMMIT mode to open a transaction. After you have opened the transaction in AUTOCOMMIT mode, you should execute a COMMIT or ROLLBACK statement to close the transaction and cause a return to AUTOCOMMIT mode.
If you are not opening a transaction in AUTOCOMMIT mode, then this statement has no effect.
Example:
EXEC SQL BEGIN TRANSACTION END-EXEC
CALL
Executes a stored procedure.
Syntax:
>>--EXEC SQL--.--------------------.--.----------------.->
+-FOR :host_integer--+ +- :result_hvar -+
>---CALL stored_procedure_name-.------------.-END-EXEC-><
| +-- , --+ |
| V | |
+(parameter)-+
Parameters:
Host Integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
result_hvar
- A host variable to receive the procedure result.
stored_procedure_name
- The name of the stored procedure.
parameter
- A literal, a DECLARE CURSOR statement
*, or a host variable parameter of the form: [keyword=]:param_hvar [IN | INPUT |INOUT | OUT | OUTPUT]- where:
- keyword The formal parameter name for a keyword parameter. Keyword parameters can be useful as an aid to readability and where the server supports default parameter values and optional parameters.
- param_hvar A host variable.
- IN An input parameter.
- INPUT An input parameter default).
- INOUT An input/output parameter.
- OUT An output parameter.
- OUTPUT An output parameter.
*Specify DECLARE CURSOR for stored procedures that return a result set. The use of DECLARE CURSOR unbinds the corresponding parameter.
Comments:
Do not use the FOR clause if the CALL is part of a DECLARE CURSOR statement.
For maximum portability, observe the following as general rules:
- Avoid literal parameters
- Use host variable parameters
- Avoid mixing positional parameters and keyword parameters
- If your server supports a mixture of positional and keyword parameters, list keyword parameters after positional parameters
Examples:
Call a stored procedure using two positional host variables as input parameters:
EXEC SQL
CALL myProc(param1,param2)
END-EXEC
Call a stored procedure using a keyword host variable as an input parameter:
EXEC SQL
CALL myProc (namedParam=:paramValue)
END-EXEC
Call a stored procedure using a result host variable and a keyword host variable as an input parameter:
EXEC SQL
:myResult = CALL myFunction(namedParam=:paramValue)
END-EXEC
Call a stored procedure using two positional host variables, one as an input parameter and one as an output parameter:
EXEC SQL
CALL getDept(:empName IN, :deptName OUT)
END-EXEC
Call a stored procedure using a DECLARE CURSOR statement and a positional host variable as an input parameter (Oracle only):
EXEC SQL
DECLARE cities CURSOR FOR CALL locateStores(:userState)
END-EXEC
CLOSE
Discards unprocessed rows and frees any locks held by the cursor.
Syntax:
\>\>---EXEC SQL---.------------.---\>
\+-AT db_name-+
\>--CLOSE---cursor_name---.------------.---END-EXEC---\>\<
Parameters:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement. Provided for backward compatibility.
cursor_name
- A previously declared and opened cursor.
Comments:
The cursor must be declared and opened before it can be closed. All open cursors are closed automatically at the end of the program.
Example:
*Declare the cursor...
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT staff_id, last_name
FROM staff
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not declare cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
OPEN C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not open cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
PERFORM UNTIL sqlcode NOT = ZERO
*SQLCODE will be zero as long as it has successfully fetched data
EXEC SQL
FETCH C1 INTO :staff-staff-id, :staff-last-name
END-EXEC
IF SQLCODE = ZERO
DISPLAY "Staff ID: " staff-staff-id
DISPLAY "Staff member's last name: " staff-last-name
END-IF
END-PERFORM
EXEC SQL
CLOSE C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not close cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
END-IF
COMMIT
Makes any changes made by the current transaction on the current connection permanent in the database.
Syntax:
>>---EXEC SQL--.------------.--->
+-AT db_name-+
>---COMMIT----.--------------.--->
+-WORK---------+
+-TRAN---------+
+-TRANSACTION--+
>---.-----------.---END-EXEC--><
+--RELEASE--+
Parameters:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection is committed. If provided, and the connection specified is different than the current connection, the commit is performed on the connection associated with the DECLARE CURSOR statement.
WORK
- WORK, TRAN, and TRANSACTION are optional and synonymous.
RELEASE
- If RELEASE is specified and the transaction was successfully committed, the current connection is closed.
Example:
* Ensure that multiple records are not inserted for a
* member of staff whose staff_id is 99
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
* Insert dummy values into table
EXEC SQL
INSERT INTO staff
(staff_id
,last_name
,first_name
,age
,employment_date)
VALUES
(99
,'Lee'
,'Phil'
,19
,'1992-01-02')
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not insert dummy values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
COMMIT
END-EXEC
* Check it was committed OK
IF SQLCODE = ZERO
DISPLAY 'Error: Could not commit values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
DISPLAY 'Values committed.'
* Delete previously inserted data
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not delete dummy values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Check data deleted OK, commit and release the connection
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not delete values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
COMMIT WORK RELEASE
END-EXEC
* Check data committed OK and release the connection.
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not commit and release.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
END-IF
DISPLAY 'Values committed and connection released.'
CONNECT
Attaches to a specific database using the supplied user name and password.
Syntax Format 1:
>>---EXEC SQL---CONNECT TO---.-------------.------------->
+-data_source-+
>--.------------.------USER-.-------------------.-------->
+-AS db_name-+ +-user-.-----------++
+-.password-+
>-.--------------------.-.-----------------------------.->
+-WITH-.----.-PROMPT-+ +-RETURNING output_connection-+
+-NO-+
>------END-EXEC--------><
Syntax Format 2:
>>---EXEC SQL---CONNECT user--.------------------------.->
+-IDENTIFIED BY password-+
+-------'/'password------+
>---.--------------.--------.--------------------.------->
+--AT db_name--+ +--USING data_source-+
>---.----------------------.----------------------------->
+--WITH-.----.-PROMPT--+
+-NO-+
>---.-----------------------------.---END-EXEC---><
+-RETURNING output_connection-+
Syntax Format 3:
>>----EXEC SQL---CONNECT WITH PROMPT-------------------->
>---.------------------------------.---END-EXEC----><
+-RETURNING output_connection -+
Syntax Format 4:
>>----EXEC SQL---CONNECT RESET-.--------.--END-EXEC-----><
+--name--+
Syntax Format 5:
>>----EXEC SQL--------CONNECT DSN input_connection------->
>---.------------------------------.-------END-EXEC-----><
+-RETURNING output_connection -+
Syntax Format 6:
>>----EXEC SQL---CONNECT USING input_connection---------->
>-----.-------------.---.---------------------.---------->
+--AS db_name-+ +--WITH-.----.-PROMPT-+
+-NO-+
>-----.------------------------------.------END-EXEC----><
+--RETURNING output_connection-+
Parameters:
data_source
- The name of the ODBC data store. For ODBC data stores, this is the DSN created via the Microsoft ODBC Data Source Administrator. If you omit data_source, the default ODBC data source is assumed. The data source can be specified as a literal or as a host variable.
db_name
- A name for the connection. Connection names can have as many as 30 characters and can include alphanumeric characters and any symbols legal in filenames. The first character must be a letter. Do not use Embedded SQL keywords or CURRENT or DEFAULT or ALL for the connection name; they are invalid. If db_name is omitted, DEFAULT is assumed. db_name can be specified as a literal or a host variable. When connecting to SQL Server, db_name is the database to which you are connecting.
user
- A valid user-id at the specified data source.
password
- A valid password for the specified user-id.
output_connection
- A PIC X(n) text string defined by ODBC as the connection string used to connect to a particular data source. Subsequently, you can specify this string as the input_connection in a CONNECT USING statement.
input_connection
- A PIC X(n) text string containing connection information used by ODBC to connect to the data source. The test string can be either a literal or a host variable.
RESET
- Resets (disconnects) the specified connection.
name
- You can specify name as CURRENT, DEFAULT or ALL.
OS Authentication:
When using Oracle, DB2 or SQL Server with ODBC, you can achieve OS authentication using either of these two methods:
- In the CONNECT statement, specify a user ID consisting of a single forward slash and either omit the password or specify all spaces
- Completely omit the user ID and password from the CONNECT statement
For complete information on OS authentication requirements for your DBMS product, consult your DBMS documentation.
Comments:
If you use only one connection, you do not need to supply a name for the connection. When you use more than one connection, you must specify a name for each connection. Connection names are global within a process. Named connections are shared by separately compiled programs that are linked into a single executable module.
After a successful CONNECT statement, all database transactions other than CONNECT RESET work through this most recently declared current connection. To use a different connection, use the SET CONNECTION statement.
To cause the ODBC run-time module to prompt at run-time for entry or confirmation of connection details, use CONNECT WITH PROMPT.
Use CONNECT DSN and CONNECT USING to simplify administration.
With CONNECT TO, CONNECT, CONNECT WITH PROMPT, CONNECT DSN and CONNECT USING, you can return connection information to the application.
Note
- If the INIT option of the SQL Compiler directive is used, an implicit connection to the database will be made at run time. In this case, it is not necessary to execute an explicit CONNECT statement.
- A File DSN cannot contain a password.
Example Format 1:
MOVE 'servername' TO svr
MOVE 'username.password' TO usr
EXEC SQL
CONNECT TO :svr USER :usr
END-EXEC
Example Format 2:
EXEC SQL
CONNECT 'username.password' USING 'servername'
END-EXEC
Example Format 3:
EXEC SQL
CONNECT WITH PROMPT
END-EXEC
Example Format 4:
EXEC SQL
CONNECT RESET
END-EXEC
Example Format 5:
EXEC SQL
CONNECT USING 'FileDSN=Oracle8;PWD=tiger'
END-EXEC
The example above uses a File DSN.
Example Format 6:
01 connectString PIC X(72) value
'DRIVER={Microsoft Excel Driver (*.xls)};'
&'DBQ=c:\demo\demo.xls;'
&'DRIVERID=22'
.
procedure division.
EXEC SQL
CONNECT USING :connectString
END-EXEC
The example above connects to an Excel spreadsheet without setting up a data source.
DECLARE CURSOR
Associates the cursor name with the specified SELECT statement and enables you to retrieve rows of data using the FETCH statement. Syntax Format 1:
>>--EXEC SQL---.------------.------DECLARE cursor_name------>
+-AT db_name-+
>--.-------------.-----.---------.------.--------------.--->
+-SENSITIVE---+ +-FORWARD-+ +-LOCK---------+
+-INSENSITIVE-+ +-KEYSET--+ +-LOCKCC-------+
+-DYNAMIC-+ +-OPTIMISTIC---+
+-STATIC--+ +-OPTCC--------+
+-SCROLL--+ +-OPTCCVAL-----+
+-READ ONLY----+
+-READONLY-----+
+-FASTFORWARD--+
+-FAST FORWARD-+
>--CURSOR-------.----------------.---------FOR------------->
+----WITH HOLD---+
>----.----select_stmt-----------------------.-------------->
+----stored_procedure_call_statement---+
+----prepared_stmt_name----------------+
+----OPTIMIZE FOR n ROWS---------------+
>--.-------------------------------.----------------------->
+-FOR READ ONLY-----------------+
+-FOR UPDATE-.----------------.-+
+-OF column_list-+
>------END-EXEC--------><
Syntax Format 2:
Note
Format 2 is supported for SQL Server only.
>>--EXEC SQL---.------------.------DECLARE cursor_name------>
+-AT db_name-+
>--CURSOR FOR---result-set-generating-dml-statement-------->
>------END-EXEC--------><
Parameters:
AT db_name
-
The name of a database that has been declared using DECLARE DATABASE.
Note
If you must use AT db_name in a DECLARE CURSOR, the connection for any following statements that reference the cursor automatically switch to the connection associated with the cursor if different than the current connection, but only for the duration of the statement.
cursor_name
- Cursor name used to identify the cursor in subsequent statements. Cursor names can contain any legal filename character and be up to 30 characters in length. The first character must be a letter.
select_stmt
- Any valid SQL SELECT statement, or a QUERY ODBC statement or a CALL statement for a stored procedure that returns a result set.
prepared_stmt_name
- The name of a prepared SQL SELECT statement or QUERY ODBC statement.
stored_procedure_call_stmt
- A valid stored procedure call which returns a result set.
n
- The number of rows per block fetched when the cursor is opened. The value of n must be less than 1000.
column_list
- A list of column-names, separated by commas.
result-set-generating-dmlstatement
- A SQL Server INSERT, non-positioned UPDATE, or DELETE statement with an OUTPUT clause.
Comments:
Two separately compiled programs cannot share the same cursor. All statements that reference a particular cursor must be compiled together.
The DECLARE CURSOR statement must appear before the first reference to the cursor. The SELECT statement runs when the cursor is opened. The following rules apply to the SELECT statement:
- It cannot contain an INTO clause or parameter markers.
- It can contain input host variables previously identified in a declaration section.
- With some ODBC drivers, the SELECT statement must include a FOR UPDATE clause if positioned updates or deletions are to be performed.
If OPTIMIZE FOR is specified, OpenESQL uses n to override the setting of the PREFETCH directive for the cursor. This allows prefetch optimization for individual cursors.
You can specify multiple SELECT statements in a DECLARE CURSOR statement, signifying the return of multiple result sets from either of the following. In either case, the client application must use the GET NEXT RESULT SET statement to retrieve additional result sets.
- A COBOL stored procedure for SQL Server
- A standard OpenESQL application program
The following applies to the behavior of certain DECLARE CURSOR options:
- SCROLL selects a scroll option, other than FORWARD, that is supported by the driver.
- LOCKCC and LOCK are equivalent.
- READ ONLY and READONLY are equivalent.
- OPTIMISTIC selects an optimistic concurrency mode (OPTCC or OPTCCVAL) that is supported by the driver.
- If a HOLD cursor is requested and the current connection closes cursors at the end of transactions, the OPEN statement will return an error (SQLCODE = -19520).
- If the database is Microsoft SQL Server and the NOANSI92 ENTRY directive setting has been used (this is the default setting), then a Microsoft SQL Server specific ODBC call will be made at connect time to request that cursors are not closed at the end of transactions. This is compatible with the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server. The setting for USECURLIB must not be YES.
- FAST FORWARD and FASTFORWARD are equivalent. This is a performance optimization parameter that applies only to FORWARD, READ-ONLY cursors. You can obtain even greater performance gains by also compiling the program with the AUTOFETCH directive; this is the most efficient method of getting a results set into an application. The AUTOFETCH directive enables two optimizations that can significantly reduce network traffic. The most dramatic improvement is seen when processing cursors with relatively small result sets that can be cached in the memory of an application. FASTFORWARD cursors work only with Microsoft SQL Server 2000 or later servers.
Example:
EXEC SQL DECLARE C1 CURSOR FOR
ELECT last_name, first_name FROM staff
END-EXEC
EXEC SQL DECLARE C2 CURSOR FOR
QUERY ODBC COLUMNS TABLENAME 'staff'
END-EXEC
DECLARE DATABASE
Declares the name of a database.
Syntax:
>>---EXEC SQL---DECLARE db_name---DATABASE---END-EXEC---->
Parameters:
db_name
- A name associated with a database. It must be an identifier and not a host variable. It cannot contain quotation marks.
Comments:
You must DECLARE db_name before using a CONNECT ... AT db_name statement. You cannot use DECLARE DATABASE with EXECUTE IMMEDIATE or with PREPARE and EXECUTE.
DELETE (Positioned)
Deletes the row most recently fetched by using a cursor.
Syntax:
>>---EXEC SQL---.------------.--->
+-AT db_name-+
>--DELETE---FROM---table_name--->
>--WHERE CURRENT OF--cursor_name---END-EXEC---><
Parameters:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
table_name
- The same table used in the SELECT FROM option (see DECLARE CURSOR).
cursor_name
- A previously declared, opened, and fetched cursor.
Comments:
- ODBC supports positioned delete, which deletes the row most recently fetched by using a cursor in the Extended Syntax (it was in the Core Syntax for ODBC 1.0 but was moved to the Extended Syntax for ODBC 2.0). Not all drivers provide support for positioned delete, although OpenESQL sets ODBC cursor names to be the same as COBOL cursor names to facilitate positioned update and delete.
- With some ODBC drivers, the select statement used by the cursor must contain a 'FOR UPDATE' clause to enable positioned delete.
- You cannot use host arrays with positioned delete.
- The other form of DELETE used in standard SQL statements is known as a searched delete.
- Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement.
- The ODBC cursor library provides a restricted implementation of positioned delete which can be enabled by compiling with SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC).
Example:
* Declare a cursor for update
EXEC SQL DECLARE C1 CURSOR FOR
SELECT staff_id, last_name FROM staff FOR UPDATE
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not declare cursor for update.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Open the cursor
EXEC SQL
OPEN C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not open cursor for update.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Display staff member's details and give user the opportunity
* to delete particular members.
PERFORM UNTIL SQLCODE NOT = ZERO
EXEC SQL FETCH C1 INTO :staff-id,:last-name END-EXEC
IF SQLCODE = ZERO
DISPLAY 'Staff ID: ' staff-id
DISPLAY 'Last name: ' last-name
DISPLAY 'Delete <y/n>? ' WITH NO ADVANCING
ACCEPT usr-input
IF usr-input = 'y'
EXEC SQL
DELETE FROM staff WHERE CURRENT OF C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not delete record.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
END-IF
END-IF
END-IF
END-PERFORM
DELETE (Searched)
Removes table rows that meet the search criteria.
Syntax:
>>--EXEC SQL--.-------------------.--->
+-FOR :host_integer-+
>--.-------------.--DELETE--.------.--->
+-AT db_name--+ +-FROM-+
>--.-table_name-.--.-------------------------.-END-EXEC-><
+--view_name-+ +-WHERE search_conditions-+
Parameters:
host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection is deleted. If provided, and the connection specified is different than the current connection, the delete is performed on the connection associated with the DECLARE CURSOR statement.
FROM
- An optional keyword. It is required for ANSI SQL 92 conformance.
table_name
- The target table for the delete operation.
view_name
- The target view for the delete operation.
WHERE
- A standard SQL WHERE clause identifying the row to be deleted.
search_conditions
- Any valid expression that can follow the standard SQL WHERE clause.
Comments:
DELETE is a standard SQL statement. See the documentation supplied with your ODBC driver for the exact syntax.
You cannot mix simple host variables with host arrays in the WHERE clause. If one of the host variables is an array, they must all be arrays.
If you do not specify a WHERE clause, all the rows in the named table are removed.
Example:
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
DESCRIBE
Provides information on prepared dynamic SQL statements and describes the result set for an open cursor.
Syntax Format 1:
>>---EXEC SQL-----DESCRIBE---.----------------------.----->
+---SELECT LIST FOR----+
+---BIND VARIABLES FOR-+
>--.--prepared_stmt_name--.---INTO---:sqlda_struct---END-EXEC--
><
Syntax Format 2:
>>---EXEC SQL-----DESCRIBE---CURSOR---cursor_name----->
>---INTO---:sqlda_struct---END-EXEC--><
Parameters:
prepared_stmt_name
- The name of a prepared SQL SELECT statement or QUERY ODBC statement. cursor-name The name of an open cursor.
: sqlda_struct
- A host variable that specifies the output SQLDA data structure to be populated. The colon is optional to provide compatibility with other embedded SQL implementations.
Comments:
This statement populates the specified SQLDA data structure with the data type, length, and column name of each column returned by the specified prepared statement.
If neither SELECT LIST FOR or BIND VARIABLES FOR is specified, SELECT LIST FOR is used by default. If BIND VARIABLES FOR is specified, information about input parameters is returned in the SQLDA rather than information about results columns.
The DESCRIBE statement inserts the number of columns into the sqld field of the SQLDA structure. If a non-select statement was prepared, sqld is set to 0. Before DESCRIBE is called, the following fields in the SQLDA data structure must be initialised by the application:
sqln
- The maximum number of sqlvar (column descriptor) entries that the structure can accommodate.
sqldabc
-
The Maximum size of the SQLDA:
- 32-bit – Calculated as sqln * 44 + 16
- 64-bit – Calculated as sqln * 56 + 16
If sqln is set to 0, no column descriptor entries are constructed, but sqld is set to the number of entries required. The DESCRIBE statement works in a similar way to a PREPARE statement with an INTO clause.
By default, the SQL types for date, time and timestamp are respectively DATE-RECORD, TIMERECORD and TIMESTAMP-RECORD. When you use the BEHAVIOR=OPTIMIZED option for the SQL Compiler directive, CitOESQL mimics the DB2 on the mainframe for these data types, providing character strings (i.e., PIC X(n)) instead of the standard, default record constructs.
Note
Few drivers fully implement the ODBC calls necessary for DESCRIBE BIND VARIABLES.
Example:
$set sql(behavior=optimized)
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE SQLDA78 END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 statement pic x(80).
01 host-var-block.
03 host-var-1 pic 99.
03 host-var-2 pic x(10).
03 host-var-3 pic x(15).
EXEC SQL END DECLARE SECTION END-EXEC
PROCEDURE DIVISION.
EXEC SQL CONNECT TO ORCL USER scott.tiger END-EXEC
EXEC SQL
DECLARE C1 CURSOR FOR stmt1
END-EXEC
move "select * from dept" to statement
move 20 to sqln
$IF P64 SET
compute sqldabc = 16 + 56 * sqln
$ELSE
compute sqldabc = 16 + 44 * sqln
$END
EXEC SQL
PREPARE stmt1 FROM :statement
END-EXEC
EXEC SQL
DESCRIBE stmt1 INTO :sqlda
END-EXEC
* The data structure "sqlda" now contains a description
* of the dynamic SQL statement.
EXEC SQL
OPEN C1
END-EXEC
* Complete the SQLDA, by adding buffer addresses and lengths
* and changeing types, as necessary and appropriate, to
* to match host variables actually used.
*
* The following SQL directives can reduce the amount of effort
* required by specifying how OpenESQL should DESCRIBE varchar
* and date/time SQL data types:
* DESCRIBEVARCHARPICX
* DESCRIBEVARCHAR49
* DESCRIBEDTCHAR
* DESCRIBEDTREC
move ESQL-UDISP-UNSIGN to sqltype(1)
set sqldata(1) to address of host-var-1
set sqldata(2) to address of host-var-2
set sqldata(3) to address of host-var-3
perform until exit
EXEC SQL
FETCH C1 USING DESCRIPTOR :sqlda
END-EXEC
if sqlerrd(3) not = 1
exit perform
end-if
display host-var-1 ' ' host-var-2 ' ' host-var-3
end-perform
goback.
DISCONNECT
Closes the connection(s) to a database. In addition, all cursors opened for that connection are automatically closed.
Syntax:
>>---EXEC SQL---DISCONNECT---.-name----.----END-EXEC----><
+-ALL-----+
+-CURRENT-+
+-DEFAULT-+
Parameters:
name
- The connection name.
ALL
- Disconnects all connections (including automatic connections made when the INIT option of the SQL Compiler directive is used).
CURRENT
- Disconnects the current connection. The current connection is either the most recent connection established by a CONNECT statement or a subsequent connection set by a SET CONNECTION statement.
DEFAULT
- Disconnects the default connection. This is the connection made by a CONNECT statement which did not specify a connection name.
Example:
EXEC SQL CONNECT TO "srv1" AS server1 USER "sa." END-EXEC EXEC SQL CONNECT TO "srv2" AS server2 USER "sa." END-EXEC
...
EXEC SQL DISCONNECT server1 END-EXEC
EXEC SQL DISCONNECT server2 END-EXEC.
END DECLARE SECTION
Terminates a host variable declaration section begun by a BEGIN DECLARE SECTION statement.
Syntax:
>>---EXEC SQL---END DECLARE SECTION---------END-EXEC----><
Example:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 staff-id pic x(4).
01 last-name pic x(30).
EXEC SQL END DECLARE SECTION END-EXEC
EXECSP
Executes a stored procedure.
Syntax:
>>--EXEC SQL-.----------------.-EXECSP-.---------------.->
+-FOR :host_integer-+ +-:result_hvar -+
>-- stored_procedure_name -----.------------.------------->
| +-- , --+ |
| V | |
+(parameter)-+
>-.----------------.------------------------END-EXEC-----<>
+-WITH RECOMPILE-+
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
:result_hvar
- A host variable to receive the procedure result.
stored_procedure_name
- The name of the stored procedure.
parameter
- A literal or a host variable parameter of the form:
- [keyword=]:param_hvar [OUT | OUTPUT]
- where:
- keyword is the formal parameter name for a keyword parameter.
- :param_hvar is a host variable.
- OUT specifies an output parameter.
- OUTPUT specifies an output parameter.
- WITH RECOMPILE Is ignored and has no effect. It is allowed for syntax compatibility only.
Example:
EXEC SQL
EXECSP myProc param1,param2
END-EXEC
EXEC SQL
EXECSP :myResult = myFunction namedParam = :paramValue
END-EXEC
EXEC SQL
EXECSP getDept :empName, :deptName OUT
END-EXEC
EXEC SQL
DECLARE cities CURSOR FOR EXECSP locateStores :userState
END-EXEC
EXECUTE
Processes dynamic SQL statements.
Syntax:
>>-EXEC SQL-----.-------------------.------EXECUTE------>
+--FOR :host_integer---+
>-prepared_stmt_name-.--------------------------------.->
+-USING DESCRIPTOR :sqlda_struct-+
| +- , -+ |
| V | |
+-USING :hvar--------------------+
>-----END-EXEC-----<>
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
prepared_stmt_name
- A previously prepared SQL statement.
:sqlda_struct
- A previously declared SQLDA data structure containing a description of the input values. The colon is optional to provide compatibility with other embedded SQL implementations.
:hvar
- One or more input host variables.
Comments:
Do not use the FOR clause if the EXECUTE is part of a DECLARE CURSOR statement.
The EXECUTE statement runs the specified prepared SQL statement after substituting values for any parameter markers. (Prepared statements are created using the PREPARE statement.) Only statements that do not return results are permitted.
If the prepared statement contains parameter markers, the EXECUTE statement must include either the USING :hvar option with the same number of host variables or the USING DESCRIPTOR :sqlda_struct option identifying a SQLDA data structure already populated by the application.
The number of parameter markers in the prepared statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda) or host variables (USING :hvar).
Example:
* Store statement to be dynamically executed...
MOVE "INSERT INTO staff VALUES(?,?,?,?,?)" TO stmtbuf.
* Ensure attempt is not made to insert an existing record
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
* Prepare the statement
EXEC SQL
PREPARE st FROM :stmtbuf
END-EXEC.
MOVE 99 TO staff-id
MOVE 'Lee' TO last-name
MOVE 'Phil' TO first-name
MOVE 19 TO age
MOVE '1997-01-01' TO employment-date
* Execute the statement with current values.
EXEC SQL
EXECUTE st USING :staff-id, :last-name
,:first-name, :age, :employment-date
END-EXEC
IF SQLCODE = ZERO
DISPLAY 'Statement executed.'
ELSE
DISPLAY 'Error: Could not execute statement.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Finally, remove the entry
EXEC SQL
DELETE FROM staff where staff_id = 99
END-EXEC
IF SQLCODE = ZERO
DISPLAY 'Values deleted.'
ELSE
DISPLAY 'Error: Could not delete inserted values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXECUTE IMMEDIATE
Immediately executes the SQL statement.
Syntax
>>--EXEC SQL--.-------------------.--EXECUTE IMMEDIATE-->
+--FOR :host_integer---+
>----:stmt_hvar----END-EXEC-<>
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
:stmt_hvar
- A character string host variable.
Comments:
Do not use the FOR clause if the EXECUTE IMMEDIATE is part of a DECLARE CURSOR statement.
The EXECUTE IMMEDIATE statement cannot contain input parameter markers or host variables. It cannot return results; any results returned from this statement are discarded. Additionally, the statement cannot contain SQL keywords that pertain exclusively to Embedded SQL.
If any rows are returned, SQLCODE is set to +1.
EXECUTE IMMEDIATE must be used for SET statements specific to the Microsoft SQL Server, that is, those that are intended to execute at that server.
Example:
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
* Put the required SQL statement in prep.
MOVE "insert into staff (staff_id, last_name, first_name ,age,
- "employment_date) VALUES (99, 'Lee', 'Phillip', 19, '1992-
- "01-02')" TO prep
* Note EXECUTE IMMEDIATE does not require the statement to be
* prepared
EXEC SQL
EXECUTE IMMEDIATE :prep
END-EXEC
* Check it worked...
IF SQLCODE = ZERO
DISPLAY 'Statement executed OK.'
ELSE
DISPLAY 'Error: Statement not executed.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Run through the same procedure again, this time deleting the
* values just inserted
MOVE "delete from staff where staff_id = 99" TO prep
EXEC SQL
EXECUTE IMMEDIATE :prep
END-EXEC
IF SQLCODE = ZERO
DISPLAY 'Statement executed OK.'
ELSE
DISPLAY 'Error: Statement not executed.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
FETCH
Retrieves a row from the cursor's results set and writes the values of the columns in that row to the corresponding host variables (or to addresses specified in the SQLDA data structure).
Syntax:
>>--EXEC SQL--.-------------------.------->
+-FOR :host_integer-+
>-----.-------------.--FETCH---.-------------.-->
+-AT db_name--+ +---PREVIOUS--+
+---LAST------+
+---PRIOR-----+
+---FIRST-----+
+---NEXT------+
>-----cursor_name---.-------------------------------------.------\>
+-USING DESCRIPTOR :sqlda_struct------+
| +--------------------------,-+|
| V ||
+-INTO--.-:hvar----------------------.+
+-:hvar:ivar-----------------+
+-:hvar-.-----------.-:ivar--+
+-INDICATOR-+
>--END EXEC--><
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement. Provided for backward compatibility.
cursor_name
- A previously declared and opened cursor.
:sqlda_struct
- An SQLDA data structure previously populated by the DESCRIBE statement and containing output value addresses. This option is used only with a cursor declared by a prepared SELECT statement. (SELECT statements are prepared using the PREPARE statement.) The colon is optional to provide compatibility with other embedded SQL implementations.
:hvar
- Specifies either of the following: One or more host variables, each separated by a comma. One or more host variable+indicator variable combinations, each combination separated by a comma.
Comments:
By default, the FETCH statement retrieves the next row, but you can also specify the previous row or last row or prior row or first row. If there are no more rows to fetch SQLCODE is set to 100 and SQLSTATE is set to "02000".
An OPEN cursor_name statement must precede a FETCH statement, and the cursor must be open while FETCH runs. If you use PREVIOUS, LAST, PRIOR, FIRST or NEXT, you must also set the appropriate cursor options via the DECLARE CURSOR statement or the SET SCROLLOPTION and SET CONCURRENCY statements. Also, the data type of the host variable must be compatible with the data type of the corresponding database column.
If the number of columns is less than the number of host variables, the value of SQLWARN3 is set to
W. If an error occurs, no further columns are processed. (Processed columns are not undone.)
Alternatively, the :hvar variable can specify a COBOL record that contains several fields, each corresponding to a column in the select list of the cursor declaration statement. To use this form, you must specify the DB2 option of the SQL Compiler directive. (Note that this will cause PREPARE INTO and DESCRIBE statements to be rejected by the COBOL compiler).
If ANSI92ENTRY is set, then attempting to fetch a null value will set SQLCODE to -19425 if there is no null indicator. If ANSI92ENTRY is not set, SQLCODE will be 0. In both cases, SQLSTATE will be 22002 and SQLWARN2 will be W.
If one of the host variables in the INTO clause is an array, they must all be arrays.
After execution, SQLERRD(3) contains the number of elements processed. For FETCH it is the number of rows fetched.
Example:
* Declare a cursor for a given SQL statement.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT last_name, first_name FROM staff
END-EXEC
EXEC SQL OPEN C1 END-EXEC
* Fetch the current values from the cursor into the host variables
* and if everything goes ok, display the values of the host
* variables
PERFORM UNTIL SQLCODE NOT = ZERO
EXEC SQL
FETCH C1 INTO :lname,:fname
END-EXEC
IF SQLCODE NOT = ZERO AND SQLCODE NOT = 100
DISPLAY 'Error: Could not perform fetch'
DISPLAY SQLERRML DISPLAY SQLERRMC
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
DISPLAY 'First name: 'fname DISPLAY 'Last name : 'lname
DISPLAY SPACES
END-PERFORM
GET HDBC
Enables you to use ODBC calls that require you to supply the ODBC connection handle.
Syntax:
>>---EXEC SQL---GET HDBC---INTO---:hvar---END-EXEC---->
Parameters:
hvar
- A host variable to store the ODBC connection handle. Must be declared as PIC X(4) COMP-5.
Example:
EXEC SQL
GET HDBC INTO :hvar
END-EXEC
GET HENV
Enables you to use ODBC calls that require you to supply the ODBC environment handle.
Syntax:
>>---EXEC SQL---GET HENV---INTO---:hvar---END-EXEC----\>
Parameters:
hvar
- A host variable to store the ODBC environment handle. Must be declared as PIC X(4) COMP-5.
Example:
EXEC SQL
GET HENV INTO :HENV
END-EXEC
GET NEXT RESULT SET
Makes the next result set available to an open cursor.
Syntax:
>>---EXEC SQL---.------------.--->
+-AT db_name-+
>--GET NEXT RESULT SET FOR---cursor_name---END-EXEC---><
Parameters:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
cursor_name
- A previously declared and opened cursor.
Comments:
GET NEXT RESULT SET makes the next result set available when retrieving multiple result sets from a stored procedure or from a DECLARE CURSOR statement defined with multiple SELECT statements.
If additional result sets are not available, GET NEXT RESULT SET returns an SQLCODE of 100 and sets SQLSTATE to 02000.
Example:
exec sql declare c cursor for
call TestProc2(:hv-country)
end-exec
exec sql open c end-exec
display " "
display "First result set from proc"
display " "
perform until exit
exec sql fetch c into
:CustomerID, :Company, :City
end-exec
if sqlcode = 100 or sqlcode < 0
exit perform
end-if
display CustomerID City
end-perform
*> Always get SQLCODE 100 at the end of a result set
*> until you close the cursor or ask for another
result set
exec sql fetch c into
:CustomerID, :Company, :City
end-exec
if sqlcode not = 100
display "FAIL: Fetch after SQLCODE 100 OK"
end-if
*> Ask for another result set, SQLCODE 100 if there
isn't one
exec sql get next result set for c end-exec
display " "
display "Second result set from proc"
display " "
perform until exit
exec sql fetch c into
:CustomerID, :Company, :City
end-exec
if sqlcode = 100 or sqlcode < 0
exit perform
end-if display CustomerID " " City
end-perform
INCLUDE
Includes the definition of the specified SQL data structure or source file in the COBOL program.
Syntax:
>>---EXEC SQL---INCLUDE-----.-SQLCA----.----END-EXEC----><
+-SQLDA----+
+-filename-+
Parameters:
SQLCA
- Indicates that a SQLCA data structure is accessed. SQLDA Indicates that a SQLDA data structure is accessed.
filename
- Indicates that a file should be included in the source at this point (this is equivalent to the COBOL COPY facility).
Comments:
This statement uses the corresponding .cpy file. Ensure that sqlca.cpy and sqlda.cpy are in the current directory or in the environment variable COBCPY directory.
Example:
EXEC SQL INCLUDE SQLCA END-EXEC
EXEC SQL INCLUDE SQLDA END-EXEC
EXEC SQL INCLUDE MYFILE END-EXEC
INSERT
Adds new rows to a table.
Syntax:
>>--EXEC SQL--.-------------------.---------->
+-FOR :host_integer-+
>---.------------.--INSERT--.------.---.-table_name-.--->
+-AT db_name-+ +-INTO-+ +-view_name--+
+-------- , ------+
V |
>---.---------------.--VALUES (constant_expression)----->
+-(column_list)-+
>------END-EXEC---><
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection executes the insert. If provided, and the connection specified is different than the current connection, the insert is performed on the connection associated with the DECLARE CURSOR statement.
table_name
- The table into which rows are to be inserted. view_name The view into which rows are to be inserted.
INTO
- An optional keyword. Required for ANSI SQL 92 conformance.
column_list
- A list of one or more columns to which data is to be added. The columns can be in any order, but the incoming data must be in the same order as the columns. The column list is necessary only when some, but not all, columns in the table are to receive data. Enclose the items in the column list parentheses. If no column list is given, all the columns in the receiving table (in CREATE TABLE order) are assumed.
The column list determines the order in which values are entered.
VALUES
- Introduces a list of constant expressions.
constant_expression
- Constant or null values for the indicated columns. The values list must be enclosed in parentheses and must match the explicit or implicit columns list.
Enclose non-numeric constants in single or quotation marks.
Comments:
The INSERT statement is passed directly to the ODBC driver. See the documentation supplied with your ODBC driver for the exact syntax.
If the host variables in the WHERE clause are arrays, the INSERT statement is executed once for each set of array elements.
Use UPDATE to modify column values in an existing row.
You can omit items in the column list and VALUES list providing that the omitted columns are defined to allow null values.
You can select rows from a table and insert them into the same table in a single statement.
After execution, SQLERRD(3) contains the number of elements processed. For INSERT it is the total number of rows inserted.
Example:
DISPLAY "Enter new staff member's id:"
ACCEPT staff-id
DISPLAY "Enter new staff member's last name:"
ACCEPT last-name
DISPLAY "Enter new staff member's first name:"
ACCEPT first-name
DISPLAY "Enter new staff member's age:"
ACCEPT age
DISPLAY "Enter new staff member's employment date(yyyy/mm/dd):"
ACCEPT employment-date
EXEC SQL
INSERT INTO staff
(staff_id
,last_name
,first_name
,age
,employment_date)
VALUES
(:staff-id
,:last-name
,:first-name
,:age
,:employment-date)
END-EXEC
INTO
Retrieves one row of results and assigns the values of the items returned by an OUTPUT clause in a SQL Server INSERT, UPDATE, or DELETE statement to the host variables specified in the INTO list.
Syntax:
>>---EXEC SQL--.-------------------.---.------------.---->
+-FOR :host_integer-+ +-AT db_name-+
+- ,-+
V |
>---INTO--:hvar--result-set-generating-dml-statement---->
>---END-EXEC---><
Parameters:
host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
db_name
- The name of a database that has been declared using DECLARE DATABASE. |
hvar
- A host variable to store the ODBC connection handle. Must be declared as PIC X(4) COMP-5. |
result-set-generating-dmlstatement
- A SQL Server INSERT, non-positioned UPDATE, or DELETE statement with an OUTPUT clause. |
Comments:
INTO is supported for SQL Server only.
OPEN
Runs the SELECT statement specified in the corresponding DECLARE CURSOR statement to produce the results set that is accessed one row at a time by the FETCH statement.
Syntax:
>>--EXEC SQL---OPEN---cursor_name------------------------>
>----.--------------------------------.----END-EXEC-----><
+-USING DESCRIPTOR :sqlda_struct-+
| +- , -+ |
| V |
+-USING :hvar--------------------+
Parameters:
cursor_name
- A previously declared cursor.
:sqlda_struct
- An SQLDA data structure previously constructed by the application. The SQLDA data structure contains the address, data type, and length of each input parameter. This option is used only with a cursor that references a prepared SQL statement in the DECLARE statement. The colon is optional to provide compatibility with other embedded SQL implementations.
:hvar
- One or more input host variables corresponding to parameter markers in the SELECT statement. This option is used only with a cursor that references a prepared SQL statement in the DECLARE statement. |
Comments:
If the cursor is declared with a static SELECT statement (that is, one that was not prepared), the SELECT statement can contain host variables but not parameter markers . The current values of the host variables are substituted when the OPEN statement runs. For a statically declared cursor, the OPEN statement cannot contain the USING :hvar or USING DESCRIPTOR :sqlda_struct option.
If the cursor is declared with a dynamic SELECT statement (that is, one that was prepared), the SELECT statement can contain parameter markers but not host variables. Parameter markers can appear wherever column values are allowed in the SELECT statement. If the SELECT statement has parameter markers, the OPEN statement must include either the USING :hvar option with the same number of host variables or the USING DESCRIPTOR :sqlda_struct option identifying an SQLDA data structure already populated by the application.
With the USING DESCRIPTOR :sqlda_struct option, values of the program variables are substituted for parameter markers in the SELECT statement. These program variables are addressed by corresponding SQLDATA entries in the SQLDA data structure.
The number of parameter markers in the SELECT statement must match the number of sqldata entries (USING DESCRIPTOR :sqlda_struct) or host variables (USING :hvar) in the OPEN statement.
Example:
*Declare the cursor...
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT staff_id, last_name
FROM staff
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not declare cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
OPEN C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not open cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
PERFORM UNTIL sqlcode NOT = ZERO
*SQLCODE will be zero as long as it has successfully fetched data
EXEC SQL
FETCH C1 INTO :staff-staff-id, :staff-last-name
END-EXEC
IF SQLCODE = ZERO
DISPLAY "Staff ID: " staff-staff-id
DISPLAY "Staff member's last name: " staff-last-name
END-IF
END-PERFORM
EXEC SQL
CLOSE C1
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not close cursor.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
END-IF
PREPARE
Processes dynamic SQL statements.
Syntax:
>>--EXEC SQL---PREPARE---stmt_name---.-------------.----->
+-INTO :sqlda-+
>---FROM---:hvar---END-EXEC-----\>\<
Parameters:
stmt_name
- The prepared statement name. This can be used by a subsequent EXECUTE or OPEN statement, and/or a previous DECLARE CURSOR statement.
:sqlda
- The output SQL descriptor area (SQLDA) data structure to be populated. The colon is optional to provide compatibility with other embedded SQL implementations.
:hvar
- The host variable that contains the SQL statement.
Comments:
You can use a prepared statement in one of two ways:
- You can open a cursor that references a prepared statement.
- You can execute a prepared statement.
If the prepared statement is used by an EXECUTE statement, :hvar cannot contain a SQL statement that returns results.
Because singleton SELECT statements (SELECT INTO) are not allowed in dynamic SQL statements, they cannot be prepared.
When using PREPARE, the SQL statement in :hvar cannot contain host variables or comments, but it can contain parameter markers. Also, the SQL statement cannot contain SQL keywords that pertain exclusively to Embedded SQL.
The INTO :sqlda option merges the functionality of DESCRIBE and PREPARE so that this example code:
EXEC SQL
PREPARE stmt1 INTO :sqlda FROM :stmt-buf
END-EXEC
Is identical to:
EXEC SQL
PREPARE stmt1 FROM :stmt-buf
END-EXEC
EXEC SQL
DESCRIBE stmt1 INTO :sqlda
END-EXEC
Example:
PROGRAM-ID. progname.
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 prep PIC X(80).
01 nme PIC X(20).
01 car PIC X(20).
01 n60 PIC x(5).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
EXEC SQL CONNECT TO 'srv1' USER 'sa' END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not connect to database.'
DISPLAY SQLERRMC
DISPLAY SQLERRMC
STOP RUN
END-IF
* Ensure attempt is not made to recreate an existing table...
EXEC SQL DROP TABLE mf_table END-EXEC
* Create a table...
EXEC SQL CREATE TABLE mf_table
(owner char(20)
,car_col char(20)
,nought_to_60 char(5))
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not create table'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
* Insert an SQL statement into host variable prep...
MOVE "insert into mf_table values(?,?,?)" TO prep
* Prepare the statement...
EXEC SQL
PREPARE prep_stat FROM :prep
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not prepare statement'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
MOVE "Owner" TO nme
MOVE "Lamborghini" TO car
MOVE "4.9" TO n60
* Execute the prepared statement using the above host variables...
EXEC SQL
EXECUTE prep_stat USING :nme, :car, :n60
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not execute prepared statement.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
* Finally, drop the now unwanted table...
EXEC SQL
DROP TABLE mf_table
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not drop table.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
DISPLAY 'All statements executed.'
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN.
QUERY ODBC
Delivers a results set in the same way as a SELECT statement, and must therefore be associated with a cursor via DECLARE and OPEN, or DECLARE, PREPARE and OPEN.
Syntax Format 1:
>>--EXEC SQL---QUERY ODBC---.-COLUMN--.------------------>
+-COLUMNS-+
>---.--------------------------.--.------------------.--->
+-QUALIFIER qualifier_name-+ +-OWNER owner_name-+
>--.----------------------.--.------------------------.-->
+-TABLENAME table_name-+ +-COLUMNNAME column_name-+
>---END-EXEC---<>
Syntax Format 2:
>>--EXEC SQL---QUERY ODBC---.-DATATYPE--.---------------->
+-DATATYPES-+
>---.-------------------------.---END-EXEC---><
+-TYPE--.-datatype_name--.+
+-BIGINT---------+
+-BINARY---------+
+-BIT------------+
+-CHAR-----------+
+-DATE-----------+
+-DECIMAL--------+
+-DOUBLE---------+
+-FLOAT----------+
+-INTEGER--------+
+-LONG VARBINARY-+
+-LONG VARCHAR---+
+-NUMERIC--------+
+-REAL-----------+
+-SMALLINT-------+
+-TIME-----------+
+-TIMESTAMP------+
+-TINYINT--------+
+-VARBINARY------+
+-VARCHAR--------+
Syntax Format 3:
>>--EXEC SQL---QUERY ODBC---.-TABLE--.------------------>
+-TABLES-+
>---.--------------------------.--.------------------.--->
+-QUALIFIER qualifier_name-+ +-OWNER owner_name-+
>--.----------------------.--.------------------------.-->
+-TABLENAME table_name-+ +-TYPE tabletype_name----+
>--END-EXEC--<>
Parameters:
qualifier_name
- A host variable, identifier or literal which specifies a qualifier to be used to select tables. Not all ODBC drivers support qualifiers, and those that do may use them in different ways. For example, if a data source supports multiple databases, a qualifier can be used to specify which database to use. Alternatively, for drivers providing access to file based data sources, a qualifier can be used to specify a particular directory to be searched. |
owner_name
- A host variable, identifier or literal which specifies a table owner to be used to select tables. Not all ODBC drivers support table ownership.
table_name
- A host variable, identifier or literal which specifies tables to be included in the query.
datatype_name
- A host variable, identifier or literal which specifies a data type to be queried.
tabletype_name
- A host variable, identifier or literal which specifies a list of table types to be included in the query.
Comments:
Search patterns consist of the legal characters for SQL identifiers plus underscore (_) which matches any single character, percent (%) which matches any sequence of zero or more characters, or a driver defined escape character which can be used to allow underscore or percent in a pattern to represent themselves rather than a wildcard.
If a search pattern parameter is not supplied, a pattern of % is used, which will match all relevant dictionary entries.
For table queries the following special rules apply:
-
If qualifier-name is % and owner-name and table-name are empty strings, the results set consists of a list of valid qualifiers at the data source. All columns apart from TABLE_QUALIFIER in the results set (see below) will be null.
-
If owner-name is % and qualifier-name and table-name are empty strings, the results set consists of a list of valid owners at the data source. All columns apart from TABLE_OWNER in the results set (see below) will be null.
-
If tabletype-name is % and qualifier-name, owner-name and table-name are empty strings the results set consists of a list of valid table types at the data source. All columns apart from TABLE_TYPE in the results set (see below) will be null.
-
If tabletype-name is not specified, tables of all types will be returned in the results set. If it is specified it must consist of a comma separated list of table types, for example'TABLE,VIEW'.
Example:
EXEC SQL
DECLARE tcurs CURSOR FOR QUERY ODBC TABLES
END-EXEC
EXEC SQL DECLARE C1 CURSOR FOR
QUERY ODBC TABLES OWNER :tab-owner TABLETYPE 'TABLE,VIEW'
END-EXEC
MOVE 'staff' to tab-name
EXEC SQL DECLARE C2 CURSOR FOR
QUERY ODBC COLUMNS TABLENAME :tab-name
END-EXEC
EXEC SQL DECLARE C3 CURSOR FOR
QUERY ODBC DATATYPES
END-EXEC
QUERY ODBC - Column Query
The results set for a column query is:
| TABLE_QUALIFIER | VARCHAR(128) | |
|---|---|---|
| TABLE_OWNER | VARCHAR(128) | |
| TABLE_NAME | VARCHAR(128) NOT NULL | |
| COLUMN_NAME | VARCHAR(128) NOT NULL | |
| DATA_TYPE | SMALLINT NOT NULL | See odbcext.cpy and odbc.cpy for constants representing the ODBC data type codes. |
| TYPE_NAME | VARCHAR(128) NOT NULL | Driver dependent name for the column's data type. |
| PRECISION | INTEGER | |
| LENGTH | INTEGER | Amount of memory required for a column value in its native representation. |
| SCALE | SMALLINT | |
| RADIX | SMALLINT | For numeric columns either 10 or 2 depending on the data type; otherwise null |
| NULLABLE | SMALLINT NOT NULL | |
| REMARKS | VARCHAR(254) |
QUERY ODBC - Data Type Query
The results set for a data type query is:
| TYPE_NAME | VARCHAR(128) NOT NULL | Driver dependent name for the column's data type. |
|---|---|---|
| DATA_TYPE | SMALLINT NOT NULL | See odbcext.cpy and odbc.cpy for constants representing the ODBC data type codes. |
| PRECISION | INTEGER | Maximum precision for columns of this type. |
| LITERAL_PREFIX | VARCHAR(128) | Character or characters required to prefix literal values for this type. |
| LITERAL_SUFFIX | VARCHAR(128) | Character or characters required to suffix literal values for this type. |
| CREATE_PARAMS | VARCHAR(128) | Parameters required when creating a column of this type, for example, 'precision,scale' for decimal types. |
| NULLABLE | SMALLINT NOT NULL | |
| CASE_SENSITIVE | SMALLINT NOT NULL | Specifies case sensitivity in comparisons for character data types. |
| SEARCHABLE | SMALLINT NOT NULL | SQL_UNSEARCHABLE, SQL_LIKE_ONLY or SQL_ALL_EXCEPT_LIKE (these are defined in odbc_cpy). |
| UNSIGNED_ATTRIBUTE | SMALLINT | Specifies if a numeric type is signed or unsigned. |
| MONEY | SMALLINT NOT NULL | Specifies if a numeric type is a money data type. |
| AUTO_INCREMENT | SMALLINT | Specifies if the data type is auto incrementing. |
| LOCAL_TYPE_NAME | VARCHAR(128) | Localized version of the data type name. |
| MINIMUM_SCALE | SMALLINT | |
| MAXIMUM_SCALE | SMALLINT |
QUERY ODBC - Table Query
The results set for a table query is:
| TABLE_QUALIFIER | VARCHAR(128) | |
|---|---|---|
| TABLE_OWNER | VARCHAR(128) | |
| TABLE_NAME | VARCHAR(128) | |
| TABLE_TYPE | VARCHAR(128) | One of TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM or a data source specific type identifier |
| REMARKS | VARCHAR(254) |
RESET CONNECTION
Closes all open cursors, even if the application has not appropriately closed them.
Syntax:
>>---EXEC SQL---RESET CONNECTION---END-EXEC---><
ROLLBACK
Backs out any changes made to the database by the current transaction on the current connection, or partially rolls back changes to a previously set save point.
Syntax:
>>---EXEC SQL---.------------.--->
+-AT db_name-+
>--ROLLBACK----.--------------.--------->
+-WORK---------+
+-TRAN---------+
+-TRANSACTION--+
>---.-----------.---END-EXEC---><
+--RELEASE--+
+--TO-.-----------.-*name*--+
+-SAVEPOINT-+
Parameter:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection is rolled back. If provided, and the connection specified is different than the current connection, the rollback is performed on the connection associated with the DECLARE CURSOR statement.
Comments:
When RELEASE is specified and the transaction is successfully rolled back, the current connection is closed.
TO [SAVEPOINT] rolls the transaction back just to the save point specified by name, which must be set by a preceding SAVEPOINT statement.
Example:
EXEC SQL
ROLLBACK
END-EXEC
EXEC SQL
ROLLBACK WORK RELEASE
END-EXEC
END-EXEC
EXEC SQL
ROLLBACK TO SP1
END-EXEC
SAVEPOINT, SAVE TRANSACTION, RELEASE [TO] SAVEPOINT
Sets a transaction save point to which a current transaction can be rolled back, resulting in a partial roll back.
Syntax:
>>---EXEC SQL--.------------.--SAVEPOINT-*name*--.------.-->
+-AT db_name-+ +UNIQUE+
>--.----------------------------------------------------------
.---END-EXEC---><
+--ON ROLLBACK RETAIN CURSORS--.----------------------------+
+--ON ROLLBACK RETAIN LOCKS--+
>---EXEC SQL--.------------.--SAVE-.-TRANSACTION--.--name-- ENDEXEC---><
+-AT db_name-+ +-TRAN---------+
>>---EXEC SQL--.------------.--RELEASE-.----.-SAVEPOINT-name--- END-EXEC---><
+-AT db_name-+ +-TO-+
Parameter:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
Comments:
You can define multiple save points for a single transaction.
When you set a save point using a unique name, and subsequently set another save point using the same unique name, the named save point is reset to the current transaction state.
The behavior of cursors and locks after a rollback to a save point is database-specific. For details, see the documentation provided by your database vendor.
Example:
EXEC SQL
SAVEPOINT SP1
END-EXEC
EXEC SQL
SAVEPOINT PHASE2 ON ROLLBACK RETAIN CURSORS
END-EXEC
SELECT DISTINCT (using DECLARE CURSOR)
Associates the cursor name with the SELECT DISTINCT statement and enables you to retrieve rows of data using the FETCH statement.
Syntax:
>>---EXEC SQL--.------------.------DECLARE cursor_name---->
+-AT db_name-+
>---CURSOR FOR------SELECT DISTINCT------select_list------>
>---FROM----table_list--.-----------------2--END-EXEC----><
+-select_options--+
Parameters:
db_name
- The name of a database that has been declared using DECLARE DATABASE.
cursor_name
- Cursor name used to identify the cursor in subsequent statements. Cursor names can contain any legal filename character and be up to 30 characters in length. The first character must be a letter.
select_list
- The name of the columns to retrieve.
table_list
- The name of the tables that contain the columns to be retrieved, as specified in select_list.
select_options
- The options specified to limit the number of rows retrieved and/or order the rows retrieved.
Comments:
Two separately compiled programs cannot share the same cursor. All statements that reference a particular cursor must be compiled together.
The SELECT DISTINCT statement runs when the cursor is opened. The following rules apply to the SELECT DISTINCT statement:
-
The statement cannot contain an INTO clause or parameter markers.
-
The statement can contain input host variables previously identified in a declaration section.
-
With some ODBC drivers, the SELECT DISTINCT statement must include a FOR UPDATE clause if positioned updates or deletions are to be performed.
Note
Use SELECT DISTINCT instead of SELECT INTO to remove duplicate rows in the row set.
Example:
01 age-array pic s9(09) comp-5 occurs 10 times.
01 lname-array pic x(32) occurs 10 times.
MOVE 5 TO staff-id
EXEC SQL
SELECT DISTINCT last_name
INTO :lname-array
FROM staff
WHERE staff_id > :staff-id
END-EXEC
EXEC SQL
SELECT DISTINCT age
INTO :age-array
FROM staff
WHERE first_name > 'George'
END-EXEC
SELECT INTO
Retrieves one row of results and assigns the values of the items in a specified SELECT list to the host variables specified in the INTO list.
Syntax:
>>---EXEC SQL--.-------------------.---.------------.---->
+-FOR :host_integer-+ +-AT db_name-+
+- ,-+
V |
>----SELECT----.-------------.---INTO--:hvar------------->
+-select_list-+
>----select_options----END-EXEC---><
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
db_name
- The name of a database that has been declared using DECLARE DATABASE.
select_list
- The portion of the table to retrieve data from.
:hvar
- One or more host variables to receive the select_list items.
select_options
- One or more statements or other options that can be used with the SQL SELECT statement (for example, a FROM or WHERE clause).
Comments:
A singleton SELECT must contain a FROM clause.
If more columns are selected than the number of receiving host variables, the value of sqlwarn3 is set to 'W'. The data type and length of the host variable must be compatible with the value assigned to it. If data is truncated, the value of sqlwarn1 is set to 'W'.
If a SELECT INTO statement returns more than one row from the database, all rows except the first one will be discarded and sqlwarn4 will be set to "W". If you want to return more than the first row, you should use a cursor. Alternatively, you can specify array items in the INTO clause. The array will be populated up to either the maximum size of the array, the value of host_integer or the number of rows returned, whichever is the smallest.
If SELECT INTO returns more rows from the database than the statement in the application is able to accept, CitOESQL returns the following for each of the specified directives:
CHECKSINGLETON SQLCODE = -811 SQLSTATE = 21000 SQLWARN4 = W
NOCHECKSINGLETON SQLCODE = 0 SQLSTATE = 00000 SQLWARN4 = space
ANSI92ENTRY SQLCODE = -1 SQLSTATE = 21000 SQLWARN4 = W
If SELECT INTO returns more rows from the database than the statement in the application is able to accept, and none of these directives are set, then CitOESQL returns:
SQLCODE = +1 SQLSTATE = 21000 SQLWARN4 = W
Note
If any one of the host variables in the INTO clause is an array, then they all must be arrays.
Example:
...
MOVE 99 TO staff-id
EXEC SQL
SELECT last_name
INTO :lname
FROM staff
WHERE staff_id=:staff-id
END-EXEC
EXEC SQL
SELECT staff_id
INTO :staff-id
FROM staff
WHERE first_name = 'Phil'
END-EXEC
SET AUTOCOMMIT
Enables you to control ODBC AUTOCOMMIT mode at run time.
Syntax:
>>--EXEC SQL--SET AUTOCOMMIT---.-ON--.---END-EXEC--><
+-OFF-+
Parameters:
ON
- Changes to AUTOCOMMIT mode, whereby each SQL statement is treated as a separate transaction and is committed immediately upon execution.
OFF
- Switches off AUTOCOMMIT mode. If the ODBC driver you are using supports transactions, statements must be explicitly committed (or rolled back) as part of a transaction.
Comments:
The SET AUTOCOMMIT statement is useful for data sources which can only execute DDL statements, such as CREATE and DROP, in AUTOCOMMIT mode.
When set to ON, AUTOCOMMIT releases locks when an outermost stored procedure executes a COMMIT or a ROLLBACK statement.
This statement overrides the AUTOCOMMIT SQL compiler directive option.
Example:
EXEC SQL SET AUTOCOMMIT ON END-EXEC
SET CONNECTION
Sets the named connection as the current connection.
Syntax:
>>--EXEC SQL--SET CONNECTION---.-name-----.---END-EXEC--><
+-DEFAULT--+
Parameters:
name
- Specifies the name of a database connection. Must match the connection name specified in a previous CONNECT statement. The name can be either the connection's literal name or the name of a host variable containing character values.
DEFAULT
- If you have established a connection using the CONNECT statement but omitting the connection name, you can refer to the connection established as DEFAULT.
Comments:
If you are using connections across compilation modules you must use named connections.
Example:
EXEC SQL CONNECT TO "srv1" AS server1 USER "sa." END-EXEC
EXEC SQL CONNECT TO "srv2" AS server2 USER "sa." END-EXEC
* server2 is the current connection
EXEC SQL CREATE TABLE phil1
(charbit CHAR(5))
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not create table.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL INSERT INTO phil1 VALUES('hello') END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not insert data.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* set the current connection to server1
EXEC SQL SET CONNECTION server1 END-EXEC
EXEC SQL
SELECT first_name
INTO :fname
FROM staff
WHERE staff_id = 10
END-EXEC
DISPLAY fname ' says ' WITH NO ADVANCING
* set the current connection back to server2
EXEC SQL SET CONNECTION server2 END-EXEC
EXEC SQL
SELECT charbit
INTO :fname
WHERE charbit = 'hello'
FROM phil1
END-EXEC
DISPLAY fname
EXEC SQL DISCONNECT server1 END-EXEC
EXEC SQL DISCONNECT server2 END-EXEC
STOP RUN
SET ERRORMAP
Changes the SQL error map file for the current connection.
Syntax:
>>--EXEC SQL--SET ERRORMAP--map-file-prefix--END-EXEC--><
Parameters:
Map-file-prefix
- The prefix of the SQL error map file name (no file extension).
Comments:
SQL Error Mapping must be enabled using the ERRORMAP SQL compiler directive option.
The SET ERRORMAP statement is most useful in applications that use multiple database connections.
SET host_variable
Provides information about CitOESQL connections and databases.
Syntax:
>>--EXEC SQL--.------------.--->
+-AT db_name-+
>--SET :host_variable = >
>------.-CURRENT CONNECTION-.------------.----------.-->
+-CURRENT DATABASE---.------------.----------+
+-OPTION-------------+-DATE-------+-USA------+
+-EUR------+
+-JIS------+
+-ODBC-----+
+-EXTERNAL-+
+-ISO------+
+-DEFAULT--+
+-TIME-------+-USA------+
+-EUR------+
+-JIS------+
+-ODBC-----+
+-EXTERNAL-+
+-ISO------+
+-DEFAULT--+
+-DATEDELIM--+-char-----+
+-TIMEDELIM--+-char-----+
+-TSTAMPSEP--+-char-----+
+-DETECTDATE-+-CLIENT---+
+-SERVER---+ +-OFF------+
>---END EXEC---><
Parameters:
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
host_variable
- A PIC X(n) host variable
char
- Any single printable character or space
Note
For a full description of each option and its corresponding parameters, see the equivalent SQL Compiler Directive Options topic as listed in the Related reference section below.
Comments:
This statement returns the name of the current connection or the type of database for the current connection as specified in the EXEC SQL CONNECT statement in the specified host variable.
If there is no current open connection, host_variable is set to NONE for both CURRENT CONNECTION and CURRENT DATABASE.
With CURRENT CONNECTION, you can use host_variable in a subsequent EXEC SQL SET CONNECTION statement.
With CURRENT DATABASE, if there is a current open connection, host_variable is set to one of the following values, depending on the connection:
-
DB2
-
SQLSERVER
-
ORACLE
-
POSTGRESQL
-
OTHER
In addition to the OPTION syntax specified above, you can optionally place an equals (=) sign between the option and its parameter. For example, the following are equivalent:
exec sql set :myhostvar option date EUR end-exec
and
exec sql set :myhostvar option date=EUR end-exec
SET OPTION
Enables you to set CitOESQL options.
Syntax:
>>---EXEC SQL---SET OPTION----.-QUERYTIME---.----------.-->
+-LOGINTIME---+
+-APPLICATION-+
+-HOST--------+
+-DATE--------+-USA------+
+-EUR------+
+-JIS------+
+-ODBC-----+
+-EXTERNAL-+
+-ISO------+
+-DEFAULT--+
+-TIME--------+-USA------+
+-EUR------+
+-JIS------+
+-ODBC-----+
+-EXTERNAL-+
+-ISO------+
+-DEFAULT--+
+-DATEDELIM--+-char-----+
+-TIMEDELIM--+-char-----+
+-TSTAMPSEP--+-char-----+
+-DETECTDATE-+-CLIENT---+
+-SERVER---+
+-OFF------+
>---value---END-EXEC---><
Parameters:
value
- A literal or the name of a host variable. The host variable must contain character values for APPLICATION or HOST and numeric values for LOGINTIME or QUERYTIME.
QUERYTIME
- Sets the number of seconds that the program waits for a response to an CitOESQL statement. The default is 0, meaning forever. This option does not override existing network timeout settings.
LOGINTIME
- Sets the number of seconds that the program waits for a response to a CONNECT TO statement. The default is 10 seconds. A value of 0 indicates an infinite timeout period.
APPLICATION
- Sets the application name which is passed by CitOESQL to the data source when a CONNECT TO statement is executed.
HOST
- Sets the host workstation name which is passed by CitOESQL when a CONNECT TO statement is executed.
Comments:
The SET OPTION statement is not supported by all ODBC drivers.
As an option, you can place an equals (=) sign between an option and its parameter. For example, the following are equivalent:
exec sql set option date EUR end-exec
and
exec sql set option date=EUR end-exec
Example:
EXEC SQL SET OPTION logintime 5 END-EXEC
EXEC SQL CONNECT TO "srv2" USER "sa." END-EXEC
* If the CONNECT statement cannot log in to the server "srv2"
* within five seconds, it will time out and return to the
program.
EXEC SQL SET OPTION querytime 2 END-EXEC
EXEC SQL SELECT name FROM sysobjects INTO :name END-EXEC
* If the SELECT statement does not respond within 2 seconds,
* the query will time out and return to the program.
SET TRACELEVEL
Enables you to dynamically set or change the reporting level of CitOESQL traces for native applications.
Syntax:
>>--EXEC SQL--SET TRACELEVEL--.-0-------.---END-EXEC--><
+-1-------+
+-2-------+
+-3-------+
+-4-------+
+-5-------+
+-6-------+
+-OFF-----+
+-DEFAULT-+
Parameters:
0
- Turns off CitOESQL trace.
1
- The following information is written to the trace file:
| BEGIN | Traces main SQL directives. |
| END | Indicates end of run. |
| DIRECTIVES | Traces per compilation unit directives the first time a compilation unit is encountered at run time |
| PREPARE | Identifies the original source code when a statement is prepared |
| DISPOSE | Provides summary information for overall statement usage when a statement is removed from the prepared statement cache at disconnect time |
| FLUSH | Provides summary information for overall statement usage when a statement is flushed from the cache usually due to a cache overflow |
2
- The following information is written to the trace file in addition to the information written when you set the trace level to 1:
| OPEN | |
| EXECUTE | Provides the number of rows selected, inserted or updated |
| EXEC_IMMED EXECUTE | Provides the number of rows selected, inserted or updated |
| ODBCCLOSE | Provides summary information for the current cursor use |
| STMT CHANGED | Reports new concurrency and scroll option settings when the ODBC driver uses different settings than those requested by CitOESQL. |
3
- The following information is written to the trace file in addition to the information written when you set the trace level to 2:
| ODBCFETCH | Provides the number of rows fetched |
| COBOLFETCH | Provides the number of rows returned to the COBOL application |
4
-
The following information is written to the trace file in addition to the information written when you set the trace level to 3:
- EXEC_SQL_BEGIN
- EXEC_SQL_END
5
-
The following information is written to the trace file in addition to the information written when you set the trace level to 4:
- ODBC_CALL_START
- ODBC_CALL_END
6
-
Only the following information is written to the trace file:
- ODBC_CALL_START
- ODBC_CALL_END
OFF
- Turns off the CitOESQL trace
DEFAULT
- Resets the trace setting to the value set by the SQL TRACELEVEL directive. If the TRACELEVEL directive was not used to compile a program, this is equivalent to setting this option to OFF. |
Example:
EXEC SQL SET TRACELEVEL DEFAULT END-EXEC
SET TRANSACTION ISOLATION
Sets the transaction isolation level for the current connection to one of the isolation level modes specified by ODBC.
Syntax:
>>--EXEC SQL--SET TRANSACTION ISOLATION------------------->
>-------.-READ UNCOMMITTED-.---------END-EXEC------------><
+-READ COMMITTED---+
+-REPEATABLE READ--+
+-SERIALIZABLE-----+
Comments:
Transactions can affect each other in the following ways, depending on the setting of the transaction isolation level:
-
Dirty read - Transaction 1 updates a row. Transaction 2 reads the row before transaction 1 commits. Transaction 1 issues a rollback. Transaction 2's results are based on invalid data.
-
Nonrepeatable read - Transaction 1 reads a row. Transaction 2 updates or deletes the row and commits the change. If transaction 1 re-reads the row, it will retrieve different values, or may not be able to re-read the row.
-
Phantom - Transaction 1 reads a set of rows using a select with a where clause. Transaction 2 inserts a row that satisfies the where clause. If transaction 1 repeats the select, it will read a different set of rows.
These situations can be controlled by locking, which means that a transaction might have to wait until another transaction completes, which limits concurrency (sometimes called pessimistic concurrency), or by forcing a transaction to rollback if the situation occurs, which has less of an impact on concurrency but may force work to be repeated (this is sometimes called optimistic concurrency).
In READ UNCOMMITED mode, dirty reads, nonrepeatable reads and phantoms are all possible.
In READ COMMITED mode, dirty reads are not possible but nonrepeatable reads and phantoms are.
In REPEATABLE READ mode, dirty reads and nonrepeatable reads are not possible, but phantoms are.
In SERIALIZABLE mode dirty reads, nonrepeatable reads and phantoms are all impossible.
Note
A driver might not support all the isolation levels defined by ODBC. If you set a mode that the driver does not support, SQLCODE and SQLSTATE are set accordingly.
Example:
EXEC SQL SET TRANSACTION ISOLATION READ UNCOMMITTED END-EXEC
SYNCPOINT
Closes all open cursors that were not opened using the WITH HOLD clause, even if the application has not appropriately closed them.
Syntax:
>>---EXEC SQL---SYNCPOINT---END-EXEC---><
UPDATE (Positioned)
Updates the row most recently fetched by using a cursor.
Syntax:
>>---EXEC SQL---.-----------------.--.------------.----->
+--FOR :host_integer-+ +-AT db_name-+
+------ ,-------+
V |
>---UPDATE---table_name-----SET--column_expression----->
>--WHERE CURRENT OF--cursor_name---END-EXEC---><
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement. |
table_name
- The table to be updated.
column_expression
- A value for a particular column name. This value can be an expression or a null value.
cursor_name
- A previously declared, opened, and fetched cursor.
Comments:
Do not use the FOR clause if the UPDATE is part of a DECLARE CURSOR statement.
After execution, SQLERRD(3) contains the number of elements processed. For UPDATE it is the total number of rows updated.
ODBC supports positioned update, which updates the row most recently fetched by using a cursor, in the Extended Syntax (it was in the core Syntax for ODBC 1.0 but was moved to the Extended
Syntax for ODBC 2.0). Not all drivers provide support for positioned update, although CitOESQL sets ODBC cursor names to be the same as COBOL cursor names to facilitate positioned update and delete.
With some ODBC drivers, the select statement used by the cursor must contain a FOR UPDATE clause to enable positioned update.
The other form of UPDATE used in standard SQL statements is known as a searched update.
You cannot use host arrays with positioned update.
Example:
EXEC SQL CONNECT TO 'srv1' USER 'sa' END-EXEC
EXEC SQL DECLARE C1 CURSOR FOR
SELECT last_name, first_name
FROM staff
FOR UPDATE
END-EXEC
EXEC SQL
OPEN C1
END-EXEC
PERFORM UNTIL SQLCODE NOT = ZERO
EXEC SQL
FETCH C1 INTO :fname,:lname
END-EXEC
IF SQLCODE = ZERO
DISPLAY fname " " lname
DISPLAY "Update?"
ACCEPT reply
IF reply = "y"
DISPLAY "New last name?"
ACCEPT lname
EXEC SQL
UPDATE staff
SET last_name=:lname WHERE CURRENT OF c1
END-EXEC
DISPLAY "update sqlcode=" SQLCODE
END-IF
END-IF
END-PERFORM
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN.
UPDATE (Searched)
Updates a table or view based on specified search conditions.
Syntax:
>>--EXEC SQL--.-------------------.---------------------->
+-FOR :host_integer-+
>-----.-------------.------UPDATE------.-table_name-.---->
+-AT db_name--+ +-view_name--+
+------ ,-------+
V |
>---SET--column_expression--.-------------------------.-->
+-WHERE search_conditions-+
>----END-EXEC---><
Parameters:
:host_integer
- A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
AT db_name
- The name of a database that has been declared using DECLARE DATABASE. This clause is optional. If omitted, the current connection executes the update. If provided, and the connection specified is different than the current connection, the update is performed on the connection associated with the DECLARE CURSOR statement.
table_name
- The table to be updated.
view_name
- The view to be updated.
column_expression
- A value for a particular column name. This value can be an expression or a null value.
search_conditions
- Any valid expression that can follow the standard SQL WHERE clause.
Comments:
UPDATE is a standard SQL statement which is passed directly to the ODBC driver. See the documentation supplied with your ODBC driver for the exact syntax.
If you do not specify a WHERE clause, all the rows in the named table are updated.
If one of the host variables used in the WHERE clause or SET clause is an array, they must all be arrays.
After execution, SQLERRD(3) contains the number of elements processed. For UPDATE it is the total number of rows updated.
Example:
EXEC SQL
UPDATE staff
SET first_name = 'Jonathan'
WHERE staff_id = 1
END-EXEC
MOVE 'Phil' TO NewName
MOVE 1 TO targetID
EXEC SQL
UPDATE staff
SET first_name = :NewName
WHERE staff_id = :targetID
END-EXEC
WHENEVER
Specifies the default action after running an Embedded SQL statement when a specific condition is met.
Syntax:
>>--EXEC SQL---WHENEVER---.-NOT FOUND--.----------------->
+-SQLERROR---+
+-SQLWARNING-+
>--.-CONTINUE--------.----END-EXEC---><
+-PERFORM label---+
+-GOTO stmt_label-+
Parameters:
CONTINUE
- Causes the next sequential statement in the source program to run.
PERFORM label
- Identifies a paragraph or section of code to be performed when a certain error level is detected.
GOTO stmt_label
- Identifies the place in the program that takes control when a certain error level is detected. |
Comments:
The WHENEVER statement specifies the default action after running an Embedded SQL statement on each of the following conditions: NOT FOUND, SQLERROR, SQLWARNING.
| Condition | Sqlcode |
|---|---|
| No error | 0 |
| NOT FOUND | 100 |
| SQLWARNING | +1 |
| SQLERROR | \<0 (negative) |
The scope of a WHENEVER statement is related to the position of statements in the source program, not in the run sequence. The default is CONTINUE for all three conditions.
Example:
The following example shows the WHENEVER statement in use:
``` EXEC SQL WHENEVER sqlerror PERFORM errormessage1 END-EXEC
EXEC SQL
DELETE FROM staff
WHERE staff_id = 'hello'
END-EXEC
EXEC SQL
DELETE FROM students
WHERE student_id = 'hello'
END-EXEC
EXEC SQL WHENEVER sqlerror CONTINUE END-EXEC
EXEC SQL
INSERT INTO staff VALUES ('hello')
END-EXEC
DISPLAY 'Sql Code is: ' SQLCODE
EXEC SQL WHENEVER sqlerror PERFORM errormessage2 END-EXEC
EXEC SQL
INSERT INTO staff VALUES ('hello again')
END-EXEC
STOP RUN.
errormessage1 SECTION.
display "SQL DELETE error: " sqlcode
EXIT.
errormessage2 SECTION.
display "SQL INSERT error: " sqlcode
EXIT.