Jump to content
Sign in to follow this  
Mechaflash

SQLite query an empty table

Recommended Posts

Mechaflash

My Code: (Runnable Example)

#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 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.”

 

Share this post


Link to post
Share on other sites
BrewManNH

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 Gude
How 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

Share this post


Link to post
Share on other sites
kylomas

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 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

Share this post


Link to post
Share on other sites
kylomas

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 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

Share this post


Link to post
Share on other sites
Mechaflash

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.”

 

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites
Mechaflash

@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 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.”

 

Share this post


Link to post
Share on other sites
kylomas

mechaflash,

Perhaps because of this...

Remarks

The 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

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites
Mechaflash

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.”

 

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites
Mechaflash

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.”

 

Share this post


Link to post
Share on other sites
jchd

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 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 here
RegExp tutorial: enough to get started
PCRE 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)

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×