|
  
Dynamic
ESQL
 Type
1 Dynamic ESQL
Type
2 Dynamic ESQL
Type
3 Dynamic ESQL
Type
4 Dynamic ESQL
The
Dynamic ESQL BLOB Interface
Dynamic
ESQL
You can
write an ESQL statement in two ways. The simpler and more common way
is by static embedding, which means that the SQL statement is written
as part of the source program text before precompiling. Until this point,
we have covered Static ESQL exclusively.
Although
static ESQL is extremely useful, it requires that you know the exact
syntax of the SQL statement at the time you write your program. Some
applications require the ability to compose SQL statements in response
to user input at run time. This can be done with dynamic ESQL, in which
the program composes an SQL statement as a string of characters and
passes it to the database at run time. All or part of the dynamic ESQL
statement is unknown at precompiled time; the complete statement is
constructed in memory during run time.
We categorize
dynamic ESQL statements into four different types according to whether
it is a SELECT statement and whether it has known or unknown host variables.
Each type of dynamic ESQL uses a different method to program the ESQL
statement.
| Type
|
Characteristic
|
Method
|
|
1
|
Non
query, no input host variable
|
Execute
Immediate
|
|
2
|
Non
query, known number of input host variables
|
Prepare/Execute
|
|
3
|
Query,
known number of input and output host variables
|
Cursor
|
|
4
|
Unknown
number of input or output host variables
|
SQLDA
|
Type
1 Dynamic ESQL
Type 1
dynamic ESQL is a non-SELECT statement without input host variables.
In this simple case you can use EXECUTE IMMEDIATE to process the dynamic
ESQL.
Example:
EXEC SQL BEGIN DECLARE SECTION;
varchar upd_str[100];
EXEC SQL END DECLARE SECTION;
sprintf(upd_str.arr, "UPDATE part SET qty = qty -1 WHERE ");
gets (update_condition); /* get dynamic upd condition */
strcat (upd_str.arr, update_condition); /* construct dynamic SQL */
upd_str.len = strlen(upd_str.arr);
EXEC SQL EXECUTE IMMEDIATE FROM :upd_str; /* execute it */
EXEC SQL COMMIT WORK;
Type
2 Dynamic ESQL
Type 2
dynamic ESQL is a little bit more complicated. It is a non-SELECT statement
with a known number of input host variables. If the number of input
host variables is undetermined at precompiled time, then you must use
type 4 dynamic ESQL (see the section later in this chapter). There are
four steps for using type 2 dynamic ESQL.
- Declare
all input host variables in the declare section.
- Prepare
this statement:
EXEC SQL PREPARE statement_name FROM :statement_string.
- Set
value of all input host and indicate variables.
- Execute
this statement:
EXEC SQL EXECUTE statement_name USING :input_var1, :input_var2.
Example:
EXEC SQL BEGIN DECLARE SECTION;
varchar del_str[80];
int ord_number;
EXEC SQL END DECLARE SECTION;
char del_condition[80];
/* there is an input variable :iVord, it is a place holder */
sprintf(del_str.arr, "DELETE FROM order WHERE ordid = :iVord AND ");
gets (del_condition); /* get the DYNAMIC delete condition */
strcat (del_str.arr, del_condition); /* construct dynamic SQL */
del_str.len = strlen(del_str.arr);
EXEC SQL PREPARE del_stmt FROM :del_str;
/* please note the relationship between the input host */
/* variable ord_number and placeholder iVord. */
gets (ord_number); /* set host variable value */
EXEC SQL EXECUTE del_stmt USING :ord_number;
EXEC SQL COMMIT WORK;
Type
3 Dynamic ESQL
Type 3
dynamic ESQL is a SELECT statement with a known number of input and
output host variables. If the number of either input or output host
variables is undetermined at precompiled time, it becomes type 4 dynamic
ESQL. There are seven steps for processing type 3 dynamic ESQL.
STEPS:
- Put
a build statement string inside a host variable, including one place
holder (or '?') for each input variable.
- Execute
an ESQL PREPARE statement specifying the statement name and statement
string.
EXEC SQL PREPARE statement_name FROM :statement_string;
- Execute
the ESQL DECLARE CURSOR statement specifying the cursor name and the
statement name.
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;
- Specify
a value for each input variable.
- Open
the cursor with a list of input variables.
- In
a loop, fetch the result to a list of output variables.
- Close
the cursor.
Example:
EXEC SQL BEGIN DECLARE SECTION;
varchar sel_str[100];
int ord_num, ord_date, custor_num;
EXEC SQL END DECLARE SECTION;
/* 1. Put a build statement string inside a host variable, including one */
/* placeholder for each input variable. */
gets(condition)
sprintf(sel_str.arr, "SELECT Ordid, Orddate FROM order WHERE CusId = :c \
AND %s", condition);
sel_str.len = strlen(sel_str.arr);
/* 2. Prepare the statement. */
EXEC SQL PREPARE sel_stmt FROM :sel_str;
/* 3. Declare the cursor. */
EXEC SQL DECLARE emp_cursor CURSOR FOR sel_stmt;
/* 4. Specify a value for each input variable. */
gets (custor_num);
/* 5. Open the cursor with a list of input variables. */
EXEC SQL OPEN emp_cursor USING :custor_num;
/* 6. In a loop, fetch the result to a list of output variables. */
do
{
EXEC SQL FETCH emp_cursor INTO :ord_num, :ord_date;
printf("ord_num = %d ord_date = %d\n", ord_num, ord_date);
} while (sqlca.sqlcode == SQL_SUCCESS ||
sqlca.sqlcode == SQL_SUCCESS_WITH_INFO)
/* 7. Close the cursor. */
EXEC SQL CLOSE emp_cursor;
Type
4 Dynamic ESQL
Type 4
dynamic ESQL is an SQL statement whose input or output host variables
are indeterminate at precompiled time. With this type of dynamic ESQL,
the SQLDA descriptor for host variables must be used. There are many
steps for type 4, however in certain situations some of the steps may
be skipped (for example, the number of output host variables is unknown
but the number of input host variables is known, or vice versa).
SQLDA
Descriptor
An SQLDA
descriptor is a descriptor area in which the application and DBMaker
store the number, value, length, data type, and name of each host variable
and indicate the variable value in the dynamic ESQL statement.
SQLDA
has the information for the number of host variables and description
information of these host variables. The number of host variables is
the number of input or output host variables involved in this current
SQL statement. The description information has two sets of information:
one contains the column information, and the other contains the host
variable information.
Describe
Command
Type 4
dynamic ESQL does not know the number of columns that are involved in
the SQL statement until the user inputs the statement at run time. Hence
the application does not know how many input or output host variables
are needed to pass information in and out of the database. This is why
we use a descriptor area to store host variable information in SQLDA.
After the
dynamic ESQL is prepared, the application program should use the Describe
command to ask DBMaker how many columns there are for input (DESCRIBE
BIND VARIABLE) and how many output columns there are for output (DESCRIBE
SELECT LIST) and what they are.
The Describe
command will put the number of columns (i.e., host variables) and information
of these columns into a descriptor. In a loop, the application should
check what kind of input or output columns there are, then allocate
space for the host variables.
Passing
information through SQLDA
DBMaker
supports two functions for allocating and freeing SQLDA:
If any
error occurs, error information will also be stored in SQLCA. The prototype
is:
int allocate_descriptor_storage(long maxNumber, char **descriptor_name);
Example:
If you
want to allocate an SQLDA descriptor 'desc1' with a maximum of 10, refer
to the following.
char *desc1;
long maxNumber = 10;
/* SQLCODE is macro of sqlca.code */
/* support error_handle() is a function for error handling */
allocate_descriptor_storage(maxNumber, &desc1);
if (SQLCODE == SQL_ERROR) error_handle();
Example:
If you
want to free an SQLDA descriptor 'desc1', refer to the following.
free_descriptor_storage(desc1);
if (SQLCODE == SQL_ERROR) error_handle();
SQLDA
contains host variable and column information. The column information
is set by DBMaker at description time. The host variable information
is set by the application. The indicated variable information is set
by either DBMaker or the application. The application can use the function
SetSQLDA() to set host variable information and use the function GetSQLDA()
to get column information. If any error occurs, error information will
also be stored in SQLCA. The prototype is:
int SetSQLDA(char *descriptor_name, short host_variable_number,
short option, long option_value);
int GetSQLDA(char *descriptor_name, short projection_column_number,
short option, void *option_value);
Argument
to the function are:
|
Option
|
Description
|
|
descriptor_name
|
An
SQLDA descriptor allocated by allocate_descriptor_storage().
|
|
Host
variable number
|
The
number of host variable
|
|
Projection
column number
|
The
number of project column list
|
|
Option
|
An
option symbol
|
|
Option
value
|
A
valid option value
|
Note: When
option is SQLDA_NUM_OF_HV or SQLDA_MAX_FETCH_ROWS, host_variable_number
or projection_column_number is not used.
Option
symbols are used to specify what kind information you will set/get,
including:
|
Option
|
Description
|
|
SQLDA_DATABUF
|
Specifies
that the option_value is the data buffer pointer.
|
|
SQLDA_DATABUF_LEN
|
Specifies
that the option_value is the data buffer max. length.
|
|
SQLDA_DATABUF_TYPE
|
Specifies
that the option_value is the data type of the data buffer.
|
|
SQLDA_BLOB_FLAG
|
Specifies
that the host variable will be handled by BLOB method
|
|
SQLDA_PUT_DATA_LEN
|
Specifies
that the option_value is the length of the host variable
put data.
|
|
SQLDA_INDICATOR
|
Specifies
that the option_value is the indicator value.
|
|
SQLDA_MAX_FETCH_ROWS
|
Specified
that the option_value is the maximum number of fetched
rows
|
|
SQLDA_STORE_FILE_TYPE
|
Specifies
option_value is type of store file (ESQL_STORE_FILE_CONTENT
or ESQL_STORE_FILE_NAME)
|
|
SQLDA_COLTYPE
|
Specifies
that the option_value is the column type of the host variable.
|
|
SQLDA_COLLEN
|
Specifies
that the option_value is the column length of the host
variable.
|
|
SQLDA_COLPREC
|
Specifies
that the option_value is the column precision of the host
variable.
|
|
SQLDA_COLSCALE
|
Specifies
that the option_value is the column scale of the host variable.
|
|
SQLDA_COLNULLABLE
|
Specifies
that the option_value is a column host variable that can
contain null values.
|
|
SQLDA_COLNAME
|
Specifies
that the option_value is the column name of the host variable.
|
|
SQLDA_COLNAME_LEN
|
Specifies
that the option_value is the column name length of the
host variable.
|
|
SQLDA_NUM_OF_HV
|
Specifies
that the option_value is the total number of host variables
involved in this current SQL statement.
|
The data
type of these option values is described in the following table. The
information regarding what option can be set or gotten is also listed
in the table.
|
Option
|
Option
values
|
|
SQLDA_NUM_OF_HV
|
Integer
0 ~ 252
|
|
SQLDA_MAX_FETCH_ROWS
|
Positive
integer
|
|
SQLDA_DATABUF
|
Valid
pointer
|
|
SQLDA_DATABUF_LEN
|
Positive
integer
|
|
SQLDA_DATABUF_TYPE
|
See
following `Data Type of data buffer table
|
|
SQLDA_STORE_FILE_TYPE
|
ESQL_STORE_FILE_CONTENT
ESQL_STORE_FILE_NAME
|
|
SQLDA_COLTYPE
|
See
following `Data Type of column' table
|
|
SQLDA_COLLEN
|
Positive
integer depend on value of SQLDA_COLTYPE
|
|
SQLDA_COLPREC
|
Positive
integer depend on value of SQLDA_COLTYPE
|
|
SQLDA_COLSCALE
|
Positive
integer depend on value of SQLDA_COLTYPE
|
|
SQLDA_COLNULLABLE
|
SQL_NO_NULLS
- column is not nullable
SQL_NULLABLE - column is nullable
|
|
SQLDA_COLNAME
|
Character
string
|
|
SQLDA_COLNAMELEN
|
Integer
1 ~ 18
|
|
SQLDA_INDICATOR
|
SQL_NULL_DATA
- input NULL data
SQL_DEFAULT_PARAM - input DEFAULT value
SQL_NTS - input data until null terminate
Positive integer - real length of input data
|
|
SQLDA_BLOB_FLAG
|
SQLDA_BLOB_ON
SQLDA_BLOB_OFF
|
|
SQLDA_PUT_DATA_LEN
|
Positive
integer
|
Data Type
of data buffer is described in following table. Value of SQLDA_DATABUF_TYPE
will tell DBMaker what kind data in data buffer specified by SQLDA_DATABUF.
| SQLDA_DATABUF_TYPE |
C
type of SQLDA_DATABUF |
|
SQL_C_CHAR
|
Character
pointer (printable character string)
|
|
SQL_C_LONG
|
long
|
|
SQL_C_SHORT
|
Short
|
|
SQL_C_FLOAT
|
Float
|
|
SQL_C_DOUBLE
|
Double
|
|
SQL_C_BINARY
|
Character
pointer (not printable character string)
|
|
SQL_C_DATE
|
ESQL define type `eq_date' (see esqltype.h)
|
|
SQL_C_TIME
|
ESQL
define type `eq_time' (see esqltype.h)
|
|
SQL_C_TIMESTAMP
|
ESQL
define type `eq_timestamp' (see esqltyep.h)
|
|
SQL_C_FILE
|
Character
string (value of SQLDA_DATABUF is a file name)
|
Data Type
of column is described in following table.
|
SQLDA_COLTYPE
|
Corresponding
data type of column
|
|
SQL_CHAR
|
char
|
|
SQL_VARCHAR
|
varchar
|
|
SQL_LONGVARCHAR
|
Long
varchar
|
|
SQL_BINARY
|
binary
|
|
SQL_LONGVARBINARY
|
Long
varbinary
|
|
SQL_INTEGER
|
int
|
|
SQL_SMALLINT
|
smallint
|
|
SQL_REAL
|
float
|
|
SQL_DOUBLE
|
double
|
|
SQL_DECIMAL
|
decimal
|
|
SQL_DATE
|
date
|
|
SQL_TIME
|
time
|
|
SQL_TIMESTAMP
|
timestamp
|
|
SQL_FILE
|
file
|
You can
get values of all referenced options using GetSQLDA(), but only some
options can be set by SetSQLDA().
The
following options cannot be set:
SQLDA_COLTYPE
SQLDA_COLLEN
SQLDA_COLPREC
SQLDA_COLSCALE
SQLDA_COLNULLABLE
SQLDA_COLNAME
SQLDA_COLNAME_LEN
SQLDA_NUM_OF_HV
The following
tables give a detailed information about who will set the SQLDA options
and when they are set. Remember the application first uses DESCRIBE
to ask for column information from DBMaker. DBMaker sets the column
information. The application then sets host variable information and
asks DBMaker to execute the statement.
For input
host variables:
|
Option
Symbol
|
Set
by
|
When
Set
|
|
SQLDA_NUM_OF_HV
|
DBMaker
|
during
DESCRIBE BIND VARIABLES
|
|
SQLDA_DATABUF
|
application
|
before
OPEN/EXECUTE
|
|
SQLDA_DATABUF_LEN
|
application
|
before
OPEN/EXECUTE
|
|
SQLDA_DATABUF_TYPE
|
application
|
before
OPEN/EXECUTE
|
|
SQLDA_STORE_FILE_TYPE
|
application
|
before
OPEN/EXECUTE
|
|
SQLDA_COLTYPE
|
DBMaker
|
During
DESCRIBE BIND VARIABLES
|
|
SQLDA_COLLEN
|
DBMaker
|
during
DESCRIBE BIND VARIABLES
|
|
SQLDA_COLPREC
|
DBMaker
|
during
DESCRIBE BIND VARIABLES
|
|
SQLDA_COLSCALE
|
DBMaker
|
during
DESCRIBE BIND VARIABLES
|
|
SQLDA_COLNULLABLE
|
DBMaker
|
during
DESCRIBE BIND VARIABLES
|
|
SQLDA_COLNAME
|
Not
used
|
--
|
|
SQLDA_COLNAME_LEN
|
Not
used
|
--
|
|
SQLDA_INDICATOR
|
application
|
before
OPEN/EXECUTE
|
|
SQLDA_MAX_FETCH_ROWS
|
application
|
before
open
|
|
SQLDA_BLOB_FLAG
|
Application
|
before
OPEN/EXECUTE
|
|
SQLDA_PUT_DATA_LEN
|
application
|
before
PUT BLOB
|
For output
host variables
|
Descriptor
Fields
|
Set
by
|
When
Set
|
|
SQLDA_NUM_OF_HV
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_DATABUF
|
Application
|
before
FETCH
|
|
SQLDA_DATABUF_LEN
|
Application
|
before
FETCH
|
|
SQLDA_DATABUF_TYPE
|
Application
|
before
FETCH
|
|
SQLDA_STORE_FILE_TYPE
|
Not
used
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLTYPE
|
DBMaker
|
during
DESCRIBE SELECTLIST
|
|
SQLDA_COLLEN
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLPREC
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLSCALE
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLNULLABLE
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLNAME
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_COLNAME_LEN
|
DBMaker
|
during
DESCRIBE SELECT LIST
|
|
SQLDA_INDICATOR
|
DBMaker
|
during
FETCH
|
|
SQLDA_BLOB_FLAG
|
application
|
before
FETCH
|
|
SQLDA_PUT_DATA_LEN
|
Not
used
|
--
|
By combining
this information with the detailed application steps and example (see
next section of this chapter), you should have a good understanding
about the type 4 dynamic ESQL.
Application
Steps
There are
many steps in constructing a type 4 dynamic ESQL application. Some of
the steps may be omitted if you know there are no input or output host
variables.
STEPS:
- Declare
descriptor variables.
- Allocate
SQLDA for dynamic input/output host variables by maxNumber.
- Execute
an SQL PREPARE statement specifying the statement name and statement
string.
EXEC SQL PREPARE statement_name FROM :statement_string;
- Declare
a cursor for the statement prepared in step 3.
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; // step 4 and 5 for input host
variable.
NOTE:
You only need to go through step 5 and step 6 when there are input
host variables.
- Describe
input host variables in the statement prepared in step 3 and put these
described information into the bound descriptor.
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name INTO descriptor_name;
- Set
the length of input host variables, set the data type of input host
variables, allocate storage for the value of input host variables,
set the value of input host variables, and set the value of input
indicated variables.
- Open
the cursor you declared in step 4, and specify the descriptor variables
the cursor should use.
EXEC SQL OPEN cursor_name USING DESCRIPTOR descriptor_name; // step 7 and 8 for output
host variable.
- Describe
the output column projection in the statement prepared in step3 and
put these describe information into the bound descriptor.
EXEC SQL DESCRIBE SELECT LIST FOR statement_name INTO descriptor_name;
- Set
the length of the output host variables, set the data type of output
host variables, and allocate storage for the value of output host
variables.
- Fetch
data by cursor declared in step 4 and put fetched data into data buffer
of bound descriptor.
EXEC SQL FETCH cursor_name USING descriptor_name;
- Close
cursor
EXEC SQL CLOSE cursor_name;
- Free
user allocated memory space for SQLDA (the pData field in SQLDA)
- De-allocate
descriptor.
Example:
#define maxNumber 10
#define STRING_LEN 128
EXEC SQL BEGIN DECLARE SECTION;
varchar stmt_str[128];
EXEC SQL END DECLARE SECTION;
/* 0. declare descriptor variables */
char *input_descriptor, *select_descriptor;
long nHv=0, nCol=0;
char *pColName, *pData;
long colType, colScale, colNullable;
long colLen, colNameLen, dataType, colPrec;
/* connect to database */
EXEC SQL CONNECT TO :dbname :user :password;
/* 1. allocate SQLDA for dynamic in/out host variables by maxNumber */
allocate_descriptor_storage(maxNumber, &input_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
allocate_descriptor_storage(maxNumber, &select_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
/* 2. EXEC SQL PREPARE statement_name FROM :statement_string */
gets(stmt_str.arr);
stmt_str.len = strlen(stmt_str.arr);
EXEC SQL PREPARE demo_stmt FROM :stmt_str;
/* 3. EXEC SQL DECLARE cursor_name CURSOR FOR statement_name */
EXEC SQL DECLARE demo_cursor CURSOR FOR demo_stmt;
/* 4. EXEC SQL DESCRIBLE BIND VARIABLES FOR statement_name INTO */
/* input_descriptor */
EXEC SQL DESCRIBE BIND VARIABLES FOR demo_stmt INTO input_descriptor;
GetSQLDA(input_descriptor, 0, SQLDA_NUM_OF_HV, &nHv);
if (SQLCODE == SQL_ERROR) error_handle();
printf("There are %d returned input host variables: \n\n", nHv);
/* 5. set length of input host variables, set dataType of input host */
/* variables,allocate storage for value of input host variables, set */
/* value of input host variables, set value of input indicates, set */
/* type, len, allocate buffer, value */
for (i = 1; i <= nHv; i++)
{
pData = malloc(STRING_LEN);
SetSQLDA(input_descriptor, i, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(input_descriptor, i, SQLDA_DATABUF_TYPE, SQL_C_CHAR);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(pData, 'dynamic ESQL/C example');
datalen = strlen(pData);
SetSQLDA(input_descriptor, i, SQLDA_INDICATOR, datalen);
if (SQLCODE == SQL_ERROR) error_handle();
}
/* 6. EXEC SQL OPEN cursor_name USING DESCRIPTOR input_descriptor */
EXEC SQL OPEN demo_cursor USING DESCRIPTOR input_descriptor;
/* 7. EXEC SQL DESCRIBLE SELECT LIST FOR statement_name INTO */
/* select_descriptor */
EXEC SQL DESCRIBE SELECT LIST FOR demo_stmt INTO select_descriptor;
GetSQLDA(select_descriptor, 0, SQLDA_NUM_OF_HV, &nCol);
if (SQLCODE == SQL_ERROR) goto error_label;
printf("There are %d returned columns: \n\n", nCol);
for (i = 1; i <= nCol; i++)
{
printf("column %d : \n");
GetSQLDA(select_descriptor, i, SQLDA_COLNAME_LEN, &colNameLen);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLNAME, &pColName);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLTYPE, &colType);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLLEN, &colLen);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLPREC, &colPrec);
if (SQLCODE == SQL_ERROR) error_handing(); )
GetSQLDA(select_descriptor, i, SQLDA_COLSCALE, &colScale);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLNULLABLE, &colNullable);
if (SQLCODE == SQL_ERROR) error_handle();
printf(" column name length = %ld \n", colNameLen);
printf(" column name = %s \n", pColName);
printf(" column type = %ld \n", colType);
printf(" column length = %ld \n", colLen);
printf(" column precision = %ld \n", colPrec);
printf(" column scale = %ld \n", colScale);
printf(" column nullable = %ld \n", colNullable);
}
/* 8. set length of output host variables, set dataType of output host */
/* variables, allocate storage for value of output host variable */
for (i = 1; i <= nCol; i++)
{
GetSQLDA(select_descriptor, i, SQLDA_COLTYPE, &colType);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_COLLEN, &colLen);
if (SQLCODE == SQL_ERROR) error_handle();
switch (colType)
{
case SQL_CHAR:
pData = malloc(colLen+1);
SetSQLDA(select_descriptor, i, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, i, SQLDA_DATABUF_LEN, colLen+1);
/* '+1' for null terminate */
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor,i,SQLDA_DATABUF_TYPE,SQL_C_CHAR);
if (SQLCODE == SQL_ERROR) error_handle();
break;
case SQL_INTEGER:
pData = malloc(4);
SetSQLDA(select_descriptor, i, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, i, SQLDA_DATABUF_LEN, 4);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, i, SQLDA_DATABUF_TYPE, SQL_C_LONG);
if (SQLCODE == SQL_ERROR) error_handle();
break;
}
}
/* 9. EXEC SQL FETCH cursor_name USING select_descriptor */
while (1)
{
EXEC SQL FETCH demo_cursor USING select_descriptor;
if (SQLCODE != SQL_SUCCESS && SQLCODE != SQL_SUCCESS_WITH_INFO)
break;
for (i = 1; i <= nCol; i++)
{
GetSQLDA(select_descriptor, i, SQLDA_DATABUF, &pData);
if (SQLCODE == SQL_ERROR) error_handle();
GetSQLDA(select_descriptor, i, SQLDA_DATABUF_TYPE, &dateType);
if (SQLCODE == SQL_ERROR) error_handle();
switch (dataType)
{
case SQL_C_CHAR:
printf(" %s ", pData);
break;
case SQL_C_LONG:
printf(" %ld ",*(long *)pData);
break;
}
}
} /* end of while loop */
/* 10. EXEC SQL CLOSE cursor_name */
EXEC SQL CLOSE demo_cursor;
/* 11. free user buffer */
for (i = 1; i <= nHv; i++)
{
GetSQLDA(input_descriptor, i, SQLDA_DATABUF, &pData);
if (SQLCODE == SQL_ERROR) error_handle();
free(pData);
}
for (i = 1; i <= nCol; i++)
{
GetSQLDA(select_descriptor, i, SQLDA_DATABUF, &pData);
if (SQLCODE == SQL_ERROR) error_handle();
free(pData);
}
/* 12. De_allocate descriptor */
free_descriptor_storage(input_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
free_descriptor_storage(select_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
Example:
Use SQLDA to retrieve multiple-row in a single FETCH statement
#include "testdb.h"
/*******************************************************************
* The example will show how to write dynamic ESQL program using SQLDA
* for query with unknown number of input and output host variables
*
* Table customer in database STORE is used in the following demo example.
* Schema of table customer is (cid int, lname(32), fname(32)).
******************************************************************/
#define MAX_ENTRY 10
#define STRING_LEN 128
#define CONDITION 103
#define MAX_FETCH_ROWS 10
#define NUM_OF_FETCH_ROWS 5
/*******************************************************************
* include header
*******************************************************************/
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE DBENVCA;
main()
{
/*****************************************************************
* error handling
******************************************************************/
EXEC SQL WHENEVER SQLERROR GOTO error_label;
/******************************************************************
* declare SQL host variables
******************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
varchar cuser[8], passwd[8];
varchar demoquery[64];
varchar democursor[8];
char dbname[18]; /* char type is fix length string */
int nFetchRows = NUM_OF_FETCH_ROWS;
EXEC SQL END DECLARE SECTION;
/*************************************************************************
* declare variables
*************************************************************************/
int i, rc = 0;
char fgConn = 0;
long datalen, colLen;
long nHv=0, nCol=0;
char *input_descriptor;
char *select_descriptor;
char *pData, *pColName;
int count;
/*************************************************************************
* connect to database
*************************************************************************/
strcpy(dbname, TEST_DBNAME); /* get db,user,password info */
strcpy(cuser.arr, "SYSADM");
cuser.len = strlen(cuser.arr);
strcpy(passwd.arr, "");
passwd.len = strlen(passwd.arr);
EXEC SQL CONNECT TO :dbname :cuser :passwd;
fgConn = 1;
EXEC SQL SET AUTOCOMMIT OFF;
/*************************************************************************
* step1. allocate SQLDA storage for input and output host variables
*************************************************************************/
rc = allocate_descriptor_storage(MAX_ENTRY, &input_descriptor);
if (rc < 0) goto error_label;
rc = allocate_descriptor_storage(MAX_ENTRY, &select_descriptor);
if (rc < 0) goto error_label;
/*************************************************************************
* clear all tuples of table customer
*************************************************************************/
EXEC SQL DELETE FROM customer;
/*************************************************************************
* insert data for test
*************************************************************************/
EXEC SQL INSERT INTO customer VALUES(1000, 'aaa', 'test1');
EXEC SQL INSERT INTO customer VALUES(2000, 'bbb', 'test2');
EXEC SQL INSERT INTO customer VALUES(3000, 'ccc', 'test3');
EXEC SQL INSERT INTO customer VALUES(4000, 'ddd', 'test4');
EXEC SQL INSERT INTO customer VALUES(5000, 'eee', 'test5');
EXEC SQL INSERT INTO customer VALUES(6000, 'fff', 'test6');
EXEC SQL INSERT INTO customer VALUES(7000, 'ggg', 'test7');
EXEC SQL INSERT INTO customer VALUES(8000, 'hhh', 'test8');
EXEC SQL INSERT INTO customer VALUES(9000, 'iii', 'test9');
EXEC SQL INSERT INTO customer VALUES(10000, 'jjj', 'test10');
EXEC SQL INSERT INTO customer VALUES(11000);
exec sql commit work;
/*************************************************************************
* specify the query demoquery with host variable, you can also ask
* user to input the query string from the terminal at run time
*************************************************************************/
sprintf(demoquery.arr, "%s %s",
"SELECT cid, lname, memo, memo2 FROM customer",
"WHERE cid > ?");
demoquery.len = strlen(demoquery.arr);
/*************************************************************************
* step2. prepare the query
*************************************************************************/
EXEC SQL PREPARE demo_stmt FROM :demoquery;
/*************************************************************************
* step3. declare cousor for the query
*************************************************************************/
EXEC SQL DECLARE democursor SCROLL CURSOR FOR demo_stmt;
/*************************************************************************
* step4. describle input host variables information (including number,
* type, length, ...) and put them into SQLDA input_descriptor
* for reference
*************************************************************************/
EXEC SQL DESCRIBE BIND VARIABLES FOR demo_stmt INTO input_descriptor;
rc = GetSQLDA(input_descriptor, 0, SQLDA_NUM_OF_HV, &nHv);
if (rc < 0) goto error_label;
printf("There are %d input host variables in the query \n\n", nHv);
/*************************************************************************
* step5. set data type and buffer length of input buffer , and
* allocate it. Then, set these values.
* (note: assume the input data is character string less than
* 128 bytes.)
*************************************************************************/
for (i = 1; i <= nHv; i++)
{
pData = MALLOC(STRING_LEN);
rc = SetSQLDA(input_descriptor, i, SQLDA_DATABUF, pData);
if (rc < 0) goto error_label;
rc = SetSQLDA(input_descriptor, i, SQLDA_DATABUF_TYPE, SQL_C_CHAR);
if (rc < 0) goto error_label;
sprintf(pData, "%d", CONDITION);
datalen = strlen(pData);
rc = SetSQLDA(input_descriptor, i, SQLDA_INDICATOR, datalen);
if (rc < 0) goto error_label;
}
/*************************************************************************
* step6. open cursor using data and information of
* SQLDA input_descriptor
*************************************************************************/
EXEC SQL OPEN democursor USING DESCRIPTOR input_descriptor;
/*************************************************************************
* step7. describle output host variables information
* (including projection number, column name, column type,
* column length, ....) and put them into SQLDA select_descriptor
* for reference
*************************************************************************/
EXEC SQL DESCRIBE SELECT LIST FOR demo_stmt INTO select_descriptor;
rc = GetSQLDA(select_descriptor, 0, SQLDA_NUM_OF_HV, &nCol);
if (rc < 0) goto error_label;
printf("There are %d columns returned \n\n", nCol);
printColInfo(select_descriptor);
/*************************************************************************
* step8. bind output host variables buffer information (including buffer
* type, buffer length) and allocate buffers for each output host
* variables.
*************************************************************************/
bindBufInfo(select_descriptor);
/*************************************************************************
* step9. fetch data by cursor and print out result, including
* column name and data
*************************************************************************/
for (i = 1; i <= nCol; i++)
{
rc = GetSQLDA(select_descriptor, i, SQLDA_COLNAME, &pColName);
if (rc < 0) goto error_label;
printf(" %s ", pColName);
}
printf("\n");
for (i = 1; i <= nCol; i++)
printf("========= ");
printf("\n");
do
{
EXEC SQL FETCH :nFetchRows ROWS democursor USING select_descriptor;
if (sqlca.sqlcode == SQL_NO_DATA_FOUND) break;
#if 0
EXEC SQL GET BLOB column 2 FOR demo_stmt;
EXEC SQL GET BLOB column 3 FOR demo_stmt;
#endif
/* print out result by buffer data type */
printResult(select_descriptor);
printf("\n");
} while(sqlca.sqlcode == SQL_SUCCESS ||
sqlca.sqlcode == SQL_SUCCESS_WITH_INFO);
printf("\n");
/*************************************************************************
* step10. close cursor
*************************************************************************/
EXEC SQL CLOSE democursor;
error_label:
/*************************************************************************
* print out error information
*************************************************************************/
if (sqlca.sqlcode)
{
printf("SQLSTATE: %ld \n", sqlca.sqlcode);
printf("error code: %ld \n", sqlca.sqlerrd[0]);
printf("error message: %s \n", sqlca.sqlerrmc);
}
/*************************************************************************
* step11. deallocate SQLDA storage
*************************************************************************/
for (i = 1; i <= nHv; i++)
{
rc = GetSQLDA(input_descriptor, i, SQLDA_DATABUF, &pData);
FREE(pData);
}
for (i = 1; i <= nCol; i++)
{
rc = GetSQLDA(select_descriptor, i, SQLDA_DATABUF, &pData);
FREE(pData);
}
if (input_descriptor)
{
rc = free_descriptor_storage(input_descriptor);
if (rc < 0)
{
printf("SQLSTATE: %ld \n", sqlca.sqlcode);
printf("error code: %ld \n", sqlca.sqlerrd[0]);
printf("error message: %s \n", sqlca.sqlerrmc);
}
}
if (select_descriptor)
{
rc = free_descriptor_storage(select_descriptor);
if (rc < 0)
{
printf("SQLSTATE: %ld \n", sqlca.sqlcode);
printf("error code: %ld \n", sqlca.sqlerrd[0]);
printf("error message: %s \n", sqlca.sqlerrmc);
}
}
/*************************************************************************
* disconnect from database
*************************************************************************/
EXEC SQL WHENEVER SQLERROR CONTINUE;
if (fgConn)
EXEC SQL DISCONNECT;
}
/****************************************************************************
* printColInfo() --
* print out column information from SQLDA by describle SELECT LIST
****************************************************************************/
void printColInfo(char *desc)
{
int i, rc=0;
long nCol=0;
char *pColName;
long colType, colPrec, colScale, colNullable;
long colLen, colNameLen;
rc = GetSQLDA(desc, 0, SQLDA_NUM_OF_HV, &nCol);
for (i = 1; i <= nCol; i++)
{
printf("column %ld information :\n", i);
rc = GetSQLDA(desc, i, SQLDA_COLNAME_LEN, &colNameLen);
rc = GetSQLDA(desc, i, SQLDA_COLNAME, &pColName);
if (pColName != NULL)
{
printf("column name = %s \n", pColName);
printf("column name length = %d \n", colNameLen);
}
rc = GetSQLDA(desc, i, SQLDA_COLTYPE, &colType);
switch (colType)
{
case SQL_CHAR:
printf("column type = char \n");
break;
case SQL_DECIMAL:
printf("column type = decimal \n");
break;
case SQL_INTEGER:
printf("column type = integer \n");
break;
case SQL_SMALLINT:
printf("column type = smallint \n");
break;
case SQL_FLOAT:
case SQL_REAL:
printf("column type = float \n");
break;
case SQL_DOUBLE:
printf("column type = double \n");
break;
case SQL_VARCHAR:
printf("column type = varchar \n");
break;
case SQL_DATE:
printf("column type = date \n");
break;
case SQL_TIME:
printf("column type = time \n");
break;
case SQL_TIMESTAMP:
printf("column type = timestamp \n");
break;
case SQL_LONGVARCHAR:
printf("column type = longvarchar \n");
break;
case SQL_BINARY:
printf("column type = binary \n");
break;
case SQL_LONGVARBINARY:
printf("column type = longvarbinary \n");
break;
case SQL_FILE:
printf("column type = file \n");
break;
default:
break;
}
rc = GetSQLDA(desc, i, SQLDA_COLLEN, &colLen);
printf("column length = %ld \n", colLen);
rc = GetSQLDA(desc, i, SQLDA_COLPREC, &colPrec);
printf("column precision = %ld \n", colPrec);
rc = GetSQLDA(desc, i, SQLDA_COLSCALE, &colScale);
printf("column scale = %d \n", colScale);
rc = GetSQLDA(desc, i, SQLDA_COLNULLABLE, &colNullable);
if (colNullable == 0)
printf("column is not nullable \n");
else
printf("column is nullable \n");
printf("\n");
}
}
/****************************************************************************
* bindBufInfo() --
* set output host variables buffer information (including buffer type,
* buffer length) and allocate buffers for each output host variables.
****************************************************************************/
void bindBufInfo(char *desc)
{
int i, rc=0;
char *pData;
long nCol=0, colType, dataType;
long dataLen;
int nFetch = MAX_FETCH_ROWS;
rc = SetSQLDA(desc, 0, SQLDA_MAX_FETCH_ROWS, nFetch);
rc = GetSQLDA(desc, 0, SQLDA_NUM_OF_HV, &nCol);
for (i = 1; i <= nCol; i++)
{
rc = GetSQLDA(desc, i, SQLDA_COLTYPE, &colType);
switch (colType)
{
case SQL_CHAR:
case SQL_VARCHAR:
case SQL_LONGVARCHAR:
case SQL_BINARY:
case SQL_LONGVARBINARY:
case SQL_FILE:
case SQL_DECIMAL:
case SQL_DATE:
case SQL_TIME:
case SQL_TIMESTAMP:
pData = MALLOC(STRING_LEN*nFetch);
dataLen = STRING_LEN-1;
/* for null terminate */
dataType = SQL_C_CHAR;
break;
case SQL_INTEGER:
pData = MALLOC(4*nFetch);
dataLen = 4;
dataType = SQL_C_LONG;
break;
case SQL_SMALLINT:
pData = MALLOC(2*nFetch);
dataLen = 2;
dataType = SQL_C_SHORT;
break;
case SQL_FLOAT:
case SQL_REAL:
pData = MALLOC(4*nFetch);
dataLen = 4;
dataType = SQL_C_FLOAT;
break;
case SQL_DOUBLE:
pData = MALLOC(8*nFetch);
dataLen = 8;
dataType = SQL_C_DOUBLE;
break;
default:
break;
}
rc = SetSQLDA(desc, i, SQLDA_DATABUF, pData);
rc = SetSQLDA(desc, i, SQLDA_DATABUF_LEN, dataLen);
rc = SetSQLDA(desc, i, SQLDA_DATABUF_TYPE, dataType);
}
}
/****************************************************************************
* printResult() --
* print out column data by their data type
****************************************************************************/
void printResult(char *desc)
{
int i,j, rc=0;
long nCol=0, dataType[MAX_ENTRY+1];
long *ind[MAX_ENTRY+1];
long *pind;
char *pData[MAX_ENTRY+1];
int retRows = pSQLCA->sqlerrd[3];
int dataLen[MAX_ENTRY+1];
int maxFetch;
rc = GetSQLDA(desc, 0, SQLDA_NUM_OF_HV, &nCol);
rc = GetSQLDA(desc, 0, SQLDA_MAX_FETCH_ROWS, &maxFetch);
for (i = 1; i <= nCol; i++)
{
rc = GetSQLDA(desc, i, SQLDA_INDICATOR, &ind[i]);
rc = GetSQLDA(desc, i, SQLDA_DATABUF_TYPE, &dataType[i]);
rc = GetSQLDA(desc, i, SQLDA_DATABUF_LEN, &dataLen[i]);
rc = GetSQLDA(desc, i, SQLDA_DATABUF, &pData[i]);
}
for (j = 0; j < retRows; j++)
{
for (i = 1; i <= nCol; i++)
{
if (*ind[i] == SQL_NULL_DATA)
printf("NULL ");
else
switch (dataType[i])
{
case SQL_C_CHAR:
printf(" %s ", pData[i]);
break;
case SQL_C_LONG:
printf(" %l5d ", *(long *)pData[i]);
break;
case SQL_C_SHORT:
printf(" %5d ", *(short *)pData[i]);
break;
case SQL_C_FLOAT:
printf(" %f ", *(float *)pData[i]);
break;
case SQL_C_DOUBLE:
printf(" %lf ", *(double *)pData[i]);
break;
default:
break;
}
ind[i]++;
pData[i] += dataLen[i];
}
printf("\n");
}
}
If you
want to pass the values of input host variables into SQLDA, you must
set the following options.
- Allocate
a valid data buffer for a host variable and set the input value into
the data buffer according to the data type of the buffer.
- Set
the pointer, type, length, and indicator of the data buffer.
Note:
If you do not set indicator value, DBMaker will use length of data buffer
as real input data length.
If you
want to get the values of output host variables from SQLDA, you must
set the following options.
Allocate a valid data buffer for a host variable.
Set the pointer, type, and length of the data buffer.
The
Dynamic ESQL BLOB Interface
You can
use the PUT BLOB or GET BLOB mechanisms in dynamic ESQL. In addition
to Type 4 dynamic ESQL, you can use the same BLOB mechanism as for static
ESQL to put or get a BLOB.
In ESQL
there are two BLOB styles - those stored in memory or files. We call
BLOBs stored in memory BLOB data, stored in files as file objects. We
will detail how to put/get BLOB data and file objects with type 4 dynamic
ESQL (SQLDA). In addition to steps for the original type 4 dynamic ESQL,
some additional steps must be considered for the BLOB interface. These
additional steps are detailed in the following section.
Putting
File Objects in Type 4 Dynamic ESQL
We can
put file object by two methods - store file object content or store
file object name. We put file object by storing file content into database
means that file content will be as data put into database and the external
file is no longer relative with database after putting it. We put file
object by storing file name into database means that file name will
be as data put into database and content of file object is still stored
in the external file.
We specify
type of put file object by option SQLDA_STORE_FILE_TYPE of function
SetSQLDA(). Setting option_value by ESQL_STORE_FILE_CONTENT means that
content of the file specified by SQLDA_DATABUF will be put into database.
Setting option_value by ESQL_STORE_FILE_NAME means that file name of
the file specified by SQLDA_DATABUF will be put into database. In DBMaker,
default value of SQLDA_STORE_FILE_TYPE is ESQL_STORE_FILE_CONTENT.
To put
a file object, you must set the following options into SQLDA.
- Allocate
a character data buffer for the file name (the maximum file name length
is MAX_FNAME_LEN = 79) and set the file name into the data buffer.
- Set
the pointer of the data buffer by SQLDA_DATABUF into the pointer of
the data buffer into SQLDA.
- Set
the data buffer type by SQLDA_DATABUF_TYPE with SQL_C_FILE into the
data buffer type by SQL_C_FILE into SQLDA.
- Set
file type by SQLDA_STORE_FILE_TYPE with ESQL_STORE_FILE_CONTENT or
ESQL_STORE_FILE_NAME into SQLDA.
- Set
the indicator by SQLDA_INDICATOR with real file name length into the
SQLDA indicator by real file name length into SQLDA.
The table
customer will be used in the following example. Its schema is (cid,
cname, memo).
In this
example, we will put data into the column "memo" from a file
object (mary_memo.fo).
#define maxNumber 10
EXEC SQL BEGIN DECLARE SECTION;
varchar stmt_str[128];
EXEC SQL END DECLARE SECTION;
char *input_descriptor;
char *pData;
long datalen;
allocate_descriptor_storage(maxNumber, &input_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(stmt_str.arr, "INSERT INTO customer VALUES(1, 'mary', ?)");
stmt_str.len = strlen(stmt_str.arr);
EXEC SQL PREPARE demo_stmt FROM :stmt_str;
EXEC SQL DESCRIBE BIND VARIABLES FOR demo_stmt INTO input_descriptor;
pData = malloc(MAX_FNAME_LEN);
SetSQLDA(input_descriptor, 1, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(input_descriptor, 1, SQLDA_DATABUF_TYPE, SQL_C_FILE);
if (SQLCODE == SQL_ERROR) error_handle();
/* Suppose we will put `file name' into database */
SetSQLDA(input_descriptor. 1, SQLDA_STORE_FILE_TYPE, ESQL_STORE_FILE_NAME);
If (SQLCODE == SQL_SRROR) error_handing();)
/* Suppose mary's memo data is stored in file 'mary_memo.fo' */
strcpy(pData, "mary_memo.fo");
datalen = strlen(pData);
SetSQLDA(input_descriptor, 1, SQLDA_INDICATOR, datalen);
if (SQLCODE == SQL_ERROR) error_handle();
EXEC SQL EXECUTE demo_stmt USING DESCRIPTOR input_descriptor;
/* suppose FreeSQLDA() can free SQLDA and all buffers allocated by application */
FreeSQLDA(input_descriptor);
Getting
a File Object in Type 4 Dynamic ESQL
If you
want to get a file object, you must set the following options into SQLDA.
- Allocate
a character data buffer for the file name (the maximum file name length
is MAX_FNAME_LEN = 79) and set the file name into the data buffer
(the file will store the data of the column "memo".
- Set
the pointer of the data buffer by SQLDA_DATABUF into the pointer of
the data buffer into SQLDA.
- Set
the data buffer type by SQLDA_DATABUF_TYPE with SQL_C_FILE into the
data buffer type by SQL_C_FILE into SQLDA.
- Set
the data buffer maximum length by SQLDA_DATABUF_LEN into the data
buffer maximum length into SQLDA.
In this
example, we will get data from the column "memo" into a file
object (mary_memo.fo).
#define maxNumber 10
EXEC SQL BEGIN DECLARE SECTION;
varchar stmt_str[128];
EXEC SQL END DECLARE SECTION;
char *select_descriptor;
char *pData;
long datalen;
allocate_descriptor_storage(maxNumber, &select_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(stmt_str.arr, "SELECT memo FROM customer WHERE cname = 'mary'");
stmt_str.len = strlen(stmt_str.arr);
EXEC SQL PREPARE demo_stmt FROM :stmt_str;
EXEC SQL DECLARE demo_cursor CURSOR FOR demo_stmt;
EXEC SQL OPEN demo_cursor;
EXEC SQL DESCRIBE SELECT LIST FOR demo_stmt INTO select_descriptor;
pData = malloc(MAX_FNAME_LEN);
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF_TYPE, SQL_C_FILE);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF_LEN, MAX_FNAME_LEN);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(pData, "mary_memo.fo");
EXEC SQL FETCH demo_cursor USING select_descriptor;
/* support PrintFile() can print out content of file */
printf("mary memo content - " \n);
PrintFile("mary_memo.fo");
EXEC SQL CLOSE demo_cursor;
/* support FreeSQLDA() can free SQLDA and all buffers allocated by application */
FreeSQLDA(select_descriptor);
Putting
BLOB Data in Type 4 Dynamic ESQL
If you
want to put data by BLOB data, you must set the following options into
SQLDA.
Before
EXECUTE:
- Allocate
a data buffer for the input data.
- Set
the pointer of the data buffer by SQLDA_DATABUF into the pointer
of the data buffer into SQLDA.
- Set
the data buffer type by SQLDA_DATABUF_TYPE into the data buffer
type into SQLDA.
- Set
the BLOB flag by SQLDA_BLOB_FLAG into SQLDA to specify that the
host variable will PUT BLOB into database Before "BEGIN PUT
BLOB":
- Fill
the data buffer with input data.
- Specify
the length of this time put data by SQLDA_PUT_DATA_LEN into the
PUT DATA length to put data into SQLDA.
In this
example, we will put data into the column "memo" from the
data buffer pData.
#define maxbufsize 256
#define maxNumber 10
EXEC SQL BEGIN DECLARE SECTION;
varchar stmt_str[128];
EXEC SQL END DECLARE SECTION;
char *input_descriptor;
char *pData;
long datalen;
boolean fgEnd = FALSE;
allocate_descriptor_storage(maxNumber, &input_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(stmt_str.arr, "INSERT INTO customer VALUES(1, 'mary', ?)");
stmt_str.len = strlen(stmt_str.arr);
EXEC SQL PREPARE demo_stmt FROM :stmt_str;
EXEC SQL DESCRIBE BIND VARIABLES FOR demo_stmt INTO input_descriptor;
pData = malloc(maxbufsize);
SetSQLDA(input_descriptor, 1, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(input_descriptor, 1, SQLDA_DATABUF_TYPE, SQL_C_CHAR);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(input_descriptor, 1, SQLDA_BLOB_FLAG, SQLDA_BLOB_ON);
if (SQLCODE == SQL_ERROR) error_handle();
EXEC SQL EXECUTE demo_stmt USING DESCRIPTOR input_descriptor;
EXEC SQL BEGIN PUT BLOB FOR demo_stmt;
/* support for mary's memo data is gotten through function getInData(), */
/* if no more input data will be gotten, fgEnd will be assigned to TRUE */
while(!fgEnd)
{
getInData(pData, maxbufsize, fgEnd);
datalen = strlen(pData);
SetSQLDA(input_descriptor, 1, SQLDA_PUT_DATA_LEN, datalen);
if (SQLCODE == SQL_ERROR) error_handle();
EXEC SQL PUT BLOB FOR demo_stmt;
}
EXEC SQL END PUT BLOB FOR demo_stmt;
/* support FreeSQLDA() can free SQLDA and all buffers allocated by application */
FreeSQLDA(input_descriptor);
Getting
BLOB Data in Type 4 Dynamic ESQL
If you
want to get data by BLOB data, you must set the following options into
SQLDA.
Before
Get BLOB
- Allocate
a data buffer for storing fetched data
- Set
the pointer of the data buffer by SQLDA_DATABUF into SQLDA
- Set
the data buffer type by SQLDA_DATABUF_TYPE into SQLDA
- Set
the data buffer maximum length by SQLDA_DATABUF_LEN into SQLDA
Before
FETCH:
- Allocate
a data buffer for stored fetch data.
- Set
the pointer of the data buffer into SQLDA.
- Set
the data buffer type into SQLDA.
- Set
the BLOB flag to specify that the column will get data into SQLDA.
- Set
the data buffer maximum length into SQLDA.
Before
GET BLOB:
- Specify
GET DATA length to get data into SQLDA.
In
this example, we will get data of the column "memo" into the
data buffer "pData".
#define maxbufsize 256
#define maxNumber 10
EXEC SQL BEGIN DECLARE SECTION;
varchar stmt_str[128];
EXEC SQL END DECLARE SECTION;
char *select_descriptor;
char *pData;
long datalen;
boolean fgEnd = FALSE;
allocate_descriptor_storage(maxNumber, &select_descriptor);
if (SQLCODE == SQL_ERROR) error_handle();
strcpy(stmt_str.arr, "SELECT memo FROM customer WHERE cname = 'mary');
stmt_str.len = strlen(stmt_str.arr);
EXEC SQL PREPARE demo_stmt FROM :stmt_str;
EXEC SQL DECLARE demo_cursor CURSOR FOR demo_stmt;
EXEC SQL OPEN demo_cursor;
EXEC SQL DESCRIBE SELECT LIST FOR demo_stmt INTO select_descriptor;
SetSQLDA(select_descriptor, 1, SQLDA_BLOB_FLAG, SQLDA_BLOB_ON);
if (SQLCODE == SQL_ERROR) error_handle();
EXEC SQL FETCH demo_cursor USING select_descriptor;
pData = malloc(maxbufsize);
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF, pData);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF_TYPE, SQL_C_CHAR);
if (SQLCODE == SQL_ERROR) error_handle();
SetSQLDA(select_descriptor, 1, SQLDA_DATABUF_LEN, maxbufsize);
if (SQLCODE == SQL_ERROR) error_handle();
printf("mary memo content - " \n);
do
{
EXEC SQL GET BLOB COLUMN 1 FOR demo_stmt;
If (SQLCODE ! = SQL_SUCCESS && SQLCODE != SQL_SUCCESS_WITH_INFO)
Break;
Printf(" %s ", pData);
}
)
while (SQLCODE == SQL_SUCCESS || SQLCODE == SQL_SUCCESS_WITH_INFO)
{
EXEC SQL GET BLOB COLUMN 1 FOR demo_stmt;
printf(" %s ", pData);
}
EXEC SQL CLOSE demo_cursor;
/* support FreeSQLDA() can free SQLDA and all buffers allocated by application */
FreeSQLDA(select_descriptor);
Note: If
you want to get total data length of one column before GET BLOB from
one column, you must use the following steps to get it before getting
any data from the column.
- Set SQLDA_DATABUF_LEN with 0
- GET BLOB from the column
- Get value of SQLDA_INDICATOR, the value is total data length of the
column".
|
