2009-10-07

An example to retreive colume from a sqlite3 db

The following example shows how to retrieve the colume info from sqlite3 by its c interface. Some basic information

  • DB: my.db
  • table: tb
  • COLUMNs: Name, Owner, Category, Duration, ID

Given record id. now I want to retrieve the other column info.

bool MyClass::initialize(int id_in)
{

sqlite3* db;
sqlite3_stmt * pSTmt;
int iReturnCode = 0;
bool bRet = true;

// Open the database
sqlite3_open("my.db", &db);
if( SQLITE_OK!=sqlite3_errcode(db) )
{
qWarning("DB error: fail to open db file.");
sqlite3_close(db);
return false;
}

//prepare the SQL statement
char sSql[1000];
sprintf(sSql, "select Name, Owner, Category, Duration from tb where ID=%d", id_in);
if(sqlite3_prepare_v2(db, sSql, -1, &pSTmt, 0))
{
qWarning("DB error: fail to prepare the sql statement: [%s]", sSql);
sqlite3_finalize(pSTmt);
sqlite3_close(db);
return false;
}

//execute the statement
iReturnCode = sqlite3_step(pSTmt);
switch (iReturnCode )
{
case SQLITE_DONE:
qWarning("Warning: No record is found!");
bRet = false;
break;

case SQLITE_ROW:

sName_ = (const char*)sqlite3_column_text(pSTmt, 0);
sCategory_ = (const char*)sqlite3_column_text(pSTmt, 1);
sOwner_ = (const char*)sqlite3_column_text(pSTmt, 2);
sDuration_ = (const char*)sqlite3_column_text(pSTmt, 3);

bRet = true;
break;

default:

qWarning("DB error: fail to step the sql statement: [%s]", sSql);
qWarning("DB error code:");
bRet = false;
}

//finialize the state statement
sqlite3_finalize(pSTmt);
sqlite3_close(db);
return bRet;
}

No comments:

Post a Comment