$Option EXPLICIT
$Include "RapidQ.inc"

Const GWL_HWNDPARENT = (-8)
Const HWND_DESKTOP   = 0
Const SQLITE_OK      = 0

Declare Function SetWindowLong _
        Lib "user32" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, ByVal nIndex As Long, _
         ByVal dwNewLong As Long) As Long

' You may have to hard-code the Lib with
'  the folder where your sqlite3.dll is located.
' If you do not have the dll...
'  the latest version can be downloaded
'  at https://www.sqlite.org/download.html
'  (for example: sqlite-dll-win32-x86-3230100.zip)
' Extract the zip file into the folder where
'  this source code resides, then compile.
Declare Function sqlite3_open _
        Lib "sqlite3.dll" Alias "sqlite3_open"_
       (ByVal zFileName As String,_
        ByRef ppDb As Long) As Long

Declare Function sqlite3_complete _
        Lib "sqlite3.dll" Alias "sqlite3_complete"_
        (ByVal zSQL As Long) As Long

Declare Function sqlite3_exec _
        Lib "sqlite3.dll" Alias "sqlite3_exec"_
        (ByVal ppDb As Long,_
         ByVal zSQL As Long,_
         ByVal pCallback As Long,_
         ByVal pUserData As Long,_
         ByVal ppErrMsg As Long) As Long

Declare Function sqlite3_close _
        Lib "sqlite3.dll" Alias "sqlite3_close"_
        (ByVal ppDb As Long) As Long

Declare Function sqlite3_malloc _
        Lib "sqlite3.dll" Alias "sqlite3_malloc"_
        (ByVal iSize As Long) As Long

Declare Function sqlite3_free _
        Lib "sqlite3.dll" Alias "sqlite3_free"_
        (ByVal ppErrMsg As Long) As Long

Declare Sub MainForm_OnShow
Declare Sub MainForm_OnClose

Declare Sub Open_SQLite3
Declare Sub Close_SQLite3

' These two sub are simply so you have
'  a database file to test the callback on.
Declare Sub mnuCreate_OnClick
Declare Sub mnuInsert_OnClick

' This sub is for testing the callback.
Declare Sub mnuTest_OnClick

Declare Function SQLite3_CallBack(ByVal lpVOID As Long, ByVal Cols As Long, ByVal lpData As Long, ByVal lpColsName As Long) As Long

DefStr sCmd  = " "
DefStr sFile = (Command$(0) - Application.Exename) + "CountryCodes.db"
DefStr sTemp = DIR$(sFile, &H1)

DefLng lRet  = 0
DefLng hSQL  = 0
DefLng pCmd  = 0
DefLng pMsg  = 0
DefLng nSize = 0

