FNXBasic and SQLite3

[Willem]

Is it possible to access a SQLite3 database in FNXBasic? How do I do this?

[Bob82604]

this might be of some help:

http://fnxbasic.com/cgi-bin/QandA/YaBB.pl?num=1326960009/1#1

[pierrf]

The link mentioned above doesn't work.

I have the following problem with this piece of code:

dim db1 as integer
Declare sql3_open as "sqlite3_open" of "sqlite3.dll" cdecl
filename As String
db As integer
result as integer
end declare
with sql3_open
filename="data.db"
db1=db
execute
end with
print db1
print sql3_open.result

The data.db and sqlite3.dll files reside in the same folder as the program.
When I run the program, I get the following error message:

Runtime error at position 1953
Access violation at address 6091A24B in module 'sqlite3.dll'. Write of address 00000000

I get the same message if I declare the sub omitting 'cdecl'.
Any ideas?

[Marco]

The link is the old forum the new link is:
viewtopic.php?f=1&t=259

not tested but try something like this:

dim db1 as integer
Declare sql3open as "sqlite3_open" of "sqlite3.dll" cdecl
Pfilename As integer
db As integer
result as integer
end declare

dim filename as string
filename="data.db"+chr$(0)
sql3open.Pfilename=varptr(filename)
sql3open.db1=db
sql3open.execute

print db1
print sql3open.result

best regards

[pierrf]

I think it should be:
sql3open.db=db1

The code suggested doesn't work either. I get the message:
Runtime error at position 1959
Access violation at address 6091A24B in module 'sqlite3.dll'. Write of address 00000000

The link you mentioned is only for ODBC/MsAccess, not sqlite.

Have you succesfully used sqlite3 with fnxbasic?

Thanks and best regards,
Frank

[Marco]

Hi, I put something together to start with,
best regards, marco

''=======declare sqlite3 api's
declare sqlite3_open as "sqlite3_open" of "sqlite3.dll" cdecl
filename as integer
PDbhandle as integer
end declare

declare prepare as "sqlite3_prepare_v2" of "sqlite3.dll" cdecl
db as integer ''Database handle
PzSql as integer ''pointer to null terminated string with SQL statement
nByte as integer ''Maximum length of string zSql in bytes.
PStmt as integer '' OUT: pointer to Statement handle.
PzTail as integer ''OUT: Pointer to unused portion of zSql
result as integer
end declare

declare stp as "sqlite3_step" of "sqlite3.dll" cdecl
stmt as integer '' Statement handle
result as integer
end declare

declare finalize as "sqlite3_finalize" of "sqlite3.dll" cdecl
stmt as integer '' Statement handle
result as integer
end declare

declare errmsg as "sqlite3_errmsg" of "sqlite3.dll" cdecl
db as integer '' Statement handle
result as integer
end declare

declare closedb as "sqlite3_close_v2" of "sqlite3.dll" cdecl
db as integer ''Statement handle
result as integer
end declare

''======wrappers for user
function error(dbhandle as integer) as string cdecl
errmsg.db=dbhandle
errmsg.execute
dim strresult as string
result=space$(255)
memcopy(varptr(result),errmsg.result,255)
result=left$(result,instr(result,chr$(0)))
result=rtrim$(result)
result="Error: "+result
end function

sub exequery(dbhandle as integer , q as string)
dim statementhandle as integer
dim tail as integer
''prepare statement
q=q+chr$(0) ''create a null terminated string
prepare.db=dbhandle
prepare.PzSql=varptr(q)
prepare.nByte=len(q)
prepare.PStmt=varptr(statementhandle)
prepare.PzTail =varptr(tail)
prepare.execute
'SQLITE_OK=0
if prepare.result<>0 then showmessage error(dbhandle)
stp.stmt=statementhandle
stp.execute
'SQLITE_DONE=101
if stp.result<>101 then showmessage error(dbhandle)
finalize.stmt=statementhandle
finalize.execute
'SQLITE_OK=0
if finalize.result<>0 then showmessage error(dbhandle)
end sub

function openfile(name as string) as integer
dim dbhandle as integer
name=name+chr$(0) ''null terminated string
sqlite3_open.filename=varptr(name) ''pass pointer of name
sqlite3_open.PDbhandle=varptr(dbhandle) ''pass memorypointer to handle
sqlite3_open.execute
result=dbhandle ''return handle of database
end function

sub closefile(dbhandle as integer)
closedb.db=dbhandle
closedb.execute
'SQLITE_OK=0
if closedb.result<>0 then showmessage error(dbhandle)
end sub

''========start of user test code=======
dim dbh as integer
dim query as string
dbh=openfile(exepath+"demo.sql")

query="CREATE TABLE demo ('name' TEXT, 'age' INTEGER);"
exequery(dbh,query)
query="INSERT INTO demo (name, age) VALUES (3,'demoname');"

exequery(dbh,query)

closefile(dbh)
showmessage "end prg"