Announcement

Collapse
No announcement yet.

Help with 2 SDK controls

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

  • #61
    Originally posted by José Roca View Post

    Because it passes pointers to elements of an ansi array.
    @pLVDI.item.pszText = STRPTR(gsArray(COL+1,ROW+1))
    This like pulling teeth

    So as I understand it, it's only a requirement of one particular code example, not a requirement of a VirtualListview,, the API or Sqlite. It's just that the use of an ANSI array in one specific example that required it. t. There is no general "%OPTIONAPI requirement" despite reference to one with no specific code mentioned.

    Or am I wrong?

    Comment


    • #62
      The latest versions of the compilers are unicode. Therefore, if you don't specify #OPTION ANSIAPI, item.pszText expects a pointer to an unicode string, and if you pass a pointer to an ansi string it will fail. If you use an array of ansi strings, you have the choice to use #OPTION ANSIAPI ---in which case the compìler will call the ansi versions of the required Windows API functions---, or use an intermediate global or static unicode string, e.g.

      Code:
      DIM wsTemp AS STATIC WSTRING
      wsTemp = gsArray(COL+1,ROW+1)
      @pLVDI.item.pszText = STRPTR(wsTemp)
      DDT statements or wrapper functions that use WSTRING parameters will work without an intermediate step if you pass an ansi string because PB will automatically convert them to unicode, but there is not automatic conversion if you pass a pointer.
      Forum: http://www.jose.it-berater.org/smfforum/index.php

      Comment


      • #63
        Originally posted by Michael Rice View Post

        Yes, on my list of things to do. Fortunately the database is read only in this case. Also given that you have the source code...you could write and submit any SQL statement you like.

        I've not really looked into what all I would need to escape. It seems like so far I've run into single quotes and it seems like the % sign. I'm sure there are others...

        Thanks for sharing "...little Bobby Tables"
        Agreed you are not concerned about malicious SQL, but you do want to escape the percent and undercore wildcards to avoid returning incorrect results..

        If you are going to be searching for percent or underscore you need use something like:

        LIKE '%" + SQLSafe(sSearchText) + "%' ESCAPE '\'"
        ...
        Code:
        FUNCTION Sqlsafe(BYVAL s AS WSTRING) AS WSTRING
        REPLACE "'" WITH "''" IN s ' doubled single quotes to avoid breaking search string
        REPLACE "_" WITH "\_" IN s 'escape wilcard
        REPLACE "%" WITH "\%" IN s 'escape wildcard
        REPLACE "\" WITH "\\" IN s 'escape the escape character
        FUNCTION = s
        END FUNCTION
        Last edited by Stuart McLachlan; 27 Aug 2020, 09:57 PM. Reason: Last replace was messed up by BB when not escaped. It didn't show the backslashes.

        Comment


        • #64
          Originally posted by José Roca View Post
          Yes. The Windows API EnableFunction is your friend.

          CWindow is 100% compatible with the Windows API. Therefore, you can use the appropriate wrapper function (*), if available, or directly the Windows API.

          Originally posted by José Roca View Post
          BTW in the code that you have posted in #38 you have two calls to AfxDoEvents, but only the one inside the DO WHILE/LOOP is needed.

          The Windows API EnableFunction is your friend and so will ShowWindow be... These sort of simple questions that I'm needing to ask tell me how far I have yet to go learning the SDK and frankly more broadly GUIs in general. I have a lot to learn about my new SDK friend

          CWindow - is there a good place for finding info on this? I looked around some in your includes. I found some examples here on the forum...including the progressbar inside of a statusbar example and I was able to lift your code directly into mine. I have probably searched for it in the wrong way and I'm likely missing something obvious.

          Comment


          • #65
            Personally, I think the progress bar is a bad idea

            Originally posted by Mike Doty View Post
            3) The select count(*) of the view seems to take 3 or more seconds. Might be a faster way..
            It's creating the view that takes almost all of the time and you can't display the progress bar until you have done that and got the record count.

            Creating the view takes more time than just using the query to return a recordset, then you add more time by doing the Select Count, then yet more time doing the Select from view.

            For the benefit of a Progress Bar which only shows in the final stage of filling the Listview (which would be very short for a reasonable search result or filling an array for a virtual listview) you are significantly increasing the overall search time.

            Comment


            • #66
              Originally posted by Mike Doty View Post
              1) If indexes are added might want to use all upper or all lower in indexes because I don't think LIKE will use indexes?
              Would have to run slSel EXPLAIN QUERY PLAN " + SQL
              No need to run EXPLAIN QUERY PLAN.
              A LIKE clause will always do a full table scan if it starts with a wildcard. Indexes can't help in that situation.

              Comment


              • #67
                CWindow - is there a good place for finding info on this? I looked around some in your includes. I found some examples here on the forum...including the progressbar inside of a statusbar example and I was able to lift your code directly into mine. I have probably searched for it in the wrong way and I'm likely missing something obvious.
                Here is a help file waiting for you:

                https://forum.powerbasic.com/forum/j...082#post711082

                BTW SDK programming with the help of wrappers isn't too hard, is it?
                Forum: http://www.jose.it-berater.org/smfforum/index.php

                Comment


                • #68
                  BTW SDK programming with the help of wrappers isn't too hard, is it?
                  No, not at all. But I'm having to ask questions that I'd rather be helping provide answers to others on... So I need to start using it regularly to develop a strong skill set.

                  Thank you for the link to the help file. downloaded!!

                  Comment


                  • #69
                    Originally posted by Stuart McLachlan View Post

                    Agreed you are not concerned about malicious SQL, but you don't want to escpe the percent and undercore wildcards to avoid returning incorrect results..

                    If you are going to be searching for percent or underscore you need use something like:

                    LIKE '%" + SQLSafe(sSearchText) + "%' ESCAPE '\'"
                    ...

                    FUNCTION Sqlsafe(BYVAL s AS WSTRING) AS WSTRING
                    REPLACE "'" WITH "''" IN s ' doubled single quotes to avoid breaking search string
                    REPLACE "_" WITH "\_" IN s 'escape wilcard
                    REPLACE "%" WITH "\%" IN s 'escape wildcard
                    REPLACE "" WITH "" IN s 'escape the escape character
                    FUNCTION = s
                    END FUNCTION
                    Thanks for thoughts and examples. I had added the single quote (my code not posted yet) by dong exactly what you're doing here. I haven't done anything with the others. I thought I tested the % and it worked without anything... guess I better double check that. Thanks for passing this along for me!!

                    Personally, I think the progress bar is a bad idea
                    HEY are you calling my baby ugly???

                    Actually I kinda like it... I really wanted it for another idea that I have. So when Michael mentioned it and I found code that looked like it was written exactly for the job...well I couldn't resist at that point.

                    It's creating the view that takes almost all of the time and you can't display the progress bar until you have done that and got the record count.

                    Creating the view takes more time than just using the query to return a recordset, then you add more time by doing the Select Count, then yet more time doing the Select from view.

                    For the benefit of a Progress Bar which only shows in the final stage of filling the Listview (which would be very short for a reasonable search result or filling an array for a virtual listview) you are significantly increasing the overall search time.
                    So first off...no war on timing here. I didn't time any of this. But I don't recall a major time jump when creating the view vs the select directly. But again...I did NOT time it to compare. The Select Count... Mike mentions that there may be a faster way. I did it the way that I knew how. Here again...I didn't time it. Mike said he did and it took 3 seconds...I believe him.

                    So while fast is great!! My primary objective was to have a way for PBCC and PBWIN users to see results in a GUI instead of my other examples outputting text to command window.. Several things here are already overkill for what I was looking for. So I more or less done it so that I would have example code of progressbar in a statusbar

                    In the process I'm learning MUCH ... context switching being a great example.

                    Comment


                    • #70
                      Originally posted by Stuart McLachlan View Post

                      No need to run EXPLAIN QUERY PLAN.
                      A LIKE clause will always do a full table scan if it starts with a wildcard. Indexes can't help in that situation.
                      Mike and Stuart
                      This is just one of the queries I hope to be able to use this for. I like the way it has turned it..much nicer than the command line version I posted a little while back. So I don't know that I need indexes for this as it's just one of the examples.

                      While pulling together the pieces for this. I found a type of listview that I never really knew about. Allows grouping with header and footer rows...and that gave me an idea for a new query. So I want to give that a try soon and post it for others if they're interested. There are examples here on the forum, I just never noticed it until my recent interest in listviews.

                      Comment


                      • #71
                        A couple of things about the DB Query...

                        1. Optimizing DB queries is a whole 'nother kettle of fish. Over the years I have drawn extensively on clients' DBAs, because I am not very good at that. (But IMO the DBAs are not as good at applications). Here you might try a request in the new "Database/Database programming" forum, but restricting the question to optimizing the query. I know we have some "DBA" types here who live to optimize SQL.

                        2. You can run the query in its own thread of execution and not have to worry about a "DoEvents or equal."

                        Code:
                        GLOBAL QueryResults () AS something
                        
                        %PWM_QUERY COMPLETE   =  %WM_USER  + 1&
                        
                        
                        FUNCTION  WindowProcedure (...)
                        
                        
                           SELECT CASE  wMSG (or CBMSG or however you process messages in your GUI(
                        
                             CASE  "appropriate time"
                        
                               THREAD CREATE   RunQueryThread (param) to hThread
                                disable GUI controls which should not be used
                        
                         CASE "PWM_QUERY_COMPLETE
                              Update control(s) with query results from     QueryResults()
                        
                             Re-enable controls which may now be used
                        
                        ....
                        
                        THREAD FUNCTION  RunQueryThread  (param) AS LONG
                        
                              SELECT .....
                        
                        
                             If good results
                               RESET  QueryResults()
                              FOR EACH ROW RETURNED
                                    Fettch move data to QueryResults (RowNo)
                               NEXT
                        
                             ' TELL GUI THREAD THE QUERY RESULTS ARE AVAILABLE
                              PostMessage  hWnd, %PWN_QUERY+COMPLETE,  0,0     ' or other params
                        
                        END FUNCTION
                        Note the thread function ONLY runs the query and saves the results... it does not even think about updating the screen!

                        MCM






                        Michael Mattias
                        Tal Systems Inc. (retired)
                        Racine WI USA
                        [email protected]
                        http://www.talsystems.com

                        Comment


                        • #72
                          Originally posted by Michael Mattias View Post
                          A couple of things about the DB Query...

                          1. Optimizing DB queries is a whole 'nother kettle of fish. Over the years I have drawn extensively on clients' DBAs, because I am not very good at that. (But IMO the DBAs are not as good at applications). Here you might try a request in the new "Database/Database programming" forum, but restricting the question to optimizing the query. I know we have some "DBA" types here who live to optimize SQL.

                          2. You can run the query in its own thread of execution and not have to worry about a "DoEvents or equal."

                          Code:
                          GLOBAL QueryResults () AS something
                          
                          %PWM_QUERY COMPLETE = %WM_USER + 1&
                          
                          
                          FUNCTION WindowProcedure (...)
                          
                          
                          SELECT CASE wMSG (or CBMSG or however you process messages in your GUI(
                          
                          CASE "appropriate time"
                          
                          THREAD CREATE RunQueryThread (param) to hThread
                          disable GUI controls which should not be used
                          
                          CASE "PWM_QUERY_COMPLETE
                          Update control(s) with query results from QueryResults()
                          
                          Re-enable controls which may now be used
                          
                          ....
                          
                          THREAD FUNCTION RunQueryThread (param) AS LONG
                          
                          SELECT .....
                          
                          
                          If good results
                          RESET QueryResults()
                          FOR EACH ROW RETURNED
                          Fettch move data to QueryResults (RowNo)
                          NEXT
                          
                          ' TELL GUI THREAD THE QUERY RESULTS ARE AVAILABLE
                          PostMessage hWnd, %PWN_QUERY+COMPLETE, 0,0 ' or other params
                          
                          END FUNCTION
                          Note the thread function ONLY runs the query and saves the results... it does not even think about updating the screen!

                          MCM
                          I agree with you about the art of good database querying. My goal with my original posts was to show what types of information could be pulled from the database with ease. After doing that, I found that I needed a better way to display easily the results and NOT as text in the command window. I really just wanted a super simple framework that I could output the results to a GUI. I also wanted customers of both PBCC and PBWIN to be able to build and use the examples...so that made the choice of using SDK an easy one!!

                          I hope in upcoming days to post some additional examples in the new database forum and will likely be using some variation of this code for now. As discussed earlier in this topic ... this probably isn't the best overall design for this. So my journey of learning the SDK will continue. I'll find or build a better working example as time goes on and begin using that. But for now, my focus is showing the information that is available if one wants it. This may be of interest to only one party (me), but I share it here in case others do want access to it.

                          As time goes on, I will certainly be asking for help writing better SQL. I'm glad there is a place now on the forums for database topics to be gathered!! Speaking of... Are you gong to share how you would get the count of rows? If you have a way and you don't mind sharing it...I'd certainly be interested in seeing it!! I also understand if this is something that you worked hard to understand and you aren't going to just post on a public forum.

                          Note the thread function ONLY runs the query and saves the results... it does not even think about updating the screen!
                          I did indeed make note of that ... my primary take away from this thread has been DO NOT update controls repeatedly from a thread and in fact avoid it completely if you can. My second biggest take away is that Michael Rice has much to learn about SDK programming (many other topics as well but for sure this one!!)

                          ' TELL GUI THREAD THE QUERY RESULTS ARE AVAILABLE
                          PostMessage hWnd, %PWN_QUERY+COMPLETE, 0,0 ' or other params
                          I was going to look at using a worker thread to create the DB view...so that the gui message pump could keep running. This isn't how I was going to go about it. But I certainly can see how this would be useful for the thread to communicate the completed status. I wrote a Windows service sometime back that used messages like this..not sure why I didn't think of that here.

                          Comment


                          • #73
                            My second biggest take away is that Michael Rice has much to learn about SDK programming (many other topics as well but for sure this one!!)
                            This thread has not so much been about SDK programming as it has been about programming in general. You could write the GUI for this program using DDT and everything else in this thread would be exactly the same. Matter of fact, this has been more about "how to use multiple threads of execution" more than it has been about the thread title's suggestion that it is about "help with 2 SDK controls."

                            The "trick" is to make sure you break up the various required activities of your program - Screen I/O, DB I/O, alpha/numeric processing, - into logical groups, and use separate procedures for each. This makes it really easy to A) maintain, B) optimize and C) ask for help here.

                            MCM
                            Michael Mattias
                            Tal Systems Inc. (retired)
                            Racine WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment


                            • #74
                              Yes...could be done with DDT...but primary objective was compile with either PBCC or PBWIN. How could I compile DDT with PBCC? So that's how SDK came into picture.

                              Comment


                              • #75
                                Originally posted by Michael Mattias View Post
                                2. You can run the query in its own thread of execution and not have to worry about a "DoEvents or equal."
                                You mean like Post #19 and #30?

                                Comment


                                • #76
                                  Originally posted by Michael Rice View Post
                                  I was going to look at using a worker thread to create the DB view...so that the gui message pump could keep running. This isn't how I was going to go about it. But I certainly can see how this would be useful for the thread to communicate the completed status. I wrote a Windows service sometime back that used messages like this..not sure why I didn't think of that here.
                                  Post #30 again, at the end of the DB query thread::
                                  Sendmessage hWnd,%GotData,0,0 EXIT FUNCTION

                                  Comment


                                  • #77
                                    Originally posted by Michael Mattias View Post
                                    The "trick" is to make sure you break up the various required activities of your program - Screen I/O, DB I/O, alpha/numeric processing, - into logical groups, and use separate procedures for each. This makes it really easy to A) maintain, B) optimize and C) ask for help here.
                                    Wise words indeed!
                                    I generally go a bit further and use separate include files with appropriate functions/procedures in each.
                                    My application.bas file is frequently just a few #RESOURCE, #INCLUDE, ENUM equates SINGULAR lines and a few lines in a PBMain() function.

                                    Comment


                                    • #78
                                      Originally posted by Stuart McLachlan View Post
                                      Personally, I think the progress bar is a bad idea



                                      It's creating the view that takes almost all of the time and you can't display the progress bar until you have done that and got the record count.

                                      Creating the view takes more time than just using the query to return a recordset, then you add more time by doing the Select Count, then yet more time doing the Select from view.

                                      For the benefit of a Progress Bar which only shows in the final stage of filling the Listview (which would be very short for a reasonable search result or filling an array for a virtual listview) you are significantly increasing the overall search time.
                                      Please compile and let me know how well the test results line up with your comments quoted above... I'm certainly not saying that what I've done here is a great practice. However I think you might want to reconsider your comments.

                                      I'm looking forward to seeing your results...please post for all to see

                                      Have you every seen a web server log?
                                      You have no idea how badly I want to return the favor of a snark remark to you...but I won't...

                                      Test1.bas
                                      Code:
                                      #COMPILE EXE
                                      #COMPILER PBCC 6, PBWIN 10
                                      #DIM ALL
                                      #IF %DEF(%PB_CC32)
                                         #CONSOLE OFF    ' <- if PBCC and you don't want/need console
                                      #ENDIF
                                      %UNICODE = 1
                                      
                                      #INCLUDE ONCE "CWindow.inc"      ' // CWindow class
                                      #INCLUDE ONCE "ListViewCtrl.inc" ' // ListView control wrapper functions
                                      #INCLUDE ONCE "EditCtrl.inc"     ' // Edit control wrapper functions
                                      #INCLUDE ONCE "C:\SQLitening\inc\SQLitening.inc"
                                      
                                      %IDC_STATUSBAR     = 1001
                                      %IDC_BUTTON        = 1002
                                      %IDC_EDIT          = 1003
                                      %IDC_LISTVIEW      = 1004
                                      %IDC_GROUPBOX      = 1005
                                      %IDC_OPTION_LOCAL  = 1006
                                      %IDC_OPTION_REMOTE = 1007
                                      
                                      ' ========================================================================================
                                      ' Main
                                      ' ========================================================================================
                                      FUNCTION WinMain (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS WSTRINGZ PTR, BYVAL nCmdShow AS LONG) AS LONG
                                      
                                         ' // Set process DPI aware
                                         AfxSetProcessDPIAware
                                      
                                         ' // Create an instance of the class
                                         LOCAL pWindow AS IWindow
                                         pWindow = CLASS "CWindow"
                                         IF ISNOTHING(pWindow) THEN EXIT FUNCTION
                                      
                                         ' // Create the main window
                                         pWindow.CreateWindow(%NULL, "PBforum.db Search Demo", 0, 0, 800, 600, 0, 0, CODEPTR(WindowProc))
                                      
                                         ' // Center the window
                                         pWindow.CenterWindow
                                      
                                         ' // Add a status bar
                                         LOCAL hStatusbar AS DWORD
                                         hStatusbar = pWindow.AddStatusBar(pWindow.hwnd, %IDC_STATUSBAR, "", 0, 0, 0, 0)
                                         pWindow.SetStatusbarPartsBySize(hStatusbar, "160, -1")
                                      
                                         ' // Add an edit control
                                         LOCAL hTextBox AS DWORD
                                         hTextBox = pWindow.AddTextBox(pWindow.hwnd, %IDC_EDIT, "", 5, 500, 525, 23)
                                      
                                         ' // Add a button
                                         pWindow.AddButton(pWindow.hwnd, %IDC_BUTTON, "Search", 550, 500, 75, 23)
                                      
                                         ' // Add a group box control
                                         pWindow.AddGroupBox(pWindow.hwnd, %IDC_GROUPBOX, "Database", 640, 490, 130, 40)
                                      
                                         ' // Add two radio buttons (the first one should have the %WS_GROUP style)
                                         pWindow.AddRadioButton(pWindow.hwnd, %IDC_OPTION_LOCAL, "Local", 645, 505, 60, 23, %WS_GROUP)
                                         pWindow.AddRadioButton(pWindow.hwnd, %IDC_OPTION_REMOTE, "Remote", 705, 505, 60, 23)
                                         ' // Check the first radio button
                                         CheckDlgButton pWindow.hwnd, %IDC_OPTION_LOCAL, %BST_CHECKED
                                      
                                         ' // Add a ListView control
                                         LOCAL hListView AS DWORD
                                         hListView = pWindow.AddListView(pWindow.hwnd, %IDC_LISTVIEW, "", 5, 5, 775, 475)
                                         ' // Add the header's column names
                                         LOCAL i AS LONG
                                         'FOR i = 0 TO 4
                                         '   ListView_AddColumn(hListView, i, "Column" & STR$(i), pWindow.ScaleX(110))
                                         'NEXT
                                         ListView_AddColumn(hListView, 0, "", pWindow.ScaleX(0))
                                         ListView_AddColumn(hListView, 1, "Thread Number", pWindow.ScaleX(110),%LVCFMT_RIGHT)
                                         ListView_AddColumn(hListView, 2, "Post Number", pWindow.ScaleX(110),%LVCFMT_RIGHT)
                                         ListView_AddColumn(hListView, 3, "Forum", pWindow.ScaleX(110))
                                         ListView_AddColumn(hListView, 4, "Author", pWindow.ScaleX(110))
                                         ListView_AddColumn(hListView, 5, "Post Date", pWindow.ScaleX(110))
                                         ListView_AddColumn(hListView, 6, "Title", pWindow.ScaleX(110))
                                         ' // Add some extended styles
                                         LOCAL dwExStyle AS DWORD
                                         dwExStyle = ListView_GetExtendedListViewStyle(hListView)
                                         dwExStyle = dwExStyle OR %LVS_EX_FULLROWSELECT OR %LVS_EX_GRIDLINES 'OR %LVS_REPORT OR %LVS_SHOWSELALWAYS OR %LVS_NOLABELWRAP OR %LVS_NOSORTHEADER
                                         ListView_SetExtendedListViewStyle(hListView, dwExStyle)
                                      
                                         ' // Set the focus in the hTextBox
                                         SetFocus hTextBox
                                      
                                         ' // Default message pump (you can replace it with your own)
                                         FUNCTION = pWindow.DoEvents(nCmdShow)
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      
                                      FUNCTION LoadListView(BYVAL hwnd AS DWORD) PRIVATE AS DWORD
                                      
                                         LOCAL wsWork AS WSTRING
                                         LOCAL hListView AS DWORD
                                         LOCAL hStatusBar AS DWORD
                                         LOCAL sSearchText AS WSTRING
                                         LOCAL sDB_LOCAL_FileName AS STRING
                                         LOCAL dwDatabaseOption AS DWORD
                                      
                                         hListView = GetDlgItem(hwnd, %IDC_LISTVIEW)
                                         hStatusBar = GetDlgItem(hwnd, %IDC_STATUSBAR)
                                      
                                         LOCAL sSQL AS STRING
                                         LOCAL sForumName AS STRING
                                         LOCAL sTitle AS STRING
                                         LOCAL sAuthor AS STRING
                                         LOCAL sPostDate AS STRING
                                         LOCAL dwRowCount AS DWORD
                                         LOCAL dwThread_Link AS DWORD
                                         LOCAL dwPostNumber  AS DWORD
                                      
                                         LOCAL qPerfFreq   AS QUAD
                                         LOCAL qStart      AS QUAD
                                         LOCAL qEnd        AS QUAD
                                         LOCAL qQueryStart AS QUAD
                                         LOCAL qQueryEnd   AS QUAD
                                         LOCAL qLoadStart  AS QUAD
                                         LOCAL qLoadEnd    AS QUAD
                                      
                                         sSearchText = Edit_GetText(GetDlgItem(hwnd, %IDC_EDIT))
                                         IF sSearchText = "" THEN
                                            MessageBoxW(hwnd, "Please enter search text", "Error", %MB_OK OR %MB_ICONEXCLAMATION)
                                            EXIT FUNCTION
                                         END IF
                                      
                                         QueryPerformanceFrequency qPerfFreq
                                         QueryPerformanceCounter qStart
                                      
                                         sDB_LOCAL_FileName = EXE.Path$ + "PBforum_LOCAL.db"
                                      
                                         ' Disable button, edit and option controls
                                         EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %FALSE)          ' disable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %FALSE)            ' disable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %FALSE)    ' disable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %FALSE)   ' disable
                                      
                                         ' // Get the checked database option button
                                         LOCAL i AS LONG
                                         FOR i = %IDC_OPTION_LOCAL TO %IDC_OPTION_REMOTE
                                            dwDatabaseOption = SendMessage(GetDlgItem(hwnd, i), %BM_GETCHECK, 0, 0)
                                            IF dwDatabaseOption = %BST_CHECKED THEN
                                               dwDatabaseOption = i
                                               EXIT FOR
                                            END IF
                                         NEXT
                                      
                                         ' Clear all listiew items
                                         ListView_DeleteAllItems(hListView)
                                         ListView_FitHeader(hListView,1)
                                         ListView_FitHeader(hListView,2)
                                         ListView_FitHeader(hListView,3)
                                         ListView_FitHeader(hListView,4)
                                         ListView_FitHeader(hListView,5)
                                         ListView_FitHeader(hListView,6)
                                      
                                         wsWork = "Searching..."
                                         SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                         wsWork = "Searching for " + $DQ + sSearchText + $DQ
                                         SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                         SendMessage hListView,%WM_SETREDRAW,%FALSE,0
                                      
                                         sSQL = "SELECT T1.Thread_Link, "                                          + _
                                                "       T1.PostNumber, "                                           + _
                                                "       T3.Name, "                                                 + _
                                                "       T3.ForumURL, "                                             + _
                                                "       T2.ThreadURL, "                                            + _
                                                "       T2.ThreadTitle, "                                          + _
                                                "       T4.Author, "                                               + _
                                                "       strftime('%Y-%m-%d %H:%M:%S', T1.PostDate,'localtime') "   + _
                                                "  FROM ThreadContent T1 "                                         + _
                                                "       LEFT OUTER JOIN Threads T2 "                               + _
                                                "                    ON T2.ThreadNumber = T1.Thread_Link "         + _
                                                "       LEFT OUTER JOIN Forums T3 "                                + _
                                                "                    ON T2.ThreadNumber = T1.Thread_Link "         + _
                                                "                   AND T2.Forum_Link = T3.RowID "                 + _
                                                "       LEFT OUTER JOIN ForumAuthors T4 "                          + _
                                                "                    ON T1.Author_Link = T4.RowID "                + _
                                                "WHERE T2.ThreadTitle LIKE '%" + sSearchText + "%' "               + _
                                                "   OR T1.PostContent LIKE '%" + sSearchText + "%' "               + _
                                                " ORDER BY T1.Thread_Link ASC, T1.PostNumber ASC;"
                                      
                                         IF dwDatabaseOption = %IDC_OPTION_REMOTE THEN
                                            slConnect "38.17.54.213",51234
                                            slOpen "PBforum\PBforum.db" + $BS + "Read%Only", "R"  '1 open with read-only password
                                         ELSEIF dwDatabaseOption = %IDC_OPTION_LOCAL THEN
                                            IF ISFILE(sDB_LOCAL_FileName) THEN
                                               slOpen sDB_LOCAL_FileName  + $BS + "Read%Only", "R"  '1 open with read-only password
                                            ELSE
                                               MessageBoxW(hwnd, "Could Not Find " + sDB_LOCAL_FileName, "Local database not found", %MB_OK OR %MB_ICONSTOP)
                                               ' Reset user controls
                                               wsWork = ""
                                               SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                               SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                               EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %TRUE)          ' enable
                                               EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %TRUE)            ' enable
                                               EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %TRUE)    ' enable
                                               EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %TRUE)   ' enable
                                               CheckDlgButton hwnd, %IDC_OPTION_LOCAL, %BST_UNCHECKED
                                               CheckDlgButton hwnd, %IDC_OPTION_REMOTE, %BST_CHECKED
                                               EXIT FUNCTION
                                            END IF
                                         END IF
                                      
                                         QueryPerformanceCounter qQueryStart
                                         slSel sSQL
                                         QueryPerformanceCounter qQueryEnd
                                      
                                         wsWork = "Loading..."
                                         SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                      
                                         QueryPerformanceCounter qLoadStart
                                         DO WHILE slGetRow
                                      
                                            AfxDoEvents(hwnd)
                                      
                                            INCR dwRowCount
                                            dwThread_Link = VAL(slF(1))
                                            dwPostNumber  = VAL(slF(2))
                                            sForumName    = slF(3)
                                            sTitle        = slF(6)
                                            sAuthor       = slF(7)
                                            sPostDate     = slF(8)
                                      
                                            ListView_AddItem(hListView, dwRowCount - 1, 0, "")
                                            ListView_SetItemText(hListView, dwRowCount - 1, 1, FORMAT$(dwThread_Link))
                                            ListView_SetItemText(hListView, dwRowCount - 1, 2, FORMAT$(dwPostNumber))
                                            ListView_SetItemText(hListView, dwRowCount - 1, 3, sForumName)
                                            ListView_SetItemText(hListView, dwRowCount - 1, 4, sAuthor)
                                            ListView_SetItemText(hListView, dwRowCount - 1, 5, sPostDate)
                                            ListView_SetItemText(hListView, dwRowCount - 1, 6, sTitle)
                                      
                                         LOOP
                                         QueryPerformanceCounter qLoadEnd
                                      
                                         slClose
                                      
                                         IF dwDatabaseOption = %IDC_OPTION_REMOTE THEN
                                            slDisconnect
                                         END IF
                                      
                                         IF dwRowCount THEN
                                            ' Resize columns based on data size
                                            ListView_FitContent(hListView,1)
                                            ListView_FitContent(hListView,2)
                                            ListView_FitContent(hListView,3)
                                            ListView_FitContent(hListView,4)
                                            ListView_FitContent(hListView,5)
                                            ListView_FitHeader(hListView,6)
                                         END IF
                                      
                                         QueryPerformanceCounter qEnd
                                      
                                         wsWork = "Rows returned: " + FORMAT$(dwRowCount,"#,")
                                         SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                         wsWork = "Search for: " + $DQ + sSearchText + $DQ + " took " + FORMAT$((qEnd-qStart)/qPerfFreq,"###.000") & " seconds" + " " + _
                                                  "Query: " + FORMAT$((qQueryEnd-qQueryStart)/qPerfFreq,"###.000") + " " + _
                                                  "Result Load: " + FORMAT$((qLoadEnd-qLoadStart)/qPerfFreq,"###.000")
                                         SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                         SendMessage hListView,%WM_SETREDRAW,%TRUE,0
                                      
                                         ' Enable button, edit and option controls
                                         EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %TRUE)           ' enable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %TRUE)             ' enable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %TRUE)     ' enable
                                         EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %TRUE)    ' enable
                                      
                                         ' // Select the first item
                                         ListView_SelectItem hListView, 0
                                         ' // Set the focus in the ListView
                                         SetFocus hListView
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      ' Main callback function.
                                      ' ========================================================================================
                                      FUNCTION WindowProc (BYVAL hwnd AS DWORD, BYVAL uMsg AS DWORD, BYVAL wParam AS DWORD, BYVAL lParam AS LONG) AS LONG
                                      
                                         LOCAL  dwRC AS DWORD
                                         STATIC pWindow AS IWindow ' // Reference to the IWindow interface
                                      
                                         SELECT CASE uMsg
                                      
                                            CASE %WM_CREATE
                                               ' // Get a reference to the IWindow interface from the CREATESTRUCT structure
                                               pWindow = CWindow_GetObjectFromCreateStruct(lParam)
                                               EXIT FUNCTION
                                      
                                            CASE %WM_COMMAND
                                               SELECT CASE LO(WORD, wParam)
                                                  CASE %IDCANCEL
                                                     ' // If the Escape key has been pressed...
                                                     IF HI(WORD, wParam) = %BN_CLICKED THEN
                                                        ' // ... close the application by sending a WM_CLOSE message
                                                        SendMessage hwnd, %WM_CLOSE, 0, 0
                                                        EXIT FUNCTION
                                                     END IF
                                                  CASE %IDC_BUTTON
                                                     ' // Search button pressed. Load the listview control
                                                     LoadListView(BYVAL hwnd)
                                               END SELECT
                                      
                                            CASE %WM_SIZE
                                               ' // If the window isn't minimized, resize the controls
                                               IF wParam <> %SIZE_MINIMIZED THEN
                                                  ' // Gets the handle of the status bar
                                                  LOCAL hStatusBar AS DWORD
                                                  hStatusBar = GetDlgItem(hwnd, %IDC_STATUSBAR)
                                                  ' // Resizes it
                                                  SendMessage hStatusBar, %WM_SIZE, wParam, lParam
                                                  ' // Redraws it
                                                  InvalidateRect hStatusBar, BYVAL %NULL, %TRUE
                                                  ' // Resize the Listview control
                                                  pWindow.MoveWindow(GetDlgItem(hwnd, %IDC_LISTVIEW), 5, 5, pWindow.ClientWidth - 5, pWindow.ClientHeight - 75, %TRUE)
                                                  ListView_FitHeader(GetDlgItem(hwnd, %IDC_LISTVIEW),6)
                                                  ' // Move the position of the edit and button controls
                                                  pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_EDIT), 0, 5, pWindow.ClientHeight - 60, 200, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                                  pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_BUTTON), 0, 550, pWindow.ClientHeight - 60, 75, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                                  ' // Move the position of the group box control
                                                  pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_GROUPBOX), 0, 640, pWindow.ClientHeight - 70, 130, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                                  ' // Move the position of the radio buttons
                                                  pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), 0, 645, pWindow.ClientHeight - 55, 60, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                                  pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), 0, 705, pWindow.ClientHeight - 55, 60, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      
                                               END IF
                                               EXIT FUNCTION
                                      
                                            CASE %WM_DESTROY
                                               ' // End the application
                                               PostQuitMessage 0
                                               EXIT FUNCTION
                                      
                                         END SELECT
                                      
                                         ' // Pass unprocessed messages to Windows
                                         FUNCTION = DefWindowProc(hwnd, uMsg, wParam, lParam)
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      Test2.bas
                                      Code:
                                      #COMPILE EXE
                                      #COMPILER PBCC 6, PBWIN 10
                                      #DIM ALL
                                      #IF %DEF(%PB_CC32)
                                      #CONSOLE OFF ' <- if PBCC and you don't want/need console
                                      #ENDIF
                                      %UNICODE = 1
                                      
                                      #INCLUDE ONCE "CWindow.inc" ' // CWindow class
                                      #INCLUDE ONCE "ListViewCtrl.inc" ' // ListView control wrapper functions
                                      #INCLUDE ONCE "EditCtrl.inc" ' // Edit control wrapper functions
                                      #INCLUDE ONCE "C:\SQLitening\inc\SQLitening.inc"
                                      
                                      %IDC_STATUSBAR = 1001
                                      %IDC_PROGRESSBAR = 1002
                                      %IDC_BUTTON = 1003
                                      %IDC_EDIT = 1004
                                      %IDC_LISTVIEW = 1005
                                      %IDC_GROUPBOX = 1006
                                      %IDC_OPTION_LOCAL = 1007
                                      %IDC_OPTION_REMOTE = 1008
                                      
                                      ' ========================================================================================
                                      ' Main
                                      ' ========================================================================================
                                      FUNCTION WinMain (BYVAL hInstance AS DWORD, BYVAL hPrevInstance AS DWORD, BYVAL lpszCmdLine AS WSTRINGZ PTR, BYVAL nCmdShow AS LONG) AS LONG
                                      
                                      ' // Set process DPI aware
                                      AfxSetProcessDPIAware
                                      
                                      ' // Create an instance of the class
                                      LOCAL pWindow AS IWindow
                                      pWindow = CLASS "CWindow"
                                      IF ISNOTHING(pWindow) THEN EXIT FUNCTION
                                      
                                      ' // Create the main window
                                      pWindow.CreateWindow(%NULL, "PBforum.db Search Demo", 0, 0, 800, 600, 0, 0, CODEPTR(WindowProc))
                                      
                                      ' // Center the window
                                      pWindow.CenterWindow
                                      
                                      ' // Add a status bar
                                      LOCAL hStatusbar AS DWORD
                                      hStatusbar = pWindow.AddStatusBar(pWindow.hwnd, %IDC_STATUSBAR, "", 0, 0, 0, 0)
                                      pWindow.SetStatusbarPartsBySize(hStatusbar, "160, -1")
                                      
                                      ' // Add a progress bar to the status bar
                                      LOCAL hProgressBar AS DWORD
                                      hProgressBar = pWindow.AddProgressBar(hStatusbar, %IDC_PROGRESSBAR, "", 0, 2, 160, 18)
                                      ' // Set the initial value
                                      SendMessage hProgressBar, %PBM_SETPOS, 0, 0
                                      ShowWindow hProgressBar, %SW_HIDE
                                      
                                      ' // Add an edit control
                                      LOCAL hTextBox AS DWORD
                                      hTextBox = pWindow.AddTextBox(pWindow.hwnd, %IDC_EDIT, "", 5, 500, 525, 23)
                                      
                                      ' // Add a button
                                      pWindow.AddButton(pWindow.hwnd, %IDC_BUTTON, "Search", 550, 500, 75, 23)
                                      
                                      ' // Add a group box control
                                      pWindow.AddGroupBox(pWindow.hwnd, %IDC_GROUPBOX, "Database", 640, 490, 130, 40)
                                      
                                      ' // Add two radio buttons (the first one should have the %WS_GROUP style)
                                      pWindow.AddRadioButton(pWindow.hwnd, %IDC_OPTION_LOCAL, "Local", 645, 505, 60, 23, %WS_GROUP)
                                      pWindow.AddRadioButton(pWindow.hwnd, %IDC_OPTION_REMOTE, "Remote", 705, 505, 60, 23)
                                      ' // Check the first radio button
                                      CheckDlgButton pWindow.hwnd, %IDC_OPTION_LOCAL, %BST_CHECKED
                                      
                                      ' // Add a ListView control
                                      LOCAL hListView AS DWORD
                                      hListView = pWindow.AddListView(pWindow.hwnd, %IDC_LISTVIEW, "", 5, 5, 775, 475)
                                      ' // Add the header's column names
                                      LOCAL i AS LONG
                                      'FOR i = 0 TO 4
                                      ' ListView_AddColumn(hListView, i, "Column" & STR$(i), pWindow.ScaleX(110))
                                      'NEXT
                                      ListView_AddColumn(hListView, 0, "", pWindow.ScaleX(0))
                                      ListView_AddColumn(hListView, 1, "Thread Number", pWindow.ScaleX(110),%LVCFMT_RIGHT)
                                      ListView_AddColumn(hListView, 2, "Post Number", pWindow.ScaleX(110),%LVCFMT_RIGHT)
                                      ListView_AddColumn(hListView, 3, "Forum", pWindow.ScaleX(110))
                                      ListView_AddColumn(hListView, 4, "Author", pWindow.ScaleX(110))
                                      ListView_AddColumn(hListView, 5, "Post Date", pWindow.ScaleX(110))
                                      ListView_AddColumn(hListView, 6, "Title", pWindow.ScaleX(110))
                                      ' // Add some extended styles
                                      LOCAL dwExStyle AS DWORD
                                      dwExStyle = ListView_GetExtendedListViewStyle(hListView)
                                      dwExStyle = dwExStyle OR %LVS_EX_FULLROWSELECT OR %LVS_EX_GRIDLINES 'OR %LVS_REPORT OR %LVS_SHOWSELALWAYS OR %LVS_NOLABELWRAP OR %LVS_NOSORTHEADER
                                      ListView_SetExtendedListViewStyle(hListView, dwExStyle)
                                      
                                      ' // Set the focus in the hTextBox
                                      SetFocus hTextBox
                                      
                                      ' // Default message pump (you can replace it with your own)
                                      FUNCTION = pWindow.DoEvents(nCmdShow)
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      
                                      FUNCTION LoadListView(BYVAL hwnd AS DWORD) PRIVATE AS DWORD
                                      
                                      LOCAL wsWork AS WSTRING
                                      LOCAL hListView AS DWORD
                                      LOCAL hStatusBar AS DWORD
                                      LOCAL hProgressBar AS DWORD
                                      LOCAL sSearchText AS WSTRING
                                      LOCAL sDB_LOCAL_FileName AS STRING
                                      LOCAL dwDatabaseOption AS DWORD
                                      
                                      hListView = GetDlgItem(hwnd, %IDC_LISTVIEW)
                                      hStatusBar = GetDlgItem(hwnd, %IDC_STATUSBAR)
                                      hProgressBar = GetDlgItem(GetDlgItem(hwnd, %IDC_STATUSBAR), %IDC_PROGRESSBAR)
                                      
                                      LOCAL dwViewRowCount AS DWORD
                                      LOCAL sSQL AS STRING
                                      LOCAL sForumName AS STRING
                                      LOCAL sTitle AS STRING
                                      LOCAL sAuthor AS STRING
                                      LOCAL sPostDate AS STRING
                                      LOCAL dwRowCount AS DWORD
                                      LOCAL dwThread_Link AS DWORD
                                      LOCAL dwPostNumber AS DWORD
                                      
                                      LOCAL qPerfFreq AS QUAD
                                      LOCAL qStart AS QUAD
                                      LOCAL qEnd AS QUAD
                                      
                                      LOCAL qCreateViewStart AS QUAD
                                      LOCAL qCreateViewEnd AS QUAD
                                      LOCAL qQueryStart AS QUAD
                                      LOCAL qQueryEnd AS QUAD
                                      LOCAL qGetRowCountStart AS QUAD
                                      LOCAL qGetRowCountEnd AS QUAD
                                      LOCAL qLoadStart AS QUAD
                                      LOCAL qLoadEnd AS QUAD
                                      
                                      sSearchText = Edit_GetText(GetDlgItem(hwnd, %IDC_EDIT))
                                      IF sSearchText = "" THEN
                                      MessageBoxW(hwnd, "Please enter search text", "Error", %MB_OK OR %MB_ICONEXCLAMATION)
                                      EXIT FUNCTION
                                      END IF
                                      
                                      QueryPerformanceFrequency qPerfFreq
                                      QueryPerformanceCounter qStart
                                      
                                      sDB_LOCAL_FileName = EXE.Path$ + "PBforum_LOCAL.db"
                                      
                                      ' Disable button, edit and option controls
                                      EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %FALSE) ' disable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %FALSE) ' disable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %FALSE) ' disable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %FALSE) ' disable
                                      
                                      ' // Get the checked database option button
                                      LOCAL i AS LONG
                                      FOR i = %IDC_OPTION_LOCAL TO %IDC_OPTION_REMOTE
                                      dwDatabaseOption = SendMessage(GetDlgItem(hwnd, i), %BM_GETCHECK, 0, 0)
                                      IF dwDatabaseOption = %BST_CHECKED THEN
                                      dwDatabaseOption = i
                                      EXIT FOR
                                      END IF
                                      NEXT
                                      
                                      ' Clear all listiew items
                                      ListView_DeleteAllItems(hListView)
                                      ListView_FitHeader(hListView,1)
                                      ListView_FitHeader(hListView,2)
                                      ListView_FitHeader(hListView,3)
                                      ListView_FitHeader(hListView,4)
                                      ListView_FitHeader(hListView,5)
                                      ListView_FitHeader(hListView,6)
                                      
                                      wsWork = "Searching..."
                                      SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                      wsWork = "Searching for " + $DQ + sSearchText + $DQ
                                      SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                      SendMessage hListView,%WM_SETREDRAW,%FALSE,0
                                      
                                      IF dwDatabaseOption = %IDC_OPTION_REMOTE THEN
                                      slConnect "38.17.54.213",51234
                                      slOpen "PBforum\PBforum.db" + $BS + "Read%Only", "R" '1 open with read-only password
                                      ELSEIF dwDatabaseOption = %IDC_OPTION_LOCAL THEN
                                      IF ISFILE(sDB_LOCAL_FileName) THEN
                                      slOpen sDB_LOCAL_FileName + $BS + "Read%Only", "R" '1 open with read-only password
                                      ELSE
                                      MessageBoxW(hwnd, "Could Not Find " + sDB_LOCAL_FileName, "Local database not found", %MB_OK OR %MB_ICONSTOP)
                                      ' Reset user controls
                                      wsWork = ""
                                      SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                      SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                      EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %TRUE) ' enable
                                      CheckDlgButton hwnd, %IDC_OPTION_LOCAL, %BST_UNCHECKED
                                      CheckDlgButton hwnd, %IDC_OPTION_REMOTE, %BST_CHECKED
                                      EXIT FUNCTION
                                      END IF
                                      END IF
                                      
                                      sSQL = "DROP VIEW IF EXISTS MyView; " + _
                                      "CREATE TEMPORARY VIEW MyView AS " + _
                                      "SELECT T1.Thread_Link, " + _
                                      " T1.PostNumber, " + _
                                      " T3.Name, " + _
                                      " T3.ForumURL, " + _
                                      " T2.ThreadURL, " + _
                                      " T2.ThreadTitle, " + _
                                      " T4.Author, " + _
                                      " strftime('%Y-%m-%d %H:%M:%S', T1.PostDate,'localtime') AS PostDate " + _
                                      " FROM ThreadContent T1 " + _
                                      " LEFT OUTER JOIN Threads T2 " + _
                                      " ON T2.ThreadNumber = T1.Thread_Link " + _
                                      " LEFT OUTER JOIN Forums T3 " + _
                                      " ON T2.ThreadNumber = T1.Thread_Link " + _
                                      " AND T2.Forum_Link = T3.RowID " + _
                                      " LEFT OUTER JOIN ForumAuthors T4 " + _
                                      " ON T1.Author_Link = T4.RowID " + _
                                      " WHERE T2.ThreadTitle LIKE '%" + sSearchText + "%' " + _
                                      " OR T1.PostContent LIKE '%" + sSearchText + "%' " + _
                                      " ORDER BY T1.Thread_Link ASC, T1.PostNumber ASC;"
                                      
                                      QueryPerformanceCounter qCreateViewStart
                                      slExe sSQL
                                      QueryPerformanceCounter qCreateViewEnd
                                      
                                      sSQL = "SELECT Thread_Link, " + _
                                      " PostNumber, " + _
                                      " Name, " + _
                                      " ForumURL, " + _
                                      " ThreadURL, " + _
                                      " ThreadTitle, " + _
                                      " Author, " + _
                                      " PostDate " + _
                                      " FROM MyView;"
                                      
                                      QueryPerformanceCounter qQueryStart
                                      slSel sSQL
                                      QueryPerformanceCounter qQueryEnd
                                      
                                      QueryPerformanceCounter qGetRowCountStart
                                      dwViewRowCount = VAL(slSelStr("SELECT COUNT(*) FROM MyView;"))
                                      QueryPerformanceCounter qGetRowCountEnd
                                      wsWork = "Loading " + FORMAT$(dwViewRowCount,"#,") + " records for search: " + $DQ + sSearchText + $DQ
                                      SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                      
                                      ShowWindow hProgressBar, %SW_SHOW
                                      
                                      QueryPerformanceCounter qLoadStart
                                      DO WHILE slGetRow
                                      
                                      AfxDoEvents(hwnd)
                                      
                                      INCR dwRowCount
                                      dwThread_Link = VAL(slF(1))
                                      dwPostNumber = VAL(slF(2))
                                      sForumName = slF(3)
                                      sTitle = slF(6)
                                      sAuthor = slF(7)
                                      sPostDate = slF(8)
                                      
                                      ListView_AddItem(hListView, dwRowCount - 1, 0, "")
                                      ListView_SetItemText(hListView, dwRowCount - 1, 1, FORMAT$(dwThread_Link))
                                      ListView_SetItemText(hListView, dwRowCount - 1, 2, FORMAT$(dwPostNumber))
                                      ListView_SetItemText(hListView, dwRowCount - 1, 3, sForumName)
                                      ListView_SetItemText(hListView, dwRowCount - 1, 4, sAuthor)
                                      ListView_SetItemText(hListView, dwRowCount - 1, 5, sPostDate)
                                      ListView_SetItemText(hListView, dwRowCount - 1, 6, sTitle)
                                      
                                      ' Update the progress bar
                                      IF dwRowCount MOD 500 = 0 THEN
                                      SendMessage hProgressBar, %PBM_SETPOS, ((dwRowCount / dwViewRowCount) * 100), 0
                                      END IF
                                      
                                      LOOP
                                      QueryPerformanceCounter qLoadEnd
                                      
                                      SendMessage hProgressBar, %PBM_SETPOS, 0, 0
                                      ShowWindow hProgressBar, %SW_HIDE
                                      
                                      slExe "DROP VIEW IF EXISTS MyView;"
                                      
                                      slClose
                                      
                                      IF dwDatabaseOption = %IDC_OPTION_REMOTE THEN
                                      slDisconnect
                                      END IF
                                      
                                      IF dwRowCount THEN
                                      ' Resize columns based on data size
                                      ListView_FitContent(hListView,1)
                                      ListView_FitContent(hListView,2)
                                      ListView_FitContent(hListView,3)
                                      ListView_FitContent(hListView,4)
                                      ListView_FitContent(hListView,5)
                                      ListView_FitHeader(hListView,6)
                                      END IF
                                      
                                      QueryPerformanceCounter qEnd
                                      
                                      wsWork = "Rows returned: " + FORMAT$(dwRowCount,"#,")
                                      SendMessage hStatusBar,%SB_SETTEXT,0,BYVAL STRPTR(wsWork)
                                      wsWork = "Search for: " + $DQ + sSearchText + $DQ + " took " + FORMAT$((qEnd-qStart)/qPerfFreq,"###.000") & " seconds" + " " + _
                                      "Create View: " + FORMAT$((qCreateViewEnd-qCreateViewStart)/qPerfFreq,"###.000") + " " + _
                                      "Get row count: " + FORMAT$((qGetRowCountEnd-qGetRowCountStart)/qPerfFreq,"###.000") + " " + _
                                      "Query: " + FORMAT$((qQueryEnd-qQueryStart)/qPerfFreq,"###.000") + " " + _
                                      "Result Load: " + FORMAT$((qLoadEnd-qLoadStart)/qPerfFreq,"###.000")
                                      SendMessage hStatusBar,%SB_SETTEXT,1,BYVAL STRPTR(wsWork)
                                      SendMessage hListView,%WM_SETREDRAW,%TRUE,0
                                      
                                      ' Enable button, edit and option controls
                                      EnableWindow(GetDlgItem(hwnd, %IDC_BUTTON), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_EDIT), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), %TRUE) ' enable
                                      EnableWindow(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), %TRUE) ' enable
                                      
                                      ' // Select the first item
                                      ListView_SelectItem hListView, 0
                                      ' // Set the focus in the ListView
                                      SetFocus hListView
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      ' Main callback function.
                                      ' ========================================================================================
                                      FUNCTION WindowProc (BYVAL hwnd AS DWORD, BYVAL uMsg AS DWORD, BYVAL wParam AS DWORD, BYVAL lParam AS LONG) AS LONG
                                      
                                      LOCAL dwRC AS DWORD
                                      STATIC pWindow AS IWindow ' // Reference to the IWindow interface
                                      
                                      SELECT CASE uMsg
                                      
                                      CASE %WM_CREATE
                                      ' // Get a reference to the IWindow interface from the CREATESTRUCT structure
                                      pWindow = CWindow_GetObjectFromCreateStruct(lParam)
                                      EXIT FUNCTION
                                      
                                      CASE %WM_COMMAND
                                      SELECT CASE LO(WORD, wParam)
                                      CASE %IDCANCEL
                                      ' // If the Escape key has been pressed...
                                      IF HI(WORD, wParam) = %BN_CLICKED THEN
                                      ' // ... close the application by sending a WM_CLOSE message
                                      SendMessage hwnd, %WM_CLOSE, 0, 0
                                      EXIT FUNCTION
                                      END IF
                                      CASE %IDC_BUTTON
                                      ' // Search button pressed. Load the listview control
                                      LoadListView(BYVAL hwnd)
                                      END SELECT
                                      
                                      CASE %WM_SIZE
                                      ' // If the window isn't minimized, resize the controls
                                      IF wParam <> %SIZE_MINIMIZED THEN
                                      ' // Gets the handle of the status bar
                                      LOCAL hStatusBar AS DWORD
                                      hStatusBar = GetDlgItem(hwnd, %IDC_STATUSBAR)
                                      ' // Resizes it
                                      SendMessage hStatusBar, %WM_SIZE, wParam, lParam
                                      ' // Redraws it
                                      InvalidateRect hStatusBar, BYVAL %NULL, %TRUE
                                      ' // Resize the Listview control
                                      pWindow.MoveWindow(GetDlgItem(hwnd, %IDC_LISTVIEW), 5, 5, pWindow.ClientWidth - 5, pWindow.ClientHeight - 75, %TRUE)
                                      ListView_FitHeader(GetDlgItem(hwnd, %IDC_LISTVIEW),6)
                                      ' // Move the position of the edit and button controls
                                      pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_EDIT), 0, 5, pWindow.ClientHeight - 60, 200, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_BUTTON), 0, 550, pWindow.ClientHeight - 60, 75, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      ' // Move the position of the group box control
                                      pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_GROUPBOX), 0, 640, pWindow.ClientHeight - 70, 130, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      ' // Move the position of the radio buttons
                                      pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_OPTION_LOCAL), 0, 645, pWindow.ClientHeight - 55, 60, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      pWindow.SetWindowPos(GetDlgItem(hwnd, %IDC_OPTION_REMOTE), 0, 705, pWindow.ClientHeight - 55, 60, 23, %SWP_NOSIZE OR %SWP_NOZORDER)
                                      
                                      END IF
                                      EXIT FUNCTION
                                      
                                      CASE %WM_DESTROY
                                      ' // End the application
                                      PostQuitMessage 0
                                      EXIT FUNCTION
                                      
                                      END SELECT
                                      
                                      ' // Pass unprocessed messages to Windows
                                      FUNCTION = DefWindowProc(hwnd, uMsg, wParam, lParam)
                                      
                                      END FUNCTION
                                      ' ========================================================================================
                                      Last edited by Michael Rice; 30 Aug 2020, 09:41 AM. Reason: I updated Test2.bas, because I think I posted the same code for Test1.bas and Test2.bas earlier

                                      Comment


                                      • #79
                                        You won't get a row count until the recordset is returned and once you have that, the task is pretty much over.

                                        One thing you can try which I have done is, make TWO query calls to the DB: one to get a count for use with your progress bar/meter without actually getting the result dataset, and a second call to get the results.

                                        eg
                                        Code:
                                        SELECT COUNT (
                                               SELECT a whole  bunch of stuff with joins and stuff )
                                        )
                                        That gets you the number of rows which WILL be returned when you execute the query "for real."

                                        The count may run quicker than the actual query.. will likely depend on the brand DBMS in use. But it's pretty easy to try and it might be worth it to be able to show some kind of numeric progress.

                                        I've actually used this method to pre-size arrays to execute queries into parameters bound as PB Arrays.

                                        MCM
                                        Michael Mattias
                                        Tal Systems Inc. (retired)
                                        Racine WI USA
                                        [email protected]
                                        http://www.talsystems.com

                                        Comment


                                        • #80
                                          RE:
                                          Code:
                                          sSQL = "SELECT T1.Thread_Link, " + _
                                          " T1.PostNumber, " + _
                                          " T3.Name, " + _
                                          " T3.ForumURL, " + _
                                          " T2.ThreadURL, " + _
                                          " T2.ThreadTitle, " + _
                                          " T4.Author, " + _
                                          " T1.PostDate, " + _
                                          " T1.PostContent " + _
                                          " FROM ThreadContent T1 " + _
                                          " LEFT OUTER JOIN Threads T2 " + _
                                          " ON T2.ThreadNumber = T1.Thread_Link " + _
                                          " LEFT OUTER JOIN Forums T3 " + _
                                          " ON T2.ThreadNumber = T1.Thread_Link " + _
                                          " AND T2.Forum_Link = T3.RowID " + _
                                          " LEFT OUTER JOIN ForumAuthors T4 " + _
                                          " ON T1.Author_Link = T4.RowID " + _
                                          "WHERE T2.ThreadTitle LIKE '%" + sSearchText + "%' " + _
                                          " OR T1.PostContent LIKE '%" + sSearchText + "%' " + _
                                          "ORDER BY T1.Thread_Link ASC, T1.PostNumber ASC;"
                                          I "ASSUME" you are PREPARING this query? Not forcing the DB to parse and plan it each time you execute it?

                                          MCM
                                          Michael Mattias
                                          Tal Systems Inc. (retired)
                                          Racine WI USA
                                          [email protected]
                                          http://www.talsystems.com

                                          Comment

                                          Working...
                                          X