Sqlite - a simple address book

xircon · September 14, 2007

Been trying to get my head around using Sqlite for a few days, so thought the best thing to do was work up a simple example. It doesn't do much, it adds records to a table and then displays them in a modified version of _arraydisplay (that uses the field names as the column headers and ignores row 0).

Overall using Sqlite is a pain as you constantly have to build up huge strings to execute and I have a few ideas for improving this (but are my programming skills up to it? )

Have a play and let me know what you think, its very rough and ready and the modified _arraydisplay is called _sqlitedisplay and appended to bottom.

Steve

:edit: Have added view button. More next week.

Zip file updated 20/9/07

Added extra fields

Changed everything to work from arrays

Added search boxes, combo box allows field name to be searched

I have one slight problem with the search facility - if there are no matches, it returns the full table!

addbook.zip

Edited September 20, 2007 by xircon

gseller · September 14, 2007

Pretty good start, once you have closed the original box with your data in it there seems to be no way to call it back up. Say if you wanted to lookup someting in your database..

xircon · September 14, 2007

Pretty good start, once you have closed the original box with your data in it there seems to be no way to call it back up. Say if you wanted to lookup someting in your database..

Thanks, its a "lunchtime" project so I will add more to it over the the next week and repost. Am reading through the array functions and teaching myself from the examples (never used arrays before!) so its slow work.

Add under button section

$button3 = GUICtrlCreateButton("View Table ", 224, 352, 129, 49, 0)

Add in case section

case $button3

dispfile()

This will create a button to view table. Thank god its friday!

Steve

gseller · September 14, 2007

That's the best way to do it. Just take your time and do it. The helpfile is great!

xircon · September 20, 2007

Have updated original post with a new zip file. Am having a strange problem with my find function - if the string doesn't exist, it returns the whole table, will post a qquestion in general later

gseller · September 21, 2007

great improvements... Valuater has a great address prog somewhere around here, have you seen it?

xircon · September 21, 2007

No I haven't seen it - could you post a link, the guy is very prolific (nearly 8000 posts!) and I tried sifting through them to find it but I am at work...........

Made some further improvements, code just needs to be tidied up and I will hopefully repost later today.

gseller · September 21, 2007

Sure.. Check this one out, might help you in your design. http://www.autoitscript.com/forum/index.ph...aluater+eztrack

xircon · September 21, 2007

Sure.. Check this one out, might help you in your design. http://www.autoitscript.com/forum/index.ph...aluater+eztrack

So much more prettier than mine, I have re-jigged a lot of my program today, to include editing, but cosmetically it stinks - job for next week is to make it look a lot better!

Code now looks like this (if any one is interested!)

#include <GUIConstants.au3>
#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <array.au3>
#Include <GuiCombo.au3>
#NoTrayIcon

#Region ### START Koda GUI section ### 
$Form01 =  GUICreate("Address Book", 733, 452, 193, 125)

;Variables
Global $first, $last, $add1, $add2, $add3, $town, $addbook="addbook.db",$c,$Input[21],$read[21],$fields[21],$rowid
Global $iRs, $iCols
;Special Characters
Global $id=0, $dq=chr(34), $sq=chr(39), $sc=";", $cr=chr(13), $lf=chr(12)

Local $aResult,$iRows,$iColumns, $aResult2

;InputBox setup
$su=100
$y=64

;Create Inputboxes in an Array.
For $i = 1 To 12
    
    $Input[$i] = GUICtrlCreateInput("", $su, $y, 200, 20)
    $y += 34
    
    if $i=6 Then
       $su=475
       $y=64
    endif
   
Next

;Labels for input boxes
;COLUMN 1
$su=17
$Label1  = GUICtrlCreateLabel("First Name    ", $su, 066, 74, 17)
$Label2  = GUICtrlCreateLabel("Last Name     ", $su, 100, 74, 17)
$Label3  = GUICtrlCreateLabel("Address Line 1", $su, 134, 74, 17)
$Label4  = GUICtrlCreateLabel("Address Line 2", $su, 169, 74, 17)
$Label5  = GUICtrlCreateLabel("Address Line 3", $su, 203, 74, 17)
$Label6  = GUICtrlCreateLabel("Town / City   ", $su, 237, 74, 17)

