Announcement

Collapse

Forum Guidelines

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

lite SQLite Class

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

  • PBWin/PBCC lite SQLite Class

    Code:
    'SQLiteClass1.inc
    Declare Function sqlite3_open CDecl Lib "sqlite3.dll" Alias "sqlite3_open" (zFilename As Asciiz, mHDB As Long) As Long
    Declare Sub      sqlite3_close CDecl Lib "sqlite3.dll" Alias "sqlite3_close" (ByVal mHDB As Long)
    Declare Function sqlite_get_table CDecl Lib "sqlite3.dll" Alias "sqlite3_get_table" (ByVal mHDB As Long, szSql As Asciiz, lpTable As Long, nRow As Long, nColumn As Long, lpErrMsg As Long) As Long
    Declare Function sqlite_free_table CDecl Lib "sqlite3.dll" Alias "sqlite3_free_table" (ByVal lpTable As Long Ptr) As Long
    Declare Function sqlite3_errmsg CDecl Lib "sqlite3.dll" Alias "sqlite3_errmsg" (ByVal mHDB As Long) As Long
        'simple little class for working with local SQLite databases
        '   need: latest DLL from SQLite.org
        '
        '   check http://planetsquires.com/ for heavy duty SQLite work
        '
        'Open("database");
        '   opens existing database
        '   creates database and opens it if file doesn't exist
        '
        'Execute(); execute SQL statement that doesn't return a result: "CREATE TABLE ..."
        'Quickie(); execute statement that only returns one result: "SELECT COUNT(*)"
        '
        'Queries:
        '   SelectBegin("select * from ...") : Queries must be closed; SelectEnd()
        '
        '   RowCount() = number of rows in query results
        '   ColumnCount() = number of columns in query results
        '
        '   First(), Next(), Last(), Previous(); move around query results
        '   Get() or GetAt() to get column's value in query results
        '
        '
        'query results indexes are ONE based
        '   first row = 1; first column in result = 1
        '
        'you can use Column Name or the order of the Column in the statement to get a column's value
        '   Get("name"); GetAt(1)
    Class SQLiteDatabaseC
        Instance mHDB As Long
        Instance mPTable As Long Ptr
        Instance mColumns() As String
        Instance mRowCount As Long
        Instance mColCount As Long
        Instance mRowNo As Long
        Instance mFirstCol As Long
        Interface SQLiteDatabaseI
            Inherit IUnknown
            Method Open(ByVal file As String) As Long
                'Create or Open a database file
                'True/False success
                If Len(file) Then
                    If sqlite3_open(ByVal StrPtr(file), mHDB) = 0 Then
                        Method = 1
                    Else
                        mHDB = 0
                    End If
                End If
            End Method
            Method Close()
                'close database file
                sqlite3_close(mHDB)
                mHDB = 0
            End Method
            Method ErrorMessage() As String
                'return last error message
                'Thanks to Don Dickinson
                Local pzErr As Asciiz Ptr
                pzErr = sqlite3_errmsg(mHDB)
                If pzErr Then Method = @pzErr
            End Method
            Method Fix(ByVal s As String) As String
                'escape single quotes in string
                Replace "'" With "''" In s
                Method = s
            End Method
            Method Execute(ByVal sql As String) As Long
                'execute an SQL statement with NO return
                'True/False success
                '   "CREATE TABLE ...", "BEGIN TRANSACTION", "END TRANSACTION", ...etc
                Local lpTable    As Long Ptr
                Local lpErrorSz  As Long
                Local RowCount&, ColCount&
                Method = IIf&( sqlite_get_table(mHDB, ByVal StrPtr(sql), lpTable, RowCount&, ColCount&, lpErrorSz)=0, 1, 0 )
                sqlite_free_table lpTable
            End Method
            Method Quickie(ByVal sql As String) As String
                'return a value from an SQL statement that will have only one return
                '   SELECT COUNT(*) ...
                If me.SelectBegin(sql) And me.First() Then Method = me.GetAt(1)
                me.SelectEnd()
            End Method
            Method SelectBegin(ByVal sql As String) As Long
                'execute a query and return a record set
                '   MUST be freed: SelectEnd()
                'True/False success
                '   thanks to Terence McDonnell
                Local i As Long
                Local lpErrorSz  As Long
                Local pzCol As Asciiz Ptr
                mPTable = 0
                Erase mColumns()
                mRowCount = 0
                mColCount = 0
                mRowNo = 0
                mFirstCol = 0
                If Len(sql) Then
                    If sqlite_get_table(mHDB, ByVal StrPtr(sql), mPTable, mRowCount, mColCount, lpErrorSz) = 0 Then
                        If mRowCount = 0 Then
                            Method = 1 'command succeed - no return
                            sqlite_free_table mPTable
                        Else
                            ReDim mColumns(1 To mColCount)
                            For i = 1 To mColCount
                                pzCol = @mPTable[i - 1]
                                mColumns(i) = @pzCol
                            Next i
                            Method = 1
                        End If
                    End If
                End If
            End Method
            Method SelectEnd()
                'close query
                '   MUST close queries to free sqlite internal table
                If mRowCount Then sqlite_free_table mPTable
                mPTable = 0
                Erase mColumns()
                mRowCount = 0
                mColCount = 0
                mRowNo = 0
                mFirstCol = 0
            End Method
            Method RowCount() As Long
                'get query result row count
                Method = mRowCount
            End Method
            Method ColumnCount() As Long
                'get query result column count
                Method = mColCount
            End Method
            Method First() As Long
                'move to first row in query results
                'True/False success
                If mRowCount Then
                    mRowNo = 1
                    mFirstCol = mRowNo * mColCount
                    Method = 1
                End If
            End Method
            Method Last() As Long
                'move to last row in query results
                'True/False success
                If mRowCount Then
                    mRowNo = mRowCount
                    mFirstCol = mRowNo * mColCount
                    Method = 1
                End If
            End Method
            Method Next() As Long
                'move to next row in query results
                'True/False success
                If mRowCount Then
                    Incr mRowNo
                    If mRowNo > 0 And mRowNo <= mRowCount Then
                        mFirstCol = mRowNo * mColCount
                        Method = 1
                    End If
                End If
            End Method
            Method Previous() As Long
                'move to previous row in query results
                'True/False success
                If mRowCount Then
                    Decr mRowNo
                    If mRowNo > 0 And mRowNo <= mRowCount Then
                        mFirstCol = mRowNo * mColCount
                        Method = 1
                    End If
                End If
            End Method
            Method GoTo(ByVal row As Long) As Long
                'move to row in query results
                'True/False success
                If mRowCount And row > 0  And row <= mRowCount Then
                    mRowNo = row
                    mFirstCol = mRowNo * mColCount
                    Method = 1
                End If
            End Method
            Method Get(ByVal Column As String) As String
                'get value for column on current row of query results
                If mRowNo > 0 And mRowNo <= mRowCount Then
                    Local x, ndx As Long
                    x = me.ColumnNo(Column)
                    ndx = mFirstCol + x - 1
                    Local pzCol As Asciiz Ptr
                    pzCol = @mPTable[ndx]
                    Method = @pzCol
                End If
            End Method
            Method pszGet(ByVal Column As String) As Long
                'get value for column on current row of query results
                'returns ASCIIZ ptr to value
                '   lot faster way to load ListView which is expecting an ASCIIZ ptr
                If mRowNo > 0 And mRowNo <= mRowCount Then
                    Local x, ndx As Long
                    x = me.ColumnNo(Column)
                    ndx = mFirstCol + x - 1
                    Method = @mPTable[ndx]
                End If
            End Method
            Method GetAt(ByVal columnNo As Long) As String
                'get value for columnNo on current row of query results
                If mRowNo > 0 And mRowNo <= mRowCount Then
                    If columnNo > 0 And columnNo <= UBound(mColumns) Then
                        Local ndx As Long
                        ndx = mFirstCol + columnNo - 1
                        Local pzCol As Asciiz Ptr
                        pzCol = @mPTable[ndx]
                        Method = @pzCol
                    End If
                End If
            End Method
            Method pszGetAt(ByVal columnNo As Long) As Long
                'get value for columnNo on current row of query results
                'returns ASCIIZ ptr to value
                '   lot faster way to load ListView which is expecting an ASCIIZ ptr
                If mRowNo > 0 And mRowNo <= mRowCount Then
                    If columnNo > 0 And columnNo <= UBound(mColumns) Then
                        Local ndx As Long
                        ndx = mFirstCol + columnNo - 1
                        Method = @mPTable[ndx]
                    End If
                End If
            End Method
            Method ColumnNo(ByVal Column As String) As Long
                'return position of column in query results
                'zero if not found
                'ToDo: replace with hash or trie
                Local x As Long
                Array Scan mColumns(), Collate UCase, = Column, To x
                Method = x
            End Method
            Method Column(ByVal columnNo As Long) As String
                'get column name for columnNo in query results
                'ONE based index
                If columnNo > 0 And columnNo <= UBound(mColumns) Then Method = mColumns(columnNo)
            End Method
        End Interface
    End Class
    stanthemanstan~gmail
    Dead Theory Walking
    Range Trie Tree
    HLib ~ Free Data Container Lib ~ Arrays, Lists, Stacks, Queues, Deques, Trees, Hashes

  • #2
    'SQLiteTest.bas
    Code:
     
    #PBForms CREATED V1.51
    'pbwin 9
    $TestSource = "SQLiteTest.bas"
    $TestTitle = "SQLite Class Test"
    #Compile Exe "SQLiteTest.exe"
    #Dim All
    #Optimize Speed
    #Include Once "SQLiteClass1.inc"
    #PBForms BEGIN INCLUDES
    #If Not %Def(%WINAPI)
        #Include Once "WIN32API.INC"
    #EndIf
    #Include Once "PBForms.INC"
    #PBForms END INCLUDES
    #PBForms BEGIN CONSTANTS
    %Dlg1    =  101
    %BtnTest = 1002
    %Lbx1    = 1001
    #PBForms END CONSTANTS
    Declare CallBack Function ShowDlg1Proc()
    Declare Function ShowDlg1(ByVal hParent As Dword) As Long
    #PBForms DECLARATIONS
    Global lbx As LBxI
    Global tmr As TimerI
    Function PBMain()
        ShowDlg1 %HWND_Desktop
    End Function
    Sub BtnTest(ByVal hDlg As Long)
        Local i, j, ok As Long
        Local file As String
        Local Col1, Col2 As String
        Local s As String
        Randomize
        lbx.Clear(2)
        Local db As SQLiteDatabaseI : db = Class "SQLiteDatabaseC"
        If IsNothing(db) Then
            ? "database object IsNothing"
            Exit Sub
        End If
        'open database - create database if it doesn't exist
        file = EXE.Path$ + "TestDB.SQLite"
        If db.Open(file) = %FALSE Then
            ? "fail to open database" : Exit Sub
        End If
        'you can use Execute() to execute an SQL statement that doesn't return a result
        db.Execute("drop table Table1")
        db.Execute("create table Table1 (Col1 text, Col2 txt)")
        For i = 1 To 10
            Col1 = ""
            Col2 = ""
            For j = 1 To 5
                Col1 += Chr$(Rnd(Asc("a"), Asc("z")))
                Col2 += Chr$(Rnd(Asc("a"), Asc("z")))
            Next j
            db.Execute("insert into Table1 (Col1, Col2) values ('"+Col1+"', '"+Col2+"')")
        Next i
        'quickie SQL query; for queries that only return one value
        lbx.Add( "Row Count = " + db.Quickie("select count(*) from Table1") )
        If IsFalse db.SelectBegin("select * from Table1") Then
            lbx.Add(db.ErrorMessage())
            ? "query failed" : Exit Sub
        End If
        lbx.Add("")
        lbx.Add("Query 1:")
        ok = db.First()
        While ok
            s = "| "
            s += db.GetAt(1)
            s += " | "
            s += db.GetAt(2)
            s += " |"
            lbx.Add(s)
            ok = db.Next()
        Wend
        lbx.Add("!!! absolutely must free a SelectBegin() query !!!: SelectEnd()")
        lbx.Add("     that frees SQLite's internal result table")
        lbx.Add("  Execute() and Quickie() automatically free SQLite table")
        db.SelectEnd()
        
        db.SelectBegin("select * from Table1 order by Col1, Col2")
        lbx.Add("")
        lbx.Add("Query 1: Backwards")
        ok = db.Last()
        While ok
            s = "| "
            s += db.Get("col1")
            s += " | "
            s += db.Get("col2")
            s += " |"
            lbx.Add(s)
            ok = db.Previous()
        Wend
        db.SelectEnd()
        'close database
        db.Close()
        lbx.Add("")
        lbx.Add("done...")
    End Sub
    CallBack Function ShowDlg1Proc()
        Select Case As Long CbMsg
            Case %WM_InitDialog
                 lbx = Class "LBxC"
                 lbx.INI(Cb.Hndl, %Lbx1)
                 lbx.SetHorizontal(1000)
                 tmr = Class "TimerC"
            Case %WM_NCActivate
                Static hWndSaveFocus As Dword
                If IsFalse CbWParam Then
                    hWndSaveFocus = GetFocus()
                ElseIf hWndSaveFocus Then
                    SetFocus(hWndSaveFocus)
                    hWndSaveFocus = 0
                End If
            Case %WM_Command
                Select Case As Long CbCtl
                    Case %BtnTest
                        If CbCtlMsg = %BN_Clicked Or CbCtlMsg = 1 Then
                            BtnTest(Cb.Hndl)
                        End If
                End Select
        End Select
    End Function
    Function ShowDlg1(ByVal hParent As Dword) As Long
        Local lRslt  As Long
    #PBForms BEGIN DIALOG %Dlg1->->
        Local hDlg   As Dword
        Local hFont1 As Dword
        Dialog New hParent, $TestTitle, 67, 61, 341, 241, %WS_Popup _
            Or %WS_Border Or %WS_DlgFrame Or %WS_Caption Or %WS_SysMenu Or _
            %WS_MinimizeBox Or %WS_ClipSiblings Or %WS_Visible Or %DS_ModalFrame _
            Or %DS_3DLook Or %DS_NoFailCreate Or %DS_SetFont, _
            %WS_Ex_ControlParent Or %WS_Ex_Left Or %WS_Ex_LtrReading Or _
            %WS_Ex_RightScrollbar, To hDlg
        Control Add ListBox, hDlg, %Lbx1, , 5, 5, 330, 210, %WS_Child Or _
            %WS_Visible Or %WS_HScroll Or %WS_VScroll Or %LBS_Notify Or _
            %LBS_NoIntegralHeight, %WS_Ex_ClientEdge Or %WS_Ex_Left Or _
            %WS_Ex_LtrReading Or %WS_Ex_RightScrollbar
        Control Add Button,  hDlg, %BtnTest, "Test", 275, 220, 60, 15
        hFont1 = PBFormsMakeFont("Courier New", 9, 400, %FALSE, %FALSE, %FALSE, _
            %ANSI_CHARSET)
        Control Send hDlg, %Lbx1, %WM_SETFONT, hFont1, 0
    #PBForms END DIALOG
        Dialog Show Modal hDlg, Call ShowDlg1Proc To lRslt
    #PBForms BEGIN CLEANUP %Dlg1
        DeleteObject hFont1
    #PBForms END CLEANUP
        Function = lRslt
    End Function
    Class LBxC
        Instance meHDlg As Long
        Instance meID As Long
        Interface LBxI
            Inherit IUnknown
            Method INI(ByVal hDlg As Long, ByVal Id As Long)
                meHDlg = hDlg
                meID = Id
            End Method
            Method SetHorizontal(ByVal Count As Long)
                Local hCntrl&
                Control Handle meHDlg, meID To hCntrl&
                SendMessage hCntrl&, %LB_SETHORIZONTALEXTENT, Count, 0
            End Method
            Method Clear(Opt doEventsCount As Long)
                ListBox Reset meHDlg, meID
                If VarPtr(doEventsCount) Then me.DoEventsCount(doEventsCount)
            End Method
            Method Add(ByVal s As String, Opt doEventsCount As Long) As String
                ListBox Add meHDlg, meID, s
                If VarPtr(doEventsCount) Then me.DoEventsCount(doEventsCount)
            End Method
        End Interface
        Class Method DoEventsCount(ByVal Count As Long)
            Local i As Long
            For i = 1 To Count
                Dialog DoEvents
            Next i
        End Method
    End Class
    Class TimerC
        Instance meTime As Double
        Interface TimerI
            Inherit IUnknown
            Method Start()
                meTime = Timer
            End Method
            Method Get() As String
                Method = "    Time: " + Format$(Timer - meTime, "###.###############")
            End Method
        End Interface
    End Class
    stanthemanstan~gmail
    Dead Theory Walking
    Range Trie Tree
    HLib ~ Free Data Container Lib ~ Arrays, Lists, Stacks, Queues, Deques, Trees, Hashes

    Comment

    Working...
    X