Dim TestData As QStringList
    TestData.AddItems("Afghanistan,AFG")
    TestData.AddItems("Albania,ALB")
    TestData.AddItems("Algeria,DZA")
    TestData.AddItems("American Samoa,ASM")
    TestData.AddItems("Andorra,AND")
    TestData.AddItems("Angola,AGO")
    TestData.AddItems("Anguilla,AIA")
    TestData.AddItems("Antarctica,ATA")
    TestData.AddItems("Antigua and Barbuda,ATG")
    TestData.AddItems("Argentina,ARG")
    TestData.AddItems("Armenia,ARM")
    TestData.AddItems("Aruba,ABW")
    TestData.AddItems("Australia,AUS")
    TestData.AddItems("Austria,AUT")
    TestData.AddItems("Azerbaijan,AZE")
    TestData.AddItems("Bahamas,BHS")
    TestData.AddItems("Bahrain,BHR")
    TestData.AddItems("Bangladesh,BGD")
    TestData.AddItems("Barbados,BRB")
    TestData.AddItems("Belarus,BLR")
    TestData.AddItems("Belgium,BEL")
    TestData.AddItems("Belize,BLZ")
    TestData.AddItems("Benin,BEN")
    TestData.AddItems("Bermuda,BMU")
    TestData.AddItems("Bhutan,BTN")
    TestData.AddItems("Bolivia,BOL")
    TestData.AddItems("Bosnia and Herzegovina,BIH")
    TestData.AddItems("Botswana,BWA")
    TestData.AddItems("Brazil,BRA")
    TestData.AddItems("British Indian Ocean Territory,IOT")
    TestData.AddItems("British Virgin Islands,VGB")
    TestData.AddItems("Brunei,BRN")
    TestData.AddItems("Bulgaria,BGR")
    TestData.AddItems("Burkina Faso,BFA")
    TestData.AddItems("Burundi,BDI")
    TestData.AddItems("Cambodia,KHM")
    TestData.AddItems("Cameroon,CMR")
    TestData.AddItems("Canada,CAN")
    TestData.AddItems("Cape Verde,CPV")
    TestData.AddItems("Cayman Islands,CYM")
    TestData.AddItems("Central African Republic,CAF")
    TestData.AddItems("Chad,TCD")
    TestData.AddItems("Chile,CHL")
    TestData.AddItems("China,CHN")
    TestData.AddItems("Christmas Island,CXR")
    TestData.AddItems("Cocos Islands,CCK")
    TestData.AddItems("Colombia,COL")
    TestData.AddItems("Comoros,COM")
    TestData.AddItems("Cook Islands,COK")
    TestData.AddItems("Costa Rica,CRI")
    TestData.AddItems("Croatia,HRV")
    TestData.AddItems("Cuba,CUB")
    TestData.AddItems("Curacao,CUW")
    TestData.AddItems("Cyprus,CYP")
    TestData.AddItems("Czech Republic,CZE")
    TestData.AddItems("Democratic Republic of the Congo,COD")
    TestData.AddItems("Denmark,DNK")
    TestData.AddItems("Djibouti,DJI")
    TestData.AddItems("Dominica,DMA")
    TestData.AddItems("Dominican Republic,DOM")
    TestData.AddItems("East Timor,TLS")
    TestData.AddItems("Ecuador,ECU")
    TestData.AddItems("Egypt,EGY")
    TestData.AddItems("El Salvador,SLV")
    TestData.AddItems("Equatorial Guinea,GNQ")
    TestData.AddItems("Eritrea,ERI")
    TestData.AddItems("Estonia,EST")
    TestData.AddItems("Ethiopia,ETH")
    TestData.AddItems("Falkland Islands,FLK")
    TestData.AddItems("Faroe Islands,FRO")
    TestData.AddItems("Fiji,FJI")
    TestData.AddItems("Finland,FIN")
    TestData.AddItems("France,FRA")
    TestData.AddItems("French Polynesia,PYF")
    TestData.AddItems("Gabon,GAB")
    TestData.AddItems("Gambia,GMB")
    TestData.AddItems("Georgia,GEO")
    TestData.AddItems("Germany,DEU")
    TestData.AddItems("Ghana,GHA")
    TestData.AddItems("Gibraltar,GIB")
    TestData.AddItems("Greece,GRC")
    TestData.AddItems("Greenland,GRL")
    TestData.AddItems("Grenada,GRD")
    TestData.AddItems("Guam,GUM")
    TestData.AddItems("Guatemala,GTM")
    TestData.AddItems("Guernsey,GGY")
    TestData.AddItems("Guinea,GIN")
    TestData.AddItems("Guinea-Bissau,GNB")
    TestData.AddItems("Guyana,GUY")
    TestData.AddItems("Haiti,HTI")
    TestData.AddItems("Honduras,HND")
    TestData.AddItems("Hong Kong,HKG")
    TestData.AddItems("Hungary,HUN")
    TestData.AddItems("Iceland,ISL")
    TestData.AddItems("India,IND")
    TestData.AddItems("Indonesia,IDN")
    TestData.AddItems("Iran,IRN")
    TestData.AddItems("Iraq,IRQ")
    TestData.AddItems("Ireland,IRL")
    TestData.AddItems("Isle of Man,IMN")
    TestData.AddItems("Israel,ISR")
    TestData.AddItems("Italy,ITA")
    TestData.AddItems("Ivory Coast,CIV")
    TestData.AddItems("Jamaica,JAM")
    TestData.AddItems("Japan,JPN")
    TestData.AddItems("Jersey,JEY")
    TestData.AddItems("Jordan,JOR")
    TestData.AddItems("Kazakhstan,KAZ")
    TestData.AddItems("Kenya,KEN")
    TestData.AddItems("Kiribati,KIR")
    TestData.AddItems("Kosovo,XKX")
    TestData.AddItems("Kuwait,KWT")
    TestData.AddItems("Kyrgyzstan,KGZ")
    TestData.AddItems("Laos,LAO")
    TestData.AddItems("Latvia,LVA")
    TestData.AddItems("Lebanon,LBN")
    TestData.AddItems("Lesotho,LSO")
    TestData.AddItems("Liberia,LBR")
    TestData.AddItems("Libya,LBY")
    TestData.AddItems("Liechtenstein,LIE")
    TestData.AddItems("Lithuania,LTU")
    TestData.AddItems("Luxembourg,LUX")
    TestData.AddItems("Macau,MAC")
    TestData.AddItems("Macedonia,MKD")
    TestData.AddItems("Madagascar,MDG")
    TestData.AddItems("Malawi,MWI")
    TestData.AddItems("Malaysia,MYS")
    TestData.AddItems("Maldives,MDV")
    TestData.AddItems("Mali,MLI")
    TestData.AddItems("Malta,MLT")
    TestData.AddItems("Marshall Islands,MHL")
    TestData.AddItems("Mauritania,MRT")
    TestData.AddItems("Mauritius,MUS")
    TestData.AddItems("Mayotte,MYT")
    TestData.AddItems("Mexico,MEX")
    TestData.AddItems("Micronesia,FSM")
    TestData.AddItems("Moldova,MDA")
    TestData.AddItems("Monaco,MCO")
    TestData.AddItems("Mongolia,MNG")
    TestData.AddItems("Montenegro,MNE")
    TestData.AddItems("Montserrat,MSR")
    TestData.AddItems("Morocco,MAR")
    TestData.AddItems("Mozambique,MOZ")
    TestData.AddItems("Myanmar,MMR")
    TestData.AddItems("Namibia,NAM")
    TestData.AddItems("Nauru,NRU")
    TestData.AddItems("Nepal,NPL")
    TestData.AddItems("Netherlands,NLD")
    TestData.AddItems("Netherlands Antilles,ANT")
    TestData.AddItems("New Caledonia,NCL")
    TestData.AddItems("New Zealand,NZL")
    TestData.AddItems("Nicaragua,NIC")
    TestData.AddItems("Niger,NER")
    TestData.AddItems("Nigeria,NGA")
    TestData.AddItems("Niue,NIU")
    TestData.AddItems("North Korea,PRK")
    TestData.AddItems("Northern Mariana Islands,MNP")
    TestData.AddItems("Norway,NOR")
    TestData.AddItems("Oman,OMN")
    TestData.AddItems("Pakistan,PAK")
    TestData.AddItems("Palau,PLW")
    TestData.AddItems("Palestine,PSE")
    TestData.AddItems("Panama,PAN")
    TestData.AddItems("Papua New Guinea,PNG")
    TestData.AddItems("Paraguay,PRY")
    TestData.AddItems("Peru,PER")
    TestData.AddItems("Philippines,PHL")
    TestData.AddItems("Pitcairn,PCN")
    TestData.AddItems("Poland,POL")
    TestData.AddItems("Portugal,PRT")
    TestData.AddItems("Puerto Rico,PRI")
    TestData.AddItems("Qatar,QAT")
    TestData.AddItems("Republic of the Congo,COG")
    TestData.AddItems("Reunion,REU")
    TestData.AddItems("Romania,ROU")
    TestData.AddItems("Russia,RUS")
    TestData.AddItems("Rwanda,RWA")
    TestData.AddItems("Saint Barthelemy,BLM    ")
    TestData.AddItems("Saint Helena,SHN")
    TestData.AddItems("Saint Kitts and Nevis,KNA")
    TestData.AddItems("Saint Lucia,LCA")
    TestData.AddItems("Saint Martin,MAF")
    TestData.AddItems("Saint Pierre and Miquelon,SPM")
    TestData.AddItems("Saint Vincent and the Grenadines,VCT")
    TestData.AddItems("Samoa,WSM")
    TestData.AddItems("San Marino,SMR")
    TestData.AddItems("Sao Tome and Principe,STP")
    TestData.AddItems("Saudi Arabia,SAU")
    TestData.AddItems("Senegal,SEN")
    TestData.AddItems("Serbia,SRB")
    TestData.AddItems("Seychelles,SYC")
    TestData.AddItems("Sierra Leone,SLE")
    TestData.AddItems("Singapore,SGP")
    TestData.AddItems("Sint Maarten,SXM")
    TestData.AddItems("Slovakia,SVK")
    TestData.AddItems("Slovenia,SVN")
    TestData.AddItems("Solomon Islands,SLB")
    TestData.AddItems("Somalia,SOM")
    TestData.AddItems("South Africa,ZAF")
    TestData.AddItems("South Korea,KOR")
    TestData.AddItems("South Sudan,SSD")
    TestData.AddItems("Spain,ESP")
    TestData.AddItems("Sri Lanka,LKA")
    TestData.AddItems("Sudan,SDN")
    TestData.AddItems("Suriname,SUR")
    TestData.AddItems("Svalbard and Jan Mayen,SJM")
    TestData.AddItems("Swaziland,SWZ")
    TestData.AddItems("Sweden,SWE")
    TestData.AddItems("Switzerland,CHE")
    TestData.AddItems("Syria,SYR")
    TestData.AddItems("Taiwan,TWN")
    TestData.AddItems("Tajikistan,TJK")
    TestData.AddItems("Tanzania,TZA")
    TestData.AddItems("Thailand,THA")
    TestData.AddItems("Togo,TGO")
    TestData.AddItems("Tokelau,TKL")
    TestData.AddItems("Tonga,TON")
    TestData.AddItems("Trinidad and Tobago,TTO")
    TestData.AddItems("Tunisia,TUN")
    TestData.AddItems("Turkey,TUR")
    TestData.AddItems("Turkmenistan,TKM")
    TestData.AddItems("Turks and Caicos Islands,TCA")
    TestData.AddItems("Tuvalu,TUV")
    TestData.AddItems("U.S. Virgin Islands,VIR")
    TestData.AddItems("Uganda,UGA")
    TestData.AddItems("Ukraine,UKR")
    TestData.AddItems("United Arab Emirates,ARE")
    TestData.AddItems("United Kingdom,GBR")
    TestData.AddItems("United States,USA")
    TestData.AddItems("Uruguay,URY")
    TestData.AddItems("Uzbekistan,UZB")
    TestData.AddItems("Vanuatu,VUT")
    TestData.AddItems("Vatican,VAT")
    TestData.AddItems("Venezuela,VEN")
    TestData.AddItems("Vietnam,VNM")
    TestData.AddItems("Wallis and Futuna,WLF")
    TestData.AddItems("Western Sahara,ESH")
    TestData.AddItems("Yemen,YEM")
    TestData.AddItems("Zambia,ZMB")
    TestData.AddItems("Zimbabwe,ZWE")

