SQLite中如何用api操作BLOB类型的字段



在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储，如图片或者音乐等等。对于这些二进制数据（blob字段）我们不能像处理普通的文本那样简单的插入或者查询，为此SQLite提供了一组函数来处理这种BLOB字段类型。下面的代码演示了如何使用这些API函数。

首先创建一个数据库，然后创建一个数据表：

	nsqSt = sqlite3_exec( handle, "CREATE TABLE IF NOT EXISTS  PARAMETERS ( FULLNAME TEXT PRIMARY KEY,  VT INTEGER, LENGTH INTEGER, VALUE BLOB, USEFUNC INTEGER  )",
			NULL, NULL, NULL);

insertParaWithValue演示了插入blob数据

dbHandle是调用这个函数就可以获得 sqlite3_open_v2( DM_DB_FILENAME, &dbHandle,SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL );

int insertParaWithValue(DBHANDLE dbHandle, const char* pszParaName, int type, const void* pData, int cbData  )
{
	const char* pTmp = "INSERT INTO PARAMETERS( FULLNAME, VT, LENGTH, VALUE, USEFUNC) VALUES('%s', %d, %d, ?, 0)";
	size_t nTmp = strlen( pTmp );
	char* pSql = DMMalloc( nTmp+strlen(pszParaName)+11+11+1);  /*32bit decimal int max length is 11*/
	if(pSql==NULL)
		return ERROR;
	sprintf( pSql, pTmp,pszParaName, type,cbData);
	
	sqlite3_stmt* pstmt=NULL;
	int nRet=sqlite3_prepare_v2(dbHandle, pSql, -1, &pstmt,  NULL );
	if(nRet!=SQLITE_OK)
	{
		goto ERR;
	}
	DMFree(pSql);
	pSql=NULL;
	
	nRet=sqlite3_bind_blob(pstmt,1,pData,cbData, NULL );
	if(nRet!=SQLITE_OK)
	{
		goto ERR;
	}
	nRet = sqlite3_step(pstmt);
	if(nRet!=SQLITE_DONE)
	{
		goto ERR;
	}
	nRet = SQLITE_OK;
	sqlite3_finalize(pstmt );
	return nRet;
ERR:
	if(pstmt)
		sqlite3_finalize(pstmt );
    if(pSql)
    	DMFree(pSql);
    return nRet;
}

updateParaRecordWithValue演示了更新BLOB数据

static int updateParaRecordWithValue(DBHANDLE dbHandle, const char* pszParaName, int type, const void* pData, int cbData  )
{
	const char* pTmp = "UPDATE PARAMETERS SET VT=%d, LENGTH=%d, VALUE=?, USEFUNC=0 WHERE FULLNAME='%s'";
	size_t nTmp = strlen( pTmp );
	char* pSql = DMMalloc( nTmp+strlen(pszParaName)+11+11+1);  /*32bit decimal int max length is 11*/
	if(pSql==NULL)
		return ERROR;
	sprintf( pSql, pTmp,type,cbData, pszParaName);
	
	sqlite3_stmt* pstmt=NULL;
	int nRet=sqlite3_prepare_v2(dbHandle, pSql, -1, &pstmt,  NULL );
	if(nRet!=SQLITE_OK)
	{
		goto ERR;
	}
	DMFree(pSql);
	pSql=NULL;
	
	nRet=sqlite3_bind_blob(pstmt,1,pData,cbData, NULL );
	if(nRet!=SQLITE_OK)
	{
		goto ERR;
	}
	nRet = sqlite3_step(pstmt);
	if(nRet!=SQLITE_DONE)
	{
		goto ERR;
	}
	nRet = SQLITE_OK;
	sqlite3_finalize(pstmt );
	return nRet;
ERR:
	if(pstmt)
		sqlite3_finalize(pstmt );
    if(pSql)
    	DMFree(pSql);
    return nRet;
}

getParaRecordWithValue演示了如何查询使用

static int getParaRecordWithValue(DBHANDLE dbHandle, const char* pszParaName, int* pType, void** ppData, int* pCbData  )
{
	int nRet = ERROR;
	sqlite3_stmt* pstmt=NULL;
	const char* pTmp = "SELECT VT, LENGTH, VALUE FROM PARAMETERS WHERE FULLNAME='%s'";
	size_t nTmp = strlen( pTmp );
	char* pSql = DMMalloc( nTmp+strlen(pszParaName)+1); 
	if(pSql==NULL)
		return nRet;
	
	sprintf( pSql, pTmp, pszParaName);
	
	nRet=sqlite3_prepare_v2(dbHandle, pSql, -1, &pstmt,  NULL );
	if(nRet!=SQLITE_OK)
	{
		DMFree(pSql);
		return nRet;
	}
	DMFree(pSql);
	pSql=NULL;
	
	nRet = sqlite3_step(pstmt);
	if(nRet!=SQLITE_ROW)
	{
		sqlite3_finalize(pstmt );
		return nRet;
	}
	
	nRet = SQLITE_OK;
	
	if(pType)
	{
		*pType = sqlite3_column_int(pstmt,0 );
	}
	
	if(pCbData )
	{
		*pCbData = sqlite3_column_int(pstmt,1 );
	}
	
	if(ppData && pCbData)
	{
		*ppData = DMMalloc(*pCbData);
		if(*ppData == NULL )
		{
			sqlite3_finalize(pstmt );
			return ERROR;
		}
		const void* pV = sqlite3_column_blob(pstmt,2);
		if( pV!=NULL )
			memcpy(*ppData, pV, *pCbData);
	}
	sqlite3_finalize(pstmt );
	return nRet;
}