;COLUMN 2
$su=380
$Label7  = GUICtrlCreateLabel("Telephone     ", $su, 066, 74, 17)
$Label8  = GUICtrlCreateLabel("Mobile        ", $su, 100, 74, 17)
$Label9  = GUICtrlCreateLabel("Email         ", $su, 134, 74, 17)
$Label10 = GUICtrlCreateLabel("User 1        ", $su, 169, 74, 17)
$Label11 = GUICtrlCreateLabel("User 2        ", $su, 203, 74, 17)
$Label12 = GUICtrlCreateLabel("User 3        ", $su, 237, 74, 17)


;Manually placed Edit boxes and Buttons
;Tweak GUI placings manually
$bts=25     ;Button Spacing
$btsinc=140 ;button space increment
$btw=125    ;Button Width
$bth=30     ;Button Height
$btv=400    ;Button Vertical offset on form

$Button2 = GUICtrlCreateButton("Create tables for testing",  $bts, $btv, $btw, $bth, 0)

$bts+=$btsinc

$button3 = GUICtrlCreateButton("View Table ",                $bts, $btv, $btw, $bth, 0)

$bts+=$btsinc

$Button1 = GUICtrlCreateButton("Save",                       $bts, $btv, $btw, $bth, 0)

$bts+=$btsinc

$Button4 = GUICtrlCreateButton("Find:",                          560, 300, 80, $bth, 0)

$col=200
$row=300
$combo=GUICtrlCreateCombo (" ", $col,$row) ; create first item

$find   = GUICtrlCreateInput("", 330, 300, 200, 20)

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

;File Open
sqlopen()

;Fill Combobox with Field Names.
fillcombo()

#region ### Main
While 1

    $nMsg = GUIGetMsg()
    Switch $nMsg
    
    Case $GUI_EVENT_CLOSE
    
    ;File Close
    sqlclose()
    
    Exit

    Case $Button1 ;Save Record
         ctrlread()
         savedata()      
         cleardata()
                 
     case $Button2
         createtbl()
         
     case $button3
         dispfile()
         
     case $button4
         $findstr=GUICtrlRead($find)
         if stringlen(StringStripWS ($findstr,8)) then findrecord()
            
    EndSwitch
    
WEnd

#endregion ## Main

#region ## Functions
;***************************************************************************************************
;***************************************************************************************************
;***************************************************************************************************

;Fill combobox with Field Names
Func fillcombo()

fieldlist()

for $i=1 to $iColumns-1
    
    GUICtrlSetData($Combo,$Fields[$i],"") 
    
next

EndFunc

;***************************************************************************************************

;Find record(s) 
Func findrecord()
    
    $search=GUICtrlRead($combo)
    
    $runstr ="SELECT * from abook where " & $search & " like " & $dq & $findstr & "%" & $dq & $sc
    $runstr2="SELECT ROWID from abook where " & $search & " like " & $dq & $findstr & "%" & $dq & $sc
    
    ;Clear array
    $aResult=""
    
    $retval = _SQLite_GetTable2d (-1, $runstr, $aResult, $iRows, $iColumns)
    
    $retval2 = _SQLite_GetTable2d (-1, $runstr2, $rowid, $iRs, $iCols)
    
    if UBound($rowid,1)=2 then $id=$rowid[1][0]
        
    if ubound($aResult,1) + ubound($aResult,2)=0 then MsgBox(0,"Warning","No information to display")
        
    if UBound($aResult,1)=2 then 
        
        fill_edit_boxes()
        $id=$aResult[1][0]
            
    Else
    
        _SqliteDisplay($aResult)

    EndIf
    
EndFunc


;***************************************************************************************************

func fill_edit_boxes()
    
    For $c = 1 To ubound($aresult,2)-1
        
        GUICtrlSetData($Input[$c],$aResult[1][$c])
        
    Next
    
EndFunc

;***************************************************************************************************

func createtbl() ;This function (re)creates the tables for testing.


    FileDelete("addbook.db") 
    
    sqlopen()   
    
    _SQLite_Exec(-1,"Create Table abook(id INTEGER PRIMARY KEY);")
    
    _SQLite_Exec(-1,"alter table abook add firstname char(50);")
    _SQLite_Exec(-1,"alter table abook add surname   char(50);")
    _SQLite_Exec(-1,"alter table abook add add1      char(50);")
    _SQLite_Exec(-1,"alter table abook add add2      char(50);")
    _SQLite_Exec(-1,"alter table abook add add3      char(50);")
    _SQLite_Exec(-1,"alter table abook add city      char(50);")
    _SQLite_Exec(-1,"alter table abook add phone     char(50);")
    _SQLite_Exec(-1,"alter table abook add mobile    char(50);")
    _SQLite_Exec(-1,"alter table abook add email     char(50);")
    _SQLite_Exec(-1,"alter table abook add user1     char(50);")
    _SQLite_Exec(-1,"alter table abook add user2     char(50);")
    _SQLite_Exec(-1,"alter table abook add user3     char(50);")
    
    ;Insert a record into table otherwise program doesn't work!!
    _SQLite_Exec(-1,"insert into abook(Firstname) values('ZZZZZZ');")
    
    sqlclose()
    
