Android Question Record absence in SqLite table

Sergey_New

Well-Known Member
Licensed User
Longtime User
Returning some records from a table
B4X: 
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery("SELECT key FROM tags WHERE person=1")
Please help me change rs to detect the absence of str in the key field.
 
Solution
If I understand your request correctly, it would be something like this:

B4X: 
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=1 AND key<>?", Array(str))

Or better:

B4X: 
Dim str As String="xxx"
Dim person as int = 1
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key<>?", Array(person, str))
Sort by date Sort by votes

JCO

Active Member
Licensed User
Longtime User
If I understand your request correctly, it would be something like this:

B4X: 
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=1 AND key<>?", Array(str))

Or better:

B4X: 
Dim str As String="xxx"
Dim person as int = 1
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key<>?", Array(person, str))
 
Upvote 0
Solution

Sergey_New

Well-Known Member
Licensed User
Longtime User
Many thanks to JCO and Mahares!
And using the operator EXISTS can be faster?
 
Upvote 0

JCO

Active Member
Licensed User
Longtime User
Just to clarify.
My line would exclude only records where key is exactly str
Mahares' line would exclude any record where the key contains str
Depending on what you need, use one or the other
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
Need key exactly equal to str.
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
P.S.
I would like the search to stop at the first mismatch
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Sergey_New said:
would like the search to stop at the first mismatch
Click to expand...
JCO said:
Mahares' line would exclude any record where the key contains str
Click to expand...

Add LIMIT 1 at the end of the query. See below
B4X: 
Dim str As String "Sergey"
    rs = SQL1.ExecQuery2("SELECT key FROM tags WHERE person= ? and key NOT LIKE ? LIMIT 1", Array As Object(1, $"%${str}%"$))
Please note that the code I posted also excludes the EXACT search and also besides anywhere in the column.
If you use JCO's code the query will be: "SELECT key FROM tags WHERE person=? AND key<>? LIMIT 1"
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User

Mahares,​

This query returns the first row of the table on a mismatch, not an empty value.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Sergey_New said:
This query returns the first row of the table on a mismatch, not an empty value.
Click to expand...
If for instance, you have 5 that do not match, it will only show the first of the 5 records. That is how I understood the question. If not, please explain, preferably with an example.
Added this: If you have keyx that have a empty string and you want to display it, as the first one, add this to the query: before LIMIT 1:
ORDER BY keyx LIMIT 1
 
Last edited:
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
This is what I need. Figured it out: replaced "<>" with "="
B4X: 
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key=? LIMIT 1", Array(person, str))
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Sergey_New said:
And using the operator EXISTS can be faster?
Click to expand...
There are ways to improve performance of queries. If you have one DB that is giving you trouble in terms of performance and you are receptive to post it or its project and show what slows it down, I am sure members will take a stab at it and report back to you.
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
So far, I do not have an urgent need to improve performance. Wanted to get the best solution.
Mahares, thank you for in resolving my issues!
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
Android Question B4A sqLite DB not loading
Replies
4
Views
3K
Erel
  • Question
Android Question SqLite create_aggregate function
Replies
14
Views
6K
rgarnett1955
  • Question
Android Question Problem saving data quickly in SqLite
Replies
5
Views
3K
Erel
  • Question
Android Question SqLite and Updating table
Replies
1
Views
2K
Lahksman
M
  • Article
B4A Library [Class] Flexible Table
64 65 66
Replies
1K
Views
381K
RB Smissaert
R
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…