SQLite Select

Targets iOS
1

How close did I get? I am having trouble finding an example query to do what I want.

I want to get the name or address from 2 edit fields that match the records that contain the text from those edit fields

rowsFound = db.SelectSQL("SELECT * FROM Team WHERE Name LIKE ? OR Address LIKE ?",myName , MyAddress)
2

Try

rowsFound = db.SelectSQL("SELECT * FROM Team WHERE Name LIKE '%" + myName + "%' OR  Name LIKE '%" + MyAddress + "%'")

This isn’t the only way but it is the way I would do it. Using the % wildcard allows you to match the string as a partial to whatever is in the Name field.

https://www.sqlitetutorial.net/sqlite-like/

1 Like
3

Thanks Tom, I was close haha I have the hang of the syntax now. What is the difference between MATCH and LIKE? I swapped them but they seem to do the same thing or is there a subtle difference?
Also thanks for the Tutorial reference it had some better explanations compared to the ones I found.

4

I’ve never used MATCH, but I would avoid using MATCH. You may get unexpected results. The documentation I Googled says this:

The MATCH operator is a special syntax for the match() application-defined function. The default match() function implementation raises an exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.

1 Like
5

Hi Tom,
I have 3 text fields that I am reading and writing to ok but want to add a couple of pictures in 2 extra fields. I have been playing for a few days but can’t get it right (IOS)
Do you have a simple example that could help?
Thanks
Martin
No problem if you are too busy

6

For pictures you need to use a BLOB field and you need to convert the picture to a string to store it in that field. I use PictureToJPEGStringMBS from the MBS Picture Plugin to convert the picture to a string to insert into the database. When I retrieve the picture string I use JPEGStringToPictureMBS to restore the string to a picture object.

There are a number of threads in the forum on doing this with encode base 64 but I found the MBS plugin the easiest to use and being compressed into a jpeg uses less database space and resources. You also may want to resize your pictures so that they are never larger than a desired max height or max width.

// p is a picture, 100 is the jpeg quality
Var pstrng As String = PictureToJPEGStringMBS(p,100)
// Store the string into the DB

// convert the string back to a picture after retrieving it from the DB
Var p As Picture = JPEGStringToPictureMBS(dbstrng)
// Use the picture
2 Likes