Good evening,
I’m trying to mimic a basic query like in MS Access (show the results if it meets this criteria)
I have a SQLite db created with sample data, from one of the baseball tutorials, along with the accompanying listbox (results) and the edit/delete/add buttons.
I’m thinking I need a button with an sql query (action) attached to it, and a way to point the results of the query to the listbox.
I added my own button, but trying to get a SELECT statement to work is evading me.
Am I on the right track?
Thanks for your patience with this beginner.
-John
Thanks guys. Everything so far is about building a dB, adding editing and deleting data. I havent found anything on running a query. Still looking. Ugh, hate being dumb.
Try the SQLiteExample.xojo_binary_project in the examples folder. The ShowData method on SQLiteWindow shows how to run a query, and display the data you get back in a Listbox.
John, you said you are using one of the baseball tutorials, my guess is the same that Tim mentioned.
If you look at the code in Show Sample Data button, you can see that the Action() is ShowData. ShowData is a Method and the query used is a simple ‘Get all rows from the Team table’, the query is “SELECT * FROM Team”
You can start adding a TextField to the window and duplicate ShowDataButton Control and ShowData Method, and just change the query from “SELECT * FROM Team” to something you want to search, like City. So you will add ‘WHERE City =’"+TextField1.Text+"’" or something like that.
Then you will need to lear about PreparedStatements to avoid SQL injections.
I always start building my queries (unless they are super simple) in a query tool then move that to the Xojo code once I have it working. It makes debugging easier than trying to use Xojo to debug a SQL statement. A really complex statement with lots of joins and other things like case statements is MUCH easier to debug in a query tool.
SQLite Expert Personal is a free SQLite client that works well under Windows. Although I mostly use a Mac I don’t have a recommendation for a free SQLite client on OS X.
Sometimes I don’t use prepared statements and I find it easier to put the SQL statement into a constant. At run time I put it in a string variable and substitute some parameters before execution.
'Put this into a string.
select field1, field2
from mytable
where <mykey> = <keyvalue>
SQL = Replace(SQL,"<mykey>",somekeystring)
SQL = Replace(SQL,"<keyvalue>",somekeyvalue)
You also need a function like this to create quoted strings when you are building a SQL statement that needs something surrounded by single quotes. Remember if the string has any embedded single quotes you need to double them up.
'Make a quoted string from the passed argument for SQL
' Handle doubling up on any embedded single quotes
' Pass in InputString
' Returns the InputString surrounded by single quotes
' Example: Pass O'Brian
' Returns 'O''Brian'
dim wk as string
wk = trim(InputString)
wk = wk.Replaceall("'", "''")
return "'" + wk + "'"
Prepared statements are better but doing clear text for learning how to make it work makes debugging and learning easier.
[quote=376856:@John Marshall]Thanks guys. After 3 hours of hunting, I got my first query with a WHERE to work. success. Thanks for the help.
It appears the SQL statement is entered as a string, then run under a different command. Is that a technique, or the end-all way to do this?
I will read up on Prepared Statements next.[/quote]
You can separate into a string or execute the command with that info. I do prefer to assign the command to a String.
Just a few months ago I didn’t know anything about SQLite or Xojo. I learned from examples, docs, webinars, videos and changing code. If you keep reading, coding, changing and trying, you will find that it becomes easier.
[quote=376914:@Jean-Yves Pochez]Attributes ( extendsString ) Public Sub AppendText(extends byref astring as String, toAppendText as String)
astring = astring + toAppendText
End Sub
[/quote]
