Announcement

Collapse

Forum Guidelines

This forum is for finished source code that is working properly. If you have questions about this or any other source code, please post it in one of the Discussion Forums, not here.
See more
See less

very simple SQLite 3.5.7 e.g. with callback

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • very simple SQLite 3.5.7 e.g. with callback

    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
    Last edited by Chris Holbrook; 29 Mar 2008, 11:17 AM. Reason: change title

  • #2
    slightly more complex but nicer looking example using a listview

    This one gets 100 rows at a time from the query and displays them in a listview until the user wants to stop! Contrast with getting the whole 90 zillion rows at once.

    Code:
    '
    ' example of using a callback with SQLite to interrupt
    ' retreival of query results.
    ' Using a listview to display the query results
    ' Chris Holbrook 25 Mar 2008
    '
    #PBFORMS CREATED V1.51
    #COMPILE EXE
    #DIM ALL
    
    #PBFORMS BEGIN INCLUDES
    #IF NOT %DEF(%WINAPI)
        #INCLUDE "WIN32API.INC"
    #ENDIF
    #IF NOT %DEF(%COMMCTRL_INC)
        #INCLUDE "COMMCTRL.INC"
    #ENDIF
    #INCLUDE "PBForms.INC"
    #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_QUERY_LV  = 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 chunks of 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 lrow, limit AS LONG
        LOCAL i         AS LONG
        STATIC hCtl      AS DWORD
        LOCAL tLVC      AS LV_COLUMN
        LOCAL tLVI      AS LV_ITEM
        LOCAL lStyle    AS LONG
        LOCAL lCol   AS LONG
        STATIC sz AS ASCIZ * 1024
    
        REDIM pzColVals (1 TO nCols) AS ASCIIZ PTR AT ColVals
        REDIM pzColNames(1 TO nCols) AS ASCIIZ PTR AT ColNames
    
    
        ' first time thru, capture the column names & set up a listview
        IF lrow = 0 THEN
            limit = 100
             ' Get ListView Handle
            CONTROL HANDLE hD, %IDC_QUERY_LV TO hCtl
            ' clear down any existing columns
            DO WHILE ListView_DeleteColumn(hCtl, 0)
            LOOP
            Listview_DeleteAllItems(hCtl)
            ' set style
            lStyle = ListView_GetExtendedListViewStyle(hCtl)
            ListView_SetExtendedListViewStyle (hCtl, lStyle OR %LVS_EX_GRIDLINES OR %LVS_EX_FULLROWSELECT)
            ' Load column headers.
            tLVC.mask    = %LVCF_FMT OR %LVCF_TEXT OR %LVCF_SUBITEM OR %LVCF_WIDTH
            tLVC.fmt     = %LVCFMT_LEFT
            tLVC.pszText = VARPTR(sz)
            FOR i = 1 TO ncols
                sz = @pzColNames(i)
                tLVC.iOrder = i - 1
                ListView_InsertColumn(hCtl, i - 1 , tLVC)
            NEXT
            ' set column widths
            FOR lcol = 0 TO ncols-1
                ListView_SetColumnWidth (hCtl, lCol, %LVSCW_AUTOSIZE_USEHEADER )
            NEXT
        END IF
        '
        FOR lcol = 0 TO nCols -1
            tLVI.stateMask = %LVIS_FOCUSED
            tLVI.pszText   = VARPTR(sz)
            tLVI.iItem     = lRow
            FOR lcol = 0 TO ncols - 1
                sz = @pzColVals(lcol + 1)
                tLVI.iSubItem = lCol
                tLVI.lParam   = lRow
                IF lCol = 0 THEN
                    tLVI.mask = %LVIF_TEXT OR %LVIF_PARAM OR %LVIF_STATE
                    ListView_InsertItem(hCtl, tLVI)
                ELSE
                    tLVI.mask = %LVIF_TEXT
                    ListView_SetItem(hCtl, tLVI)
                END IF
            NEXT
        NEXT
        '
        INCR lrow
        IF lrow < limit THEN
            FUNCTION = %SQLITE_OK ' keep going!
        ELSE
            IF MSGBOX  ("another 100 rows?", %MB_YESNO, "Choose") = %IDYES THEN
                limit = limit + 100
                FUNCTION = %SQLITE_OK
                EXIT FUNCTION
            END IF
            lrow = 0
            limit = 100
            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 "SysListView32", hDlg, %IDC_QUERY_LV, "", 5, 70, 295, 155, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR _
            %LVS_REPORT, %WS_EX_LEFT 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()
        PBFormsInitComCtls (%ICC_WIN95_CLASSES OR %ICC_DATE_CLASSES OR %ICC_INTERNET_CLASSES)
    
        InitCommonControls
        ShowDIALOG1 %HWND_DESKTOP
    END FUNCTION

    Comment


    • #3
      Using prepare, step &amp; finalize instead of callback

      Code:
      '
      ' run SQLITE3 queries using sqlite3_prepare, sqlite3_stpe and sqlite3_finalize
      ' instead of using sqlite3_exe and a call back.
      '
      ' Chris Holbrook Mar 2008
      #COMPILE EXE
      #DIM ALL
      #INCLUDE "WIN32API.INC"
      ' 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"
      
      %IDD_DIALOG1    =  101
      %IDC_START_BN   = 1001
      %IDC_TEXTBOX1   = 1003
      %IDC_STATS_LAB  = 1005
      %IDC_MORE_BN    = 1006
      %IDC_RESULTS_LB = 1012
      %IDC_STOP_BN    = 1014
      %ROWCHUNKSIZE = 1000
      
      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
      '--------------------------------------------------------------------------------------
      FUNCTION exec_sql( hDB AS DWORD, hD AS DWORD, BYVAL pzSQL AS ASCIZ PTR, _
                         LctlStats AS LONG, lCTLEnough AS LONG, lCtlMore AS LONG, lCtlLB AS LONG ) AS LONG
      
          LOCAL lresult           AS LONG: lresult = %SQLITE_OK
          LOCAL pzTail            AS ASCIZ PTR
          LOCAL pzPrepared        AS ASCIZ PTR
          LOCAL pzStmt            AS ASCIZ PTR
          LOCAL pzErmsg           AS ASCIZ PTR
          LOCAL azcols()          AS ASCIZ PTR      ' array of dword pointers to column name strings
          LOCAL azvals()          AS ASCIZ PTR      ' array of dword pointers to column values
          LOCAL ncols             AS LONG           ' the column count
          LOCAL nErmsg            AS LONG
          LOCAL szErmsg           AS ASCIZ * 512
          LOCAL nRetry            AS LONG
          LOCAL s                 AS STRING
          LOCAL i, l              AS LONG
          LOCAL lrowcount, lrowlimit         AS LONG: lrowlimit = %ROWCHUNKSIZE
      
           IF @pzSQL = "" THEN
               FUNCTION = %SQLITE_OK
               EXIT FUNCTION ' called with null SQL statement
           END IF
           lresult = sqlite3_prepare ( hDB, @pzSQL, -1, pzPrepared, pzTail)
           IF lresult <> %SQLITE_OK THEN GOTO finish
           IF pzPrepared = 0 THEN GOTO finish
           ncols = sqlite3_column_count(pzPrepared)
           REDIM azvals(0 TO ncols-1)
           REDIM azcols(0 TO ncols-1)
           l =  2 * nCols * 4 ' 4 = SIZEOF(DWORD)
           INCR l
               DO WHILE 1
                  lresult = sqlite3_step(pzPrepared)
                  FOR i = 0 TO ncols -1
                      azcols(i) = sqlite3_column_name(pzPrepared, i)
                  NEXT
                  s = ""
                  SELECT CASE lresult
                      CASE %SQLITE_ROW
                          FOR i = 0 TO ncols - 1
                              azvals(i) = sqlite3_column_text(pzPrepared, i)
                              s = s + @azvals(i) + ","
                          NEXT
                          LISTBOX ADD hD, lCTLLB, s
                          INCR lrowcount
                          ' if the limit is exceeded,
                          IF lrowcount > lrowLimit THEN
                              IF %IDYES = MSGBOX (STR$(lrowcount -1) + " rows retrieved, get some more?", %MB_YESNO) THEN
                                  lrowlimit = lrowlimit + %ROWCHUNKSIZE
                              ELSE
                                  GOTO finish
                              END IF
                          END IF
                      CASE %SQLITE_DONE
                          INCR lrowcount
                          GOTO Finish
                      CASE ELSE
                          ? "unexpected result = " + STR$(lresult)
                          GOTO finish
                  END SELECT
              LOOP
          '
      Finish:
          CONTROL SET TEXT hD, lCtlStats, STR$(lrowcount-1) + " rows fetched from database"
          IF pzPrepared <> 0 THEN sqlite3_finalize(pzPrepared)
          IF lresult <> %SQLITE_OK THEN
              lresult = sqlite3_errcode(hDB)
              pzErmsg = sqlite3_errmsg(hDB)
          END IF
          FUNCTION = lresult
      END FUNCTION
      '-------------------------------------------------------------------------
      CALLBACK FUNCTION ShowDIALOG1Proc()
          LOCAL sSQL AS STRING
          LOCAL lresult, n AS LONG
          LOCAL pzerms AS ASCIZ PTR
          STATIC hchilddlg AS DWORD
          LOCAL   hQF             AS DWORD
      
          SELECT CASE AS LONG CBMSG
              CASE %WM_INITDIALOG
                  open_database(CBHNDL)
      
              CASE %WM_COMMAND
                  SELECT CASE AS LONG CBCTL
      
                      CASE %IDC_START_BN
                          IF CBCTLMSG = %BN_CLICKED OR CBCTLMSG = 1 THEN
                              CONTROL SET TEXT CBHNDL, %IDC_STATS_LAB, ""
                              LISTBOX RESET CBHNDL, %IDC_RESULTS_LB
                              CONTROL GET TEXT CBHNDL, %IDC_TEXTBOX1 TO sSQL
                              exec_sql( ghDB, CBHNDL, BYVAL STRPTR(sSQL), %IDC_STATS_LAB,_
                                        %IDC_STOP_BN, %IDC_MORE_BN,%IDC_RESULTS_LB)
                              sqlite3_free(BYVAL pzErms)' free error message memory
                          END IF
                      CASE %IDC_TEXTBOX1
                          CONTROL SET TEXT CBHNDL, %IDC_STATS_LAB, ""
                          LISTBOX RESET CBHNDL, %IDC_RESULTS_LB
      
                  END SELECT
              CASE %WM_DESTROY
                  sqlite3_close(ghDB)
      
          END SELECT
      END FUNCTION
      '-----------------------------------------------------------------------
      FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
          LOCAL lRslt AS LONG
          LOCAL hDlg  AS DWORD
      
          DIALOG NEW hParent, "SQLITE3.5.7 example (Jose Roca's 3.5.6 headers),  without callback", 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_START_BN, "Start Query", 245, 5, 55, 15
          CONTROL ADD TEXTBOX, hDlg, %IDC_TEXTBOX1, "Enter query here", 5, 5, 240, 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
          CONTROL ADD LISTBOX, hDlg, %IDC_RESULTS_LB, , 0, 70, 455, 145, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %WS_HSCROLL OR _
              %WS_VSCROLL, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
      
          DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
      
          FUNCTION = lRslt
      END FUNCTION
      '============================================================================
      FUNCTION PBMAIN()
      
          InitCommonControls
          ShowDIALOG1 %HWND_DESKTOP
      END FUNCTION

      Comment


      • #4
        thx
        Last edited by Dean Gwilliam; 20 Jun 2011, 07:39 AM.

        Comment


        • #5
          yr wlcm

          Comment


          • #6
            Following discussion of SQLite elsewhere, I thought it would be best to bring the up to date, not only has SQLite gone from v3.7 to v 3.27, but I have become better at spotting "own goals" in my code:

            Code:
            '
            ' run SQLITE3 queries using sqlite3_prepare, sqlite3_stpe and sqlite3_finalize
            ' instead of using sqlite3_exe and a call back.
            '
            ' Chris Holbrook Mar 2008
            ' 3 April 2019 fixed useless error reporting
            '              adjusted font size to eyesight
            '              centred dialog
            '              tested with sqlite3.dll v3.27.2
            #compile exe
            #dim all
            #include "WIN32API.INC"
            ' get the DLL from http://www.sqlite.org/download.html, tested with v3.27.2 32-bit
            
            ' 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
            ' NB Jose probably has a later version, but as this app uses only
            ' about 10 functions from the library, you can easily
            ' write your own headers for these based on info from sqlite.org
            ' instead of using Jose's headers.
            
            #include "sqlite3.inc"
            
            %IDD_DIALOG1    =  101
            %IDC_START_BN   = 1001
            %IDC_TEXTBOX1   = 1003
            %IDC_STATS_LAB  = 1005
            %IDC_MORE_BN    = 1006
            %IDC_RESULTS_LB = 1012
            %IDC_STOP_BN    = 1014
            %ROWCHUNKSIZE = 1000
            
            global ghDB as dword, pzerms as asciz ptr
            ' 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_hidereadonly   ' or %ofn_filemustexist
                Buf   = "Database files (*.SDB)|*.SDB|"
                'spath = gddlpath
            '    if OpenFileDialog (hD, "Locate SDB file ", sfile, spath, buf, "SDB", dwstyle) = 0 then
            '       exit function ' returning null
                display openfile hd, 50, 50, "create or select a database file","",_
                        "SQLite database file" + chr$(0) + "*.SDB" + chr$(0),"","SDB", dwstyle to sfile
                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
            '--------------------------------------------------------------------------------------
            function exec_sql( hDB as dword, hD as dword, byval pzSQL as asciz ptr, _
                               LctlStats as long, lCTLEnough as long, lCtlMore as long, lCtlLB as long ) as long
            
                local lresult           as long: lresult = %SQLITE_OK
                local pzTail            as asciz ptr
                local pzPrepared        as asciz ptr
                local pzStmt            as asciz ptr
                local azcols()          as asciz ptr      ' array of dword pointers to column name strings
                local azvals()          as asciz ptr      ' array of dword pointers to column values
                local ncols             as long           ' the column count
                local nRetry            as long
                local s                 as string
                local i, l              as long
                local lrowcount, lrowlimit         as long: lrowlimit = %ROWCHUNKSIZE
            
                 local sdebug as string
                 sdebug = @pzsql
                 if @pzSQL = "" then
                     function = %SQLITE_OK
                     exit function ' called with null SQL statement
                 end if
                 lresult = sqlite3_prepare ( hDB, @pzSQL, -1, pzPrepared, pzTail)
                 if lresult <> %SQLITE_OK then goto finish
                 if pzPrepared = 0 then goto finish
                 ncols = sqlite3_column_count(pzPrepared)
                 redim azvals(0 to ncols-1)
                 redim azcols(0 to ncols-1)
                 l = 2 * nCols * 4 ' 4 = SIZEOF(DWORD)
                 incr l
                     do while %true
                        lresult = sqlite3_step(pzPrepared)
                        for i = 0 to ncols -1
                            azcols(i) = sqlite3_column_name(pzPrepared, i)
                        next
                        s = ""
                        select case lresult
                            case %SQLITE_ROW
                                for i = 0 to ncols - 1
                                    azvals(i) = sqlite3_column_text(pzPrepared, i)
                                    s = s + @azvals(i) + ","
                                next
                                listbox add hD, lCTLLB, s
                                incr lrowcount
                                ' if the limit is exceeded,
                                if lrowcount > lrowLimit then
                                    if %idyes = msgbox (str$(lrowcount -1) + " rows retrieved, get some more?", %mb_yesno) then
                                        lrowlimit = lrowlimit + %ROWCHUNKSIZE
                                    else
                                        goto finish
                                    end if
                                end if
                            case %SQLITE_DONE
                                incr lrowcount
                                goto Finish
                            case else
                                ? "unexpected result = " + str$(lresult)
                                goto finish
                        end select
                    loop
                '
            Finish:
                if lresult = %SQLITE_ERROR then
                    lresult = sqlite3_errcode(hDB)
                    pzErms = sqlite3_errmsg(hDB)
                else
                    control set text hD, lCtlStats, str$(lrowcount-1) + " rows fetched from database"
                    if pzPrepared <> 0 then sqlite3_finalize(pzPrepared)
                    lresult = %SQLITE_OK
                end if
                function = lresult
            end function
            '-------------------------------------------------------------------------
            callback function ShowDIALOG1Proc()
                local sSQL as string
                local lresult, n as long
                static hchilddlg as dword
                local   hQF             as dword
            
                select case as long cbmsg
                    case %wm_initdialog
                        open_database(cbhndl)
            
                    case %wm_command
                        select case as long cbctl
            
                            case %IDC_START_BN
                                if cbctlmsg = %bn_clicked or cbctlmsg = 1 then
                                    control set text cbhndl, %IDC_STATS_LAB, ""
                                    listbox reset cbhndl, %IDC_RESULTS_LB
                                    control get text cbhndl, %IDC_TEXTBOX1 to sSQL
                                    lresult = exec_sql( ghDB, cbhndl, byval strptr(sSQL), %IDC_STATS_LAB,_
                                              %IDC_STOP_BN, %IDC_MORE_BN,%IDC_RESULTS_LB)
                                    if lresult <> %SQLITE_OK then
                                        local sdebug as string
                                        sdebug = @pzerms
                                        ?  str$(lresult) + ": " + @pzerms , ,"SQLite error"
                                        sqlite3_free(byval pzErms)' free error message memory
                                    end if
            
                                end if
                            case %IDC_TEXTBOX1
                                control set text cbhndl, %IDC_STATS_LAB, ""
                                listbox reset cbhndl, %IDC_RESULTS_LB
            
                        end select
                    case %wm_destroy
                        sqlite3_close(ghDB)
            
                end select
            end function
            '-----------------------------------------------------------------------
            function ShowDIALOG1(byval hParent as dword) as long
                local lRslt as long
                local hDlg  as dword
                local X, Y, W, H as long
                local T1, t2, t3 as long
            
                dialog font "COMIC SANS MS", 10
                desktop get client to W, H
                X = W * 0.125!: W *=0.75!: Y = H*0.125!: H *= 0.75!
                T1 = 10: T2 = W*0.40!: T3 = W*0.55!
                dialog new pixels, hParent, "SQLITE3.5.7 example (Jose Roca's 3.5.6 headers),  without callback", X, Y, W, H, %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 textbox, hDlg, %IDC_TEXTBOX1, "Enter query here", T1, 5, W*0.35!, 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 button,  hDlg, %IDC_START_BN, "Start Query", T2, 5, W*0.12!, 25
                control add label,   hDlg, %IDC_STATS_LAB, "", T3, 5, W - T3, 60, %ws_child or %ws_visible or %ss_left or %ss_sunken, _
                    %ws_ex_left or %ws_ex_ltrreading
                control add listbox, hDlg, %IDC_RESULTS_LB, , T1, 70, W-2*T1, H*0.90!, %ws_child or %ws_visible or %ws_tabstop or %ws_hscroll or _
                    %ws_vscroll, %ws_ex_clientedge or %ws_ex_left or %ws_ex_ltrreading or %ws_ex_rightscrollbar
            
                dialog show modal hDlg, call ShowDIALOG1Proc to lRslt
            
                function = lRslt
            end function
            '============================================================================
            function pbmain()
            
                InitCommonControls
                ShowDIALOG1 %hwnd_desktop
            end function

            Comment


            • #7
              'wrong thread, admin please delete
              How long is an idea?

              Comment

              Working...
              X