Create MainForm As QForm
  Width   = 640
  Height  = 480
  Left    = (Screen.Width\2)-(MainForm.Width\2)
  Top     = (Screen.Height\2)-(MainForm.Height\2)
  OnShow  = MainForm_OnShow
  OnClose = MainForm_OnClose
  Create mnuMain As QMainMenu
    Create mnuCreate As QMenuItem
      Caption = "Create"
      OnClick = mnuCreate_OnClick
    End Create
    Create mnuInsert As QMenuItem
      Caption = "Insert"
      OnClick = mnuInsert_OnClick
    End Create
    Create mnuTest As QMenuItem
      Caption = "Test"
      OnClick = mnuTest_OnClick
    End Create
    Create mnuExit As QMenuItem
      Caption = "Exit"
      OnClick = MainForm_OnClose
    End Create
  End Create
  Create RichEdit As QRichEdit
    Align      = alClient
    Font.Name  = "Courier New"
    Font.Size  = 10
    ScrollBars = ssBoth
  End Create
End Create

SetWindowLong(MainForm.Handle, GWL_HWNDPARENT, HWND_DESKTOP)
SetWindowLong(Application.Handle, GWL_HWNDPARENT, MainForm.Handle)

MainForm.ShowModal

Sub MainForm_OnShow
    If sTemp <> "" Then
      nSize = FileRec.Size
      If nSize > 0 Then
        mnuCreate.Enabled = False
        mnuInsert.Enabled = False
      Else
        mnuInsert.Enabled = False
        mnuTest.Enabled = False
      End If
    Else
      mnuInsert.Enabled = False
      mnuTest.Enabled = False
    End If
    nSize = 0
    sTemp = 0
