SQLite query beat'n me down

[PsaltyDS]

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:

expandcollapse popup

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

[ResNullius]

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 July 23, 2007 by ResNullius

[PsaltyDS]

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!

[PsaltyDS]

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.

expandcollapse popup

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