PsaltyDS Posted July 23, 2007 Share Posted July 23, 2007 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? 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 Link to comment Share on other sites More sharing options...
ResNullius Posted July 23, 2007 Share Posted July 23, 2007 (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] & @CRLFto $sMsg &= $aRow[0] & @CRLFWorks a treat then! Edited July 23, 2007 by ResNullius Link to comment Share on other sites More sharing options...
PsaltyDS Posted July 23, 2007 Author Share Posted July 23, 2007 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! 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted July 25, 2007 Author Share Posted July 25, 2007 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! 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 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