using external dll

[m.francescon]

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?

Code Select Expand


/*
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



[LarryMc]

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.

Code Select Expand

/*
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]


[fasecero]

Try this. Still not calling the callback function because the ".help" command.

Code Select Expand


$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


[acelli]

So are you seeking some iwBasic code examples that allow you to create/update using the sqlite3.dll ?

Let me know,
Alan

[m.francescon]

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

[LarryMc]

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.

[m.francescon]

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

[LarryMc]

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.