嵌入式SQL语言imp(7)

free_da(sqldaPointer); return SQLCODE; } /* end if */

sqlda_d = sqldaPointer->sd_sqld ; if ( sqlda_d > 0 ) {

/* 为存放列数据的sd_column结构申请空间 */ if (alloc_host_vars( sqldaPointer ) == -1) {free_da(sqldaPointer); return -1; }

/*声明游标*/

EXEC SQL DECLARE pcurs CURSOR FOR statement1 ; /打开游标*/

EXEC SQL OPEN pcurs ; if (SQLCODE < 0) return SQLCODE;

/*取一行数据到SQLDA结构*/

EXEC SQL FETCH pcurs INTO DESCRIPTOR sqldaPointer; if (SQLCODE < 0) { EXEC SQL CLOSE pcurs ; return SQLCODE; }

/*显示列标题 */ colnamelist[0] = 0;

for ( idx=0; idx< sqlda_d; idx++) { strcat(colnamelist, readColName(sqldaPointer, idx, buffer)); if (idx < sqlda_d -1) strcat(colnamelist, \ }

/* 显示行数据*/

while ( SQLCODE == 0 ) { counter++ ;

for ( idx=0; idx< sqlda_d; idx++) printf(\ EXEC SQL FETCH pcurs INTO DESCRIPTOR sqldaPointer ; } /* endwhile */ /*关闭游标*/

EXEC SQL CLOSE pcurs ; EXEC SQL DEALLOCATE CURSOR pcurs; /* 释放为SQLDA申请的空间 */ free_da( sqldaPointer ) ;

} else { /* 不是SELECT语句*/

EXEC SQL EXECUTE statement1 ;

31

free_da( sqldaPointer ) ; if (SQLCODE < 0) return SQLCODE; } /* end if */ return( 0 ) ;

} /* end of program : ADHOC.CP */

/******************************************************************************* PROCEDURE : init_da

*为SQLDA分配空间。使用SQLDASIZE 获得SQLDA的大小。如果返回-1，则表示分配 *空间不成功。

******************************************************************************/ int init_da (SQLDA **DAPointer, int DAsqln) { int idx;

*DAPointer = (SQLDA *)malloc(SYB_SQLDA_SIZE(DAsqln)); if (*DAPointer == NULL) return (-1);

memset (*DAPointer, '\\0', SYB_SQLDA_SIZE(DAsqln)); (*DAPointer)->sd_sqln = DAsqln; (*DAPointer)->sd_sqld = 0; return 0; }

/******************************************************************************* FUNCTION : alloc_host_vars

*为存放列数据的sd_column结构申请空间。如果返回-1，则表示不能获得足够内存。

******************************************************************************/ int alloc_host_vars (SQLDA *sqldaPointer) { short idx;

for (idx = 0; idx < sqldaPointer->sd_sqld; idx++) {

switch (sqldaPointer->sd_column[idx].sd_datafmt.datatype ) { case CS_CHAR_TYPE: case CS_VARCHAR_TYPE: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (sqldaPointer->sd_column[idx].sd_sqllen + 1 ); sqldaPointer->sd_column[idx].sd_sqllen ++; sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; case CS_TINYINT_TYPE: case CS_SMALLINT_TYPE: case CS_INT_TYPE: case CS_VOID_TYPE:

32

case CS_USHORT_TYPE: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (MAX_CHAR_FOR_LONG); sqldaPointer->sd_column[idx].sd_sqllen = MAX_CHAR_FOR_LONG; sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; case CS_REAL_TYPE: case CS_FLOAT_TYPE: case CS_BIT_TYPE: case CS_MONEY_TYPE: case CS_MONEY4_TYPE: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (MAX_CHAR_FOR_DOUBLE); sqldaPointer->sd_column[idx].sd_sqllen = MAX_CHAR_FOR_DOUBLE; sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; case CS_DATETIME_TYPE: case CS_DATETIME4_TYPE: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (MAX_CHAR_FOR_DATETIME); sqldaPointer->sd_column[idx].sd_sqllen = MAX_CHAR_FOR_DATETIME; sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; case CS_NUMERIC_TYPE: case CS_DECIMAL_TYPE: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (sqldaPointer->sd_column[idx].sd_datafmt.precision + 3 ); sqldaPointer->sd_column[idx].sd_sqllen = sqldaPointer->sd_column[idx].sd_datafmt.precision + 3; sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; default: sqldaPointer->sd_column[idx].sd_datafmt.datatype = CS_CHAR_TYPE; sqldaPointer->sd_column[idx].sd_sqldata = (char *) malloc (MAX_CHAR_FOR_DEFAULT); sqldaPointer->sd_column[idx].sd_sqllen = MAX_CHAR_FOR_DEFAULT;

33

sqldaPointer->sd_column[idx].sd_datafmt.format = CS_FMT_NULLTERM; break; } /* endswitch */

if (sqldaPointer->sd_column[idx].sd_sqldata == NULL) { return (-1); }

} /* endfor */ return 0; }

/******************************************************************************* FUNCTION : free_da

* 释放SQLDA申请的空间。

******************************************************************************/ void free_da (SQLDA *sqldaPointer) { short idx;

for (idx = 0; idx < sqldaPointer->sd_sqld; idx++) { free (sqldaPointer->sd_column[idx].sd_sqldata); } /* endfor */

free (sqldaPointer); }

/******************************************************************************* PROCEDURE : readColName * 返回列名

******************************************************************************/ char * readColName (SQLDA *sqldaPointer, short sd_columnIndex, char * buffer) { strcpy(buffer, sqldaPointer->sd_column[sd_columnIndex].sd_datafmt.name); return buffer; }

/******************************************************************************* PROCEDURE : readCol * 返回列数据。

******************************************************************************/ char * readCol (SQLDA *sqldaPointer, short sd_columnIndex, char * buffer){ short numBytes;

short idx, ind ; /* Array idx variables */ /* Variables for decoding packed decimal data */ char tmpstr[1024]; short collen; char *dataptr;

/* 检查是否为NULL */

if ( sqldaPointer->sd_column[sd_columnIndex].sd_sqlind ) { buffer[0] = 0; return buffer; }

34

/*返回列数据到buffer变量*/

strcpy( buffer, (char *) sqldaPointer->sd_column[ sd_columnIndex ].sd_sqldata); return buffer; }

/* COMMENT OUT OFF */

第三节 IBM DB2嵌入SQL语言

DB2支持SQL嵌入到C/C++、JAVA、COBOL、FORTRAN和REXX等语言。本节以SQL嵌入C/C++为例子，讲解静态的嵌入SQL编程和动态的嵌入SQL编程。

静态SQL嵌入C语言编程是指，应用程序在书写时，每个SQL语句的大部分都已确定下来（如：查询的表、列和语句的格式等），唯一不确定的是查询语句中某些特定变量的值，这些值可以在执行时由变量传进去，但是，值的类型要事先确定。

3.1 一个简单示例

首先，我们来看一个嵌入静态SQL语句的C程序。

例1、连接到SAMPLE数据库，查询LASTNAME为JOHNSON的FIRSTNAME信息。 #include #include #include #include \#include

EXEC SQL INCLUDE SQLCA; （1）

#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1; int check_error (char eString[], struct sqlca *caPointer) { char eBuffer[1024]; char sBuffer[1024]; short rc, Erc;

if (caPointer->sqlcode != 0) { printf (\

printf (\ caPointer->sqlcode); }

return 0; }

int main(int argc, char *argv[]) {

EXEC SQL BEGIN DECLARE SECTION; （2） char firstname[13]; char userid[9]; char passwd[19];

EXEC SQL END DECLARE SECTION;

35