No announcement yet.

Another SQLite Query to Listview PB8 or PB9 comments

  • Filter
  • Time
  • Show
Clear All
new posts

  • Another SQLite Query to Listview PB8 or PB9 comments

    Source code is over here.

    I posted the original of this in this thread. Since then I have added more comments, converted it to compile with PB8 (Classic) and now I'm converting it to 64-bit Pascal. Sorry, I just wanted to say "64-bit". There, I've done it again.

    It compiles with PB9 too of course.

    To run it you will need to download SQLite3.dll from

    Maybe you are just thinking about starting with SQLite. Rather than ask you to download the whole header from Jose Roca's website I have prototyped the few functions required in the source code. If you do much with SQLite you will want to download those heades anyway, because they contain much excellent documentation as well as Jose's translation of the C++ headers.
    Last edited by Chris Holbrook; 16 Jan 2011, 03:46 PM. Reason: add link

  • #2
    See post #5
    Last edited by Mike Doty; 7 Feb 2019, 09:26 AM. instead of google


    • #3
      lparam points to NMLVDISPINFOA UDT (structure) variable, not nmhdr.
      .hdr there contains .code


      Slow edit from when I first posted (just in case someone else posted in mean time)

      Or, figure it from scratch again because it has me confused too. says lparam is pointer to nmhdr.

      Instead of deleteing, I'll just say good luck.


      • #4
        Unicode=1 should not be used. See Dave's fix in next post #5 instead of google


        • #5
          That is a problem with PBWin10 creating Unicode Common Controls (eg ListView) - the wide version (%LVN_GetDispinfoW) of the notification is sent.

          Normally you can add %Unicode=1 to the source and use wide strings. That doesn't seem to work in this instance - maybe SQLite needs Ansi strings?

          The other 'fix' (which does work) is to force the ListView to use Ansi character mode by adding the following line, just after the Control Add "syslistview32" statement..
          SendMessage (GetDlgItem(hDlg, 1001), %LVM_SETUNICODEFORMAT, 0, 0)
          Rgds, Dave


          • #6
            SQLite with PBWin10
            Thanks Dave! for the fix.
            SendMessage (GetDlgItem(hDlg, 1001), %LVM_SETUNICODEFORMAT, 0, 0) after the Control Add "syslistview32" statement

            Thanks Chris Holbrook for all the code!

            PB10 version with the fix (see post #5 above that is applied here.)
            Great addition to library.

            To use SQLite requires SQLite3.DLL
            Select the Precompiled Binaries for Windows current 32-bit DLL
            As of 2/7/19 this is the current version of SQLite3.DLL 3.26.0 (2018-12-01)

            Amazing how little code if LISTVIEW is not needed.

            ' Q2LV.bas Query to listview - any query to a List View
            ' Chris Holbrook Easter 2010
            ' small changes and more comments Jan 2011
            #COMPILE EXE
            #DIM ALL
            REM %UNICODE=1   'was not in original code and is not correct remove this line 2/7/19 at 9:17 AM
            #INCLUDE "WIN32API.INC"
            #INCLUDE ""
            ' SQLite constants
            %SQLITE_OK = 0
            ' SQLite code declarations
            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 - used in the dialog callback proc
            ' You need to declare these vars by name in any function/sub
            ' which uses mQuery and mQdone. Usually you can declare them as LOCAL
            ' but that would not work in a dialog callback proc.
            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
                    STATIC hlv   AS DWORD                ' listview handle
                    LOCAL pLVDI  AS LV_DISPINFO PTR      ' for the OWNERDATA / LVN_GETDISPINFO stuff
            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 returning SQLite result array of asciz ptrs
            ' SQLite returns an array of ncols x nrows asciz pointers
            ' each one of these points to the string representation of
            ' the contents of that row, column in the database.
            ' The memory containing the results array and the associated data
            ' is allocated by SQlite and freed when no longer required
            ' in response to sqlite_free_table (see macro above)
            ' there is no need to put this data into a table, unless you
            ' want it to be more persistent that the SQLite memory. You
            ' can just refer to it via the pointer - that is how the macro
            ' mdispinfo does it.
            ' The mQuery macro redims the results pointer array with a
            ' single dimension. There is no reason why you should not
            ' redim it again as a 2D array of column then row after calling the
            ' macro.
            MACRO mQuery(hDB)
                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!"
                    EXIT MACRO
                    IF ncols THEN REDIM psz(0 TO (nrows +1)* ncols -1) AT presults
                END IF
                '? using$("nrows #  ncols #",nrows,ncols)
                '? join$(psz(),$CR)
            END MACRO
            ' set up listview columns from an array of pointers to asciz text
            SUB LVStart ( hLV AS DWORD, BYREF pColNames AS DWORD, ncols AS LONG, nrows AS LONG)
                LOCAL psz()  AS ASCIZ PTR
                LOCAL tLVC   AS LV_COLUMN
                LOCAL tLVI   AS LV_ITEM
                LOCAL sz AS ASCIZ * 64
                LOCAL i, l AS LONG
                LOCAL hctl AS DWORD
                REDIM pSz(0 TO ncols-1) AS LOCAL ASCIZ PTR AT pColnames
                    ' Get ListView Handle
                l = ListView_GetExtendedListViewStyle(hLV)
                ListView_SetExtendedListViewStyle (hLV, l OR %LVS_EX_GRIDLINES OR %LVS_EX_FULLROWSELECT)
                tLVC.fmt     = %LVCFMT_LEFT
                tLVC.pszText = VARPTR(sz)
                ' Insert column headers
                FOR i = 0 TO ncols -1
                    sz = @pSz(i)
                    tLVC.iOrder = i
                    l = ListView_InsertColumn(hLV, i , tLVC)
            ' set column widths
                FOR i = 0 TO ncols - 1
                    ListView_SetColumnWidth(hLV, i, %LVSCW_AUTOSIZE_USEHEADER)
                listview_setitemcountex(hLV, nrows+1, %LVSICF_NOINVALIDATEALL )
            END SUB
                ' declare query variables
                mQvarsStatic() '<====================== MACRO!! constant re-useable code!
                LOCAL COL, ROW, l AS LONG
                LOCAL s                 AS STRING
                LOCAL pnmh AS NMHDR PTR
                    CASE %WM_INITDIALOG
                        ' open the database connection
                        ' create a table and populate it, then select data from it
                        sql = "begin transaction;" + _
                              "drop table if exists membership;" + $CRLF + _
                              "create table membership (member, date, annualsubs, payments);" +  $CRLF + _
                              "insert into membership values ('Fred',    '10-APR-2010', 123, 54);" + $CRLF +  _
                              "insert into membership values ('James',   '10-APR-2010', 123, 0);" + $CRLF +  _
                              "insert into membership values ('Molly',   '10-MAY-2010', 123, 0);" + $CRLF +  _
                              "insert into membership values ('Angus',   '10-APR-2010',  90, 10);" + $CRLF +  _
                              "insert into membership values ('Patrick', '10-JUN-2010', 123, 0);" + $CRLF +  _
                              "select member, date ""date due"", annualsubs - payments ""amount due"" from membership" +  _
                              " where member <> 'Angus' order by member;" + _
                              "commit transaction;"
                        ' ask SQlite to run the query
                        ? sql
                        mQuery (hDB) '<====================== MACRO - constant re-usable code!!
                        IF lresult <> %SQLITE_OK THEN
                            ? "Error",,"INITDIALOG"
                            EXIT SELECT
                        END IF
                        CONTROL HANDLE CBHNDL, 1001 TO hLV
                        LVStart hLV, BYREF VARPTR(psz(0)), ncols, nrows   ' set up the listview from the query results
                    CASE %WM_NOTIFY
                        SELECT CASE AS LONG CBCTL
                            CASE 1001
                            pnmh = CBLPARAM 'nmcode
                            SELECT CASE AS LONG @pnmh.code
                              CASE %LVN_GETDISPINFO
                                    pLVDI = CBLPARAM
                                    COL = @pLVDI.item.iSubItem
                                    ROW = @pLVDI.item.iitem + 1
                                    IF ROW > nrows THEN EXIT SELECT
                                    IF COL > ncols THEN EXIT SELECT
                                    @pLVDI.item.pszText = VARPTR(sz)
                                    IF (@pLVDI.item.mask AND %LVIF_TEXT) THEN
                                       @pLVDI.item.pszText = psz(((@pLVDI.item.iitem + 1)* ncols) + @pLVDI.item.iSubItem)
                                      '@pLVDI.item.pszText = psz(((@pLVDI.item.iitem + 1)* ncols) + @pLVDI.item.iSubItem)
                                    END IF
                            END SELECT
                        END SELECT
                    CASE %WM_DESTROY
                        'return results set memory to SQLite
                        mQdone   '<=================================== MACRO!!
                END SELECT
            END FUNCTION
                LOCAL hDlg AS DWORD
                DIALOG NEW PIXELS, 0, "SQL LV", , , 300, 300, %WS_SYSMENU,, TO hDlg
                CONTROL ADD "syslistview32", hDlg, 1001, "", 0, 0, 300, 280, _
                SendMessage (GetDlgItem(hDlg, 1001), %LVM_SETUNICODEFORMAT, 0, 0)  'added this 2/7/19 9:11 AM  Thanks Dave Biggs!
                DIALOG SHOW MODAL hDlg, CALL CBProc
            END FUNCTION
   instead of google


            • #7
              I just spotted this! Really should look in more often.

              Thanks to Dave Biggs for the fix.

              Mike Doty, SQLite is great but sqlite3_get_table. etc are deprecated and replacing them does add a bit of code!