14.12.2011, 20:28
(
Last edited by Slice; 07/10/2013 at 11:00 AM.
)
The built-in SQLite is more powerful than most people realize. One issue, however, is that the native functions given are very few and low-level. This library is doing something about that!
While this library mainly has functions that facilitate development, it also brings new functionality and solves crucial bugs.
A few issues taken care of:
- You can see error messages from db_query (for example, no such column name: test).
- NULL values in results caused the server to crash when using db_get_field.
- Freeing results twice caused a crash.
- Not freeing results at all caused memory leaks. SQLitei will take care of this, though you should still free results (especially if you're running many queries in a loop for example).
- You couldn't browse through results back and forth - db_next_row was the only function available.
Prepared statements
Inserting data
Example of how it could look to insert a row into a database:
pawn Code:
// Prepare a statement
new DBStatement:stmt = db_prepare(db, "INSERT INTO mytable VALUES(?, ?, ?)");
// Set the values for the question-marks (first one is 0, second one is 1, etc.)
stmt_bind_value(stmt, 0, DB::TYPE_FLOAT , 123.456);
stmt_bind_value(stmt, 1, DB::TYPE_INT , 1234567);
stmt_bind_value(stmt, 2, DB::TYPE_STRING, "there's no need to \"escape\" anything!");
new array[] = {1, 2, 3, 4, 5};
stmt_bind_value(stmt, 2, DB::TYPE_ARRAY, array, sizeof(array));
// Run the statement
stmt_execute(stmt);
// Then close it
stmt_close(stmt);
pawn Code:
// These variables will be used to store the data
new Float:float_value, int_value, string_value[128];
// Prepare a new statement
new DBStatement:stmt = db_prepare(db, "SELECT a, b, c FROM mytable");
// Bind the results. Whenever a row is fetched, the variables passed to stmt_bind_result_field
// will have their values set to the respective field (by its index).
stmt_bind_result_field(stmt, 0, DB::TYPE_FLOAT , float_value);
stmt_bind_result_field(stmt, 1, DB::TYPE_INT , int_value);
stmt_bind_result_field(stmt, 2, DB::TYPE_STRING, string_value, sizeof(string_value));
// Execute the statement!
if (stmt_execute(stmt)) {
// When the last row is fetched, the result will be freed.
while (stmt_fetch_row(stmt)) {
printf("float: %f", float_value);
printf("integer: %d", int_value);
printf("string: %s", string_value);
}
}
// Close the statement
stmt_close(stmt);
You can easily, for example, change single params or binds and execute the statement again. Examples on this later.
For now, you can have a look through the following ready-to-run gamemode for more examples:
http://pastebin.com/z4KcbdJC
Download
Download: sqlitei.inc
Functions
Each function is described below.
pawn Code:
// DB connections
DB:db_open(name[])
db_close(DB:db)
DB:db_open_persistent(name[])
bool:db_is_persistent(DB:db)
db_free_persistent(DB:db)
db_changes(DB:db)
db_total_changes(DB:db)
db_last_insert_rowid(DB:db)
db_get_struct_info(DB:db, offset)
db_set_struct_info(DB:db, offset, value)
// Queries and results
DBResult:db_query(DB:db, query[], autorelease = true)
bool:db_exec(DB:db, query[])
db_insert(DB:db, query[])
db_query_int(DB:db, query[], field = 0)
Float:db_query_float(DB:db, query[], field = 0)
db_print_result(DBResult:dbresult, max_field_length = 40)
db_num_rows(DBResult:dbresult)
db_next_row(DBResult:dbresult)
db_set_row_index(DBResult:dbresult, row)
db_get_row_index(DBResult:dbresult)
db_num_fields(DBResult:dbresult)
db_field_name(DBResult:dbresult, field, result[], max_length)
db_get_field(DBResult:dbresult, field, result[], max_length)
db_get_field_assoc(DBResult:dbresult, field[], result[], max_length)
db_get_field_int(DBResult:dbresult, field = 0)
Float:db_get_field_float(DBResult:dbresult, field = 0)
db_autofree_result(DBResult:dbresult)
db_free_result(DBResult:dbresult)
db_is_result_freed(DBResult:dbresult)
db_escape_string(string[], enclosing[] = "'", size = sizeof(string))
// Prepared statements
DBStatement:db_prepare(DB:db, query[])
bool:stmt_execute(&DBStatement:statement, store_result = true, auto_free_result = true)
stmt_bind_value(&DBStatement:statement, param, DBDataType:type, ...)
stmt_bind_result_field(&DBStatement:statement, field, DBDataType:type, ...)
stmt_skip_row(&DBStatement:statement)
bool:stmt_fetch_row(&DBStatement:statement)
stmt_rows_left(&DBStatement:statement)
DBResult:stmt_free_result(&DBStatement:statement)
stmt_close(&DBStatement:statement)
stmt_autoclose(&DBStatement:statement)
DB:db_open(name[])
Open a database.db_close(DB:db)
Close a database. If the database is persistent, it will be closed but re-opened if you do something with it.
DB:db_open_persistent(name[])
Open a persistent database. A persistent database only has to be opened once. When it's not being used, it will be closed then re-opened when used again.bool:db_is_persistent(DB:db)
Check if a DB handle is persistent.db_free_persistent(DB:db)
Free a persistent database; as opposed to db_close, this will actually free the database completely.
db_changes(DB:db)
Return the number of changes made by the previous query.db_total_changes(DB:db)
Return the total number of changes made since the DB was opened.db_last_insert_rowid(DB:db)
Return the rowid from the last row you inserted. See this if you don't know what "rowid" is.
db_get_struct_info(DB:db, offset)
Get information from the SQLite C-struct. Mainly used internally.db_set_struct_info(DB:db, offset, value)
Same as above, but sets it.Queries and results
DBResult:db_query(DB:db, query[], autorelease = true)
Query the database and return the result. All results will be autoreleased by default, unless the 3rd argument is "false".bool:db_exec(DB:db, query[])
Run a query and return true if it succeeded.db_insert(DB:db, query[])
Run a query and return the rowid of the inserted row. Again, see this if you don't know what "rowid" is.db_query_int(DB:db, query[], field = 0)
Run a query and return an integer from the first row at field 0 (or another field if specified in the 3rd argument).Float:db_query_float(DB:db, query[], field = 0)
Same as above, but return a float value.
db_print_result(DBResult:dbresult, max_field_length = 40)
Print out the full result from the query. This doesn't change the result's current row index, so you can use the result as usual.
db_num_rows(DBResult:dbresult)
Return the number of rows in the result.db_next_row(DBResult:dbresult)
Go to the next row. Returns true if there was another row to go to, otherwise false.db_get_row_index(DBResult:dbresult)
Get the current row index (i.e. if you use db_next_row twice the row index 2).db_set_row_index(DBResult:dbresult, row)
Set the current row index.
db_num_fields(DBResult:dbresult)
Return the number of fields in the result.db_field_name(DBResult:dbresult, field, result[], max_length)
Get a field name.db_get_field(DBResult:dbresult, field, result[], max_length)
Get a field's value as a string.db_get_field_assoc(DBResult:dbresult, field[], result[], max_length)
Get a field's value as a string. The field is specified by its name instead of an index.db_get_field_int(DBResult:dbresult, field = 0)
Get a field as an integer.Float:db_get_field_float(DBResult:dbresult, field = 0)
Get a field as a float.
db_autofree_result(DBResult:dbresult)
Make the result automatically be freed after the current callback finishes.db_free_result(DBResult:dbresult)
Free the result.db_is_result_freed(DBResult:dbresult)
Returns whether or not the result was freed. Note that this only works until you open another DB or perform another query. It should be avoided.
db_escape_string(string[], enclosing[] = "'", size = sizeof(string))
Escape a string for use within a query.Prepared statements
DBStatement:db_prepare(DB:db, query[])
Prepare a statement. Put question marks as placeholders for values to be inserted. Don't put quotes around the question marks.
bool:stmt_execute(&DBStatement:statement, store_result = true, auto_free_result = true)
Execute a statement. Returns true on success.
stmt_bind_value(&DBStatement:statement, param, DBDataType:type, ...)
Bind a value to a question mark (first question mark is param 0, second one is 1, etc.).
stmt_bind_result_field(&DBStatement:statement, field, DBDataType:type, ...)
Bind a field in the result to a variable.stmt_skip_row(&DBStatement:statement)
Skip the current row.bool:stmt_fetch_row(&DBStatement:statement)
Fetch a row and go to the next one. When there are no rows left, the result will be freed and the function returns false.stmt_rows_left(&DBStatement:statement)
Return the number of rows left.DBResult:stmt_free_result(&DBStatement:statement)
Free the result from the previous statement. Not necessary as the statements take care of that themselves anyway.
stmt_close(&DBStatement:statement)
Close a statement.stmt_autoclose(&DBStatement:statement)
Make a statement automatically close after the current callback.
. Your releases with SQLite are pretty awesome 
.
