Hey everybody…
I am sure there’s a very easy way of doing this that I am overlooking…
I have Clients table with Name TEXT, Surename TEXT and ID TEXT…
In Xojo’s GUI I have 3 textfields one for each column and I want to perform a search by any or all of those fields…
I could do a select * from Clients Where name = xxxx OR surname = xxxx or ID = xxx but that won’t work… I will get all john’s, all Pete’s (or any common name) regardless fo their ID or surname…
What’s the simple way to do that ?
Do you want those conditions ANDed so the search results have match all the fields or ORed where any one of them can match ?
Do the results have to match exactly ? (if the name search field held “Jon” would “Jon” and “Jonathan” be matches ?)
Do you want those conditions ANDed so the search results have match all the fields or ORed where any one of them can match ?
If the field is completed ( Name.text <> “”) then it has to be ANDed…
Do the results have to match exactly ?
yes, match exactly…
Typed straight into the forum… may need tweaking…
Also note this is bad practice… people may type something into the text boxes that breaks the code (‘SQL injection’)
Parameterised queries are safer, but harder to handle when there is a variable number of parameters.
[code]dim sql as string
sql = "select * from Clients Where "
if txtName.text > “” then sql = sql + “name = '” + name.text + "’ and "
if txtSurname.text > “” then sql = sql+ “surname = '” + txtsurname.text + "’ and "
if txtID.text > “” then sql = sql+ “ID = '” + txtID.text + “’”
if right (sql,4) = "and " then sql = left(sql, len(sql)-4) //discard any trailing and
if sql = "select * from Clients Where " then
//nothing typed
else
//use the sql here
end if
[/code]
ok
so you need to see if each of those fields has any contents and built the query up from pieces
I’m going to avoid the additional complexity of prepared statements for now - BUT long term that might be the preferable way to do this because it avoids other issues
I want to keep this simple enough you can follow it and then move on to more complex and better ways
NOTE This code WILL fail on certain names that contain ’
Just so you know that going in
const kBaseSQLStatement = "select * from Clients Where "
dim sql as string
if nameField.Text <> "" then
if sql <> "" then sql = sql + " AND "
sql = sql + "name = '" + nameField.text + "' "
end if
if surnameField.Text <> "" then
if sql <> "" then sql = sql + " AND "
sql = sql + "surname = '" + surnameField.Text + "' "
end if
if idField.Text <> "" then
if sql <> "" then sql = sql + " AND "
sql = sql + "id = " + idField.text
end if
sql = kBaseSQLStatement + sql
if sql <> "" then
dim rs as recordset = db.sqlselect(sql)
// whatever else you need to do
end ifRoman, thank you for your question. I think I do learn trying to solve problems more than just reading docs or watching youtube.
This is what I did:
[code]Listbox1.DeleteAllRows
Dim sql As String
Dim ps As SQLitePreparedStatement
Dim rs As RecordSet
sql = “SELECT * FROM Clients WHERE Name LIKE ? AND Surname LIKE ? AND ID LIKE ?”
ps = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
If TFName.Text <> “” Then
ps.Bind(0, TFName.Text)
Else
ps.Bind(0, “%”)
End if
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
If TFSurname.Text <> “” Then
ps.Bind(1, TFSurname.Text)
Else
ps.Bind(1, “%”)
End if
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
If TFid.Text <> “” Then
ps.Bind(2, TFid.Text)
Else
ps.Bind(2, “%”)
End if
rs = ps.SQLSelect
If rs <> Nil and rs.RecordCount > 0 Then
do until rs.EOF
Listbox1.AddRow (rs.Field(“Name”).StringValue,rs.Field(“Surname”).StringValue,rs.Field(“ID”).StringValue)
rs.MoveNext
loop
end if
rs = Nil[/code]
From my test, this code works like this:
By using LIKE and not = I can get results searching for ID “tt3” when the database contains “TT3”, or find Jon/JON when the textfield contains “jon”
I’m learning about PreparedStatements and why to use them.
Remember, I’m a newbie but I hope this code helps. If you have any comments or fixes, they are welcome.
Alright guys… first, thanks a lot for taking the time to think about this for me…
As my code supports both CubeSQL/SQLite the prepared statement solution that Alberto suggested can’t be implemented as it will not work with CubeSQL… that would require extra work. Also this is for internal corporate use… so the risk of SQL injection is minor… (I understand that it still could be injected by an inside person)
So I stuck with Norman’s proposal (with a little tweaking to escape SQL ’ characters)…
Eventually I might think of a prepared statement solution for both CubeSQL AND standard SQLite
Again, thanks a lot. This forum rocks.
R
ISA is a friend 
If db ISA SQLITEDatabase then
// use the sqlite prepared statement version
elseif DB ISA CubeSQLDatabase then
// use the cube sql version
else
MSGBOX "I have no idea what DB you want !"
end ifand there are LOTS of variations on this that would work like a db subclass etc etc
Ok, but then I’d have to re write all my SQL queries… Ain’t no fun…
Not necessarily
But I’d have to spend a lot more time seeing whats the same vs different between CubeSQL & the built in SQLiteDatabase to be able to offer much more advice
I have read about SQLdeLite and that it can handle SQLite, CubeSQL and others.
I just converted my code above to SQLdeLite:
[code]Listbox1.DeleteAllRows
Dim rs As RecordSet
Dim _record As new SQLdeLite.Record
_record.Name = TFName.Text.ToText
_record.Surname = TFSurname.Text.ToText
_record.ID = TFid.Text.ToText
If _record.Name = “” Then _record.Name = “%”
If _record.Surname = “” Then _record.Surname = “%”
if _record.ID = “” Then _record.ID = “%”
rs = db.SQLdeLiteSelect(“SELECT * FROM Clients WHERE Name LIKE $Name AND Surname LIKE $Surname AND ID LIKE $ID”, _record)
If rs <> Nil and rs.RecordCount > 0 Then
do until rs.EOF
Listbox1.AddRow (rs.Field(“Name”).StringValue,rs.Field(“Surname”).StringValue,rs.Field(“ID”).StringValue)
rs.MoveNext
loop
end if[/code]
I see there is a CubeSQL free license (2 connections). I’ll test that. I’ve got lots to learn.
I’m curious why you’d use variable names starting with _ ?
I’m learning, so I start by copying the code in the example, then change it to what is more natural for me (using words in Spanish). This is the first time I see variables starting with _. But it was my first try with the code, so I left the variable name as is.
You will have to ask Phillip why he used that for the example.
[quote=362783:@Norman Palardy]Not necessarily
But I’d have to spend a lot more time seeing whats the same vs different between CubeSQL & the built in SQLiteDatabase to be able to offer much more advice[/quote]
that would be wonderful…
I really meant that in the sense of “well IF I had extra time to devote to doing this I would but since I don’t have that spare time …”
spare time - what a concept
[quote=362798:@Norman Palardy]I really meant that in the sense of “well IF I had extra time to devote to doing this I would but since I don’t have that spare time …”
spare time - what a concept :P[/quote]
Who has spare time?
What is spare time? 
[quote=362735:@Roman Varas]Alright guys… first, thanks a lot for taking the time to think about this for me…
As my code supports both CubeSQL/SQLite the prepared statement solution that Alberto suggested can’t be implemented as it will not work with CubeSQL… that would require extra work. Also this is for internal corporate use… so the risk of SQL injection is minor… (I understand that it still could be injected by an inside person)
So I stuck with Norman’s proposal (with a little tweaking to escape SQL ’ characters)…
Eventually I might think of a prepared statement solution for both CubeSQL AND standard SQLite
Again, thanks a lot. This forum rocks.
R[/quote]
I wanted to test SQLdeLite and CubeSQL. Just finished my first test.
The Action code is the same, just needed to copy the CubeSQL plugin to Xojo, change SQLdeLite CubeSQL plugin use to True, db as CubeSQLServer instead of SQLiteDatabase and open the db with host, port, username, password and database name.
Tomorrow I’ll test Valentina Server.
I think it is similar to “spare change”