End Sub

Sub MainForm_OnClose
    If pMsg <> 0 Then sqlite3_free(pMsg)
    If hSQL <> 0 Then sqlite3_close(hSQL)
    sCmd  = ""
    sFile = ""
    lRet  = 0
    hSQL  = 0
    pCmd  = 0
    pMsg  = 0
    Application.Terminate
End Sub

Sub Open_SQLite3
    If hSQL = 0 Then
      lRet = sqlite3_open(sFile, hSQL)
      If lRet <> SQLITE_OK Then
        RichEdit.AddStrings("Open Error. Error Code("+Str$(lRet)+")")
      Else
        RichEdit.AddStrings("Open Successful.")
      End If
    Else
      RichEdit.AddStrings(" [File Already Opened.]")
    End If
End Sub

Sub Close_SQLite3
    If hSQL <> 0 Then
      lRet = sqlite3_close(hSQL)
      If lRet <> SQLITE_OK Then
        RichEdit.AddStrings("Close Error. Error Code("+Str$(lRet)+")")
      Else
        RichEdit.AddStrings("Close Successful.")
        hSQL = 0
      End If
    Else
      RichEdit.AddStrings(" [File Already Closed.]")
    End If
    RichEdit.AddStrings("...")
End Sub

Sub mnuCreate_OnClick
    pMsg = 0
    mnuCreate.Enabled = False
    DoEvents
    If hSQL = 0 Then Open_SQLite3
    If hSQL <> 0 Then
      sCmd = "CREATE TABLE CountryCodes (COUNTRY_ID INTEGER PRIMARY KEY, COUNTRY_NAME TEXT UNIQUE, COUNTRY_CODE TEXT);"
      pCmd = VarPtr(sCmd)
      lRet = sqlite3_complete(pCmd)
      If lRet <> 0 Then
        pMsg = sqlite3_malloc(256)
        lRet = sqlite3_exec(hSQL, pCmd, 0, 0, pMsg)
        If lRet <> SQLITE_OK Then
          RichEdit.AddStrings(" Error Creating Table. Error Code("+Str$(lRet)+")")
          RichEdit.AddStrings(" [Table already exists?]")
        Else
          RichEdit.AddStrings(" Create Successful.")
        End If
      Else
        RichEdit.AddStrings(" SQL statement Error. Error Code("+Str$(lRet)+")")
        RichEdit.AddStrings(sCmd)
      End If
    End If
    If pMsg <> 0 Then
      sqlite3_free(pMsg)
      pMsg = 0
    End If
    If hSQL <> 0 Then Close_SQLite3
    mnuInsert.Enabled = True
