6. Embedded SQL Statements#

Overview#

The term "embedded SQL statement" refers to a SQL statement that resides within an application.

Syntax#

EXEC SQL … ;

An embedded SQL statement begins with the words "EXEC SQL" and ends with a semicolon (";").

Between the EXEC SQL keyword and the semicolon, various kinds of SQL statements can be used, including DML statements, such as SELECT and UPDATE statements, and DDL statements, such as CREATE and DROP statements.

Limitation#

The maximum possible length of a SQL statement is 32KB (kilobytes).

Example#

The following is an example of embedded SQL statement.

< SELECT statement : select.sc >

EXEC SQL BEGIN DECLARE SECTION;
short      s_dno;
char       s_dname[30+1];
char       s_dep_location[9+1];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT DNAME, DEP_LOCATION 
INTO :s_dname, :s_dep_location
           FROM DEPARTMENTS 
WHERE DNO = :s_dno;

< INSERT statement : insert.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    s_gno[10+1];
char    s_gname[20+1];
char    s_goods_location[9+1];
int     s_stock;
double  s_price;
EXEC SQL END DECLARE SECTION;

EXEC SQL INSERT INTO GOODS 
VALUES (:s_gno, :s_gname, 
:s_goods_location, 
:s_stock, :s_price);

More detailed information about the syntax of each embedded SQL statement will be provided later in this chapter.

Static Versus Dynamic SQL Statements#

Embedded SQL statements can be broadly classified as either static SQL statements or dynamic SQL statements, depending on whether the contents of the SQL statement are determined when the application is written, or at runtime. This chapter describes only static SQL statements. For more information about dynamic SQL statements, please refer to the Chapter 10.

Embedded SQL statements can also classified into the following categories based on how they handle data and the role that they play.

Host Variable Declaration Section#

These statements are used to delimit a block of code for declaring host variables for use in other embedded SQL statements. For more detailed information, please refer to Chapter 3: Host Variable Declaration Section.

Function Argument Declaration Section#

These statements are used to delimit a block of code for declaring function arguments for use in other embedded SQL statements. For more detailed information, please refer to Chapter 3: Host Variable Declaration Section.

These statements are used to connect to and disconnect from a database.

Basic Embedded SQL Statements#

These statements include DML statements, such as SELECT, UPDATE, INSERT, and DELETE statements, as well as DDL statements, such as CREATE, DROP, and ALTER statements.

Cursor Control SQL Statements#

These statements are used together with cursors to process data, and include statements for defining cursors, opening cursors, using cursors to retrieve data, and closing cursors. For more detailed information, please refer to Chapter 8: Using Cursors.

SQL Statements for Controlling Stored Procedures and Functions#

These statements are used for handling stored procedures and stored functions, and include statements for creating, recompiling, executing, and deleting stored procedures and functions. For more detailed information, please refer to Chapter 11: Using Stored Procedures in C/C++

Other Embedded SQL Statements#

This category includes all SQL statements supported in Altibase that do not fall into one of the above categories. These statements include task control statements and DCL statements for controlling the system and individual transactions.

OPTION Statements#

These statements are used to set various options provided by the APRE*C/C++precompiler.

Connection-related SQL statements are those that are used to manage a connection with a database server, including the CONNECT and DISCONNECT statements.

CONNECT#

This statement is used to connect to the database server.

Syntax#

EXEC SQL CONNECT <:user> IDENTIFIED BY <:passwd>
[ USING <:conn_opt1> [ , <:conn_opt2> ] ];

Arguments#

<:user>: This is the name of the user with which to connect to the database server.
<:passwd>: This is the password corresponding to the user with which a connection is established to the database server.
<:conn_opt1>: This is used to specify various options related to the database server connection.
- DSN: This is the IP address of the database server with which to establish a connection.
- CONNTYPE: This is used to specify the protocol with which to communicate with the database server.
  - 1: TCP/IP
  - 2: UNIX DOMAIN
  - 3: IPC
- PORT_NO: This is used to specify the port number via which to communicate with the database server.
- NLS_USE: This is used to set the character set to use during the session.
  - KO16KSC5601: Korean
  - US7ASCII: English
  - MS949
  - BIG5
  - GB231280
  - MS936
  - UTF8
  - SHIFTJIS
  - MS932
  - EUCJP
- BATCH: This is used to specify the batch processing mode for the session.
  - ON: Batch Processing Mode
  - OFF: Non Batch Processing Mode