EndFunc ;createtbl

;***************************************************************************************************

;Obtain a list of the fields into an array.
Func fieldlist()
    
    ;Get a field list from table - fails if record count = 0
    $retval = _SQLite_GetTable2d (-1, "SELECT * from abook", $aResult, $iRows, $iColumns)
    
    
    for $i=0 to $iColumns-1

        $fields[$i]=$aResult[0][$i]
        
    Next

EndFunc ;fieldlist

;***************************************************************************************************

func _sqlupdate($table,$fieldnam,$value,$rec_id)

;SQLite webpage
;sql-statement ::=  UPDATE [ OR conflict-algorithm ] [database-name .] table-name
;SET assignment [, assignment]*
;[WHERE expr]
;assignment ::=     column-name = expr
    
    $update="update " & $table & " set " & $fieldnam & "=" & $dq & $value & $dq & " where ROWID = " & $rec_id & ";"
    
    _SQLite_Exec(-1,$update)    
    
EndFunc

;***************************************************************************************************

func update_record()
    
    $sp=" "
    
    ctrlread()
    
    for $i = 1 to $iColumns -1
        
        _sqlupdate("abook",$fields[$i],$read[$i],$id)
                        
    Next
    
    $id=0
    
EndFunc 

;***************************************************************************************************

;Save the data to table
func savedata()
    
    ;Save changed fields
    if  $id > 0 then ;Do an update, do not create a new record.
    
        update_record()
        Return
    
    EndIf

    ;New Method
    ;Insert Basics of Record, then use update function to do rest.
    
    $insert="insert into abook(surname) values(" & $dq & "TEMPORARY"& $dq &");"
    
    _SQLite_Exec(-1,$insert)
    $id=_SQLite_LastInsertRowID ( -1 )  
    
    update_record()
    
EndFunc ;Savedata

;***************************************************************************************************

;Display results in a simple ArrayDisplay (Code obtained from the forum ) 
Func dispfile()

    $retval = _SQLite_GetTable2d (-1, "SELECT * from abook", $aResult, $iRows, $iColumns)
    
    If $retval = $SQLITE_OK Then
        
        $f=_SqliteDisplay($aResult, "Query Result")
        
    Else
        
        MsgBox(16, "SQLite Error: " & $retval, _SQLite_ErrMsg ())
        
    EndIf
           
EndFunc ;dispfile

;***************************************************************************************************

;Open Database
Func sqlopen()
    
    _SQLite_Startup()
    _SQLite_Open($addbook) 

EndFunc ;sqlopen

;***************************************************************************************************

;Close Database
func sqlclose()
    
    _SQLite_Close ()
    _SQLite_Shutdown ()

EndFunc ;sqlclose
    
;***************************************************************************************************

;Read state of input controls
Func ctrlread()
    
    For $c = 1 To 20
        $read[$c]=GUICtrlRead($Input[$c])
    Next

EndFunc 

;***************************************************************************************************

;Clear Input controls
func cleardata()
    
;Thanks to Smashly for the following loop code.

For $c = 1 To 12
    GUICtrlSetData($Input[$c],"")
Next
    
EndFunc ;cleardata

;***************************************************************************************************

