Extension Modules > SQLite
SQLite3 SB ext. module
Support:
I have created a new board on the forum to make room for the new SQLite3 multi-platform extension module for ScriptBasic. Armando (AIR) has released a proof of concept as a sneak preview of what's to come.
--- Quote from: AIR ---This is the .00000001 version, there are a lot more functions in the SQLite3 package. Note that the actual SQLite3 package is statically linked into the library, thus the somewhat larger library size but the net result is that it doesn't require that SQLite3 be installed, which is a plus. All other libraries it needs are dynamically linked.
--- End quote ---
--- Code: ---INCLUDE sqlite.bas
hdb=sqlite::open("testsql")
sqlite::execute(hdb,"create table demo (someval integer, sometxt text);")
sqlite::execute(hdb, "INSERT INTO demo VALUES (123,'hello');")
sqlite::execute(hdb, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(hdb, "INSERT INTO demo VALUES (345, 'world');")
stmt = sqlite::query(hdb,"SELECT * FROM demo;")
while (sqlite::row(stmt) = 100)
pr = pr & sqlite::row_value(stmt,0) & " - " & sqlite::row_value(stmt,1) & "\n"
wend
sqlite::close(hdb)
print pr
print "SQLite Version: ",sqlite::version(),"\n"
--- End code ---
jrs@ip-10-166-185-35:~/tmp$ scriba sqltest.sb
123 - hello
234 - cruel
345 - world
SQLite Version: 3.7.12.1
jrs@ip-10-166-185-35:~/tmp$
kryton9:
Can't be any simpler than that. Great job!
Support:
Armando (AIR) released a beta version of his SQLite3 extension module for ScriptBasic. This release follows the standard SB high level function calls that are used in the MySQL, ODBC and PostgreSQL extensions modules. In most cases, just changing open/connect statement is all that is needed to move between DB extension module libraries. You don't need to install any SQLite3 libraries or dependencies to use this extension module. Everything is included in the extension module shared object.
t_sqlite3.sb
--- Code: ---import sqlite.bas
db = sqlite::open("testsql")
sqlite::execute(db,"CREATE TABLE demo (someval integer, sometxt text);")
sqlite::execute(db,"INSERT INTO demo VALUES (123,'hello');")
sqlite::execute(db, "INSERT INTO demo VALUES (234, 'cruel');")
sqlite::execute(db, "INSERT INTO demo VALUES (345, 'world');")
stmt = sqlite::query(db,"SELECT * FROM demo")
while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
if sqlite::fetchhash(stmt,column) then
print column{"someval"},"\t-\t",column{"sometxt"},"\n"
end if
wend
sqlite::close(db)
--- End code ---
jrs@ip-10-166-185-35:~/test$ scriba t_sqlite3.sb
123 - hello
234 - cruel
345 - world
jrs@ip-10-166-185-35:~/test$
sqlite.bas
--- Code: ---' """
FILE: sqlite.bas
This is the BASIC import file for the module sqlite.
This file was generated by headerer.pl from the file interface.c
Do not edit this file, rather edit the file interface.c and use
headerer.pl to regenerate this file.
"""
module sqlite
SQLITE3_OK = 0
SQLITE3_ERROR = 1
SQLITE3_INTERNAL = 2
SQLITE3_PERM = 3
SQLITE3_ABORT = 4
SQLITE3_BUSY = 5
SQLITE3_LOCKED = 6
SQLITE3_NOMEM = 7
SQLITE3_READONLY = 8
SQLITE3_INTERRUPT = 9
SQLITE3_IOERR = 10
SQLITE3_CORRUPT = 11
SQLITE3_NOTFOUND = 12
SQLITE3_FULL = 13
SQLITE3_CANTOPEN = 14
SQLITE3_PROTOCOL = 15
SQLITE3_EMPTY = 16
SQLITE3_SCHEMA = 17
SQLITE3_TOOBIG = 18
SQLITE3_CONStraint = 19
SQLITE3_MISMATCH = 20
SQLITE3_MISUSE = 21
SQLITE3_NOLFS = 22
SQLITE3_AUTH = 23
SQLITE3_ROW = 100
SQLITE3_DONE = 101
SQLITE3_STATIC = 0
SQLITE_TRANSIENT = -1
SQLITE_INTEGER = 1
SQLITE_FLOAT = 2
SQLITE_TEXT = 3
SQLITE_BLOB = 4
SQLITE_NULL = 5
' FUNCTION DECLARATIONS
declare sub ::OPEN alias "sql3_open" lib "sqlite"
declare sub ::CLOSE alias "sql3_close" lib "sqlite"
declare sub ::EXECUTE alias "sql3_execute" lib "sqlite"
declare sub ::QUERY alias "sql3_query" lib "sqlite"
declare sub ::ROW alias "sql3_row" lib "sqlite"
declare sub ::ROW_VALUE alias "sql3_row_value" lib "sqlite"
declare sub ::COLUMN_COUNT alias "sql3_column_count" lib "sqlite"
declare sub ::COLUMN_NAME alias "sql3_column_name" lib "sqlite"
declare sub ::FINALIZE alias "sql3_finalize" lib "sqlite"
declare sub ::VERSION alias "sql3_version" lib "sqlite"
declare sub ::ErrorCode alias "sql3_errorcode" lib "sqlite"
declare sub ::ErrorMsg alias "sql3_errormsg" lib "sqlite"
declare sub ::FETCHHASH alias "sql3_fetchhash" lib "sqlite"
declare sub ::FETCHARRAY alias "sql3_fetcharray" lib "sqlite"
end module
--- End code ---
interface.c
--- Code: ---/*
FILE : interface.c
HEADER : interface.h
BAS : sqlite.bas
AUTHOR : Armando I. Rivera
DATE:
CONTENT:
This is the interface.c file for the ScriptBasic module sqlite3
NTLIBS:
UXLIBS: -lc -ldl -lpthread
DWLIBS: -lsqlite3 -lpthread
ADLIBS:
*/
/*
TO_BAS:
SQLITE3_OK = 0
SQLITE3_ERROR = 1
SQLITE3_INTERNAL = 2
SQLITE3_PERM = 3
SQLITE3_ABORT = 4
SQLITE3_BUSY = 5
SQLITE3_LOCKED = 6
SQLITE3_NOMEM = 7
SQLITE3_READONLY = 8
SQLITE3_INTERRUPT = 9
SQLITE3_IOERR = 10
SQLITE3_CORRUPT = 11
SQLITE3_NOTFOUND = 12
SQLITE3_FULL = 13
SQLITE3_CANTOPEN = 14
SQLITE3_PROTOCOL = 15
SQLITE3_EMPTY = 16
SQLITE3_SCHEMA = 17
SQLITE3_TOOBIG = 18
SQLITE3_CONStraint = 19
SQLITE3_MISMATCH = 20
SQLITE3_MISUSE = 21
SQLITE3_NOLFS = 22
SQLITE3_AUTH = 23
SQLITE3_ROW = 100
SQLITE3_DONE = 101
SQLITE3_STATIC = 0
SQLITE_TRANSIENT = -1
SQLITE_INTEGER = 1
SQLITE_FLOAT = 2
SQLITE_TEXT = 3
SQLITE_BLOB = 4
SQLITE_NULL = 5
*/
#include <stdio.h>
#include <string.h>
#include "../../basext.h"
#include "sqlite3.h"
besVERSION_NEGOTIATE
return (int)INTERFACE_VERSION;
besEND
besSUB_START
long *p;
besMODULEPOINTER = besALLOC(sizeof(long));
if( besMODULEPOINTER == NULL )return 0;
p = (long*)besMODULEPOINTER;
return 0;
besEND
besSUB_FINISH
long *p;
p = (long*)besMODULEPOINTER;
if( p == NULL )return 0;
return 0;
besEND
/**
=section OPEN
=H Open/Create an sqlite3 database file
*/
besFUNCTION(sql3_open)
sqlite3 *db;
const char *fileName;
int i;
besARGUMENTS("s")
&fileName
besARGEND
i = sqlite3_open(fileName, &db);
besRETURN_POINTER(db)
besEND
/**
=section CLOSE
=H Close an sqlite3 database file
*/
besFUNCTION(sql3_close)
sqlite3 *db;
int i;
besARGUMENTS("p")
&db
besARGEND
i = sqlite3_close(db);
besRETURN_LONG(i)
besEND
/**
=section EXECUTE
=H Execute an SQL statement/command
*/
besFUNCTION(sql3_execute)
sqlite3 *db;
char *sqlcmd;
int ret;
besARGUMENTS("ps")
&db,&sqlcmd
besARGEND
ret = sqlite3_exec(db,sqlcmd,NULL,NULL,NULL);
besRETURN_LONG(ret)
besEND
/**
=section QUERY
=H Pass a Query to an sqlite3 database file
*/
besFUNCTION(sql3_query)
sqlite3 *db;
sqlite3_stmt *stmt;
char *sqlcmd;
int ret;
besARGUMENTS("ps")
&db,&sqlcmd
besARGEND
ret = sqlite3_prepare_v2(db,sqlcmd,strlen(sqlcmd)+1,&stmt,NULL);
besRETURN_POINTER(stmt)
besEND
/**
=section ROW
=H Retrieve the next Row from a database file
*/
besFUNCTION(sql3_row)
sqlite3_stmt *stmt;
int i;
besARGUMENTS("p")
&stmt
besARGEND
i = sqlite3_step(stmt);
besRETURN_LONG(i)
besEND
/**
=section ROW_VALUE
=H Retrieve the value at position y in database row
*/
besFUNCTION(sql3_row_value)
sqlite3_stmt *stmt;
const char* cur_column_text;
int i;
besARGUMENTS("pi")
&stmt,&i
besARGEND
cur_column_text = sqlite3_column_text(stmt,i);
besRETURN_STRING(cur_column_text)
besEND
/**
=section COLUMN_COUNT
=H Retrieve number of columns in table
*/
besFUNCTION(sql3_column_count)
sqlite3_stmt *stmt;
int i;
besARGUMENTS("p")
&stmt
besARGEND
i = sqlite3_column_count(stmt);
besRETURN_LONG(i)
besEND
/**
=section COLUMN_NAME
=H Retrieve column name at pos i in table
*/
besFUNCTION(sql3_column_name)
sqlite3_stmt *stmt;
const char* cur_column_name;
int i;
besARGUMENTS("pi")
&stmt,&i
besARGEND
cur_column_name = sqlite3_column_name(stmt,i);
besRETURN_STRING(cur_column_name)
besEND
/**
=section FINALIZE
=H Finalize an sqlite3 database file
*/
besFUNCTION(sql3_finalize)
sqlite3_stmt *stmt;
int i;
besARGUMENTS("p")
&stmt
besARGEND
i = sqlite3_finalize(stmt);
besRETURN_LONG(i)
besEND
/**
=section VERSION
=H Retrieve sqlite3 version string
*/
besFUNCTION(sql3_version)
const char *ver = sqlite3_libversion();
besRETURN_STRING(ver)
besEND
/**
=section ErrorCode
=H Returns status code for sqlite function call
*/
besFUNCTION(sql3_errorcode)
sqlite3 *db;
int i;
besARGUMENTS("p")
&db
besARGEND
i = sqlite3_errcode(db);
besRETURN_LONG(i)
besEND
/**
=section ErrorMsg
=H Returns text that describes the error
*/
besFUNCTION(sql3_errormsg)
sqlite3 *db;
const char *errString;
besARGUMENTS("p")
&db
besARGEND
errString = sqlite3_errmsg(db);
besRETURN_STRING(errString)
besEND
/**
=section FETCHHASH
=H Returns Hash containing row contents in Key/Value pair
*/
besFUNCTION(sql3_fetchhash)
VARIABLE Argument;
unsigned long __refcount_;
sqlite3_stmt *stmt;
LEFTVALUE Lval;
unsigned int numfields;
int i;
const char* cur_column_name;
const char* cur_column_text;
besARGUMENTS("p")
&stmt
besARGEND
Argument = besARGUMENT(2);
besLEFTVALUE(Argument,Lval);
if( ! Lval )return 0x00081001;
besRELEASE(*Lval);
*Lval = NULL;
numfields = sqlite3_column_count(stmt);
if( numfields == 0 ){
besRETURNVALUE = NULL;
return COMMAND_ERROR_SUCCESS;
}
*Lval = besNEWARRAY(0,2*numfields-1);
if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
for( i= 0 ; ((unsigned)i) < numfields ; i++ ) {
cur_column_name = sqlite3_column_name(stmt,i);
cur_column_text = sqlite3_column_text(stmt,i);
ARRAYVALUE(*Lval,2*i) = besNEWSTRING(strlen(cur_column_name));
if( ARRAYVALUE(*Lval,2*i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i)),cur_column_name, strlen(cur_column_name));
ARRAYVALUE(*Lval,2*i+1) = besNEWSTRING(strlen(cur_column_text));
if( ARRAYVALUE(*Lval,2*i+1) == NULL )return COMMAND_ERROR_MEMORY_LOW;
memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i+1)),cur_column_text,strlen(cur_column_text));
}
besALLOC_RETURN_LONG;
LONGVALUE(besRETURNVALUE) = -1;
besEND
/**
=section FETCHARRAY
=H Returns Array containing row contents
*/
besFUNCTION(sql3_fetcharray)
VARIABLE Argument;
unsigned long __refcount_;
sqlite3_stmt *stmt;
LEFTVALUE Lval;
unsigned int numfields;
int i;
const char* cur_column_text;
besARGUMENTS("p")
&stmt
besARGEND
Argument = besARGUMENT(2);
besLEFTVALUE(Argument,Lval);
if( ! Lval )return 0x00081001;
besRELEASE(*Lval);
*Lval = NULL;
numfields = sqlite3_column_count(stmt);
if( numfields == 0 ){
besRETURNVALUE = NULL;
return COMMAND_ERROR_SUCCESS;
}
*Lval = besNEWARRAY(0,numfields-1);
if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
for( i= 0 ; ((unsigned)i) < numfields ; i++ ) {
cur_column_text = sqlite3_column_text(stmt,i);
ARRAYVALUE(*Lval,i) = besNEWSTRING(strlen(cur_column_text));
if( ARRAYVALUE(*Lval,i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
memcpy(STRINGVALUE(ARRAYVALUE(*Lval,i)),cur_column_text,strlen(cur_column_text));
}
besALLOC_RETURN_LONG;
LONGVALUE(besRETURNVALUE) = -1;
besEND
START_FUNCTION_TABLE(SQLITE_SLFST)
// Ext. module
EXPORT_MODULE_FUNCTION(versmodu)
EXPORT_MODULE_FUNCTION(bootmodu)
EXPORT_MODULE_FUNCTION(finimodu)
// MOUDLE FUNCTIONS
EXPORT_MODULE_FUNCTION(sql3_open)
EXPORT_MODULE_FUNCTION(sql3_close)
EXPORT_MODULE_FUNCTION(sql3_execute)
EXPORT_MODULE_FUNCTION(sql3_query)
EXPORT_MODULE_FUNCTION(sql3_row)
EXPORT_MODULE_FUNCTION(sql3_row_value)
EXPORT_MODULE_FUNCTION(sql3_column_count)
EXPORT_MODULE_FUNCTION(sql3_column_name)
EXPORT_MODULE_FUNCTION(sql3_version)
EXPORT_MODULE_FUNCTION(sql3_finalize)
EXPORT_MODULE_FUNCTION(sql3_version)
EXPORT_MODULE_FUNCTION(sql3_errorcode)
EXPORT_MODULE_FUNCTION(sql3_errormsg)
EXPORT_MODULE_FUNCTION(sql3_fetchhash)
EXPORT_MODULE_FUNCTION(sql3_fetcharray)
END_FUNCTION_TABLE
--- End code ---
I would like to thank Armando for all his contributions to the ScriptBasic open source project.
Support:
Attached is Armando's SQLite3 ScriptBasic extension module for Android Linux. SQLite3 is statically linked into the SB ext. module so there is no external SQLite3 dependencies required. (my SGT2 10.1 doesn't seem to have a /system/bin/sqlite3 command line utility and I haven't found where there may be a share object version of SQLite3 installed) This Android Developer SQLite3 reference doesn't hold true on my SGT2_10.1 at least.
Support:
While testing the new SQLite3 extension module, I discovered a problem with the FetchHash() and FetchArray() functions. Armando sent me a fix which seems to work based on my limited testing. The following is an example of reading the raw_contacts table from the Android system DB.
--- Code: ---import sqlite.bas
db = sqlite::open("contacts2.db")
stmt = sqlite::query(db,"select * from raw_contacts")
while (sqlite::row(stmt) = sqlite::SQLITE3_ROW)
if sqlite::fetchhash(stmt,column) then
for x = 0 to ubound(column) step 2
PRINT column[x]," - ",column[x+1],"\n"
next x
end if
wend
sqlite::close(db)
--- End code ---
jrs@laptop:~/sbdev$ scriba t6_sqlite.sb
_id - 1
account_name - 1
account_type - 1
data_set - 1
sourceid - 1
raw_contact_is_read_only - 0
version - 2
dirty - 1
deleted - 0
contact_id - 1
aggregation_mode - 0
aggregation_needed - 0
custom_ringtone - 0
send_to_voicemail - 0
times_contacted - 0
last_time_contacted - 0
starred - 0
display_name - John Spikowski
display_name_alt - Spikowski, John
display_name_source - 40
phonetic_name - 40
phonetic_name_style - 3
sort_key - John Spikowski
sort_key_alt - Spikowski, John
name_verified - 0
sync1 - 0
sync2 - 0
sync3 - 0
sync4 - 0
jrs@laptop:~/sbdev$
Navigation
[0] Message Index
[#] Next page
Go to full version