B4J Question SQL Query - Detect if value is not in database

aaronk

Well-Known Member
Licensed User
Hi,

I am using a SQL Query like the following:

B4X:
Dim Value as String
Value = LoadSQL.ExecQuerySingleResult("SELECT Surname FROM users WHERE ClientName='John'")
log(Value)
In my database the table 'users' has the ClientName Set to 'John' and the Surname set to 'Smith'.
In my example above the 'Value' should log 'Smith', which it does.

However lets say the ClientName is set to 'Bill' and that doesn't exist in the database it seems to return 'null' since

Is there a way to make 'value' log something like 'no user in database' instead ?

The only way I see doing this is having something like:

B4X:
If Value = "null" then Value = "No such user"
Or, What I should be looking for is something like this SQL Query:

SELECT Surname FROM users WHERE ClientName='john' IF clientName NOT IN users THEN Surname='Not in database'
 
Last edited:

wonder

Expert
Licensed User
Hi!

I'm new to SQL, but this might work:
B4X:
UserCheck = LoadSQL.ExecQuerySingleResult("SELECT COUNT(*) AS User_Exists FROM users WHERE ClientName='John'")
If 'John' doesn't exist on the given table, the count will be zero.
B4X:
If UserCheck = 0 Then Value = "No such user"
 

eps

Well-Known Member
Licensed User
almost right it's something like... no need for the AS part and you can insert a Value to search for as opposed to hardcoding, but I'm sure you've worked that out already :)

B4X:
UserCheck = LoadSQL.ExecQuerySingleResult("SELECT count(*) FROM users where series ClientName = '" & Value & "'")
[\code]

but you may well have to tweak to suit your environment / App
 

rwblinn

Well-Known Member
Licensed User
Hi,

alternative is to use a sub like:
B4X:
Sub getClientName(name As String) As String
    Dim result As String = "Not in Database"
    Dim cursor As ResultSet
    Try
        cursor = gSQL.ExecQuery2("SELECT ClientName FROM users WHERE ClientName=?",Array As String(name))
        Do While cursor.NextRow
            result = cursor.GetString2(0)       
        Loop
    Catch
        Log(LastException.Message)
    End Try
    Return result
End Sub
 

aaronk

Well-Known Member
Licensed User
Thanks for your help guys..
I ended up using something like this:
B4X:
Dim Value as String = "John"
Dim UserLastName as String
UserCheck = LoadSQL.ExecQuerySingleResult("SELECT count(*) FROM users WHERE ClientName = '" & Value & "'")
If UserCheck = 0 Then
      Log("User is Not in database")
   Else
      Log("User is in database")
      Dim Value asString
      UserLastName = LoadSQL.ExecQuerySingleResult("SELECT Surname FROM users WHERE ClientName = '" & Value & "'")
      Log(UserLastName)
End If
 
  • Like
Reactions: eps

hbruno

Member
Licensed User
Test this :
B4X:
Dim Value as String
Dim UserLastName as String = LoadSQL.ExecQuerySingleResult("SELECT Surname FROM users WHERE ClientName = '" & Value & "'")
if UserLastName != Null Then
    Log(UserLastName)
else
    Log(Value & " is Not in database")
end if
 
Top