June 13, 2024, 02:20:46 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Sqlite wrapper...

Started by zaphod, November 15, 2009, 03:06:15 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

zaphod

Hello,

I need a sqlite wrapper for Emergence Basic.
I need blob functions.
I see that Sapero make one for Aurora... but maybe i miss something ?


sapero

November 15, 2009, 06:08:40 AM #1 Last Edit: November 15, 2009, 06:17:55 AM by sapero
It is also for Emergence, just $include "sqlite3.inc" to include raw api's, and wrapper classes. To have a reference on returned pointers, look at equivalent method in Aurora header.

Ebasic:
class CSQLite3
declare blob_open(pointer zDb,pointer zTable,pointer zColumn, sqlite_int64 iRow, int flags, pointer ppBlob),int
...
pointer blob
settype blob, CSQLite3Blob
*db.blob_open(..., &blob)
*blob.some_method()

Aurora:
class CSQLite3
{
declare blob_open(string *zDb, string *zTable, string *zColumn, sqlite_int64 iRow, int flags, CSQLite3Blob *ppBlob),int;
...
CSQLite3Blob *blob;
db->blob_open(..., &blob);
blob->some_method();

zaphod

I have trying to convert sqlite3.inc in EB format : too hard for me....

I make a wrapper but i have an error with query :



DECLARE IMPORT, GetProcAddress ALIAS GetProcAddress(hModule AS INT,lpProcName AS STRING),INT
DECLARE IMPORT, LoadLibrary ALIAS LoadLibraryA(lpLibFileName AS STRING),INT
DECLARE IMPORT, FreeLibrary ALIAS FreeLibrary(hLibModule AS INT),INT
'
typedef sqlite3 pointer
typedef sqlite3_stmt pointer
'
const SQLITE3_OK  =   0   '; Successful Result
const SQLITE3_ERROR  =   1  ' ; SQL error Or missing database
const SQLITE_ROW = 100
'
declare sqlite_open(fn:string,pdb:pointer),INT
declare sqlite_close(db:sqlite3)
declare sqlite_libversion(),pointer
DECLARE sqlite_exec(db:sqlite3, zSql:string,sqlite3_callback:int, pArg:pointer,ppzErrMsg:pointer),int
DECLARE sqlite_prepare(db:sqlite3, query:string, nBytes:int, ppsqlite3_stmt:pointer, pppzTail=0:pointer),int
DECLARE sqlite_step(stmt:sqlite3_stmt),int
DECLARE sqlite_column_count(stmt:sqlite3_stmt),int
DECLARE sqlite_finalize(stmt:sqlite3_stmt),int
DECLARE sqlite_column_text(stmt:sqlite3_stmt, iCol:int),pointer
'
declare sqlite_last_insert_rowid(db:sqlite3),INT
declare sqlite_free_table(table:SQ3table)
declare sqlite_get_table(DB:sqlite3, statement:string, table:sq3table, nRow:int, nColumn:int, errmsg=0:int )
'
sqlite3 db
sqlite3_stmt stmt
'pointer pStr : settype pStr,string
'
uint _open,_close,_version,_exec,_prepare,_step,_column_count,_finalize,_column_text,_last_insert_rowid,_free_table,_get_table
initdll()
pointer s
s=!<sqlite_libversion>_version()
pointer n
string fn=GetStartPath()+"sqlitetest.db"
n=&fn
!<sqlite_open>_open(fn, &db)

OPENCONSOLE
print #<string>s
'
test()
'
!<sqlite_close>_close(db)
WAITCON
CLOSECONSOLE

freedll()
END


sub test()
if (SQLITE3_OK=!<sqlite_exec>_exec(db, "CREATE TABLE files (name TEXT,date INTEGER)", 0, 0, 0))
' insert only once
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('sqlite.eba', '20070619')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('readme.htm', '20061113')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('zdemo3.txt', '20060628')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('acpars.exe', '20060210')", 0, 0, 0)
endif
'
' ---------------- query 1 -------------------------
if (SQLITE3_OK= !<sqlite_prepare>_prepare(db, "SELECT * FROM files", -1, &stmt, 0))
'pointer pstr
pointer pStr : settype pStr,string

while SQLITE_ROW = !<sqlite_step>_step(stmt)
pstr=!<sqlite_column_text>_column_text(stmt, 1) '>>>> ERROR HERE
print *<string>pStr, " "
'pStr = !<sqlite_column_text>_column_text(stmt, 0)
'print *pStr
wend

!<sqlite_finalize>_finalize(stmt)
print "---------------------"
endif
/*
' ---------------- query 2 -------------------------
if (SQLITE3_OK= !<sqlite_prepare>_prepare(db, "SELECT * FROM files WHERE date<20070101", -1, &stmt, 0))

while SQLITE_ROW = !<sqlite_step>_step(stmt)
pStr = !<sqlite_column_text>_column_text(stmt, 1)
print *pStr, " ",
pStr = !<sqlite_column_text>_column_text(stmt, 0)
print *pStr
wend

!<sqlite_finalize>_finalize(stmt)
print "---------------------"
endif
*/
ENDSUB

sub initdll()
db=loadlibrary("sqlite3.dll")
_open=getprocaddress(db,"sqlite3_open")
_close=getprocaddress(db,"sqlite3_close")
_version=getprocaddress(db,"sqlite3_libversion")
_exec=getprocaddress(db,"sqlite3_exec")
_prepare=getprocaddress(db,"sqlite3_prepare")
_step=getprocaddress(db,"sqlite3_step")
_column_count=getprocaddress(db,"sqlite3_column_count")
_finalize=getprocaddress(db,"sqlite3_finalize")
_column_text=getprocaddress(db,"sqlite3_column_text")

'
_last_insert_rowid = Getprocaddress(db, "sqlite3_last_insert_rowid")
_free_table = Getprocaddress(db, "sqlite3_free_table")
_get_table = Getprocaddress(db, "sqlite3_get_table")
ENDSUB

sub freedll()
if db
freelibrary(db)
ENDIF
ENDSUB



If someone can help....

sapero

Hi zaphod, you don't need to  rewrite it again, the header is already there (see my sig)
$include "windowssdk.inc"
$include "sqlite3.inc"

sqlite3 db
if (!sqlite3_open("test.db", &db))
' insert only once

sqlite3_stmt stmt
BOOL prepared = !sqlite3_prepare(db, "SELECT * FROM files", -1, &stmt, 0)
if (!prepared)
sqlite3_exec(db, "CREATE TABLE files (name TEXT,date INTEGER)", 0,0,0)
sqlite3_exec(db, "INSERT INTO files VALUES('sqlite.eba', '20070619')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('readme.htm', '20061113')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('zdemo3.txt', '20060628')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('acpars.exe', '20060210')", 0, 0, 0)
prepared = !sqlite3_prepare(db, "SELECT * FROM files", -1, &stmt, 0)
endif

if (prepared)
while (sqlite3_step(stmt) = SQLITE_ROW)
pointer pstr = sqlite3_column_text(stmt, 1)
print *<string>pStr
wend
sqlite3_finalize(stmt)
endif

sqlite3_close(db)
endif

zaphod

Thanx Sapero, i found your sqlite3.inc and use it and it works.
I found my error in my wrapper : the declare must have CDECL, like that :



DECLARE IMPORT, GetProcAddress ALIAS GetProcAddress(hModule AS INT,lpProcName AS STRING),INT
DECLARE IMPORT, LoadLibrary ALIAS LoadLibraryA(lpLibFileName AS STRING),INT
DECLARE IMPORT, FreeLibrary ALIAS FreeLibrary(hLibModule AS INT),INT
'
typedef sqlite3 pointer
typedef sqlite3_stmt pointer
'
const SQLITE3_OK  =   0   '; Successful Result
const SQLITE3_ERROR  =   1  ' ; SQL error Or missing database
const SQLITE_ROW = 100
'
declare cdecl sqlite_open(fn:string,pdb:pointer),INT
declare cdecl sqlite_close(db:sqlite3)
declare cdecl sqlite_libversion(),pointer
DECLARE CDECL sqlite_exec(db:sqlite3, zSql:string,sqlite3_callback:int, pArg:pointer,ppzErrMsg=0:pointer),int
DECLARE cdecl sqlite_prepare(db:sqlite3, query:string, nBytes:int, ppsqlite3_stmt:pointer, pppzTail=0:pointer),int
DECLARE cdecl sqlite_step(stmt:sqlite3_stmt),int
DECLARE cdecl sqlite_column_count(stmt:sqlite3_stmt),int
DECLARE cdecl sqlite_finalize(stmt:sqlite3_stmt),int
DECLARE cdecl sqlite_column_text(stmt:sqlite3_stmt, iCol:int),pointer
'
declare CDECL sqlite_last_insert_rowid(db:sqlite3),INT
declare CDECL sqlite_free_table(table:SQ3table)
declare CDECL sqlite_get_table(DB:sqlite3, statement:string, table:sq3table, nRow:int, nColumn:int, errmsg=0:int )
'
sqlite3 db
sqlite3_stmt stmt
'
uint _open,_close,_version,_exec,_prepare,_step,_column_count,_finalize,_column_text,_last_insert_rowid,_free_table,_get_table
initdll()
pointer s
s=!<sqlite_libversion>_version()
pointer n
string fn=GetStartPath()+"sqlitetest.db"
n=&fn
!<sqlite_open>_open(fn, &db)

OPENCONSOLE
print #<string>s
'
test()
'
!<sqlite_close>_close(db)
WAITCON
CLOSECONSOLE
'DELETE stmt
freedll()
END


sub test()
if !<sqlite_exec>_exec(db,"CREATE TABLE files (name TEXT,date INTEGER)" , 0, 0, 0)=SQLITE3_OK
' insert only once
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('sqlite.eba', '20070619')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('readme.htm', '20061113')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('zdemo3.txt', '20060628')", 0, 0, 0)
!<sqlite_exec>_exec(db, "INSERT INTO files VALUES('acpars.exe', '20060210')", 0, 0, 0)
endif
'
' ---------------- query 1 -------------------------
'
if !<sqlite_prepare>_prepare(db, "SELECT * FROM files", -1, &stmt, 0)=SQLITE3_OK
pointer pStr : settype pStr,string
while  !<sqlite_step>_step(stmt)=SQLITE_ROW
pstr=!<sqlite_column_text>_column_text(stmt, 1)
print *pStr, " "
pStr = !<sqlite_column_text>_column_text(stmt, 0)
print *pStr
wend
!<sqlite_finalize>_finalize(stmt)
print "---------------------"
endif

' ---------------- query 2 -------------------------
if (SQLITE3_OK= !<sqlite_prepare>_prepare(db, "SELECT * FROM files WHERE date<20070101", -1, &stmt, 0))

while SQLITE_ROW = !<sqlite_step>_step(stmt)
pStr = !<sqlite_column_text>_column_text(stmt, 1)
print *pStr, " ",
pStr = !<sqlite_column_text>_column_text(stmt, 0)
print *pStr
wend

!<sqlite_finalize>_finalize(stmt)
print "---------------------"
endif

ENDSUB

sub initdll()
db=loadlibrary("sqlite3.dll")
_open=getprocaddress(db,"sqlite3_open")
_close=getprocaddress(db,"sqlite3_close")
_version=getprocaddress(db,"sqlite3_libversion")
_exec=getprocaddress(db,"sqlite3_exec")
_prepare=getprocaddress(db,"sqlite3_prepare")
_step=getprocaddress(db,"sqlite3_step")
_column_count=getprocaddress(db,"sqlite3_column_count")
_finalize=getprocaddress(db,"sqlite3_finalize")
_column_text=getprocaddress(db,"sqlite3_column_text")

'
_last_insert_rowid = Getprocaddress(db, "sqlite3_last_insert_rowid")
_free_table = Getprocaddress(db, "sqlite3_free_table")
_get_table = Getprocaddress(db, "sqlite3_get_table")
ENDSUB

sub freedll()
if db
freelibrary(db)
ENDIF
ENDSUB


If someone have an exemple of how to use BLOB...