"ecpg(Oracle Pro*c compatible) can be run in a so-called Oracle
compatibility mode. If this mode is active, it tries to
behave as if it were Oracle Pro*C.
Specifically, this mode changes "ecpg(Oracle Pro*c compatible) in three ways:
Pad character arrays receiving character string types with trailing spaces to the specified length
Zero byte terminate these character arrays, and set the indicator variable if truncation occurs
Set the null indicator to -1 when character
arrays receive empty character string types
In Oracle compatibility mode, support get data from database to a char variable.
Oracle-compatible mode supports a structure different from that described in Section 33.7.2. As follows:
struct SQLDA
{
int N; /* Number of entries */
char **V; /* Variables */
int *L; /* Variable lengths */
short *T; /* Variable types */
short **I; /* Indicators */
int F; /* Count of variables discovered by DESCRIBE */
char **S; /* Variable names */
short *M; /* Variable name maximum lengths */
short *C; /* Variable name actual lengths */
char **X; /* Indicator names */
short *Y; /* Indicator name maximum lengths */
short *Z; /* Indicator name actual lengths */
};
The descriptions of each descriptor variable are as follows:
N variableSpecifies the maximum number of select-list items or placeholders that can be described.
Before DESCRIBE: Use sqlald() to set N to the dimension of the descriptor array.
After DESCRIBE: N is reset to the actual number of variables described, which is stored in F.
V variableA pointer to an array of addresses of data buffers for select-list or bind variable values.
For select descriptors, allocate data buffers and set this array before executing the FETCH command.
For bind descriptors, set this array before executing the OPEN command.
L variableA pointer to an array of lengths of select-list or bind variable values stored in the data buffers.
For select descriptors, DESCRIBE SELECT LIST sets the length array to the maximum expected length of each select-list item.
Before FETCH, some lengths may need to be reset to meet actual requirements.
For bind descriptors, the length array must be set before executing the OPEN command.
T variableA pointer to an array of data type codes for select-list or bind variable values. These codes determine how Oracle data is converted when stored in the data buffers pointed to by the V array elements.
For select descriptors, DESCRIBE SELECT LIST sets the data type code array to the internal data type of the select-list items.
Before FETCH, some data types may need to be reset for easier handling.
For bind descriptors, DESCRIBE BIND VARIABLES sets the data type code array to zero. Each element's data type code must be set before executing the OPEN command.
I variableA pointer to an array of addresses of data buffers storing indicator variable values.
For select descriptors, the address array must be set before executing the FETCH command. If a select-list value is NULL, the indicator variable value is set to -1.
For bind descriptors, the address array must be set before executing the OPEN command. If the indicator variable value is -1, the bind variable value is NULL.
F variable
The number of select-list items or placeholders actually found.
Set by DESCRIBE. If F is less than zero, it indicates the number of described select-list items or placeholders exceeded the allocated size of the descriptor.
S variable
A pointer to an array of addresses of data buffers storing select-list or placeholder names.
Use sqlald() to allocate data buffers and store their addresses in the S array.
M variableA pointer to an array of addresses of data buffers storing the maximum lengths of select-list or placeholder names.
C variableA pointer to an array of addresses of data buffers storing the actual lengths of select-list or placeholder names.
X variableA pointer to an array of addresses of data buffers storing indicator names.
Y variableA pointer to an array of addresses of data buffers storing the maximum lengths of indicator names.
Z variableA pointer to an array of addresses of data buffers storing the actual lengths of indicator names.
DESCRIBE BIND VARIABLES puts descriptions of placeholders into a bind descriptor. In our example, DESCRIBE readies bind_des, as follows:
EXEC SQL DESCRIBE BIND VARIABLES FOR sql_stmt INTO bind_des;
The DESCRIBE BIND VARIABLES statement must follow the PREPARE statement but precede the OPEN statement.
If the dynamic SQL statement is a query, the DESCRIBE SELECT LIST statement must follow the OPEN statement but precede the FETCH statement. DESCRIBE SELECT LIST puts descriptions of select-list items in a select descriptor. In our example, DESCRIBE readies select_des, as follows:
EXEC SQL DESCRIBE SELECT LIST FOR sql_stmt INTO select_des;
Accessing the data dictionary, DESCRIBE sets the length and datatype of each select-list value.
FETCH returns a row from the active set, stores select-list values in the data buffers, and advances the cursor to the next row in the active set. If there are no more rows, FETCH sets sqlca.sqlcode to the "no data found" error code. In our example, FETCH returns the values of columns to select_des, as follows:
EXEC SQL FETCH emp_cursor USING DESCRIPTOR select_des;
To fetch multiple rows at a time, use the FETCH statement with a specified row count. The following example demonstrates how to fetch a specified number of rows (FETCH_ROWS_COUNT) into a selection descriptor area (select_des):
EXEC SQL FOR :fetch_rows_count FETCH emp_cursor USING DESCRIPTOR select_des;
#include <stdlib.h>
#include <string.h>
#include <limits.h>
// exec sql include ../regression;
exec sql include sqlda.h;
exec sql include pgtypes_numeric.h;
exec sql whenever sqlerror stop;
/* These shouldn't be under DECLARE SECTION */
SQLDA *inp_sqlda, *outp_sqlda;
static void
dump_sqlda(SQLDA *sqlda, int row)
{
int i;
if (sqlda == NULL)
{
printf("dump_sqlda called with NULL sqlda\n");
return;
}
for (i = 0; i < sqlda->F; i++)
{
short* ptr = (short*)sqlda->I[i];
if (ptr[row] == -1)
printf("name sqlda descriptor: '%s' value NULL'\n", sqlda->S[i]);
else
{
switch (sqlda->T[i])
{
case 96:
printf("name sqlda descriptor: '%s' value '%s'\n", sqlda->S[i], sqlda->V[i] + row * sqlda->L[i]);
break;
case 3:
printf("name sqlda descriptor: '%s' value %d\n", sqlda->S[i], *(int *)(sqlda->V[i] + row * sqlda->L[i]));
break;
case 22:
printf("name sqlda descriptor: '%s' value %f\n", sqlda->S[i], *(double *)(sqlda->V[i] + row * sqlda->L[i]));
break;
case 2:
{
char *val;
val = PGTYPESnumeric_to_asc((numeric*)(sqlda->V[i] + row * sqlda->L[i]), -1);
printf("name sqlda descriptor: '%s' value NUMERIC '%s'\n", sqlda->S[i], val);
PGTYPESchar_free(val);
break;
}
default:
printf("type = %d \n", sqlda->T[i]);
}
}
}
}
int
main (void)
{
exec sql begin declare section;
char *stmt1 = "SELECT * FROM t1";
char *stmt2 = "SELECT * FROM t1 WHERE id = :bindID";
int rec;
int id;
int fetch_rows_count;
exec sql end declare section;
char msg[128];
ECPGdebug(1, stderr);
// exec sql connect to REGRESSDB1 as regress1;
exec sql connect to postgres as regress1;
exec sql drop table if exists t1;
exec sql set datestyle to iso;
exec sql create table t1(
id integer,
t text,
d1 numeric,
d2 float8,
c char(10));
exec sql insert into t1 values
(1, 'a', 1.0, 1, 'a'),
(2, null, null, null, null),
(4, 'd', 4.0, 4, 'd');
exec sql commit;
/* SQLDA test for getting all records from a table */
outp_sqlda = NULL;
outp_sqlda = sqlald(10, 20, 20);
outp_sqlda->N = 10;
exec sql prepare st_id1 from :stmt1;
exec sql declare mycur1 cursor for st_id1;
exec sql open mycur1;
exec sql whenever not found do break;
// exec sql describe st_id1 into outp_sqlda;
exec sql describe select list for st_id1 into outp_sqlda;
outp_sqlda->N = outp_sqlda->F;
for (int i = 0; i < outp_sqlda->F; i++)
{
outp_sqlda->V[i] = calloc(1, 100);
outp_sqlda->I[i] = calloc(1, 100);
}
rec = 0;
while (1)
{
exec sql fetch mycur1 using descriptor outp_sqlda;
printf("FETCH RECORD %d\n", ++rec);
dump_sqlda(outp_sqlda, 0);
}
exec sql whenever not found continue;
exec sql close mycur1;
exec sql deallocate prepare st_id1;
for (int i = 0; i < outp_sqlda->F; i++)
{
free(outp_sqlda->V[i]);
free(outp_sqlda->I[i]);
}
sqlclu(outp_sqlda);
/* SQLDA test for getting FETCH_ROWS_COUNT records from a table */
printf("FETCH_ROWS_COUNT RECORD \n");
outp_sqlda = NULL;
outp_sqlda = sqlald(10, 20, 20);
outp_sqlda->N = 10;
exec sql prepare st_id1 from :stmt1;
exec sql open mycur1;
// exec sql describe st_id1 into outp_sqlda;
exec sql describe select list for st_id1 into outp_sqlda;
outp_sqlda->N = outp_sqlda->F;
/* 注意，获取多行记录时，一定要分配足够的空间 */
for (int i = 0; i < outp_sqlda->F; i++)
{
outp_sqlda->V[i] = calloc(1, 200);
outp_sqlda->I[i] = calloc(1, 20);
}
fetch_rows_count = 3;
/* 每次提取 FETCH_ROWS_COUNT 条数据到选择描述区 */
/* sqlca.sqlerrd[2] 保存当前处理成功的总行数 */
EXEC SQL FOR :fetch_rows_count FETCH mycur1 USING DESCRIPTOR outp_sqlda;
for (int iRow = 0; iRow < sqlca.sqlerrd[2]; iRow++) /*循环行*/
{
dump_sqlda(outp_sqlda, iRow);
}
exec sql close mycur1;
exec sql deallocate prepare st_id1;
for (int i = 0; i < outp_sqlda->F; i++)
{
free(outp_sqlda->V[i]);
free(outp_sqlda->I[i]);
}
sqlclu(outp_sqlda);
/* SQLDA test for getting one record using an input descriptor */
/* Input sqlda has to be built manually */
inp_sqlda = sqlald(10, 20, 20);
outp_sqlda = sqlald(10, 20, 20);
printf("EXECUTE RECORD 4\n");
id = 4;
exec sql prepare st_id1 from :stmt2;
// exec sql declare mycur1 cursor for st_id1;
exec sql DESCRIBE BIND VARIABLES FOR st_id1 into inp_sqlda;
printf("inp_sqlda->F = %d \n", inp_sqlda->F);
printf("inp_sqlda->S[0] = %s \n", inp_sqlda->S[0]);
printf("inp_sqlda->C[0] = %d \n", inp_sqlda->C[0]);
inp_sqlda->N = inp_sqlda->F;
printf("inp_sqlda->N = %d \n", inp_sqlda->N);
inp_sqlda->V[0] = (char *)&id;
inp_sqlda->T[0] = 3; /* ECPGt_int */
inp_sqlda->L[0] = sizeof(id);
exec sql open mycur1 USING DESCRIPTOR inp_sqlda;;
exec sql whenever not found do break;
// exec sql describe st_id1 into outp_sqlda;
exec sql describe select list for st_id1 into outp_sqlda;
outp_sqlda->N = outp_sqlda->F;
for (int i = 0; i < outp_sqlda->F; i++)
{
outp_sqlda->V[i] = calloc(1, 200);
outp_sqlda->I[i] = calloc(1, 20);
}
rec = 0;
while (1)
{
exec sql fetch mycur1 using descriptor outp_sqlda;
printf("FETCH RECORD %d\n", ++rec);
dump_sqlda(outp_sqlda, 0);
}
exec sql whenever not found continue;
exec sql close mycur1;
exec sql deallocate prepare st_id1;
for (int i = 0; i < outp_sqlda->F; i++)
{
free(outp_sqlda->V[i]);
free(outp_sqlda->I[i]);
}
sqlclu(inp_sqlda);
sqlclu(outp_sqlda);
/* End test */
exec sql drop table t1;
exec sql commit;
exec sql disconnect;
return 0;
}
sqlaldAllocates storage space for the descriptor.
sqlald(unsigned int max_vars,
unsigned int max_name,
unsigned int max_ind_name)
The function receives the maximum number of variables that can be stored in the descriptor (max_vars), a flag to allocate space for an array to store variable names (max_name), and a flag to allocate space for an array to store indicator variable names (max_ind_name).
The function returns a SQLDA *, which is a pointer to the descriptor structure. If the allocation is successful, it returns this pointer; otherwise, it returns zero.
sqlclu
Frees the sqlda structure itself.
sqlnul
Clears the high bit of T[i], which stores the NULL \ NOT NULL status information of the ith query list item. This must be done before executing the OPEN or FETCH command.
sqlnul(unsigned short *value_type, unsigned short *type_code, int *null_status);
The function receives a pointer to an unsigned short integer variable that stores the data type code of the query list item (value_type), a pointer to an unsigned short integer variable that returns the data type code of the query list item (type_code), and a pointer to an integer variable that returns the NULL status of the query list item (null_status). Here, 1 indicates that the column is allowed to be NULL, and 0 indicates that the column is not allowed to be NULL.