End Sub

Sub mnuInsert_OnClick
    pMsg = 0
    mnuInsert.Enabled = False
    DoEvents
    DefLng i = 0
    DefLng x = 0
    DefLng y = 0
    DefStr name = ""
    DefStr code = ""
    If hSQL = 0 Then Open_SQLite3
    If hSQL <> 0 Then
      RichEdit.AddStrings(" PLEASE WAIT for the [Close Successful] message.")
      RichEdit.AddStrings(" Inserting ("+Str$(TestData.ItemCount)+") records.")
L1:   x = Instr(TestData.Item(i),",")
      name = Left$(TestData.Item(i), x-1)
      code = Mid$(TestData.Item(i), x+1, 3)
      sCmd = "INSERT INTO CountryCodes (COUNTRY_NAME, COUNTRY_CODE) VALUES ('" + name + "', '" + code + "');"
      pCmd = VarPtr(sCmd)
      lRet = sqlite3_complete(pCmd)
      If lRet <> 0 Then
        pMsg = sqlite3_malloc(256)
        lRet = sqlite3_exec(hSQL, pCmd, 0, 0, pMsg)
        If lRet <> SQLITE_OK Then
          RichEdit.AddStrings(" Insert Error. Error Code("+Str$(lRet)+")")
          RichEdit.AddStrings(" [Record already exists?]")
          i = TestData.ItemCount
         Else
          If i = 0 Then
            RichEdit.AddStrings(" Insert Record "+Format$("%.3d", (i+1))+" Succesful.")
          Else
            y = RichEdit.LineCount - 1
            RichEdit.Line(y) = " Insert Record "+Format$("%.3d", (i+1))+" Succesful."
          End If
        End If
      Else
        RichEdit.AddStrings(" SQL statement Error. Error Code("+Str$(lRet)+")")
        RichEdit.AddStrings(sCmd)
        i = TestData.ItemCount
      End If
      DoEvents
      i = i + 1
      If i < TestData.ItemCount Then Goto L1
    End If
    If pMsg <> 0 Then
      sqlite3_free(pMsg)
      pMsg = 0
    End If
    If hSQL <> 0 Then Close_SQLite3
    mnuTest.Enabled = True
