Embedded SQL (DB2) and Pro*C (Oracle) allow to use C structure as hosts variable. Instead of enumerating all columns in INSERT or SELECT statement the developer can use name of the structure variable. Implementation of structure in DB2 and Oracle is similar but a little different.
Oracle code
Pro*C Oracle sample code
create table personal_data (id integer, name varchar(100));
int addPersonalData(personal_data data) {
int failed;
EXEC SQL WHENEVER SQLERROR DO sql_error("Insert personal_data",&failed);
EXEC SQL INSERT INTO personal_data (id,name)
VALUES (:data);
if (failed) return 0;
EXEC SQL INSERT INTO personal_data (id,name)
VALUES (:data.id,NULL);
if (failed) return 0;
return 1;
}
int calculatePersonalStat(int *all, int *nullno) {
int failed;
personal_data data;
struct
{
short ind_id;
short ind_name;
} data_info_ind;
EXEC SQL WHENEVER SQLERROR DO sql_error("calculate personal stat",&failed);
EXEC SQL DECLARE persdata CURSOR FOR
SELECT ID,NAME
FROM personal_data;
if (failed) return 0;
EXEC SQL OPEN persdata;
if (failed) return 0;
EXEC SQL WHENEVER NOT FOUND DO break;
*all = *nullno = 0;
for (;;)
{
EXEC SQL FETCH persdata INTO :data INDICATOR :data_info_ind;
if (failed) return 0;
(*all)++;
if (data_info_ind.ind_name != 0) (*nullno)++;
}
EXEC SQL CLOSE persdata;
return 1;
}
In this code we see usage of structure to FETCH and INSERT data into the table and also structure as INDICATOR host variable.DB2 code
Embedded SQL sample code, equivalent of Oracle code above
EXEC SQL BEGIN DECLARE SECTION;
typedef struct {
int id;
char name[100];
} personal_data_struct_db2 ;
EXEC SQL END DECLARE SECTION;
int addPersonalData(personal_data data) {
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
personal_data_struct_db2 rec;
EXEC SQL END DECLARE SECTION;
int error = 0;
memcpy(&rec,&data, sizeof(rec));
EXEC SQL INSERT INTO personal_data (id,name)
VALUES (:rec);
EVALRESULT("Insert personal rec");
if (error) return 0;
EXEC SQL INSERT INTO personal_data (id,name)
VALUES (10,NULL);
EVALRESULT("Insert personal rec with null");
if (error) return 0;
return 1;
}
int calculatePersonalStat(int *all, int *nullno) {
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
personal_data_struct_db2 data;
short data_info_ind[2];
EXEC SQL END DECLARE SECTION;
int error = 0;
// EXEC SQL WHENEVER SQLERROR DO sql_error("calculate personal stat",&failed);
EXEC SQL DECLARE persdata CURSOR FOR
SELECT ID,NAME
FROM personal_data;
EVALRESULT("Declare cursor");
if (error) return 0;
EXEC SQL OPEN persdata;
EVALRESULT("Open cursor");
if (error) return 0;
*all = *nullno = 0;
for (;;)
{
EXEC SQL FETCH persdata INTO :data INDICATOR :data_info_ind;
EVALRESULT("Fetch next");
if (error) return 0;
if (SQLCODE != 0) break;
(*all)++;
if (data_info_ind[1] != 0) (*nullno)++;
}
EXEC SQL CLOSE persdata;
return 1;
}
The DB2 code is similar but there are differences:- Structure declaration must be enclosed in EXEC SQL BEGIN DECLARE and EXEC SQL END DECLARE SECTION. Unfortunately it requires duplicating of the structure definiton.
- Also structure variable should be declared in similar way. Pay attention too memcpy invocation.
- Also implementation of INDICATOR host variable is different. Istead of structure a table should be used
Conclusion
Despite these differences migration is simple and straightforward. The code should be modifed but no changes in application logic are necessary.
