I was using IBasic since long and now I encountered a problem that I hoped it could have been solved by IWBasic, but results are the same. Likely I'm doing something wrong but I find everytime difficult to translate from C...
Following is a working example of a C usage of the popular sqlite3.dll (commented) and my translation in basic, which fails likely cause of some error in the interfacing.
The first call to sqlite3_open seems to work: return is 0 and the file is created if not already existing; but, if for instance a file with that name and set as read only exists, no errors are found.
The second call to sqlite3_exec always (I tried several variants of that code) returns 0x15 and doesn't work.
Can anyone give me some hint?
/*
01 #include <stdio.h>
02 #include <sqlite3.h>
03
04 static int callback(void *NotUsed, int argc, char **argv, char **azColName){
05 int i;
06 for(i=0; i<argc; i++){
07 printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
08 }
09 printf("\n");
10 return 0;
11 }
12
13 int main(int argc, char **argv){
14 sqlite3 *db;
15 char *zErrMsg = 0;
16 int rc;
17
18 if( argc!=3 ){
19 fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
20 return(1);
21 }
22 rc = sqlite3_open(argv[1], &db);
23 if( rc ){
24 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
25 sqlite3_close(db);
26 return(1);
27 }
28 rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
29 if( rc!=SQLITE_OK ){
30 fprintf(stderr, "SQL error: %s\n", zErrMsg);
31 sqlite3_free(zErrMsg);
32 }
33 sqlite3_close(db);
34 return 0;
35 }
*/
$main
$use "C:\Data\Software\TechDB\sqlite\sqlite3.lib"
declare cdecl import,sqlite3_open(fn:pointer,db:pointer),int
declare cdecl import,sqlite3_exec(db:pointer,sql:pointer,callback:pointer,void:pointer,errmsg:pointer),int
type sqlite3
def a:int64
def b:uint64
endtype
def db:sqlite3
db.a = 0
db.b = 0
def db1:pointer
db1 = db
'def rep:int
def errmsg:pointer
def errormessage:string
errmsg = errormessage
def pcmd:pointer
def cmd:string
cmd = ".help"
pcmd = cmd
rep = sqlite3_open(getstartpath + "\\aaa.db",&db)
print "sqlite3_open returned " + hex$(rep)
rep = sqlite3_exec(db1,pcmd,&callback,0,errmsg)
print "sqlite3_exec returned " + hex$(rep)
print "db = " + hex$(db.a) + " ; " + hex$(db.b)
print "errmsg = " + hex$(errmsg)
print "errormessage:" + errormessage +":"
do:until inkey$<>""
end
sub callback
print "Callback!"
return 0
endsub
I'm not any good at converting C either; that's why I program in Basic.
This is something that Joske did. Maybe something in it will help you.
/*
simple adressbook with using sqlite.dll (version 2.8.15)
For more information about sqlite.dll see www.sqlite.org
program language: Ibasic Professional
created by Jos de Jong, 2004, www.josdejong.tk
with code-parts from Ian Fletcher
Remark: before you can compile this program in IBasic Professional, you need to
create an import library for the sqlite.dll: menu->Tools->Create Import Library
addition compared to version v01:
popupmenu added when user right-clicks in listview
*/
TYPE NMLISTVIEW
def hwndFrom:INT:def idFrom:INT:def code:INT:def iItem:INT: def iSubItem:INT:def uNewState:INT
def uOldState:INT:def uChanged:INT:def ptActionx:INT:def ptActiony:INT:def lParam:INT
ENDTYPE
DEF mem:MEMORY
DEF nm:NMLISTVIEW
TYPE LV_KEYDOWN
DEF hwndFrom:INT
DEF idFrom:INT
DEF code:INT
DEF vkey:WORD
DEF flags:INT
ENDTYPE
DEF lv:LV_KEYDOWN
type SQLParams
def DB_Handle:int
def ArPo:int
def TabXM:int
def TabYM:int
endtype
$USE "sqlite.lib"
declare cdecl import, sqlite_libversion(),int
declare cdecl import, sqlite_open(dbname:string,mode:int,errmsg:int byref),int
declare cdecl import, sqlite_close(sqlite:int)
declare cdecl import, sqlite_freemem(mem:int)
declare cdecl import, sqlite_get_table(sqlite:int,sql:string,result:int byref,nrow:pointer,ncolumn:pointer,errmsg:int byref),int
declare cdecl import, sqlite_free_table(mem:int)
declare "kernel32",lstrcpyA(lpstring1:string,lpstring2:int),int
declare "kernel32",RtlMoveMemory(dest:pointer,source:int,length:int),int
declare "user32",GetCursorPos(pPoint:POINTAPI),INT
def dbname,err,mode:string
def mydb:SQLParams
def curid,lvid:int
dbname = getstartpath+"adresses.db"
if left$(sqliteversion(),1)<>"2"
'this program is based on sqlite 2.8.15
'if the user uses sqlite 3.xx, then this program may not work. In that case give a warning
messagebox 0, "This program is written for use with SQLite 2.8"+""+"You are using SQLite "+sqliteversion()+","+""+"therefore the program may not work correctly.", "Warning", 48
endif
'check if the file "adresses.db" exists.
'If not, create it and create the table "adreslist" in it (with an example adress)
def temp:file
IF OPENFILE(temp, dbname, "R") = 0
'the file exist. close it
CLOSEFILE temp
ELSE
'the file doesn't exist, create a new database file
SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,"CREATE TABLE adreslist (Name text, Street text, Postcode text, Place text, Phone text, Email text, Remark text, ID INTEGER PRIMARY KEY)",err)
SqlCmd(mydb,"INSERT INTO adreslist (Name, Street, Postcode, Place, Phone, Email, Remark) VALUES ('My friend bob', 'NewStreet 1', '1234 AB', 'MyPlace', '+31-12345678', 'Example@email.com','Big friend')",err)
SqlCmd(mydb,"INSERT INTO adreslist (name, street, Postcode, Place, Phone, Email, Remark) VALUES ('Another friend', 'OtherStreet 10', '3333 BB', 'HisPlace', '+31-88886666', 'Myfriend@email.com','Also a big friend')",err)
SqlClose(mydb)
ENDIF
window main
double WindowColor
OPENWINDOW main,100,100,530,370,@size|@MINBOX|@maxbox,0,"Adressbook", &routinemain
declare "user32.dll",GetSysColor(what:int),int
WindowColor = GetSysColor(15)
SETWINDOWCOLOR main, WindowColor
drawmode main, @transparent
CONTROL main, @listview, "", 5, 35, 500, 300, @VSCROLL|@LVSreport|@LVSSINGLESEL|@border, 1
control main, @button, "New", 5,5,75,25, 0,2
control main, @button, "Edit", 85,5,75,25, 0,3
control main, @button, "Delete", 165,5,75,25, 0,4
setfont main, "ms sans serif", 8, 400, 0, 2
setfont main, "ms sans serif", 8, 400, 0, 3
setfont main, "ms sans serif", 8, 400, 0, 4
'create a dialog for adding new adres records.
dialog rec
CreateDialog rec, 0,0,250,185, @CAPTION|@SYSMENU, main, "New Record", &routinerec
control rec, @static, "Name", 5,5+0*20,75,15, 0, 100
control rec, @static, "Street", 5,5+1*20,75,15, 0, 101
control rec, @static, "Postcode", 5,5+2*20,75,15, 0, 102
control rec, @static, "Place", 5,5+3*20,75,15, 0, 103
control rec, @static, "Phone", 5,5+4*20,75,15, 0, 104
control rec, @static, "Email", 5,5+5*20,75,15, 0, 105
control rec, @static, "Remark", 5,5+6*20,75,15, 0, 106
control rec, @edit, "", 80,5+0*20,155,18, @tabstop, 110
control rec, @edit, "", 80,5+1*20,155,18, @tabstop, 111
control rec, @edit, "", 80,5+2*20,155,18, @tabstop, 112
control rec, @edit, "", 80,5+3*20,155,18, @tabstop, 113
control rec, @edit, "", 80,5+4*20,155,18, @tabstop, 114
control rec, @edit, "", 80,5+5*20,155,18, @tabstop, 115
control rec, @edit, "", 80,5+6*20,155,18, @tabstop, 116
control rec, @button, "&Cancel", 80,150,75,25, @tabstop, 120
control rec, @button, "&OK", 160,150,75,25, @tabstop, 121
'to show the content of the database:
'sqlcmd="SELECT type, name, rootpage, sql FROM sqlite_temp_master UNION ALL SELECT type, name, rootpage, sql FROM sqlite_master"
curid=0
refreshform()
Refreshlv(main,1) :'show the content of the table "adreslist"
refreshbuttons()
run=1
WAITUNTIL run = 0
CLOSEWINDOW main
END
'__________________________________
sub routinemain
select @message
case @IDCLOSEWINDOW
run=0 :'closes the window
case @idcontrol
if @controlid=1:'user did something in the listview
curid=GetCurrentID()
refreshbuttons()
if @notifycode=@nmrclick:'user rightclicked in the listview
popupmenu()
endif
endif
if @controlid=2:'new record
mode="New"
domodal rec, main
endif
if @controlid=3:'edit record
mode="Edit"
domodal rec, main
endif
if @controlid=4:'delete button
Delete_Rec()
endif
case @idsize
refreshform()
case @idmenupick
'user clicked an item in the contextmenu
if @menunum=302:'new record
mode="New"
domodal rec, main
endif
if @menunum=303:'edit record
mode="Edit"
domodal rec, main
endif
if @menunum=304:'delete button
Delete_Rec()
endif
endselect
'end of sub mainroutine
return
endsub
'__________________________________
sub routinerec
select @message
CASE @IDINITDIALOG
centerwindow rec
setcaption rec, mode + " record"
if mode="Edit" then Read_Rec()
case @IDCLOSEWINDOW
closedialog rec
case @idcontrol
if @controlid=120:'cancel button
closedialog rec
endif
if @controlid=121:'OK button
Write_Rec()
closedialog rec
endif
endselect
return
endsub
'____________________________________
sub GetCurrentID(), int
'this sub returns the ID of the record that is currently selected
def n,result:int
def idstring:string
'search the list for a selected item
lvid=-1
for n=0 to CONTROLCMD(main,1, @LVGETCOUNT)
if CONTROLCMD(main,1, @LVGETSELECTED, n)<>0 then lvid=n
next n
'get the ID of the selected item
if lvid>=0 then result=controlcmd(main,1,@lvgettext, lvid,7,idstring)
return val(idstring)
endsub
'_____________________________________
sub refreshbuttons()
'this sub enables/disables the right buttons New, Edit, Delete
if curid<=0
'disable the Edit and Delete button
enablecontrol main, 3,0
enablecontrol main, 4,0
else
'enable the Edit and Delete button
enablecontrol main, 3,1
enablecontrol main, 4,1
endif
return
endsub
'_____________________________________
sub refreshform()
'resize controls in the window
def l,t,w,h:int
getclientsize main, l,t,w,h
setsize main, 5, 35,w-10,h-40, 1
return
endsub
'_____________________________________
sub Read_Rec()
'copy the content of the current selected item in the listview to the edit-Dialog
def n,result:int
def content:string
'get the contents of the selected item, and set them in the editboxes of the dialog
if lvid>=0
for n=0 to 6
result=controlcmd(main,1,@lvgettext, lvid,n,content)
setcontroltext rec, 110+n, content
next n
endif
return
endsub
'_____________________________________
sub Write_Rec()
'write the content of the dialog to the current selected item in the listview
def n,result:int
def content,sqlcmd:string
if mode="Edit"
'write the contents to the sql-database
sqlcmd= "UPDATE adreslist SET "
sqlcmd=sqlcmd+ "name='" + getcontroltext(rec, 110) + "', "
sqlcmd=sqlcmd+ "street='" + getcontroltext(rec, 111) + "', "
sqlcmd=sqlcmd+ "postcode='" + getcontroltext(rec, 112) + "', "
sqlcmd=sqlcmd+ "place='" + getcontroltext(rec, 113) + "', "
sqlcmd=sqlcmd+ "phone='" + getcontroltext(rec, 114) + "', "
sqlcmd=sqlcmd+ "email='" + getcontroltext(rec, 115) + "', "
sqlcmd=sqlcmd+ "remark='" + getcontroltext(rec, 116) + "'"
sqlcmd = sqlcmd + " WHERE id='"+using("#", curid)+"';"
SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,sqlcmd,err)
SqlClose(mydb)
'copy the content in the editboxes in the dialog to the listview
if lvid>=0
for n=0 to 6
content = getcontroltext(rec, 110+n)
result=controlcmd(main,1,@lvsettext, lvid,n,content)
next n
endif
endif
if mode="New"
'write the contents to the sql-database
sqlcmd= "INSERT INTO adreslist (name, street, postcode, place, phone, email, remark) VALUES ("
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 110) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 111) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 112) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 113) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 114) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 115) + "', "
sqlcmd=sqlcmd+ "'" + getcontroltext(rec, 116) + "');"
SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,sqlcmd,err)
SqlClose(mydb)
'refresh the listview
curid=0
Refreshlv(main,1) :'show the content of the table "adreslist"
refreshbuttons()
endif
return
endsub
sub Delete_Rec()
'delete the currently selected item
def sqlcmd, curname:string
def result,n:int
if lvid>=0 and curid>0
result = controlcmd(main,1,@lvgettext, lvid,0,curname)
result = messagebox(main, "Do you realy want to delete " +curname+"?", "Delete Record",3|32)
'yes=6, no=7, cancel=2
if result = 6:'user answered YES
'delete the record out of the database
sqlcmd = "DELETE FROM adreslist WHERE id='" +using("#",curid)+ "';"
SqlOpen(dbname,0,err,mydb)
SqlCmd(mydb,sqlcmd ,err)
SqlClose(mydb)
'refresh the listview
curid=0
Refreshlv(main,1)
refreshbuttons()
endif
endif
return
endsub
'_____________________________________
Sub RefreshLV(wd:window, cid:int)
SqlOpen(dbname,0,err,mydb) :'open the database
SqlCmd(mydb,"SELECT * FROM adreslist;",err) :'select all ore something from a table (depends on the content of SQL_str)
SqlDisplayLV(mydb,wd,1) :'show the content of the just made selection
SqlClose(mydb) :'close the database
Return
endsub
'_____________________________________
sub popupmenu()
'show a contextmenu when left-clicked in the listview
int L,T,W,H,dh
string curname
TYPE POINTAPI
int x
int y
ENDTYPE
POINTAPI Cursor
int x,y,border, MenuChecks[10]
int Wcl,Hcl :'clientvalues
GetCursorPos(Cursor)
Getclientsize main, L,T,W,H :dh=h
Getsize main, L,T,W,H :dh=h-dh
x=Cursor.x-L
y=Cursor.y-T-dh:'compensate the captionbarheight
controlcmd(main,1,@lvgettext, lvid,0,curname)
if curid>0
CONTEXTMENU main, x, y
MENUITEM "&New record", 0,302
MENUITEM "&Edit "+curname, 0,303
SEPARATOR
MENUITEM "&Delete "+curname, 0,304
ENDMENU
else
CONTEXTMENU main, x, y
MENUITEM "&New record", 0,302
MENUITEM "&Edit", @MENUDISABLE ,303
SEPARATOR
MENUITEM "&Delete", @MENUDISABLE ,304
ENDMENU
endif
return
endsub
'[Fletchy's sqlitecom-code]
'_________________________________________________________________
sub SqlOpen(FileName:string,mode:int,ErrorText:string,DBHnd:SQLParams)
def et:int
DBHnd.DB_Handle=sqlite_open(FileName,mode,et)
if DBHnd.DB_Handle=0
lstrcpyA(ErrorText,et)
sqlite_freemem(et)
else
ErrorText=""
endif
return len(ErrorText)
endsub
'_________________________________________________________________
sub SqlClose(DBHnd:SQLParams)
if DBHnd.ArPo<>0
sqlite_free_table(DBHnd.ArPo)
DBHnd.Arpo=0
DBHnd.TabXM=0
DBHnd.TabYM=0
endif
if DBHnd.DB_Handle<>0
sqlite_close(DBHnd.DB_Handle)
DBHnd.DB_Handle=0
endif
return
endsub
'____________________________________________________________________
sub SqlCmd(DBHnd:SQLParams,SQL_Statement:string,ErrorText:string)
def et,ec:int
if DBHnd.ArPo<>0 then sqlite_free_table(DBHnd.ArPo)
ec=sqlite_get_table(DBHnd.DB_Handle, SQL_Statement, DBHnd.ArPo, DBHnd.TabYM, DBHnd.TabXM, et)
if et<>0
lstrcpyA(ErrorText,et)
sqlite_freemem(et)
else
ErrorText=""
endif
return ec
endsub
'______________________________________________________________________
sub SqlGetField(DBHnd:SQLParams,x:int,y:int,field:string)
def stra:int
RtlMoveMemory(stra,DBHnd.ArPo+((y*DBHnd.TabXM+x)*4),4)
lstrcpyA(field,stra)
return
endsub
'______________________________________________________________________
sub SqlCheckForTable(db:SQLParams,tabname:string)
def rows:int
def err,s:string
s="select * from sqlite_master '"+tabname+"'" :'creates the table if non existant
SqlCmd(db,s,err)
if db.TabYM<>0 then return 1
rows=db.TabYM
return rows
endsub
'______________________________________________________________________
'Returns the version of the DLL currently being used...
sub SqliteVersion(),string
def p:pointer
p=sqlite_libversion()
return #<string>p
endsub
'______________________________________________________________________
'display the contents of a table in a listview
sub SqlDisplayLV(DBHnd:SQLParams,wd:window,cid:int)
def x,y:int
def dat[100]:istring
def straddr:int
def cur:int
controlcmd wd,cid,@lvdeleteall
while sendmessage (wd,4124,0,0,cid)
endwhile
if DBHnd.TabXM>0
sendmessage wd,4143,DBHnd.TabYM,0,cid : 'Turns updating display off...
sendmessage wd,11,0,0,cid
cur=DBHnd.ArPo
for x=0 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@lvinsertcolumn,x,dat
next x
if DBHnd.TabYM>0
for y=0 to DBHnd.TabYM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@lvinsertitem,y,dat
for x=1 to DBHnd.TabXM-1
RtlMoveMemory(straddr,cur,4)
lstrcpyA(dat,straddr)
cur=cur+4
controlcmd wd,cid,@LVSETTEXT, y,x,dat
next x
next y
else
endif
'LVSCW_AUTOSIZE,-1 (Autosized columns)
for x=0 to DBHnd.TabXM-1
controlcmd wd,cid,@LVSETCOLWIDTH,x,-1
next x
'Turns updating of display back on......
sendmessage wd,11,1,0,cid
else
controlcmd wd,cid,@lvinsertcolumn,0,"Use Execute SQL button to create a entry"
controlcmd wd,cid,@lvinsertitem,1,"Empty fields"
endif
SQL_tab_cols=DBHnd.TabXM
SQL_tab_rows=DBHnd.TabYM
return
endsub
'______________________________________________________________________
'[Fletchy's sqlitecom-code]
Try this. Still not calling the callback function because the ".help" command.
$INCLUDE "windowssdk.inc"
$INCLUDE "sqlite3.inc"
$use "sqlite3.lib"
def db:sqlite3
def errmsg:pointer
def errormessage:string
errmsg = errormessage
def pcmd:pointer
def cmd:string
cmd = "help"
pcmd = cmd
pointer p = 0
rep = sqlite3_open(getstartpath + "\\aaa.db",&db)
print "sqlite3_open returned " + hex$(rep)
rep = sqlite3_exec(db,pcmd,&callback,0,errmsg)
print "sqlite3_exec returned " + hex$(rep)
print "errmsg = " + hex$(errmsg)
p = sqlite3_errmsg(db)
errormessage = #<string>p
print "errormessage: " + errormessage
do:until inkey$<>""
end
rep = sqlite3_close(db)
sub callback(pointer NotUsed, int argc, pointer argv, pointer azColName), INT
print "Callback!"
return 0
endsub
So are you seeking some iwBasic code examples that allow you to create/update using the sqlite3.dll ?
Let me know,
Alan
Hello everybody. First of all, thanks for your kind support.
So far I quickly tried both proposal with IBasic and with IWBasic, getting some errors.
I will deepen the subject in the next days and let you know.
1. Yes, I'm interested in interfacing sqlite3 from Basic
2. I'm still not so much into IWBasic (running the evaluation version so far); what is windowssdk.inc? Something that should be in the environment?
Good night from Italy.
M
In you include windowssdk.inc in your application(after running the install program) it will take care of the declaration of just about every windows api you might use.
I use it in every program I write.
Larry, your code is working fine!
I just had to find a copy of sqlite 2.8.17 (I didn't find 2.8.15); no way to get the linker happy with sqlite3...
I think in the next days I'll have a lot to learn from it, thanks so much.
Just a remark for all the (inexpert) IBasic users like me: libtool has problems if it is installed in a directory with some space in the path. It seems to work on the dll but with no results. I solved that, by copying libtool.exe on a directory with different naming. Just call libtool with the dll name as parameter (or drag and drop the dll over libtool) and the .lib file will come.
Thanks again to all guys that replied to my questions.
M
Quote from: m.francescon on April 14, 2014, 03:01:43 PM
libtool has problems if it is installed in a directory with some space in the path. It seems to work on the dll but with no results.
I'll double check that in the iwb2.5 beta and see if it is a problem there.
Thanks for reporting.