<:conn_opt2>: This is used to specify the same database server connection options that are specified using conn_opt1. If an attempt to connect with a database server using the options specified in conn_opt1 fails, another connection attempt will automatically be made using the options specified in conn_opt2.

Description#

One application is permitted to establish one or more connections to a database using embedded SQL statements. When multiple connections to a database are established within a single application, there can be only one connection that does not have a connection name. This chapter only describes the connection that does not have a connection name.

For more information about establishing multiple connections within a single application, please refer to Chapter 12: Applications with Multiple Database Connections. For more information about multi-threaded applications, please refer to Chapter 13: Multithreaded Applications.

* Note: If PORT_NO and NLS_USE are not specified in the connection string, then the environment variables shown below must exist, and must have the same values that are set in the corresponding properties in the altibase.properties file

export ALTIBASE_PORT_NO=20300
export ALTIBASE_NLS_USE=US7ASCII

Specifying Two Sets of Connection Options#

SQL_SUCCESS: This is returned when a connection is successfully established using the first set of options.

SQL_SUCCESS_WITH_INFO: This is returned when the connection attempt using the first set of options fails, but a connection is then successfully established using the second set of options. The error message related to the failure of the first connection attempt is stored in sqlca.sqlerrm.sqlerrmc.

SQL_ERROR: This is returned when both connection attempts fail. The error messages related to the failure of both connection attempts are consecutively stored in sqlca.sqlerrm.sqlerrmc.

Considerations#

If an attempt to establish a connection is made when a connection already exists, an error message indicating that a connection has already been established will be displayed. Therefore, if it is desired to establish a connection while a connection exists, it is first necessary to execute DISCONNECT to terminate the existing connection.

If the connection method (CONNTYPE) is set to 2 or 3 in the connection string, the DSN and PORT_NO options will be ignored even if they are set, and an attempt will be made to connect with the local database server.

Example#

Various database server connection examples are shown below.

[Example 1] This example shows how to connect to the database server by specifying only the user name and the user password. In this case, the other information that is necessary in order to establish a connection with the database server will be read from the environment variables.

< Sample Program: connect1.sc >

EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
EXEC SQL END DECLARE SECTION;

strcpy(usr, "SYS");
strcpy(pwd, "MANAGER"); 

EXEC SQL CONNECT :usr IDENTIFIED BY :pwd;

[Example 2] This example shows how to connect to the database server by specifying the connection method in the USING clause. In this case, the connection to the database server will be established using the user name and the user password stored in the usr and pwd host variables and the connection information stored in the conn_opt3 host variable. Any information that is necessary in order to establish a connection with the database server but could not be found in the conn_opt3 host variable will be read from the environment variables.

< Sample Program : connect1.sc >

EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
char conn_opt3[100];
EXEC SQL END DECLARE SECTION;

strcpy(usr, "SYS");
strcpy(pwd, "MANAGER"); 
strcpy(conn_opt3, "DSN=192.168.11.12;CONNTYPE=1;PORT_NO=53000"); 

EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :conn_opt3;

[Example 3] This example shows how to connect to the database server by specifying two different sets of connection options in the USING clause. In this case, an attempt will be made to connect to the database server using the user name and the user password stored in the usr and pwd host variables and the connection information stored in the conn_opt1 host variable. If this attempt fails, another attempt will be made to connect to the database server using the same user name and user password, but this time with the connection information stored in the conn_opt2 host variable.

< Sample Program: connect2.sc >

EXEC SQL BEGIN DECLARE SECTION;
char usr[10];
char pwd[10];
char conn_opt1[100];
char conn_opt2[100];
EXEC SQL END DECLARE SECTION;

strcpy(usr, "SYS");
strcpy(pwd, "MANAGER"); 
strcpy(conn_opt1, "DSN=192.168.11.12;CONNTYPE=1;PORT_NO=53000"); 
strcpy(conn_opt2, "DSN=192.168.11.22;CONNTYPE=1;PORT_NO=53000");

EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :conn_opt1, :conn_opt2;  
if (sqlca.sqlcode == SQL_SUCCESS) /* check sqlca.sqlcode */
{
    printf("Success connection to altibase server with first option\n\n");
}
else if (sqlca.sqlcode == SQL_SUCCESS_WITH_INFO)
{
    /* fail connection with first option and then success connection with second option */
    printf("Success connection to altibase server with second option\n");
    printf("First connection error : [%d] %s\n\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
}
else
{
    printf("Fail connection to altibase server both first option and second option\n");
    printf("Error : [%d]\n", SQLCODE);
    printf("%s\n\n", sqlca.sqlerrm.sqlerrmc);
    exit(1);
}

DISCONNECT#

This statement is used to disconnect from the database server.

Syntax#

EXEC SQL DISCONNECT;

Argument#

None

Description#

This statement disconnects from the database server and releases all resources that were allocated to the connection.

Example#

The following example shows the use of the DISCONNECT statement.

< Sample Program : connect1.sc >

EXEC SQL DISCONNECT;

Sample Programs#

connect1.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/connect1.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make connect1
$ connect1
<CONNECT 1>
------------------------------------------------------
[Connect]
------------------------------------------------------
Success connection to altibase server

------------------------------------------------------
[Disconnect]
------------------------------------------------------
Success disconnection from altibase server

connect2.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/connect2.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make connect2
$ connect2
<CONNECT 2>
------------------------------------------------------
[Connect With Two ConnOpt]
------------------------------------------------------
Fail connection to altibase server both first option and second option
Error : [-327730]
Failed first connection : Client unable to establish connection
Failed second connection : Client unable to establish connection

Using DDL and DML in Embedded SQL Statements#

The notional category "basic embedded SQL statements" essentially refers to those embedded SQL statements in which DML statements, such as SELECT, UPDATE, INSERT, and DELETE, and DDL statements, such as CREATE, DROP, and ALTER, are executed.

SELECT#

This statement is used to search one or more database tables for records that meet the specified conditions and store the returned data in host variables. The basic syntax is the same as the general SELECT statement supported in Altibase SQL, however, in order to be able to use host variables, an additional INTO clause is needed.

Syntax#

EXEC SQL SELECT [ ALL | DISTINCT ] <target_list>
INTO <host_var_list> 
FROM <table_expression> [ WHERE … ];

Argument#

<target_list> : Please refer to the SQL Reference.
<host_var_list> : This is a list of output host variables and output indicator variables.
<table_expression> : Please refer to the SQL Reference.

Description#

Unless the host variable is an array, only one record must be returned. If more than one record is returned, the value returned in the sqlca.sqlcode variable will be SQL_ERROR, and the value returned in the sqlca.sqlerrm.sqlerrmc variable will be an error message indicating that too many rows were returned. When it is expected that more than one record is to be returned, it is necessary to use an array or a cursor.

If the host variable is an array, the number of returned records must be the same as or less than the size of the array. If the number of returned records is greater than the array size, the value returned in the sqlca.sqlcode variable will be SQL_ERROR and the value returned in the sqlca.sqlerrm.sqlerrmc variable will be an error message indicating that too many rows were returned. In such cases, it is necessary either to increase the size of the array, or use a cursor.

Result#

When the host variable is not an array		When the host variable is an array
Number of returned records	Result of Execution	Number of returned records	Result of Execution
0	SQL_NO_DATA	0	SQL_NO_DATA
1	SQL_SUCCESS	Fewer than the size of the array	SQL_SUCCESS
1	SQL_SUCCESS	The same number as the size of the array	SQL_SUCCESS
More than 1	SQL_ERROR	More than the size of the array	SQL_ERROR

An sqlca.sqlcode value of SQL_NO_DATA means that zero (0) records were returned. In this case, the contents of the host variable will not have any meaning (i.e. will be a garbage value).

Limitations#

An input host variable cannot be an array.

Example:

EXEC SQL BEGIN DECLARE SECTION;
int var1;
int var2[10];
int var3[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT * INTO :var1 
FROM T1 WHERE i1 = :var3;   (X)

or          
EXEC SQL SELECT * INTO :var2 
FROM T1 WHERE i1 = :var3;   (X)

If the CAST operator is used in the GROUP BY clause to specify the host variable type, this expression cannot be used in the TARGET clause.

Example:

prepare select trunc(QTY - cast(:jstm as integer)) / cast(:unit as integer) from orders;                        (O)

prepare SELECT trunc(QTY - cast(:jstm as integer)) from orders GROUP BY trunc(QTY - cast(:jstm as integer));    (X)

If the output host variable in the INTO clause is an array of structures, only one output host variable can be used. For more information, please refer to Chapter 5: Host Variable Data Types.
When the output host variable in an INTO clause is a varchar array, it cannot be used with any other output host variables. For more information, please refer to Chapter 5: Host Variable Data Types.
An input indicator variable cannot be used in the LIMIT clause of a SELECT statement; only an input host variable can be used for this purpose. Additionally, the only input host variable data type that is supported for use with the LIMIT clause is int.

Example#

Various SELECT statement examples are shown below.

[Example 1] ] In the following example, the departments table is searched for records whose DNO column value matches the value of the s_dno host variable, and the values in the DNAME and DEP_LOCATION columns are loaded into the s_dname and s_dep_location host variables, respectively.

< Sample Program : select.sc >

EXEC SQL BEGIN DECLARE SECTION;
short s_dno;
char  s_dname[30+1];
char  s_dep_location[9+1];
EXEC SQL END DECLARE SECTION;

s_dno = 1001;
EXEC SQL SELECT DNAME, DEP_LOCATION 
INTO :s_dname, :s_dep_location
FROM DEPARTMENTS 
WHERE DNO = :s_dno;

[Example 2] The following example shows the use of a structure type host variable. In this example, the departments table is searched for records whose DNO column value matches the value of the s_dno input host variable, and the column values are stored in the corresponding elements of the s_department structure.

< Sample Program : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct department
{
    short dno; 
    char  dname[30+1];
    char  dep_location[9+1];
    int   mgr_no;
} department;
EXEC SQL END DECLARE SECTION;

< Sample Program : select.sc >

/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;

EXEC SQL BEGIN DECLARE SECTION;
short s_dno;
department s_department;
EXEC SQL END DECLARE SECTION;

s_dno = 1002;
EXEC SQL SELECT * 
INTO :s_department
FROM DEPARTMENTS 
WHERE DNO = :s_dno;

[Example 3] The following is an example of retrieving the T_LOB table and storing the INTEGER column in the sI1 host variable and the CLOB column in the file sI2FName created with the APRE_FILE_CREATE option.

< Sample Program : clobSample.sc>

EXEC SQL BEGIN DECLARE SECTION;
int          sI1;
char         sI2FName[33];
unsigned int sI2FOpt;
SQLLEN       sI2Ind;
EXEC SQL END DECLARE SECTION;

strcpy(sI2FName, aOutFileName);
sI2FOpt = APRE_FILE_CREATE;

EXEC SQL SELECT * INTO :sI1, CLOB_FILE :sI2FName OPTION :sI2FOpt INDICATOR :sI2Ind FROM T_LOB;

* Refer to Appendix A for an example of retrieving data of BLOB and CLOB type columns and saving them to a file.

INSERT#

This statement is used to insert new records into a table.

Syntax#

Please refer to the SQL Reference.

Argument#

None

Description#

Host variables and indicator variables can both be used in the VALUES clause.

Example#

Various INSERT statement examples are shown below.

[Example 1] In the following example, new records are inserted into the GOODS table.

< Sample Program: insert.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    s_gno[10+1];
char    s_gname[20+1];
char    s_goods_location[9+1];
int     s_stock;
double  s_price;
EXEC SQL END DECLARE SECTION;

strcpy(s_gno, "F111100002");
strcpy(s_gname, "XX-101");
strcpy(s_goods_location, "FD0003");
s_stock = 5000;
s_price = 9980.21;

EXEC SQL INSERT INTO GOODS 
VALUES (:s_gno, :s_gname, :s_goods_location, 
:s_stock, :s_price);

[Example 2] In the following example, a structure-type host variable is used to insert new records into the GOODS table.

< Sample Program : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;
EXEC SQL END DECLARE SECTION;

< Sample Program : insert.sc >

/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;

EXEC SQL BEGIN DECLARE SECTION;
goods   s_goods;
EXEC SQL END DECLARE SECTION;

strcpy(s_goods.gno, "F111100003");
strcpy(s_goods.gname, "XX-102");
strcpy(s_goods.goods_location, "AD0003");
s_goods.stock = 6000;
s_goods.price = 10200.96;

EXEC SQL INSERT INTO GOODS VALUES (:s_goods);

* For an example of the use of an INSERT statement to insert data from a file into a BLOB or CLOB column, please refer to Appendix A.

UPDATE#

This statement is used to find records that meet specified conditions and change the values in certain columns of those records.

Syntax#

Please refer to the SQL Reference

Arguments#

None

Description#

Both host variables and indicator variables can be used in both the SET and WHERE clauses.

Limitations#

Arrays must not be used together with non-array type variables. For example, if the host variable used in the SET clause is an array, the host variable used in the WHERE clause must also be an array.

Example:
```
EXEC SQL BEGIN DECLARE SECTION;
int var1[10]; 
int var2[10];
int var3;
EXEC SQL END DECLARE SECTION;

EXEC SQL UPDATE T1 
SET I1 = :var1, I2 = :var2 
WHERE I1 = :var3;               (X)
```

Example#

Various UPDATE statement examples are shown below.

[Example 1] In this example, records for which the value in the ENO column matches the value of the s_eno host variable are found, and the values in the DNO and EMP_JOB columns for those records are updated with the values of the s_dno and s_emp_job.arr host variables, respectively.

< Sample Program : update.sc >

EXEC SQL BEGIN DECLARE SECTION;
int      s_eno;
short    s_dno;
varchar  s_emp_job[15+1];
EXEC SQL END DECLARE SECTION;

s_eno = 2;
s_dno = 1001;
strcpy(s_emp_job.arr, "ENGINEER");
s_emp_job.len = strlen(s_emp_job.arr);

EXEC SQL UPDATE EMPLOYEES 
SET DNO      = :s_dno,
     EMP_JOB = :s_emp_job
WHERE ENO = :s_eno;

[Example 2] This example illustrates the use of a structure-type host variable in an UPDATE statement. Records for which the value in the ENO column matches the value of the s_eno host variable are found, and the values in the DNO, EMP_JOB, and JOIN_DATE columns for those records are updated with the values of s_employee.s_dno, s_employee.s_emp_job.arr, and SYSDATE, respectively.

< Sample Program : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct employee
{
      int        eno;
      char      ename[20+1];
      varchar   emp_job[15+1];
      char      emp_tel[15+1];
      short     dno;
      double    salary;
      char      sex;
      char      birth[4+1];
      char      join_date[19+1];
      char      status[1+1];
} employee;
EXEC SQL END DECLARE SECTION;

< Sample Program : update.sc >

/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;

EXEC SQL BEGIN DECLARE SECTION;
employee s_employee;
EXEC SQL END DECLARE SECTION;

s_eno = 20;
s_employee.dno = 2001;
strcpy(s_employee.emp_job.arr, "TESTER");
s_employee.emp_job.len = strlen(s_employee.emp_job.arr);

EXEC SQL UPDATE EMPLOYEES 
SET DNO        = :s_employee.dno,
     EMP_JOB   = :s_employee.emp_job,
               JOIN_DATE = SYSDATE
           WHERE ENO = :s_eno;

DELETE#

This statement is used to delete the records that satisfy the specified conditions from the corresponding table.

Syntax#

Please refer to the SQL Reference

Argument#

None

Description#

Both host variables and indicator variables can be used in the WHERE clause.

Example#

The following example shows how to delete records that satisfy the specified conditions from the employees table.

< Sample Program : delete.sc >

EXEC SQL BEGIN DECLARE SECTION;
int      s_eno;
short    s_dno;
EXEC SQL END DECLARE SECTION;

s_eno = 5;
s_dno = 1000;

EXEC SQL DELETE FROM EMPLOYEES 
           WHERE ENO > :s_eno AND 
DNO > :s_dno AND 
EMP_JOB LIKE 'P%';

Sample Programs#

select.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/select.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make select
$ ./select
<SELECT>
------------------------------------------------------
[Scalar Host Variables]                                           
------------------------------------------------------
DNO      DNAME                          DEP_LOCATION              
------------------------------------------------------
1001     RESEARCH DEVELOPMENT DEPT 1    New York 

------------------------------------------------------
[Structure Host Variables]                                        
------------------------------------------------------------------
DNO      DNAME                          DEP_LOCATION       MGR_NO 
------------------------------------------------------------------
1002     RESEARCH DEVELOPMENT DEPT 2    Sydney             13

------------------------------------------------------
[Error Case : Scalar Host Variables]                              
------------------------------------------------------
Error : [-594092] Returns too many rows

insert.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/insert.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make insert
$ ./insert
<INSERT>
------------------------------------------------------
[Scalar Host Variables]                                           
------------------------------------------------------
1 rows inserted

------------------------------------------------------
[Structure Host Variables]                                        
------------------------------------------------------
1 rows inserted

update.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/update.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make update
$ ./update
<UPDATE>
------------------------------------------------------
[Scalar Host Variables]                                           
------------------------------------------------------
1 rows updated

------------------------------------------------------
[Structure Host Variables]                                        
------------------------------------------------------
1 rows updated

delete.sc#

This sample program can be found at $ALTIBASE_HOME/sample/APRE/delete.sc.

Result of Execution#

$ is -f schema/schema.sql
$ make delete
$ ./delete
<DELETE>
------------------------------------------------------
[Scalar Host Variables]                                           
------------------------------------------------------
7 rows deleted

Using Other Embedded SQL Statements#

This category includes task control statements, DCL statements for controlling the system and individual transactions, and the INCLUDE OPTION and THREAD OPTION statements.

AUTOCOMMIT#

Syntax#

EXEC SQL AUTOCOMMIT { ON | OFF };

Argument#

None

Description#

This statement is used to change the AUTOCOMMIT mode for the current session.

Example#

The following examples illustrate how to change the AUTOCOMMIT mode.

EXEC SQL AUTOCOMMIT ON;    -- To change to AUTOCOMMIT mode
EXEC SQL AUTOCOMMIT OFF;   -- To change to Non-AUTOCOMMIT mode

COMMIT#

Syntax#

EXEC SQL COMMIT;

Argument#

None

Description#

This statement is used to indicate that the current transaction was successful and terminate it. The changes effected by the transaction will be stored in the database permanently.

Note that an error is never raised when this statement is executed, even when the autocommit mode of the current session is AUTOCOMMIT.

Example#

The following example shows how to use the COMMIT statement in an embedded SQL statement:

EXEC SQL COMMIT;

SAVEPOINT#

Syntax#

EXEC SQL SAVEPOINT <savepoint_name>;

Argument#

<savepoint_name>: This is the name to be given to the savepoint.

Description#

A savepoint is a defined point in time at which the changes made by a transaction that has not finished executing are temporarily stored. The SAVEPOINT embedded SQL statement is used to define an explicit savepoint, to which the transaction can be rolled back if necessary

Note that an error is never raised when this statement is executed, even when the autocommit mode of the current session is AUTOCOMMIT.

Example#

The following example shows how to use the SAVEPOINT statement in an embedded SQL statement:

EXEC SQL SAVEPOINT sp;

ROLLBACK#

Syntax#

EXEC SQL ROLLBACK [ TO SAVEPOINT <savepoint_name> ];

Arguments#

<savepoint_name> : This is the name of a savepoint to which to return.

Description#

This statement is used to restore the database to the state that existed prior to the commencement of execution of the current transaction, or to the state that existed when a specified savepoint was defined. That is, this statement undoes all or some of the operations that have been performed by the current transaction.

If a previously defined savepoint is specified in this statement, the current transaction will be only partially rolled back. That is, the operations performed since the savepoint was defined will be undone.

Note that an error is never raised when this statement is executed, even when the autocommit mode of the current session is AUTOCOMMIT.

Example#

The following example shows how to use the ROLLBACK statement in an embedded SQL statement:

EXEC SQL ROLLBACK;

or

EXEC SQL ROLLBACK TO SAVEPOINT sp;

BATCH#

This statement is used to change a connection property so as to activate or deactivate batch processing.

Syntax#

EXEC SQL BATCH { ON | OFF };

Argument#

None

Description#

When batch processing mode is active, the execution (i.e. transmission to the server) of embedded SQL statements is delayed until the transaction is committed or a SELECT statement is subsequently executed. Batch processing is possible because the result of uncommitted INSERT, UPDATE, and DELETE statements can only be read from within the same transaction.

Batch processing can improve performance in environments in which INSERT, UPDATE, and DELETE statements are frequently executed.

Example#

The following examples show how to use the BATCH statement in an embedded SQL statement to change the batch mode.

EXEC SQL BATCH ON;        - To activate batch processing mode
EXEC SQL BATCH OFF;       - To deactivate batch processing mode

INCLUDE#

This statement is used to specify a header file that is to be included in a precompile operation.

Syntax#

EXEC SQL INCLUDE <filename>;

Argument#

<filename> : This is the name of the header file to be included in the precompile operation.

Description#

The definitions of host variables and host variable data types are important information that APRE needs to know in order to perform the precompile operation. Therefore, header files that contain host variable type definitions or host variable declarations to be used in the application must be included using the INCLUDE statement if the -parse precompiler option will not be set to "full".

The EXEC SQL INCLUDE command can be used both in the main source file to be precompiled (i.e. the file with the .sc extension) and in any header files (.h) that are also included using the EXEC SQL INCLUDE command. Note however that this command cannot be used within header files that are included using the #include command.

Limitation#

Recursive header file inclusions are not allowed. In other words, if myheader2.h is included in myheader1.h, then myheader1.h must not also be included in myheader2.h

Example: <myheader1.h>

EXEC SQL INCLUDE myheader2.h;
…

<myheader2.h>
EXEC SQL INCLUDE myheader1.h;   (X)
…

Example#

The following example shows how to use the INSERT statement to specify the header file hostvar.h for inclusion in a precompile operation.

< Sample Program : insert.sc >

EXEC SQL INCLUDE hostvar.h;

OPTION Statements#

Various options provided by the C/C++ preprocessor can be specified using the OPTION statement.

INCLUDE#

APRE provides various methods of using embedded SQL statements to specify the location of the header files to be included in a precompile operation. One of them is the INCLUDE OPTION statement.

Syntax#

EXEC SQL OPTION (INCLUDE = <pathname>);

Argument#

<pathname> : This is the location of the header files to be included in the precompile operation.

Description#

This command is used to specify one or more locations in which to look for the header files to be included in the precompile operation. The current directory does not need to be specified. When specifying multiple locations, they must be separated by commas (","). The INCLUDE OPTION statement must precede all INCLUDE statements.

Example#

In the following example, the INCLUDE OPTION statement is used to specify the location in which to look for hostvar.h (namely, the ./include directory), after which the hostvar.h file is included using the INCLUDE statement.

< Sample Program : insert.sc >

EXEC SQL OPTION (INCLUDE=./include);
EXEC SQL INCLUDE hostvar.h;

THREADS#

APRE supports the use of embedded SQL statements in multi-threaded applications. The THREADS OPTION statement provides a basis on which the precompiler can determine whether or not the file to be precompiled is a multi-threaded program.

Syntax#

EXEC SQL OPTION (THREADS = { TRUE | FALSE });

Argument#

None

Description#

The THREADS OPTION statement can have either of the following two values:

TRUE : When the file to be precompiled is a multi-threaded program
FALSE : When the file to be precompiled is not a multi-threaded program

If the THREADS OPTION statement is not specified, APRE assumes that the file to be precompiled is not a multi-threaded program. Therefore, if the file to be precompiled is a multi-threaded program, then the THREADS OPTION must specified, and must be set to TRUE. If the -mt option is used on the command line when precompiling a multi-threaded program, the THREADS OPTION statement can be omitted.

Example#

The following example shows how to set the THREADS OPTION to TRUE using the OPTION statement when the file to be precompiled is a multi-threaded program.

< Sample Program : mt1.sc >

EXEC SQL OPTION (THREADS=TRUE);

6. Embedded SQL Statements#

Overview#

Syntax#

Limitation#

Example#

Static Versus Dynamic SQL Statements#

Host Variable Declaration Section#

Function Argument Declaration Section#

Connection-Related SQL Statements#

Basic Embedded SQL Statements#

Cursor Control SQL Statements#

SQL Statements for Controlling Stored Procedures and Functions#

Other Embedded SQL Statements#

OPTION Statements#

Connection related SQL statements#

CONNECT#

Syntax#

Arguments#

Description#

Specifying Two Sets of Connection Options#

Considerations#

Example#

DISCONNECT#

Syntax#

Argument#

Description#

Example#

Sample Programs#

connect1.sc#

Result of Execution#

connect2.sc#

Result of Execution#

Using DDL and DML in Embedded SQL Statements#

SELECT#

Syntax#

Argument#

Description#

Result#

Limitations#

Example#

INSERT#

Syntax#

Argument#

Description#

Example#

UPDATE#

Syntax#

Arguments#

Description#

Limitations#

Example#

DELETE#

Syntax#

Argument#

Description#

Example#

Sample Programs#

select.sc#

Result of Execution#

insert.sc#

Result of Execution#

update.sc#

Result of Execution#

delete.sc#

Result of Execution#

Using Other Embedded SQL Statements#

AUTOCOMMIT#

Syntax#

Argument#

Description#

Example#

COMMIT#

Syntax#

Argument#

Description#

Example#

SAVEPOINT#

Syntax#

Argument#

Description#