xircon Posted September 14, 2007 Share Posted September 14, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
gseller Posted September 14, 2007 Share Posted 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.. Link to comment Share on other sites More sharing options...
xircon Posted September 14, 2007 Author Share Posted 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 Link to comment Share on other sites More sharing options...
gseller Posted September 14, 2007 Share Posted September 14, 2007 That's the best way to do it. Just take your time and do it. The helpfile is great! Link to comment Share on other sites More sharing options...
xircon Posted September 20, 2007 Author Share Posted 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 Link to comment Share on other sites More sharing options...
gseller Posted September 21, 2007 Share Posted September 21, 2007 great improvements... Valuater has a great address prog somewhere around here, have you seen it? Link to comment Share on other sites More sharing options...
xircon Posted September 21, 2007 Author Share Posted 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. Link to comment Share on other sites More sharing options...
gseller Posted September 21, 2007 Share Posted September 21, 2007 Sure.. Check this one out, might help you in your design. http://www.autoitscript.com/forum/index.ph...aluater+eztrack Link to comment Share on other sites More sharing options...
xircon Posted September 21, 2007 Author Share Posted 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!) expandcollapse popup#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 Link to comment Share on other sites More sharing options...
gseller Posted September 22, 2007 Share Posted September 22, 2007 Ah yes, much better and still using your db method. I like that. A little cosmetic work and your ready.. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now