25. SQLite and databases
Database support is quite sparse, but there is support for the simple SQLite (SQL means Structured Query Language). SQLite is not really a proper relational database like mSQL, MySQL, PostGreSql, Oracle DB or Microsoft SQL Server. Sqlite is a file based database for applications with light usage. You can get SQLite from Aminet or OS4Depot.
Before using SQLite, in your programs you need to create a database file with sqlite3 program:
1> sqlite3 mydatabase.db
To use SQLite you will need to include the sqlite3.h header file in your program. An alternative, is to use the SQLiteQuery routines (see links above).
#include "sqlite.3"
There are several functions required to use mysql, which are described below:
a) sqlite3_open - create a a database connection to a sqlite database.
int result = sqlite3_open(char *database_filename, sqlite3 **db_handle);
e.g.
int result;
sqlite3 dbcon;
result = sqlite3_open( "file:mydatabase.db", dbcon);
if (result != SQLITE_OK) printf("DB failed to open.\n");
b) sqlite3_prepare - Prepares a SQL statement into byte-code before it can be executed.
int result = sqlite3_prepare_v2( sqlite3 *db_handle, void *sql_statement, int max_length, sqlite3_stmt statement_handler, char **tail_pointer);
e.g.
int result;
sqlite3_stmt hdr;
char **pztail;
result = sqlite3_prepare_v2(dbcon, "Select * from mytable where num = 100", 50, hdr, pztail);
if (result !=
SQLITE_OK) printf("Command failed to preapre.\n");
c) sqlite3_bind - stores application data into parameters of original SQL.
int result = sqlite3_bind_int (sqlite3_stmt *statement_handler, int index, int value);
int result =
sqlite3_bind_int (sqlite3_stmt *statement_handler, int index, sqlite3_int64 value);
int result = sqlite3_bind_double (sqlite3_stmt *statement_handler, int index, double value);
int result = sqlite3_bind_text (sqlite3_stmt *statement_handler,const char*text, int num_bytes, void *destructor);
There are other bind statements for blob, value, pointer parameter types.
Here you can configure parameters via parameters in the sql statement in the format :
where NNN is an integer literal and VVV is an alphanumeric identifier. Indexes starts from 1, upto 999. If multiple parameters are used then you can use ?<index> e.g. ?5 = fifth parameter.
e.g.
result = sqlite3_prepare_v2(dbcon, "Select * from mytable where num = ?", 50, hdr, pztail);
result = sqlite3_bind_int ( hdr, 1, 100); /* Set parameter ? to value 100 */
d) sqlite3_step - Evaluation SQL statement and returns status.
int result = sqlite3_step (sqlite3_stmt *statement_handler)
The result can be one of the following:
i) SQLITE_BUSY =
sqlite is busy and cannot aquire the database locks to do its job.
ii) SQLITE_DONE = the statement has finished executing successfully. sqlite3_step() should not be called again.
iii) SQLITE_ERROR = a runtime error has occurent. Use sqlite3_errmsg() to get more information.
iv) SQLITE_MISUSE = the routine was called inappropiately eg. a prepared statement had alreay be finalised or connection used multiple times.
v) SQLITE_ROW = Another row of output is available.
e) sqlite3_column - returns result values from a query.
int result = sqlite3_column_int( sqlite3_stmt *statement_handler, int column);
sqlite3_int64 result = sqlite3_column_int64( sqlite3_stmt *statement_handler, int column);
double result = sqlite3_column_double( sqlite3_stmt *statement_handler, int column);
const unsigned char result =
sqlite3_column_text( sqlite3_stmt *statement_handler, int column);
There are other statements for blob, text, value, byte result types.
The first argument is a pointer to a sqlite statement
handler, and the second is a column index value, first column is index 0.
e.g.
result = sqlite3_column_int (hdr, 0); /* return integer result for prepared statement at index 0 */
f) sqlite3_finalize = This deletes a prepared statement.
int result = sqlite3_finalize(sqlite3_stmt *statement_handler)
The statement destroys the sqlite3_stmt argument and returns an error code. SQLITE_DONE means it was successful.
e.g.
sqlite_finalize (hdr);
g) sqlite3_close = Closes a database connection.
int result = sqlite3_close (sqlite3 *dbconnetion);
Closes a sqlite3 connection that was previously created with sqlite3_open function.
e.g.
sqlite3_close(dbcon);
h) sqlite3_exec = This is a wrapper that combines the prepare, step and finalize statements into one command.
int result = sqlite3_exec (sqlite3 *dbcon, const char *sql_statement, int (*callback), void *argument, char **errmsg);
result = result of the execution of the sql statement.
sql_statement = the SQL statement to execute on database.
callback = function called for each row of the result.
argument = argument for the callback function.
errmsg = an error message is written here.
e.g.
int DisplayResults(void *NotUsed, int argc, char **argv, char **ColName)
{
/* function to display results for each row from sql query here */
}
result = sqlite3_exec( dbcon, request, DisplayResults, lst_result, &zerrMsg);