Mechaflash Posted October 19, 2012 Share Posted October 19, 2012 (edited) My Code: (Runnable Example) expandcollapse popup#include <Array.au3> #include <ListboxConstants.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <GUIConstants.au3> #include <Misc.au3> #include <Sqlite.au3> #include <EditConstants.au3> Global $hMain, $ahMainControls, $hDB Global $sTitle = "Recipe Manager" _BuildMain() _InitializeDB() _LoadData() GUISetState() While 1 $msg = GUIGetMsg() Switch $msg Case $GUI_EVENT_CLOSE _ShutdownDB() Exit Case $ahMainControls[1] _ShowRecipeDetails() Case $ahMainControls[2] Case $ahMainControls[3] Case $ahMainControls[4] GUISetState(@SW_DISABLE, $hMain) _AddRecipe() GUISetState(@SW_ENABLE, $hMain) WinActivate($hMain) EndSwitch WEnd Func _BuildMain() $hMain = GUICreate($sTitle, 800, 600, Default, Default, Default, -1) GUICtrlCreateLabel("Your Recipe List", 10, 10, 300, 20) GUICtrlCreateLabel("Recipe Details", 320, 10, 470, 20) Dim $ahMainControls[5] = _ [ _ 4, _ GUICtrlCreateList("", 10, 30, 300, 520, Default, $WS_EX_ACCEPTFILES), _ GUICtrlCreateLabel("", 320, 30, 470, 300, BitOR($GUI_SS_DEFAULT_LABEL,$SS_SUNKEN)), _ GUICtrlCreateButton("PRINT", 670, 340, 120, 40), _ GUICtrlCreateButton("Create New Recipe", 320, 340, 120, 40) _ ] GUICtrlSetFont($ahMainControls[2], 12, 600) Return EndFunc Func _InitializeDB() _SQLite_Startup() If @error Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $hDB = _SQLite_Open("Recipes.db") ; Open a :memory: database If @error Then MsgBox(16, "SQLite Error", "Can't Load Database!") Exit -1 EndIf Return EndFunc Func _ShutdownDB() _SQLite_Close($hDB) EndFunc Func _LoadData() Local $iRval, $aResult, $iRows, $iColumns $sResult = _SQLite_Exec($hDB, "SELECT Name FROM Recipes;") ;~ ConsoleWrite(@error & @CRLF & @extended & @CRLF & $sResult & @CRLF & _SQLite_ErrMsg()) If @error = -1 And StringInStr(_SQLite_ErrMsg(), "no such table") Then ;~ ConsoleWrite(@error & @CRLF & @extended & @CRLF & $sResult & @CRLF & _SQLite_ErrMsg()) If Not _SQLite_Exec($hDB, "CREATE TABLE Recipes(Name, Time, Ingredients);") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error | " & @error & ", " & @extended, _SQLite_ErrMsg()) ;~ Return EndIf _SQLite_Exec($hDB, "SELECT Name FROM Recipes WHERE Name <> 'Name';", "_InsertData") ;~ ConsoleWrite(@error & @CRLF & @extended & @CRLF & $sResult & @CRLF & _SQLite_ErrMsg()) Return EndFunc Func _InsertData(Const $aRow) Local $sData = "" For $x in $aRow $sData&= "|" & $x Next GUICtrlSetData($ahMainControls[1], $sData) Return EndFunc Func _ShowRecipeDetails() Local $sName $sName = GUICtrlRead($ahMainControls[1]) _SQLite_Exec($hDB, "SELECT * FROM Recipes;", "_PopulateDetails") Return EndFunc Func _PopulateDetails(Const $aRows) Local $sData = "", $aSplit For $i = 0 To UBound($aRows) - 1 Switch $i Case 0 $sData&= "Recipe Name:" & @CRLF & @TAB & $aRows[$i] & @CRLF & @CRLF Case 1 $sData&= "Approximate Cook Time:" & @CRLF & @TAB & $aRows[$i] & " Minutes" & @CRLF & @CRLF Case 2 $aSplit = StringSplit($aRows[$i], @CR) $sData&= "Ingredient List:" & @CRLF For $x = 1 To $aSplit[0] $sData&= @TAB & "> " & $aSplit[$x] Next EndSwitch Next GUICtrlSetData($ahMainControls[2], $sData) Return EndFunc Func _AddRecipe() Local $hAdd, $ahAddControls Local $sDefaultText = "i.e. Diced Tomatoes - 1/4 Cup" & @CR & "Garlic - 2 Cloves" ConsoleWrite("Enter" & @CRLF) $hAdd = GUICreate("Add Recipe", 300, 250, Default, Default) GUICtrlCreateLabel("Recipe Name", 10, 10, 120, 20) GUICtrlCreateLabel("Cook Time (In Minutes)", 10, 40, 120, 20) GUICtrlCreateLabel("Ingredients - Include measurements." & @CRLF & "Have each ingredient on its own line.", 10, 70, 300, 30) Dim $ahAddControls[6] = _ [ _ 5, _ GUICtrlCreateInput("", 150, 10, 140, 20, BitOR($GUI_SS_DEFAULT_LABEL, $SS_SUNKEN)), _ GUICtrlCreateInput("", 150, 40, 40, 20, BitOr($GUI_SS_DEFAULT_LABEL, $SS_SUNKEN)), _ GUICtrlCreateInput($sDefaultText, 10, 100, 280, 100, BitOr($GUI_SS_DEFAULT_LABEL, $SS_SUNKEN, $ES_MULTILINE, $ES_WANTRETURN)), _ GUICtrlCreateButton("Cancel", 10, 210, 80, 30), _ GUICtrlCreateButton("Save", 210, 210, 80, 30) _ ] GUISetState(@SW_SHOW, $hAdd) While 1 $msg = GUIGetMsg() Switch $msg Case $GUI_EVENT_CLOSE GUIDelete($hAdd) ExitLoop Case $GUI_EVENT_PRIMARYDOWN If ControlGetFocus("Add Recipe") = "Edit3" And GUICtrlRead($ahAddControls[3]) = $sDefaultText Then GUICtrlSetData($ahAddControls[3], "") ElseIf StringStripWS(StringStripCR(GUICtrlRead($ahAddControls[3])), 8) = "" Then GUICtrlSetData($ahAddControls[3], $sDefaultText) EndIf Case $ahAddControls[4] GUIDelete($hAdd) ExitLoop Case $ahAddControls[5] _WriteData(GUICtrlRead($ahAddControls[1]), GUICtrlRead($ahAddControls[2]), GUICtrlRead($ahAddControls[3])) _LoadData() GUIDelete($hAdd) ExitLoop EndSwitch WEnd Return EndFunc Func _WriteData(Const $sName, Const $sTime, Const $sIngredients) If Not _SQLite_Exec(-1, "INSERT INTO Recipes VALUES ('" & $sName & "','" & $sTime & "', '" & $sIngredients & "');") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) Return EndFunc I would expect that line 91 would not return a single value (would be empty) due to the table not having any values stored yet, but it returns the column headers. If you create the add recipe, create the entry and save, the value that was added previously (the column headers) disappear and the new entry is there. EDIT: If you add another recipe, you'll see that the new recipe is appended to the list, so what SHOULD have happened is that entry should have stayed there along with the added recipe. I would suspect this is a bug in the SQLite UDF? Also, I added a WHERE clause to filter for values <> 'Name' but it still grabs it Edited October 19, 2012 by Mechaflash Spoiler “Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.” Link to comment Share on other sites More sharing options...
BrewManNH Posted October 19, 2012 Share Posted October 19, 2012 You're not using anything from the SQL UDF except the _SQLExec, which is running the query string you send it. So perhaps that's normal for an empty table. It doesn't seem to be a bug in the code, it's just executing the query. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 (edited) mechaflash, Use _sqlite_gettable2d for data manipulation and _sqlite_exec for DDL. Also, I use this function to check for the existence of a table func table_exists($tbl) local $rows, $nbrows, $nbcols, $ret $ret = _SQLite_GetTable2d(-1, "select * from sqlite_master where name = " & "'" & $tbl & "'" & ";", $rows, $nbrows, $nbcols) If @error Then ConsoleWrite(@error & ' ' & @extended & ' ' & $ret & @LF) if $nbrows > 0 then return true Else return False endif endfunc Nota bene - I am a rookie with sqlite. This is stuff I found from trial and error and excellent advice from jchd. kylomas edit: additional info sqlite 3.3 also supports the stmt "create table if not exist" Edited October 19, 2012 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 (edited) mechaflash, After looking at the recipe db using sqlite expert I see that all the data is there. If your issue is what is being displayed then the problem may be in the guictrlsetdata in function insertdata(). You might try this GUICtrlSetData($ahMainControls[1], $sData & @lf,1) As for accessing sqlite data, I do not understand the difference between using sql_exec with a callback, sqlite_gettable2d or a query, fetchdata, finalize query sequence. I suspect that this is application dependent and in the miniscule db apps that we are doing this probably makes zero difference. Perhaps one of the sqlite guru's will comment? kylomas Edited October 19, 2012 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Mechaflash Posted October 19, 2012 Author Share Posted October 19, 2012 You're not using anything from the SQL UDF except the _SQLExec, which is running the query string you send it. So perhaps that's normal for an empty table. It doesn't seem to be a bug in the code, it's just executing the query.You may be right, that it's just the way the function works, but it still doesn't make sense to me that you're querying data from a table and it returns the headers as an entry in the table... especially when you actually INSERT data into the table, the same execution DOESN'T return the headers with the rest of the data. UGH Spoiler “Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.” Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 mechaflash, I changed your insertdata function to this and it works. Func _InsertData(Const $aRow) GUICtrlSetData($ahMainControls[1],$aRow[0]) Return EndFunc My above comments regarding guictrlsetdata do NOT apply to a list control. kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Mechaflash Posted October 19, 2012 Author Share Posted October 19, 2012 (edited) @Kylomas I looked at that as well Func _InsertData(Const $aRow) Local $sData = "" For $x in $aRow $sData&= "|" & $x Next GUICtrlSetData($ahMainControls[1], $sData) Return EndFunc $sData is cleared at the start of the function. If data is in the row called, parse it to $sData and then insert it into the control. In this usage of GUICtrlSetData(), it's appending to the list each iteration. If the table is empty, it should return a blank list, but instead it returns the column headers. HOWEVER! You use the 'add recipe' button to create a row in the table. The same function runs against the table, but it doesn't return the headers as it did previously. EDIT: Reading your last post... ah screw it... I'm just being overly analytical. I'll accept it for what it is. Edited October 19, 2012 by Mechaflash Spoiler “Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.” Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 mechaflash,Perhaps because of this... RemarksThe Callback function must accept 1 parameter and can return $SQLITE_ABORT to stop processing (See example).The first row in the Callback sequence will be the column names. Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 mechaflash, The way that you access the data always displays the last row entered...don't know wh at the moment kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Mechaflash Posted October 19, 2012 Author Share Posted October 19, 2012 LOL @post #8 I just read that in the helpfile and was about to post 'derp' =/ Spoiler “Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.” Link to comment Share on other sites More sharing options...
kylomas Posted October 19, 2012 Share Posted October 19, 2012 mechaflash, Being called away...two other things the token "name" looks suspiciously like a reserved word and probably should not be used as a column name this stmt need to be qualified in some way otherwise you are always showing the last row_SQLite_Exec([color="#5a5a5a"]$hDB[/color], [color="#008080"]"SELECT * FROM Recipes;"[/color], [color="#008080"]"_PopulateDetails"[/color]) maybe something like select * from recipes where recipename = ... kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Mechaflash Posted October 19, 2012 Author Share Posted October 19, 2012 You're right about that as well Func _ShowRecipeDetails() Local $sName $sName = GUICtrlRead($ahMainControls[1]) _SQLite_Exec($hDB, "SELECT * FROM Recipes WHERE Name = '" & $sName & "';", "_PopulateDetails") Return EndFunc I took a long break from the code and came back... started noticing some mistakes and things that could've been simplified. Spoiler “Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.” Link to comment Share on other sites More sharing options...
jchd Posted October 19, 2012 Share Posted October 19, 2012 (edited) I've been away from the forum and just read this thread right now. I believe you're making your life difficult by using a callback for handling SELECTs results. You'll have to duplicate error handling in multiple points and at the end of the day your code will become more complex than it should be IMHO. If I were you and unless there are solid reasons to do otherwise, I'll stick to _SQLite_QuerySingleRow (in case you're after a single result row), _SQLite_GetTable2d (the general case) and, eventually, _SQLite_GetTable. Also don't forget to escape possible quotes in litterals passed: use _SQLite_FastEscape for strings. EDIT: if you keep on using the resultset callback feature and run into some roadblock, please keep me informed. It's possible that I didn't test that part thoroughly enough. I don't remember changing anything causing that part of the code to fail but it's marginal use might have required more attention. Edited October 19, 2012 by jchd This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) 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