Sign in to follow this  
Followers 0
PsaltyDS

SQLite query beat'n me down

4 posts in this topic

I have a script I was working to learn SQLite and share it with another forum member. But what seemed a very simple query has very confused now. Here's the demo:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $hDB, $hQuery, $aRow, $sMsg, $aNames
Global $avSampleData[4] = ["coolj, Joe Cool, Marketing", "pattyp, Peppermint Patty, Maintenance", "brownc, Charlie Brown, Accounting", "vanpeltl, Lucy VanPelt, Marketing"]

Global $SqlLiteDll = _SQLite_Startup()
If @error Then
    MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error)
Else
    $hDB = _SQLite_Open() ; Opens a database in memory
    If $hDB = 0 Or @error Then
        MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended)
    Else
        ; Create a database table
        _SQLite_Exec(-1, "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY, User_Name, Full_Name, Department);") ; Create Users table
        
        ; Add users
        For $n = 0 To 3
            $avSplit = StringSplit($avSampleData[$n], ",")
            $sQuery = "INSERT INTO Users(User_Name, Full_Name, Department) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "');"
            If _SQLite_Exec(-1, $sQuery) <> $SQLITE_OK Then MsgBox(16, "Error", "Error adding user to database: " & $avSplit[1])
        Next
        
        ; Query for users in Marketing
        $sQuery = "SELECT * FROM Users"
        If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then
            ; Read data out of the query
            $sMsg = "Marketing users:" & @LF
            While 1
                $RET = _SQLite_FetchData($hQuery, $aRow)
                If $RET = $SQLITE_OK Then
                    _ArrayDisplay($aRow, "Debug: $aRow")
                    $sMsg &= $aRow[2] & @CRLF
                Else
                    ConsoleWrite("Debug:  Exit FetchData loop, $RET = " & $RET & @LF)
                    ExitLoop
                EndIf
            WEnd
            MsgBox(64, "Results", $sMsg)
        Else
            MsgBox(16, "Error", "Error executing query: " & $sQuery)
        EndIf
        
        ; Shut it down
        _SQLite_Close()
        _SQLite_Shutdown()
    EndIf
EndIf

Now, this works nicely as far as it goes. My problems come when I try to make $sQuery more specific. The following did not work:

$sQuery = "SELECT Full_Name,Department FROM Users WHERE Department='Marketing'"

$sQuery = "SELECT * FROM Users WHERE Department='Marketing'"

Using == vice =

Swapping the single/double quotes

...etc., etc., etc.

I know I'm missing something stupid, could anyone just let me know what the correct syntax is to pull the Full_Name's of just those in 'Marketing' Department?

:whistle:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Nothing wrong with your SQL $sQuery syntax.

Your Global $avSampleData[4] declaration is putting a space before the word Marketing (as well as before the names, and the term Marketing)

So, change your declararation to remove the spaces following the commas in each data string:

Global $avSampleData[4] = ["coolj,Joe Cool,Marketing", "pattyp,Peppermint Patty,Maintenance", "brownc,Charlie Brown,Accounting", "vanpeltl, Lucy VanPelt,Marketing"]

Also, since you're only retrieving two fields, your array is smaller so you'll need to change your

$sMsg &= $aRow[2] & @CRLF

to

$sMsg &= $aRow[0] & @CRLF

Works a treat then!

Edited by ResNullius

Share this post


Link to post
Share on other sites

Nothing wrong with your SQL $sQuery syntax.

Your Global $avSampleData[4] declaration is putting a space before the word Marketing (as well as before the names, and the term Marketing)

So, change your declararation to remove the spaces following the commas in each data string:

Global $avSampleData[4] = ["coolj,Joe Cool,Marketing", "pattyp,Peppermint Patty,Maintenance", "brownc,Charlie Brown,Accounting", "vanpeltl, Lucy VanPelt,Marketing"]

Also, since you're only retrieving two fields, your array is smaller so you'll need to change your

$sMsg &= $aRow[2] & @CRLF

to

$sMsg &= $aRow[0] & @CRLF

Works a treat then!

I had been adjusting the $aRow[n] reference for each different query, but never even considered there might be a space in the column names.

Thanks!

:whistle:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Updated demo that works! Can be used to see how to make basic use of SQLite.au3 UDF.

Just a warning: Spaces bite! In a couple of places having something like ", Marketing" vice ",Marketing" caused No Match Found errors. That's why I left the StringStripWS() in the demo. It's something to watch out for.

#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $hDB, $hQuery, $aRow, $sMsg, $aNames
Global $avSampleData[4] = ["coolj, Joe Cool, Marketing", "pattyp, Peppermint Patty, Maintenance", "brownc, Charlie Brown, Accounting", "vanpeltl, Lucy VanPelt, Marketing"]

Global $SqlLiteDll = _SQLite_Startup()
$hDB = _SQLite_Open() ; Opens a database in memory

; Create a database table
$sExec = "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY,User_Name,Full_Name,Department);"
_SQLite_Exec($hDB, $sExec)

; Add users
For $n = 0 To 3
    $avSplit = StringSplit($avSampleData[$n], ",")
    ; Strip leading and trailing whitespace before using entries
    For $i = 1 To $avSplit[0]
        $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2)
    Next
    $sExec = "INSERT INTO Users(User_Name,Full_Name,Department) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "');"
    If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug:  Successfully added to Users table: " & $avSplit[2] & @LF)
Next

; Show current data
_QueryMarketing()

; Change a record
$sExec = "UPDATE Users SET Department='Marketing' WHERE User_Name='pattyp'"
If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug:  Successfully updated a record." & @LF)
    
; Show data again
_QueryMarketing()

; Shut it down
_SQLite_Close()
_SQLite_Shutdown()


Func _QueryMarketing()
    ; Query for users in Marketing
    $sQuery = "SELECT * FROM Users WHERE Department='Marketing'"
    If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then
        ; Read data out of the query
        $sMsg = "Marketing users:" & @LF
        While 1
            $RET = _SQLite_FetchData($hQuery, $aRow)
            If $RET = $SQLITE_OK Then
                ConsoleWrite("Debug:  FetchData loop got one row of data..." & @LF)
                $sMsg &= $aRow[2] & @CRLF
            Else
                ConsoleWrite("Debug:  Exit FetchData loop, $RET = " & $RET & @LF)
                ExitLoop
            EndIf
        WEnd
        MsgBox(64, "Results", $sMsg)
    Else
        MsgBox(16, "Error", "Error executing query: " & $sQuery)
    EndIf
EndFunc   ;==>_QueryMarketing

Enjoy!

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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