Simple SQL select statement

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 if

Roman, 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:

  • if all 3 are empty, it will list all records in database
  • if 1, 2 or 3 have info, it will use that to search

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 :stuck_out_tongue:

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 if

and 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 :stuck_out_tongue:

[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? :wink:

[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”

:wink: