Hi there,
I have started using recently SQLite which is pretty great for what I need,
(i.e. a scientific application). After going through PostgreSQL, CDB, Tiny CDB
and Berkeley DB it feels like coming home when I am looking at the size,
speed, number of features and license. :)
Anyway I have got a bit of an issue retrieving data from database. I have the
code below which works fine until the second fetch_first. It keeps crashing
with segmentation fault error. Is there something else that I should do before
that, like preparing the stmt again (in documentation it says that is not
needed) or clear_bindings (I tried that but I got an error in clear_bindings
now and I am thinking that shouldn't really be needed). As a new user I can't
seem to find too many C code examples but other than that everything is just
great.
Is there any difference between execute() & callback() and step() in term of
speed?
Below is the source code.
Any help would be greatly appreciated.
Thanks,
David
db_type *dbo;
db_sql_stmt *stmt;
const char *object_id;
int return_code;
char *sql;
db_open_read_only("objects.db", dbo, &dbo);
sql = "SELECT * FROM Objects ORDER BY ID ASC;";
db_prepare_stmt(dbo, sql, &stmt);
return_code = db_fetch_first(dbo, stmt); /* initial call to retrieve first row
of data */
while(return_code == SQLITE_ROW)
{
object_id = (const char *)db_get_column_string(stmt, 0);
(printf object_id - the objects are printed fine, one by one)
return_code = db_fetch_next(dbo, stmt); /* subsequent calls retrieve the
next rows */
}
(return_code is now SQLITE_DONE)
while(criteria)
{
(I need to reuse the same SQL statement a couple of times.)
db_reset_stmt(dbo, stmt);
********************************************* SEGMENTATION FAULT AT THE
NEXT LINE *********************************************
return_code = db_fetch_first(dbo, stmt); /* initial call to retrieve first
row of data */
while(return_code == SQLITE_ROW)
{
object_id = (const char *)db_get_column_string(stmt, 0);
(printf object_id )
return_code = db_fetch_next(dbo, stmt); /* subsequent calls retrieve the
next rows */
}
}
db_finalize_stmt(dbo, stmt); /* free statement memory */
db_close(dbo);
________________________________________________________________________
void db_open_read_only(const char *file_name, db_type *db, db_type **pdb)
{
int return_code;
return_code = sqlite3_open_v2(file_name, pdb, SQLITE_OPEN_READONLY, NULL);
if(return_code != SQLITE_OK)
{
/* get the message first and than free the memory before displaying the
message */
db_err_msg = sqlite3_errmsg(db);
db_close(db);
error_message(ERR_FATAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg,
NULL);
}
}
void db_close(db_type *db)
{
/* free database pointer memory */
int return_code;
return_code = sqlite3_close(db);
if(return_code != SQLITE_OK)
error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, "Unable to
close database file.", db_err_msg);
}
void db_prepare_stmt(db_type *db, const char *sql, db_sql_stmt **pstmt)
{
int return_code;
return_code = sqlite3_prepare_v2(db, sql, strlen(sql), pstmt, NULL);
if (return_code != SQLITE_OK)
{
/* get the message and free memory before displaying the message */
db_err_msg = sqlite3_errmsg(db);
db_close(db);
error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg,
NULL);
}
}
void db_reset_stmt(db_type *db, db_sql_stmt *stmt)
{
int return_code;
return_code = sqlite3_reset(stmt);
if (return_code != SQLITE_OK)
{
/* get the message and free memory before displaying the message */
db_err_msg = sqlite3_errmsg(db);
db_close(db);
error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg,
NULL);
}
}
void db_finalize_stmt(db_type *db, db_sql_stmt *stmt)
{
int return_code;
return_code = sqlite3_finalize(stmt);
if (return_code != SQLITE_OK)
{
/* get the message and free memory before displaying the message */
db_err_msg = sqlite3_errmsg(db);
db_close(db);
error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__, db_err_msg,
NULL);
}
}
int db_fetch_first(db_type *db, db_sql_stmt *stmt)
{
int return_code;
int retry_count = 0; /* counter that indicates how many times we have retried
the same operation */
return_code = sqlite3_step(stmt);
/* check if the returned code indicates low processing resources and if true
retry the operation */
while((return_code == SQLITE_BUSY) && (retry_count < 3))
{
sqlite3_sleep(500); /* 500 milliseconds or rounded up to 1 second if not
supported */
retry_count++;
return_code = sqlite3_step(stmt);
}
return return_code;
}
int db_fetch_next(db_type *db, db_sql_stmt *stmt)
{
int return_code;
int retry_count = 0; /* counter that indicates how many times we have retried
the same operation */
return_code = sqlite3_step(stmt);
/* check if the returned code indicates low processing resources and if true
retry the operation */
while((return_code == SQLITE_BUSY) && (retry_count < 3))
{
sqlite3_sleep(500); /* 500 milliseconds or rounded up to 1 second if not
supported */
retry_count++;
return_code = sqlite3_step(stmt);
}
return return_code;
}
const unsigned char *db_get_column_string(db_sql_stmt *stmt, int
column_position)
{
static const unsigned char *buffer = NULL;
db_type *db = NULL;
buffer = sqlite3_column_text(stmt, column_position);
/* check if there was an error or NULL is the correct value returned */
if (buffer == NULL)
{
db = db_get_handle(stmt); /* retrieve associated database handle for the
current statement */
if (sqlite3_errcode(db) == SQLITE_NOMEM) /* an error occured*/
{
/* get the message and free memory before displaying the message */
db_err_msg = sqlite3_errmsg(db);
db_close(db);
error_message(ERR_CRITICAL, ERR_ACTION_ABORT, __FILE__, __LINE__,
db_err_msg, NULL);
}
}
return buffer;
}
---------------------------------
Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how.
