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

run SQL queries against a ListView

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

  • PBWin run SQL queries against a ListView

    comments here.

    Code:
    ' SQLite stuff
    ' a little program to show how a SQL query can be run against a list view
    ' it opens a memory database (nothing on disk)
    ' creates a table like the List View (no duplicate column names or spaces thank you!)
    ' runs the query to create a SQLite result set
    ' populates another List View with the query results
    '
    ' you will need the SQLite3.dll file from sqlite.org
    ' Chris Holbrook Jan 29 2011
    '
    #compile exe
    #dim all
    
    #include "WIN32API.INC"
    #include "COMMCTRL.INC"
    '--------------------------------------------------------------------------
    declare function sqlite3_open lib "SQLITE3.DLL" alias "sqlite3_open" ( byref asciiz, byref dword ) as long
    declare function sqlite3_close lib "SQLITE3.DLL" alias "sqlite3_close" ( byval dword ) as long
    declare sub sqlite3_free_table lib "SQLITE3.DLL" alias "sqlite3_free_table" ( byval dword )
    declare sub sqlite3_free lib "SQLITE3.DLL" alias "sqlite3_free" ( byval dword )
    declare function sqlite3_get_table lib "SQLITE3.DLL" alias "sqlite3_get_table" ( _
       byval dword, _                      ' database handle
       byref asciiz, _                     ' ptr to SQL statements as an asciz string
       byref dword, _                      ' pointer to results table pointer variable
       byref long, _                       ' ptr to nrows variable
       byref long, _                       ' ptr to ncolumn variable
       byref dword _                       ' ptr to error messaeg pointer variable
       ) as long                           ' error code - zero is success
    '=========================================================
    ' Query Vars - needed in each function/sub which uses mQuery etc
    '=========================================================
    macro mQVars()
        local hDB as dword                  ' database handle
        local presults as dword             ' ptr to results pointer array
        local psz() as asciz ptr            ' mapping of results ptr array 1D
        local p2d() as asciz ptr            ' mapping of results ptr array 2D
        local lresult, nrows, ncols as long ' error#, rows in results, cols in results
        local pzer as asciz ptr             ' ptr to error msg
        local SQL as string                 ' SQL code executed by mQuery
    end macro
    ' use this version in a window callback proc if the action is spread amongst different
    ' message handlers
    macro mQVarsStatic()
        static hDB as dword                  ' database handle
        static presults as dword             ' ptr to results pointer array
        static psz() as asciz ptr            ' mapping of results ptr array
        static lresult, nrows, ncols as long ' error#, rows in results, cols in results
        static pzer as asciz ptr             ' ptr to error msg
        static SQL as string                 ' SQL code executed by mQuery
        static sz as asciz * %max_path       ' to contain listview 'cell' data
    end macro
    '=========================================================
    ' Post-Query tidy up by freeing the results array which SQLite manages
    '=========================================================
    macro mQdone = if presults then sqlite3_free_table(presults)
    
    '=========================================================
    ' Query macro creates SQLite result array of asciz ptrs
    '=========================================================
    macro mQuery(hDB)
        if presults then sqlite3_free_table(presults)
    
        lresult = SQLite3_Get_Table( byval hDB, byval strptr(SQL), presults, _
                                             nRows, nCols, byval varptr(pzer) )
        if lresult <> 0 then
            ? funcname$ & ": SQLite error:" + @pzer, %mb_systemmodal, "Error!"
            sqlite3_free(pzer)
            exit macro
        else
            if ncols then
                redim psz(0 to (nrows +1)* ncols -1) at presults ' 1D array
                redim p2d(1 to ncols, 1 to nrows) at presults    ' 2D array
            end if
        end if
    
    end macro
    '------------------------------------------------------------------
    
    %IDC_LVSOURCE       = 1001
    %IDC_SQLTEXT        = 1002
    %IDC_QUERY_BN       = 1003
    %IDC_INFO_LABEL     = 1005
    %IDC_LVDEST         = 1006
    
    '------------------------------------------------------------------
    callback function mainDlgProc()
        local s as string
        local i, j, lstyle, n, w as long
        static nLVcols, nLVrows as long
        mQvars ' <================= macro to declare variables used in query
        '
        select case as long cbmsg
            case %wm_initdialog
                ' initialise and load data to the source List View
                nLVcols = 3: nLVrows = 100
                listview get stylexx cbhndl, %IDC_LVSOURCE to lstyle
                listview set stylexx cbhndl, %IDC_LVSOURCE, lStyle or %lvs_ex_fullrowselect or %lvs_ex_gridlines
    
                for i = 1 to nLVCols
                    select case as long i
                        case 1: w = 75
                        case 2: w = 120
                        case 3: w = 75
                    end select
                    listview insert column cbhndl, %IDC_LVSOURCE, i, parse$("Name, selection, menu", i), w, 0
                next
                for i = 1 to nLVrows
                    listview insert item cbhndl, %IDC_LVSOURCE, i, 0, parse$("you,tarzan,me,jane",rnd(1,4))
                    listview set text    cbhndl, %IDC_LVSOURCE, i, 2, parse$("artichoke,boiled cabbage,crispy noodles,dog,egg fried rice,fur lined boots,golf balls,ham",rnd(1,8))
                    listview set text    cbhndl, %IDC_LVSOURCE, i, 3, parse$("childrens,special,main",rnd(1,3))
                next
                ' set up info label
                s = "The top List View contains the SOURCE data" + $crlf + _
                    "The text box contains a query, replace it with yours" + $crlf + _
                    "Click the button" + $crlf + _
                    "Results are shown in the lower List View" + $crlf + _
                    "" + $crlf + _
                    "How it works: a memory database table called MYTAB is created" + $crlf + _
                    "with column names the same as the source List View." + $crlf + _
                    "The data in the List View is copied into the table." + $crlf + _
                    "Your query is run against this table." + $crlf + _
                    "No disk files other than SQLite3.dll are required, and none created." + $crlf + _
                    "" + $crlf + _
                    "It doesn't handle duplicate column names!"
                control set text cbhndl, %IDC_INFO_LABEL, s
                
            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_QUERY_BN
                        ' reset the destination listview
                        listview reset cbhndl, %IDC_LVDEST
                        for i = 1 to nLVcols
                            listview delete column cbhndl, %IDC_LVDEST, i
                        next
                        listview get stylexx cbhndl, %IDC_LVDEST to lstyle
                        listview set stylexx cbhndl, %IDC_LVDEST, _
                                 lStyle or %lvs_ex_fullrowselect or %lvs_ex_gridlines
                        ' extract column names from listview and construct create table statement
                        for i = 1 to nLVcols
                            listview get header cbhndl, %IDC_LVSOURCE, i to S
                            sql += "," + s
                        next
                        SQL = "create table mytab(" + mid$(sql,2) + ")"
                        ? sql
                        sqlite3_open(":memory:",hDB)
                        mQuery(hDB) ' <=========== run the query in SQL string, report errors
                        mQdone ' <==== macro to return results buffer to SQLite
                        ' now insert dat from the source listview into the table
                        listview get count cbhndl, %IDC_LVSOURCE to nLVrows
                        for i = 1 to nLVrows
                            sql = ""
                            for j = 1 to nLVcols
                                listview get text cbhndl, %IDC_LVSOURCE, i, j to s
                                sql += "," + $sq + s + $sq
                            next
                            sql = "insert into mytab values(" + mid$(sql,2) + ")"
                            mQuery(hDB) ' <=========== run the query in SQL string, report errors
                            if lresult <> 0 then ' error already reported
                                mQdone ' <==== macro to return results buffer to SQLite
                                exit select
                            end if
                        next
                        control get text cbhndl, %IDC_SQLTEXT to SQL
                        mQuery(hDB) ' <=========== run the query in SQL string, report errors
                        if lresult <> 0 then ' error already reported
                            mQdone ' <==== macro to return results buffer to SQLite
                            exit select
                        end if
                        ? sql + $crlf + "query returned "+ format$(ncols) + " columns, " + format$(nrows) + " rows"
                        ' load data from results table to list view
                        for i = 1 to nrows ' first row is headings!
                            for j = 1 to ncols
                                s = @p2D(j, i) ' <========== look up results table by row and column
                                if i = 1 then ' columns labels in row 1
                                    listview insert column cbhndl, %IDC_LVDEST, j, s, 100, 0
                                else
                                    if j = 1 then
                                        listview insert item cbhndl, %IDC_LVDEST, i - 1, 0, s
                                    else
                                        listview set text cbhndl, %IDC_LVDEST, i - 1, j, s
                                    end if
                                end if
                            next
                        next
    
                        mQdone ' <==== macro to return results buffer to SQLite
                end select
        end select
    end function
    '-------------------------------------------------------------------------------
    function mainDlg(byval hParent as dword) as long
        local lRslt, W, H, buttonheight as long
        local hfontbig as dword
        local hDlg  as dword
        buttonheight = 26
        font new "comic sans", 12, 0, 0 to hfontbig
        dialog new pixels, hParent, "SQL Query on a List View" + space$(60) + "Chris Holbrook Jan 2011", 0, 0, 620, 440, _
            %ws_sysmenu or %ds_center or %ws_thickframe or %ws_visible _
            or %ds_3dlook or %ds_nofailcreate or %ds_setfont or %ds_center, _
            %ws_ex_controlparent, to hDlg
        dialog get client hdlg to W, H
        control add listview, hDlg, %IDC_LVSOURCE, "", _
            0, 0, w/2, h/2, _
            %ws_child or %ws_visible or %ws_tabstop or %lvs_report or %lvs_showselalways
        control add textbox, hDlg, %IDC_SQLTEXT, "select * from mytab", _
            w/2, 0, w/2, h/2 - buttonheight, _
            %es_wantreturn or %es_multiline
        control set font hDlg, %IDC_SQLTEXT, hfontbig
        control add button, hDlg, %IDC_QUERY_BN, "Run Query against List View Data", _
           w/2 , h/2 - buttonheight, w/2, buttonheight
        control add listview, hDlg, %IDC_LVDEST, "", _
            0, h/2, w/2, h/2, _
            %ws_child or %ws_visible or %ws_tabstop or %lvs_report or %lvs_showselalways
        control add label,   hDlg, %IDC_INFO_LABEL, "", _
            w/2, h/2, w/2, h/2
        dialog show modal hDlg, call mainDlgProc to lRslt
        font end hfontbig
        function = lRslt
    end function
    '--------------------------------------------------------------------------
    function pbmain()
        initcommoncontrols
    
        mainDlg %hwnd_desktop
    end function
    Last edited by Chris Holbrook; 29 Jan 2011, 04:13 AM. Reason: add link
Working...
X