sqlite3. Binding Values To Prepared Statements

The basic use of sqlite3 c++ interface can be found here:

• sqlite3_open()
• sqlite3_prepare()
• sqlite3_step()
• sqlite3_column()
• sqlite3_finalize()
• sqlite3_close()

A simple but nice example can be found here.

Another very important and useful function is sqlite3_bind. Why do we need it?

Usually, though, it is not useful to evaluate exactly the same SQL statement more than once. More often, one wants to evalute similar statements. sqlite3_prepare_v2() can not only parse the state SQL statement, but also possible a SQL statement template, in which some parameters can be given later. The literals parameters are bound by the function sqlite3_bind. It is very flexible and efficient, because the SQL statement is need to be prepared only once and be used forever. In many SQL statements, the time needed to run sqlite3_prepare() equals or exceeds the time needed by sqlite3_step().

The following code is c++ class do a select sql operation, retrieving "File" according given "ID".

"select File from tb_image where ID=?1"

The parametrized SQL statement is prepared in constructor once using

SqlHandler::prepare_sql(const char* sql_in, sqlite3_stmt*& pStmt_in)

The image object can be retrieved with different id.

bool SqlHandler::getRecipeHeaderImage( const int& imageId_in, QImage& image_out)

//-----------------------Example Code---------------------


static const char SQL_SELECT_HEADERIMAGE[]= "select File from tb_image where ID=?1";

SqlHandler::SqlHandler()
:bIsDBAvailable_(false)
,db_(0)
,pStmtHeaderImage_(0)
{
// Open the database
sqlite3_open(COKI_DB_FILE, &db_);
if( SQLITE_OK!=sqlite3_errcode(db_) )
{
qCritical("DB error: fail to open db file.");
sqlite3_close(db_);
return;
}

//prepare the SQL statement for select header info
if(!prepare_sql(SQL_SELECT_HEADERINFO, pStmtHeaderInfo_))
{
return;
}
SqlHandler::~SqlHandler()
{
sqlite3_finalize(pStmtHeaderImage_);
sqlite3_close(db_);
}
bool SqlHandler::isDBAvailable()
{
return bIsDBAvailable_;
}
bool SqlHandler::getRecipeHeaderImage( const int& imageId_in,
QImage& image_out)
{
if(!isDBAvailable())
{
return false;
}

// add id into sql statement
int iReturnCode = sqlite3_bind_int( pStmtHeaderImage_, 1, imageId_in);
if(iReturnCode != SQLITE_OK)
{
qCritical("Error: sqlite3_bind_in fails for ImageId = %d",imageId_in);
return false;
}

QString strFileName = COKI_IMAGE_DIR;
//execute the statement
iReturnCode = sqlite3_step( pStmtHeaderImage_ );
switch (iReturnCode )
{
case SQLITE_DONE:
return false;
break;

case SQLITE_ROW:
//load image
strFileName.append ((const char*)sqlite3_column_text( pStmtHeaderImage_, 0));
image_out.load(strFileName);
return true;
break;

default:
return false;

}


}
bool SqlHandler::prepare_sql(const char* sql_in, sqlite3_stmt*& pStmt_in)
{
if(sqlite3_prepare_v2(db_, sql_in, -1, &pStmt_in, 0))
{
sqlite3_finalize(pStmt_in);
return false;
}
return true;

}