Tweet
Code:
'
' very simple SQLite example to show use of sqlite callback proc
' Chris Holbrook 26 Mar 2008
'
#PBFORMS CREATED V1.51
#COMPILE EXE
#DIM ALL
#PBFORMS BEGIN INCLUDES
#IF NOT %DEF(%WINAPI)
#INCLUDE "WIN32API.INC"
#ENDIF
#PBFORMS END INCLUDES
' get the DLL from http://www.sqlite.org/download.html, tested with v3.5.7
' members of the Jose Roca software forums can get the 3.5.6 include file
' from http://www.jose.it-berater.org/smfforum/index.php?topic=1604.msg5547
#INCLUDE "sqlite3.inc"
'
#INCLUDE "commctrl.inc"
#INCLUDE "comdlg32.inc"
#PBFORMS BEGIN CONSTANTS
%IDD_DIALOG1 = 101
%IDC_BUTTON1 = 1001
%IDC_TEXTBOX1 = 1003
%IDC_RESULTS_LB = 1002
%IDC_STATS_LAB = 1005
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
GLOBAL ghDB AS DWORD
' mustn't call sqlite3_close on a closed DB, hence this Global:
GLOBAL gDBOPEN AS LONG ' 0 = closed 1 = open
'--------------------------------------------------------------
FUNCTION SelDBFile (hD AS DWORD) AS STRING
LOCAL buf, spath, sfile AS STRING
LOCAL dwstyle AS DWORD
LOCAL hFile AS LONG
'------------------------ get database file
dwStyle = %OFN_EXPLORER OR %OFN_FILEMUSTEXIST OR %OFN_HIDEREADONLY
Buf = "Database files (*.SDB)|*.SDB|"
'spath = gddlpath
IF OpenFileDialog (hD, "Locate SDB file ", sfile, spath, buf, "SDB", dwstyle) = 0 THEN
EXIT FUNCTION ' returning null
END IF
FUNCTION = sfile
END FUNCTION
'------------------------------------------------------------------------------
' open database
SUB Open_Database ( hd AS DWORD )
LOCAL sz AS ASCIZ * 512
' can only call sqlite3_close once!
IF gDBOPEN = 1 THEN sqlite3_close(ghDB) ' in case one is already open
' choose a DB
sz = SelDBFile(hD)
IF sqlite3_open( sz, BYREF ghDB) <> %SQLITE_OK THEN
? "unable to open database", %mb_applmodal,"Warning"
gDBOPEN = 0
EXIT SUB
END IF
gDBOPEN = 1
END SUB
'-------------------------------------------------------------------------------
' SQLITE CALLBACK FUNCTION
' It is called from sqlite3_exec i.e. inside SQLite. It MUST be declared with CDECL
' the 1st param is supplied by the 4th param to call to sqlite3_exec - see below
' 2nd param is the number of columns in the result set (NBV result set is for a single row)
' 3rd param is an array of pointers to ASCIZ strings containing the column data values
' 4th param is an array of pointers to ASCIZ strings containing the column names
'
' because this proc is called for every row returned by the query, it can be used
' both to extract data and to limit the size of the query, or allocate memory, etc.
' here it is being used to limit the number of rows returned to a maximum of 100.
'
FUNCTION sqlite_callback CDECL( BYVAL hD AS DWORD, BYVAL nCols AS LONG, BYVAL ColVals AS DWORD, _
BYVAL ColNames AS DWORD ) AS LONG
STATIC count AS LONG
LOCAL i AS LONG
REDIM pzColVals (1 TO nCols) AS ASCIIZ PTR AT ColVals
REDIM pzColNames(1 TO nCols) AS ASCIIZ PTR AT ColNames
FOR i = 1 TO nCols
LISTBOX ADD hD, %IDC_RESULTS_LB, STRING$(i-1, $TAB) + "<" + TRIM$(@pzColNames(i) ) + ">" + TRIM$(@pzColVals(i))
NEXT
INCR count
IF count < 100 THEN
FUNCTION = %SQLITE_OK ' keep going!
ELSE
count = 0
FUNCTION = 1 ' finish the query
END IF
END FUNCTION
'-------------------------------------------------------------------------------
CALLBACK FUNCTION ShowDIALOG1Proc()
LOCAL sSQL AS STRING
LOCAL lresult, n AS LONG
LOCAL pzerms AS ASCIZ PTR
SELECT CASE AS LONG CBMSG
CASE %WM_INITDIALOG
open_database(CBHNDL)
CASE %WM_NCACTIVATE
STATIC hWndSaveFocus AS DWORD
IF ISFALSE CBWPARAM THEN
hWndSaveFocus = GetFocus()
ELSEIF hWndSaveFocus THEN
SetFocus(hWndSaveFocus)
hWndSaveFocus = 0
END IF
CASE %WM_COMMAND
SELECT CASE AS LONG CBCTL
CASE %IDC_BUTTON1
IF CBCTLMSG = %BN_CLICKED OR CBCTLMSG = 1 THEN
LISTBOX RESET CBHNDL, %IDC_results_lb
CONTROL SET TEXT CBHNDL, %IDC_STATS_LAB, ""
CONTROL GET TEXT CBHNDL, %IDC_TEXTBOX1 TO sSQL
lresult = sqlite3_exec ( ghDB, BYVAL STRPTR(sSQL), CODEPTR(Sqlite_CallBack), BYVAL CBHNDL, pzErms)
CONTROL SET TEXT CBHNDL, %IDC_STATS_LAB, "result = " + STR$(lresult)+ $CRLF + @pzErms
END IF
CASE %IDC_TEXTBOX1
LISTBOX RESET CBHNDL, %IDC_results_lb
CONTROL SET TEXT CBHNDL, %IDC_STATS_LAB, ""
END SELECT
CASE %WM_DESTROY
sqlite3_close(ghDB)
END SELECT
END FUNCTION
'-----------------------------------------------------------------------
FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
LOCAL lRslt AS LONG
#PBFORMS BEGIN DIALOG %IDD_DIALOG1->->
LOCAL hDlg AS DWORD
DIALOG NEW hParent, "SQLITE3.5.7 example (Jose Roca's 3.5.6 headers)", 175, 118, 457, 230, %WS_POPUP OR %WS_BORDER OR _
%WS_DLGFRAME OR %WS_SYSMENU OR %WS_CLIPSIBLINGS OR %WS_VISIBLE OR %DS_MODALFRAME OR %DS_3DLOOK OR %DS_NOFAILCREATE OR _
%DS_SETFONT, %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR, TO hDlg
CONTROL ADD BUTTON, hDlg, %IDC_BUTTON1, "Execute Query", 310, 195, 65, 25
CONTROL ADD LISTBOX, hDlg, %IDC_RESULTS_LB, , 5, 70, 295, 155, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %WS_VSCROLL, _
%WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
CONTROL ADD TEXTBOX, hDlg, %IDC_TEXTBOX1, "Enter query here", 5, 5, 295, 60, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR _
%ES_LEFT OR %ES_MULTILINE OR %ES_AUTOHSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
%WS_EX_RIGHTSCROLLBAR
CONTROL ADD LABEL, hDlg, %IDC_STATS_LAB, "", 305, 5, 150, 60, %WS_CHILD OR %WS_VISIBLE OR %SS_LEFT OR %SS_SUNKEN, _
%WS_EX_LEFT OR %WS_EX_LTRREADING
#PBFORMS END DIALOG
DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
#PBFORMS BEGIN CLEANUP %IDD_DIALOG1
#PBFORMS END CLEANUP
FUNCTION = lRslt
END FUNCTION
'============================================================================
FUNCTION PBMAIN()
InitCommonControls
ShowDIALOG1 %HWND_DESKTOP
END FUNCTION
Comment