 | Documentation |
|---|---|
28.5. Embedded SQL CommandsThe following sections explain the various types of embedded SQL statements that can be used with ECPG. 28.5.1. Connecting to the Database ServerThe user can connect to the underlying database using the following command: EXEC SQL CONNECT TO target [AS connection-name] [USER user-name]; The target can be specified in the following ways:
The following are the variations that can be used:
The port used with EnterpriseDB is 5444. If you specify the connection target literally (that is, not through a variable reference) and you don't quote the value, then the case-insensitivity rules of normal SQL are applied. In that case you can also double-quote the individual parameters separately as needed. In practice, it is probably less error-prone to use a (single-quoted) string literal or a variable reference. The connection target DEFAULT initiates a connection to the default database under the default user name. No separate user name or connection name may be specified in that case. There are also different ways to specify the user name:
As above, the parameters username and password may be an SQL identifier, an SQL string literal, or a reference to a character variable. The connection-name is used to handle multiple connections in one program. It can be omitted if a program uses only one connection. The most recently opened connection becomes the current connection, which is used by default when an SQL statement is to be executed. 28.5.2. Closing a ConnectionTo close a connection, use the following statement: EXEC SQL DISCONNECT [connection];
The connection can be specified in the following ways:
If no connection name is specified, the current connection is closed. It is a good practice that to always explicitly disconnect from every connection that has been opened. 28.5.3. Running SQL CommandsAny SQL command can be run from within an embedded SQL application. Below are some examples of how to do that: 28.5.3.1. Creating a TableEXEC SQL CREATE TABLE foo (a INTEGER, b CHAR(16)); EXEC SQL CREATE UNIQUE INDEX foo_idx ON foo(a); EXEC SQL COMMIT; 28.5.3.2. Inserting RowsEXEC SQL INSERT INTO foo (a, b) VALUES (9999, 'doodad'); EXEC SQL COMMIT; 28.5.3.3. Deleting RowsEXEC SQL DELETE FROM foo WHERE number = 9999; EXEC SQL COMMIT; 28.5.3.4. Single-Row SelectEXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE b = 'doodad'; 28.5.3.5. Select Using CursorsEXEC SQL DECLARE foo_bar CURSOR FOR
SELECT a, b FROM foo
ORDER BY b;
EXEC SQL OPEN foo_bar;
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;The tokens of the form ": something" are "host variables", that is, they refer to variables in the C program. Note The embedded SQL interface also supports autocommit of transactions (similar to libpq behavior) via the -t command-line option to ecpg or via the EXEC SQL SET AUTOCOMMIT TO ON statement. In autocommit mode, each command is automatically committed unless it is inside an explicit transaction block. This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO OFF 28.5.4. Choosing a ConnectionThe SQL statements shown in the previous section are executed on the current connection, that is, the most recently opened one. If an application needs to manage multiple connections, then there are two ways to handle this. The first option is to explicitly choose a connection for each SQL statement, for example: EXEC SQL AT connection-name SELECT ...;
This option is particularly suitable if the application needs to use several connections in mixed order. If the application uses multiple threads of execution, then they cannot share a connection concurrently. The user must either explicitly control access to the connection (using mutexes) or use a separate connection for each thread. If each thread uses its own connection, then the user will need to use the "AT clause" in order to specify which connection the thread should use. The second option is to execute a statement to switch the current connection. That is: EXEC SQL SET CONNECTION connection-name;
This option is particularly convenient if many statements are to be executed on the same connection. However, this option is not thread-aware. 28.5.5. Using Host VariablesThis section explains in detail how the user can pass data between a C program and the embedded SQL statements using a simple mechanism called host variables. 28.5.5.1. OverviewPassing data between the C program and the SQL statements is particularly simple in embedded SQL. Instead of having the program paste the data into the statement, which entails various complications, such as properly quoting the value, the user can simply write the name of a C variable into the SQL statement, prefixed by a colon. For example: EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);
The above statement refers to two C variables named v1 and v2 and also uses a regular SQL string literal, to illustrate that the user is not restricted to use one kind of data or the other. This style of inserting C variables in SQL statements works anywhere a value expression is expected in an SQL statement. In the SQL environment, the references to C variables are known as the host variables. 28.5.5.2. Declare SectionsTo pass data from the program to the database, for example, in the form of parameters to a query, or to pass data from the database back to the program, the C variables that are intended to contain this data need to be declared in specially marked sections, so that the embedded SQL preprocessor is made aware of them. The section starts with the following command: EXEC SQL BEGIN DECLARE SECTION;
and ends with the command given below: EXEC SQL END DECLARE SECTION;
Between these lines, there must be normal C variable declarations as shown below: int x;
char foo[16], bar[16];
The user can have as many declare sections in a program as needed. The declarations are also echoed to the output file like normal C variables, thus, there is no need to declare them again. Variables that are not intended to be used in SQL commands can be declared outside these special sections. The definition of a structure or union must also be listed inside a DECLARE section. Otherwise the preprocessor cannot handle these types because then it does not know of the definition. The special data type called "VARCHAR" is converted into a named struct for every variable. For example, consider the following declaration: VARCHAR var[180];
The above declaration is converted into the following: struct varchar_var { int len; char arr[180]; } var;
This structure is suitable for interfacing with SQL datums of type varchar. 28.5.5.3. SELECT INTO and FETCH INTONow the user should be able to pass data generated by the program into a SQL command. For retrieving the results, embedded SQL provides special variants of the usual commands SELECT and FETCH. These commands have a special INTO clause that specifies which host variables the retrieved values are to be stored in. The following example illustrates the above concept: /* * assume this table: * CREATE TABLE test1 (a int, b varchar(50)); */ EXEC SQL BEGIN DECLARE SECTION; int v1; VARCHAR v2; EXEC SQL END DECLARE SECTION; - ... EXEC SQL SELECT a, b INTO :v1, :v2 FROM test; So the INTO clause appears between the select list and the FROM clause. The number of elements in the select list and the list after INTO (also called the target list) must be equal. The following is an example using the FETCH command: EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
...
do {
...
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
...
} while (...);
Here the INTO clause appears after all the normal clauses. Both of these methods only allow the retrieval of one row at a time. If the user needs to process result sets that potentially contain more than one row, then the user is required to use a cursor, as shown in the second example above. 28.5.5.4. IndicatorsThe examples above do not handle null values. In fact, the retrieval examples will raise an error if they fetch a null value from the database. To be able to pass null values to the database or retrieve null values from the database, the user needs to append a second host variable specification to each host variable that contains data. This second host variable is called the indicator and contains a flag that tells whether the datum is null, in which case the value of the real host variable is ignored. Here is an example that handles the retrieval of null values correctly: EXEC SQL BEGIN DECLARE SECTION; VARCHAR val; int val_ind; EXEC SQL END DECLARE SECTION: ... EXEC SQL SELECT b INTO :val :val_ind FROM test1; The indicator variable val_ind will be zero if the value passed was not null, and it will be negative if the value passed was null. The indicator has another function and, that is, if the indicator value is positive, then it means that the value is not null. Actually, it was truncated when it was being stored in the host variable. 28.5.6. Using SQL Descriptor AreasAn SQL descriptor area is a more sophisticated method for processing the result of a SELECT or FETCH statement. An SQL descriptor area groups the data of one row of data together with metadata items into one data structure. The metadata is particularly useful when executing dynamic SQL statements, where the nature of the result columns may not be known ahead of time. An SQL descriptor area consists of a header, which contains information concerning the entire descriptor, and one or more item descriptor areas, which basically each describe one column in the result row. Before you can use an SQL descriptor area, you need to allocate one: EXEC SQL ALLOCATE DESCRIPTOR identifier;
The identifier serves as the "variable name" of the descriptor area. When you don't need the descriptor anymore, you should deallocate it: EXEC SQL DEALLOCATE DESCRIPTOR identifier;
To use a descriptor area, specify it as the storage target in an INTO clause, instead of listing host variables: EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
The descriptor area is like a structure with named fields. To retrieve the value of a field from the header and store it into a host variable, the following command is used: EXEC SQL GET DESCRIPTOR name :hostvar = field;
Currently, there is only one header field defined: COUNT, which tells how many item descriptor areas exist (that is, how many columns are contained in the result). The host variable needs to be of an integer type. To get a field from the item descriptor area, use the following command: EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;
num can be a literal integer or a host variable containing an integer. Possible fields are:
28.5.7. Usage and ExamplesEmbedded SQL programs are typically named with an extension .pgc. If you have a program file called prog1.pgc, you can preprocess it by simply calling: ecpg prog1.pgc
This will create a file called prog1.c where prog1.pgc is located. If your input files do not follow the suggested naming pattern, you can specify the output file explicitly using the -o option. The following example demonstrates the usage of EnterpriseDB ECPG interface library: The following piece of code shows how to create a procedure that uses EnterpriseDB sample tables: #include<stdlib.h>
EXEC SQL WHENEVER SQLERROR SQLPRINT;
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
//
// log file
//
FILE *dbgs;
if ((dbgs = fopen("log", "w")) != NULL)
ECPGdebug(1, dbgs);
//
// Connect to DB Server
//
EXEC SQL CONNECT TO edb USER enterprisedb/edb;
//
// Create a procedure
//
EXEC SQL BEGIN TRANSACTION;
EXEC SQL CREATE OR REPLACE PROCEDURE emp_query (p_deptno IN NUMBER,p_empno IN OUT NUMBER,p_ename IN OUT VARCHAR2,
p_job OUT VARCHAR2,p_sal OUT NUMBER)
IS
BEGIN
SELECT empno, ename, job, sal INTO p_empno, p_ename, p_job, p_sal FROM emp
WHERE deptno = p_deptno AND (empno = p_empno OR ename = UPPER(p_ename));
END;
EXEC SQL COMMIT;
if (sqlca.sqlcode == 0)
{
printf("Procedure Successfully created\n");
}
//
// Disconnect from DB Server
//
EXEC SQL DISCONNECT;
if( dbgs != NULL )
fclose(dbgs);
return EXIT_SUCCESS;
}
The following piece of code shows how to call the above procedure: #include<stdlib.h>
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char * stmt2 = " { CALL emp_query(?,?,?,?,?,?) }" ;
Oid paramTypes[7];
int paramDirection[7];
int val1 = 30;
int val2 = 7900;
char val3[] =" ";
char val2output[] = "AAA";
EXEC SQL END DECLARE SECTION;
paramTypes[0] = 1700;
paramTypes[1] = 1700;
paramTypes[2] = 1043;
paramTypes[3] = 1043;
paramTypes[4] = 1700;
paramTypes[5] = 0;
paramDirection[0] = 1;
paramDirection[1] = 3;
paramDirection[2] = 3;
paramDirection[3] = 2;
paramDirection[4] = 2;
paramDirection[5] = 0;
EXEC SQL ALLOCATE DESCRIPTOR indesc;
EXEC SQL ALLOCATE DESCRIPTOR outdesc;
EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = :val1;
EXEC SQL SET DESCRIPTOR indesc VALUE 2 DATA = :val2;
EXEC SQL SET DESCRIPTOR indesc VALUE 3 DATA = :val3;
//
// Connect to DB Server
//
EXEC SQL CONNECT TO edb USER enterprisedb/edb;
EXEC SQL PREPAREOUT foo2 FROM :stmt2 WITH paramType :paramTypes AND paramDirection :paramDirection;
EXEC SQL EXECUTE foo2 USING DESCRIPTOR indesc INTO DESCRIPTOR outdesc;
EXEC SQL GET DESCRIPTOR outdesc VALUE 1 :val2output = DATA;
printf("\n emp no = %s\n", val2output);
EXEC SQL GET DESCRIPTOR outdesc VALUE 2 :val2output = DATA;
printf("\n ename = %s\n", val2output);
EXEC SQL GET DESCRIPTOR outdesc VALUE 3 :val2output = DATA;
printf("\n job = %s\n", val2output);
EXEC SQL GET DESCRIPTOR outdesc VALUE 4 :val2output = DATA;
printf("\n sal = %s\n", val2output);
EXEC SQL COMMIT;
EXEC SQL PREPARED FREE;
EXEC SQL DEALLOCATE DESCRIPTOR indesc;
EXEC SQL DEALLOCATE DESCRIPTOR outdesc;
//
// Disconnect from DB Server
//
EXEC SQL DISCONNECT;
return EXIT_SUCCESS;
}28.5.7.1. OutputThe following is the output that is obtained after executing the above program: Procedure successfully created
emp no = 7900
ename = JAMES
job = CLERK
sal = 950.00
28.5.7.2. Example Using a FunctionThe following example shows how to create a function that uses the EnterpriseDB sample tables: EXEC SQL WHENEVER SQLERROR SQLPRINT;
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
FILE *dbgs;
if ((dbgs = fopen("log", "w")) != NULL)
ECPGdebug(1, dbgs);
//
// Connect to DB Server
//
EXEC SQL CONNECT TO edb USER enterprisedb/edb;
//
// Create a procedure
//
EXEC SQL BEGIN TRANSACTION;
EXEC SQL CREATE or Replace FUNCTION total_sal_by_dept(p_deptno IN Number) RETURN NUMBER
IS
v_total NUMBER :=0;
CURSOR emp_check IS
SELECT e.sal FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.deptno = p_deptno;
BEGIN
FOR i in emp_check
LOOP
v_total := v_total+i.sal;
END LOOP;
RETURN v_total;
END;
EXEC SQL COMMIT;
printf("Function successfully created\n");
//
// Disconnect from DB Server
//
EXEC SQL DISCONNECT;
if( dbgs != NULL )
fclose(dbgs);
return 0;
}The following piece of code shows how to call the above function: EXEC SQL WHENEVER SQLERROR SQLPRINT;
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char * stmt2 = "{? = CALL total_sal_by_dept(?) }" ;
Oid paramTypes[2];
int paramDirection[2];
int val1 = 10;
char val2output[] = "AAA";
int val1output = 2, val2i = 0, val10 = 0;
char str[100];
EXEC SQL END DECLARE SECTION;
paramTypes[0] = 1700;
paramTypes[1] = 0;
paramDirection[0] = 1;
paramDirection[1] = 0;
EXEC SQL ALLOCATE DESCRIPTOR indesc;
EXEC SQL ALLOCATE DESCRIPTOR outdesc;
EXEC SQL SET DESCRIPTOR indesc VALUE 1 DATA = :val1;
//
// Connection created
//
EXEC SQL CONNECT TO edb USER enterprisedb/edb;
EXEC SQL PREPAREOUT foo2 FROM :stmt2 WITH paramType :paramTypes AND paramDirection :paramDirection;
EXEC SQL EXECUTE foo2 USING DESCRIPTOR indesc INTO DESCRIPTOR outdesc;
EXEC SQL GET DESCRIPTOR outdesc VALUE 1 :val2output = DATA;
printf("\n total_sal for dept 10 = %s\n", val2output);
EXEC SQL COMMIT;
EXEC SQL PREPARED FREE;
//
// Disconnect from DB Server
//
EXEC SQL DISCONNECT;
EXEC SQL DEALLOCATE DESCRIPTOR indesc;
EXEC SQL DEALLOCATE DESCRIPTOR outdesc;
return 0;
}28.5.8. Running Programs Using ECPGIn order to run TEST.pgc, follow the following three steps:
28.5.9. Error Handling Using ECPG28.5.9.1. Setting CallbacksOne simple method to catch errors and warnings is to set a specific action to be executed whenever a particular condition occurs. In general: EXEC SQL WHENEVER condition action;
condition can be one of the following:
action can be one of the following:
The SQL standard only provides for the actions CONTINUE and GOTO (and GO TO). Here is an example that you might want to use in a simple program. It prints a simple message when a warning occurs and aborts the program when an error happens. EXEC SQL WHENEVER SQLWARNING SQLPRINT; EXEC SQL WHENEVER SQLERROR STOP;
The statement EXEC SQL WHENEVER is a directive of the SQL preprocessor, not a C statement. The error or warning actions that it sets apply to all embedded SQL statements that appear below the point where the handler is set, unless a different action was set for the same condition between the first EXEC SQL WHENEVER and the SQL statement causing the condition, regardless of the flow of control in the C program. So neither of the following two C program excerpts will have the desired effect. /*
* WRONG
*/
int main(int argc, char *argv[])
{
...
if (verbose) {
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
}
...
EXEC SQL SELECT ...;
...
}
/*
* WRONG
*/
int main(int argc, char *argv[])
{
...
set_error_handler();
...
EXEC SQL SELECT ...;
...
}
static void set_error_handler(void)
{
EXEC SQL WHENEVER SQLERROR STOP;
}
To be able to detect errors from EnterpriseDB server the user can include the following line of code in the include section of the file: exec sql include sqlca;
This will define a struct and a variable with the name sqlca as follows: struct sqlca
{
char sqlcaid[8];
long sqlabc;
long sqlcode;
struct
{
int sqlerrml;
char sqlerrmc[70];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
/* 0: empty */
/* 1: OID of processed tuple if applicable */
/* 2: number of rows processed in an INSERT, UPDATE */
/* or DELETE statement */
/* 3: empty */
/* 4: empty */
/* 5: empty */
char sqlwarn[8];
/* 0: set to 'W' if at least one other is 'W' */
/* 1: if 'W' at least one character string */
/* value was truncated when it was */
/* stored in a host variable. */
/* 2: empty */
/* 3: empty */
/* 4: empty */
/* 5: empty */
/* 6: empty */
/* 7: empty */
char sqlext[8];
} sqlca;
If an error occurred in the last SQL statement then sqlca.sqlcode will be non-zero. If sqlca.sqlcode is less that 0 then this is a serious error, for example, the database definition does not match the query given. If it is bigger than 0 then it is a normal error, for example, the table did not contain the requested row. sqlca.sqlerrm.sqlerrmc will contain a string that describes the error. The string ends with the line number in the source file. The following is the list of the various types of errors that a user can come across:
|