With the new Release 2019r2, I’m using the new db.SelectSQL with parameters that needs to be wildcards (popupMenu: *, Item1, Item2…)
Ex:
db.SelectSQL(“SELECT * FROM table WHERE CountryID = ? AND Region = ? ;”, CountryID, popupMenu.SelectedRowValue)
How do I have one of the parameters being a wildcard - MySQL always returns Nil when wildcard is selected
db.SelectSQL("SELECT * FROM table WHERE CountryID = ? AND Region = ? ;", CountryID, If(popupMenu.SelectedRowValue = "*", "%", popupMenu.SelectedRowValue))In standard SQL you have to use the LIKE operator along with % to do wildcard matching. So you will have to build different statements.
hmm. I must be doing something wrong, as I’ve tried this. I use Localized Strings for populating the “All” selection (lstr_FilterAll) , and the my LoadList is like this:
Dim sql As Text = “SELECT * FROM GRADUS.v_UserLocations WHERE CompanyID = ? AND UserID = ? AND MainGroup = ? AND SubGroup = ? AND CustomerName = ? ORDER BY Name ;”
If mGrp = lstr_FilterAll Then
mGrp = “%”
sql = sql.Replace(“AND MainGroup = ?”, “AND MainGroup LIKE ?”)
End If
If sGrp = lstr_FilterAll Then
sGrp = “%”
sql = sql.Replace(“AND SubGroup = ?”, “AND SubGroup = ?”)
End If
If cName = lstr_FilterAll Then
cName = “%”
sql = sql.Replace(“AND CustomerName = ?”, “AND CustomerName LIKE ?”)
End If
Try
rs = srv_DB.SelectSQL(sql, cID, uID, mGrp, sGrp, cName )
Catch error As DatabaseException
MessageDialog.Show("Error: " + error.Message)
End Try
This give me a sql like this:
SELECT * FROM GRADUS.v_UserLocations WHERE CompanyID = ? AND UserID = ? AND MainGroup LIKE ? AND SubGroup = ? AND CustomerName LIKE ? ORDER BY Name ;
Sorry. Forgot to Replace SubString = with LIKE. Just corrected this in the code, and it still doesn’t work??
This is the sql
SELECT * FROM GRADUS.v_UserLocations WHERE CompanyID = ? AND UserID = ? AND MainGroup LIKE ? AND SubGroup LIKE ? AND CustomerName LIKE ? ORDER BY Name ;
Are you saying you still have a problem, or just confirming that it works?
Still having the problem. It returns Nil
Are you checking for errors/exceptions? Or is the result maybe really a NIL one?
BTW:
If sGrp = lstr_FilterAll Then
sGrp = "%"
sql = sql.Replace("AND SubGroup = ?", "AND SubGroup = ?")
End If
If you really don’t care what SubGroup contains, you would remove it completely from the querrie instead of telling SQL explicitly that you don’t care whats inside this field. 
If sGrp = lstr_FilterAll Then
sql = sql.Replace("AND SubGroup = ?", "")
End If
There is no Error as there is no Exception generated (I use Try Catch), and if I run the SQL directly from MySqlWorkbench I get 3 rows (as I would expect).
I know that I could remove the SubGroup from the sql if ‘%’ is selected, but when I have 3 search fields it means 8 different possibilities, and in my next listview I need even more search fields - and then the possibilities is multiplied. This is due to the parameters in the SelectSQL will need to be adjusted according to the number of search fields, or it use parameters from Left and onwards.
I think there might be an error in Xojo???. I could also just load the entire list, and then not add the ‘invalid’ rows to the ListBox. My thoughts is just that why load rows from MySQL that are not needed.
first i would ensure that xojo let the % inside of the query.
can you see the query in MySqlWorkbench made from xojo?
So are you running the exact SQL in MySqlWorkbench that Xojo is generating?
[quote=458341:@Bobby Kurell]This is the sql
SELECT * FROM GRADUS.v_UserLocations WHERE CompanyID = ? AND UserID = ? AND MainGroup LIKE ? AND SubGroup LIKE ? AND CustomerName LIKE ? ORDER BY Name ;[/quote]
of course, replacing the ? placeholders with the appropriate values.
Do any of these columns have NULL values? If so, the wildcards won’t match those. As Sascha stated, it would be better to leave out any condition where you want all the records.
Instead of starting with all possible conditions, try building the query piece by piece, only adding a condition if the “all” choice is not selected. Then add its value to an array to use in the SelectSQL call. That way you don’t have to build every possible combination. So something like this:
[code]Var params() As Variant
params.AddRow(cID)
params.AddRow(uID)
Var sql As Text = “SELECT * FROM GRADUS.v_UserLocations WHERE CompanyID = ? AND UserID = ?”
If mGrp <> lstr_FilterAll Then
sql = sql + " AND MainGroup = ?"
params.AddRow(mGrp)
End If
If sGrp = lstr_FilterAll Then
sql = sql + " AND SubGroup = ?"
params.AddRow(sGrp)
End If
If cName = lstr_FilterAll Then
sql = sql + " AND CustomerName = ?"
params.AddRow(cName)
End If
sql = sql + " ORDER BY Name ;"
Try
rs = srv_DB.SelectSQL(sql, params)
Catch error As DatabaseException
MessageDialog.Show("Error: " + error.Message)
End Try
[/code]
Edit: forgot to add first two parameters. Corrected above
Oops, I missed the LIKE, thanks Jay, I also missed the surrounding %'s on the false result, teach me for rush posting just before zzz’s 
If you want to find a whole string using LIKE you’ll need to use %string% as long as that string isn’t in a subset of another word it’ll find only that record but as Sascha points out, if you want to find any results and you can modify the sql then just drop the AND
See here for more info https://www.w3schools.com/sql/sql_like.asp
I just checked, MySQL Workbench optimises the sql and internally changes the LIKE ‘xyz’ to = ‘xyz’ as there are no wilcards in the string. I suspect this isn’t being done with your query using the framework which is why you are seeing the different results.
To get around this you’ll have to code in a change from LIKE to = if there are no wildcards or add %'s around the string depending on your search type and data you’re searching through.
Hi Jay,
Thank you for the idea of building the params. I’ll implement that. That will work for all the rest of the list searches I need to do
Strange?? Complier will not accept params as Variant in the SelectSQL, and If I convert Params() As String, the I get an Error at runtime…
Had to make a workaround, as I need to get on… - It’s quite a bit of ‘ifs’ but ti works…
Var GroupSearch As String
Var RowIsValid As Boolean = False
GroupSearch = pop_MainGroup.RowTagAt(pop_MainGroup.SelectedRowIndex) + pop_SubGroup.RowTagAt(pop_SubGroup.SelectedRowIndex) + pop_Customer.RowTagAt(pop_Customer.SelectedRowIndex)
SelectionList.RemoveAllRows
If rs <> Nil Then
While Not rs.AfterLastRow
RowIsValid = False
Select Case GroupSearch
Case "000"
RowIsValid = True
Case "001"
If cName = rs.Column("CustomerName").StringValue.DefineEncoding(Encodings.UTF8) Then
RowIsValid = True
End If
Case "010"
If sGrp = rs.Column("SubGroup").StringValue.DefineEncoding(Encodings.UTF8) Then
RowIsValid = True
End If
Case "011"
If (sGrp = rs.Column("SubGroup").StringValue.DefineEncoding(Encodings.UTF8)) And (cName = rs.Column("CustomerName").StringValue.DefineEncoding(Encodings.UTF8)) Then
RowIsValid = True
End If
Case "100"
If mGrp = rs.Column("MainGroup").StringValue.DefineEncoding(Encodings.UTF8) Then
RowIsValid = True
End If
Case "101"
If (mGrp = rs.Column("MainGroup").StringValue.DefineEncoding(Encodings.UTF8)) And (cName = rs.Column("CustomerName").StringValue.DefineEncoding(Encodings.UTF8)) Then
RowIsValid = True
End If
Case "110"
If (mGrp = rs.Column("MainGroup").StringValue.DefineEncoding(Encodings.UTF8)) And (sGrp = rs.Column("SubGroup").StringValue.DefineEncoding(Encodings.UTF8)) Then
RowIsValid = True
End If
Case "111"
If (mGrp = rs.Column("MainGroup").StringValue.DefineEncoding(Encodings.UTF8)) And (sGrp = rs.Column("SubGroup").StringValue.DefineEncoding(Encodings.UTF8)) And (cName = rs.Column("CustomerName").StringValue.DefineEncoding(Encodings.UTF8)) Then
RowIsValid = True
End If
End SelectHmm, you’re right. I haven’t tried using this before, just going by what the docs say. I have started another thread about this.
Bobby, the problem turns out to be defining the sql variable as Text instead of String. Make that one change and the code I wrote works (except needing to change the = to <> in the second and third If statements, which I overlooked).