编程实例

下面给出在嵌入式SQL中使用大对象的例子：

例 10-1

#include <stdio.h>
EXEC SQL DEFINE BUFLEN 50;

void error()
{
	/* 对错误情况不予处理，以防止死循环*/
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/* 输出错误信息*/
	sqlprint();
	EXEC SQL ROLLBACK WORK;
	exit(1);
}
int main()
{
	FILE *fp;
	long SQLCODE;
	
	EXEC SQL BEGIN DECLARE SECTION;
	ocbloblocator *a;
	char buf[BUFLEN];
	int amt;
	int offset;
	EXEC SQL END DECLARE SECTION;

	/*连接数据库*/
	EXEC SQL WHENEVER SQLERROR DO error();
	EXEC SQL CONNECT TO osrdb@localhost USER
	sysdba USING szoscar55;

	/*初始化LOB大对象*/
	EXEC SQL LOB ALLOCATE :a;

	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/*创建表之前先删除已经存在的表*/
	EXEC SQL DROP TABLE t1;
	EXEC SQL WHENEVER SQLERROR DO error();
	
	/*创建表*/
	EXEC SQL CREATE TABLE t1(id int, comments blob);
	if(SQLCODE == 0)
	{
		printf("创建表t1(id int, comments blob)成功 ！\n");
	}

	/*插入空lob*/
	EXEC SQL INSERT INTO t1 VALUES(1,EMPTY_BLOB());
	if(SQLCODE == 0)
	{
		printf("插入空lob对象成功 ! \n");
	}

	EXEC SQL SELECT COMMENTS INTO :a FROM t1 WHERE id =1;
	/* 读文件，写入大对象 */
	fp = fopen("a.txt","r");
	amt = BUFLEN;
	while(!feof(fp))
	{
		fread((void*)buf, (size_t)BUFLEN, (size_t)1, fp);
		EXEC SQL LOB WRITE APPEND :amt FROM :buf INTO :a ;
	}

	/*get length*/
	offset = 0;
	EXEC SQL LOB DESCRIBE :a GET LENGTH INTO :amt;
	printf("大对象长度为%d字节.\n",amt);

	/* 读大对象，写入文件 \*/
	fp = fopen("b.txt","w");
	EXEC SQL WHENEVER SQLERROR DO error();
	EXEC SQL LOB READ :amt FROM :a INTO :buf;
	fwrite((void*)buf, (size_t)BUFLEN, (size_t)1, fp);
	
	EXEC SQL WHENEVER SQLERROR DO error();
	fwrite((void*)buf, (size_t)amt, (size_t)1, fp);
	fclose(fp);

	EXEC SQL DROP TABLE t1;
	EXEC SQL COMMIT;
	EXEC SQL DISCONNECT;
	return 0;
}

注解

这个例子中涉及的文件名必须和这个文件编译后生成的可执行文件在同一个目录下，否则对文件的操作将会出错。 因为这个可执行文件将无法找到相应的文件名进行操作。