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

[aaronk]

Hi,

I am using a SQL Query like the following:

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:

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'

 

[wonder]

Hi!

I'm new to SQL, but this might work:

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.

If UserCheck = 0 Then Value = "No such user"

 

[eps]

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

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]

Hi,

alternative is to use a sub like:

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]

Thanks for your help guys..
I ended up using something like this:

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

 

[hbruno]

Test this :

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