End Sub

Sub mnuTest_OnClick
    DefLng pCallBack = CodePtr(SQLite3_CallBack)
    pMsg = 0
    mnuTest.Enabled = False
    DoEvents
    If hSQL = 0 Then Open_SQLite3
    If hSQL <> 0 Then
      sCmd = "SELECT * FROM CountryCodes;"
      pCmd = VarPtr(sCmd)
      lRet = sqlite3_complete(pCmd)
      If lRet <> 0 Then
        pMsg = sqlite3_malloc(256)

' Here's where a problem exists.
' The following line of code is "suppose to" execute the callback
'  for "each AND every record" in the database file.
' For reasons unknown.. it executes the callback ONLY 3 times!!!
'  (even though the database file [created above] contains 240 records)
' The repetitive execution of the callback just stops
'  AND nothing after this line of code executes.
'  (you will have to click 'Exit')
' I tried "numerous" ways to find out why.
'  (not even the error message {pMsg} contains a clue)
' This is why I gave up on trying to use sqlite3_exec
'  to "get" information from a database file.
'  (sqlite3_exec seems to work fine for creating, inserting, updating, and deleting)
' If you can figure out how to solve this problem...
'  PLEASE post a message in the RapidQ group.
        lRet = sqlite3_exec(hSQL, pCmd, pCallBack, 0, pMsg)

        If lRet <> SQLITE_OK Then
          RichEdit.AddStrings(" CallBack Error. Error Code("+Str$(lRet)+")")
        Else
          RichEdit.AddStrings(" CallBack Successful.")
        End If
      Else
        RichEdit.AddStrings(" SQL statement Error. Error Code("+Str$(lRet)+")")
        RichEdit.AddStrings(sCmd)
      End If
    End If
    If pMsg <> 0 Then
      RichEdit.AddStrings(VarPtr$(pMsg))
      sqlite3_free(pMsg)
      pMsg = 0
    End If
    If hSQL <> 0 Then Close_SQLite3
    pCallBack = 0
End Sub

Function SQLite3_CallBack(ByVal lpVOID As Long, ByVal Cols As Long, ByVal lpData As Long, ByVal lpColsName As Long) As Long
    DefLng p1 = lpVOID
    DefLng p2 = Cols
    DefLng p3 = lpData
    DefLng p4 = lpColsName

    DefLng x1 = 0
    DefLng x2 = 0
    DefLng y1 = 0
    DefLng y2 = 0
    DefLng z = 0

L2: x1 = p4 + (z * 4)
    MemCpy(VarPtr(x2), x1, 4)

    y1 = p3 + (z * 4)
    MemCpy(VarPtr(y2), y1, 4)

    RichEdit.AddStrings(" Column '" + VarPtr$(x2) + "' contains the data '" + VarPtr$(y2) + "'")
    DoEvents
    z = z + 1
    If z < p2 Then Goto L2

    RichEdit.AddStrings(" ...")

    Result = 0
End Function