;===============================================================================
;
; Function Name:  _SqliteDisplay() a modified version of _ArrayDisplay()
;
; Parameter(s):     $ar_2DArray      - Name of Array to display; 1 or 2 dimensional
;                   $sTitle          - [optional] The new title for Array Display ListView.
;                   $i_ShowOver4000  - [optional]   0 = Limit number of rows displayed to 4000 for speed
;                                    -              1 = (default) Display all rows [may slow down over 4000]
;                   $i_Transpose     - [optional]   0 = (default) don't transpose view
;                                    -              1 = transpose view
;                   $GUIDataSeparatorChar - [optional]  (default="|") change option of character for ListView display [see Opt("GUIDataSeparatorChar", $GUIDataSeparatorChar)]
;                   $GUIDataReplace       - [optional]  (default="~") change character to use in items of display if the item contains the separator character
; Requirement(s):   None
; Return Value(s):  On Success -  Returns 1
;                   On Failure -  0 , @error is set to 1.
; Author(s):        randallc
;
;=====================================================================
;Ammended SPF 14/9/07
Func _SqliteDisplay($ar_2DArray, $sTitle = "ListView array 1D and 2D Display", $i_ShowOver4000 = 1, $i_Transpose = 0, $GUIDataSeparatorChar = "|", $GUIDataReplace = "~")
    
    Local $searchlistView, $hndButton_Close, $sTempHeader = 'Row', $i_Pos, $size, $ar_ExcelValueTrans[1][1], $s_NotDoneLine
    Local $ret, $ar_TempSingle[1], $msg, $hndButton_Array1Box_TextSelect, $searchGUI, $ar_2dCurrent[1][1]
    Local $GUICtrlCreateListViewItem
    
    If Not IsArray($ar_2DArray) Then Return SetError(1, 0, 0)
    
    ; Create GUI and Buttons 

    $searchGUI = GUICreate($sTitle, 810, 623, (@DesktopWidth - 800) / 2, (@DesktopHeight - 600) / 2, $WS_MAXIMIZEBOX + $WS_MINIMIZEBOX)
    
    $hndButton_Array1Box_TextSelect = GUICtrlCreateButton('&Text ' & 'Selected', 10, 550, 100, 24)
    
    GUICtrlSetResizing($hndButton_Array1Box_TextSelect, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))
    
    $hndButton_Close = GUICtrlCreateButton('&Close', 190, 550, 70, 24)
    
    GUICtrlSetResizing($hndButton_Close, BitOR($GUI_DockRight, $GUI_DockBottom, $GUI_DockSize))
    
    GUICtrlSetState($hndButton_Array1Box_TextSelect, ($GUI_DefButton))
    
    ; for 1D; add index column; change to 2D array
    
    If UBound($ar_2DArray, 0) = 1 Then
        
        ReDim $ar_2dCurrent[UBound($ar_2DArray) ][1]
        
        For $i = 0 To UBound($ar_2DArray) - 1
            $ar_2dCurrent[$i][0] = $ar_2DArray[$i]
        Next
        
        $ar_2DArray = $ar_2dCurrent
        
    EndIf
    
    ; transpose if requested
    
    If $i_Transpose And IsArray($ar_2DArray) And UBound($ar_2DArray, 0) = 2 Then ; transpose if requested
        
        ReDim $ar_ExcelValueTrans[UBound($ar_2DArray, 2) ][UBound($ar_2DArray, 1) ]
        
        For $j = 0 To UBound($ar_2DArray, 2) - 1
            
            For $numb = 0 To UBound($ar_2DArray, 1) - 1
                
                If $numb > 250 Then ExitLoop  ; limit cols to about 250 as limitation  of GUICreatelistView?
                $ar_ExcelValueTrans[$j][$numb] = $ar_2DArray[$numb][$j]
                
            Next
            
        Next
        
        $ar_2DArray = $ar_ExcelValueTrans
        
    EndIf
    
    ;Replace any array items containing the LV separator character =============================================
    
    Opt("GUIDataSeparatorChar", $GUIDataSeparatorChar) ;"|" is the default
    
    For $x = 0 To UBound($ar_2DArray) - 1 Step 1
        
        For $y = 0 To UBound($ar_2DArray, 2) - 1 Step 1
            $ar_2DArray[$x][$y] = StringReplace($ar_2DArray[$x][$y], $GUIDataSeparatorChar, $GUIDataReplace)
        Next
        
    Next
    
    ;; make LV header==================================================================
    For $i = 1 To UBound($ar_2DArray, 2)
        
        ;***********************************************************************
        ;$sTempHeader &= $GUIDataSeparatorChar & 'Col ' & $i - 1 - Original Line
        ;New Line added SPF 14/9/07
        ;************************************************************************
        
        $sTempHeader &= $GUIDataSeparatorChar & $ar_2DArray[0][$i - 1]
        
    Next
    
    StringReplace($sTempHeader, $GUIDataSeparatorChar, "<")
    
    If @extended > 252 Then
        
        $i_Pos = StringInStr($sTempHeader, $GUIDataSeparatorChar, 0, 252)
        $sTempHeader = StringLeft($sTempHeader, $i_Pos - 1)
    
    EndIf
        
    $s_NotDoneLine = StringReplace($sTempHeader, "Col", "ND")
    
    ;change 2D array to Array LV formatted rows
    
    If UBound($ar_2DArray, 0) = 2 Then
        
        ReDim $ar_TempSingle[UBound($ar_2DArray) ]
        
        For $i = 0 To UBound($ar_2DArray) - 1
            
            $ar_TempSingle[$i] = "[" & $i & "]"
            
            For $c = 0 To UBound($ar_2DArray, 2) - 1 ;0 is base
                
                If $c < 251 Then
                    $ar_TempSingle[$i] &= $GUIDataSeparatorChar & $ar_2DArray[$i][$c]
                Else
                    ExitLoop;$c = UBound($ar_2DArray, 2) - 1
                EndIf
                
            Next
            
            $ar_TempSingle[$i] = StringMid($ar_TempSingle[$i], 1, StringLen($ar_TempSingle) - 1)
        Next
        
    Else
        
        $ar_TempSingle = $ar_2DArray
        
    EndIf
    
    ;Create Listview==================================================================
    
    $size = WinGetClientSize($searchGUI)
    
    GUICtrlDelete($searchlistView)
    
    $searchlistView = GUICtrlCreateListView($sTempHeader, 0, 16, $size[0] - 10, $size[1] - 90, BitOR($LVS_SHOWSELALWAYS, $LVS_EDITLABELS), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_FULLROWSELECT, $LVS_EX_REGIONAL))
    
    GUICtrlSetResizing($searchlistView, BitOR($GUI_DockLeft, $GUI_DockTop, $GUI_DockRight, $GUI_DockBottom))
    
    ;Changed $c=0 to 1 to lose field list.
    
    For $c = 1 To UBound($ar_TempSingle) - 1
        
        If ($c < 3999) Or $i_ShowOver4000 Then
            
            If $c < 3999 Then
                
                $GUICtrlCreateListViewItem = GUICtrlCreateListViewItem($ar_TempSingle[$c], $searchlistView)
                
                If Not $GUICtrlCreateListViewItem Then GUICtrlCreateListViewItem($s_NotDoneLine, $searchlistView)
                
            Else
                $ret = _GUICtrlListViewInsertItem($searchlistView, -1, $ar_TempSingle[$c])
                
                If ($ret = $LV_ERR) Then _GUICtrlListViewInsertItem($searchlistView, -1, $s_NotDoneLine)
                
            EndIf
                
        ElseIf ($c >= 3999) Then
                
            ExitLoop
            
        EndIf
        
    Next
    
    _GUICtrlListViewSetColumnWidth($searchlistView, 2, $LVSCW_AUTOSIZE)
    
    If UBound($ar_2DArray, 2) = 1 Then _GUICtrlListViewSetColumnWidth($searchlistView, 1, $LVSCW_AUTOSIZE)
    
    GUISetState()
    
    Local $SaveEventMode = Opt("GUIOnEventMode",0) 
    
    Do ; LOOP
        
        $msg = GUIGetMsg(1)
        Select
            
        ;Copy all to clipboard(default); or multiple selected rows
        
        Case $msg[0] = $hndButton_Array1Box_TextSelect
            
                Local $a_indices[1], $s_CopyLines = ""
                
                If _GUICtrlListViewGetItemCount($searchlistView) Then $a_indices = _GUICtrlListViewGetSelectedIndices($searchlistView, 1)
                
                If (IsArray($a_indices)) Then
                    
                    ClipPut("")
                    
                    For $i = 1 To $a_indices[0]
                        $s_CopyLines &= $ar_TempSingle[ $a_indices[$i]] & @LF
                    Next
                    
                Else
                    
                    For $i = 0 To UBound($ar_TempSingle) - 1
                        $s_CopyLines &= $ar_TempSingle[$i] & @LF
                    Next
                    
                EndIf
                
                ClipPut($s_CopyLines)
                
        EndSelect
        
    Until $msg[0] = $GUI_EVENT_CLOSE Or $msg[0] = $hndButton_Close
    
    GUIDelete($searchGUI)
    
    Opt("GUIOnEventMode",$SaveEventMode) 
    
    Return SetError(0, 0, 1)
    
EndFunc   ;==>_ArrayDisplay

;***************************************************************************************************
    
#endregion ##Functions

gseller · September 22, 2007

Ah yes, much better and still using your db method. I like that. A little cosmetic work and your ready..

Sqlite - a simple address book

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members