Android Question [SOLVED] Use correct SQL LIKE operator

Bladimir Silva Toro

Active Member
Licensed User
I am developing an application in B4A using jRDC2 to connect to an MS SQL Server database

In the config file of jRDC2 I wrote the SQL query using the LIKE operator as shown below:

sql.scustomers = SELECT NIT, Name, City FROM Customers WHERE Name LIKE '%?%'

When executing in B4A this SQL query appears in the following Log error:

ResponseError. Reason: java.sql.SQLException: Invalid parameter index 1., Response: <html>
<head>
<meta http-equiv = "Content-Type" content = "text / html; charset = utf-8" />
<title> Error 500 java.sql.SQLException: Invalid parameter index 1. </ title>
</ head>
<body> <h2> HTTP ERROR 500 </ h2>
<p> Problem accessing / rdc. Reason:
<pre> java.sql.SQLException: Invalid parameter index 1. </ pre> </ p> <hr> <a href="http://eclipse.org/jetty"> Powered by Jetty: // 9.4.z -SNAPSHOT </a> <hr />
</ body>
</ html>

I appreciate your help.
 

LucaMs

Expert
Licensed User
sql.scustomers = SELECT NIT, Name, City FROM Customers WHERE Name LIKE '%?%'
Maybe it is due to the use of parameterized query; try to use:

... LIKE '\%?\%'.

Also, shouldn't your line be so:
sql.scustomers = "SELECT NIT, Name, City FROM Customers WHERE Name LIKE '%?%'"

?
 

Bladimir Silva Toro

Active Member
Licensed User
Hello friends

Here is the query in the config file of jRDC2

upload_2018-5-15_9-7-37.png



Here I leave the code in B4A that I use to call the query with the LIKE operator

B4X:
Sub SCustomers(MyName As String) As ResumableSub
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("scustomers",Array(MyName))
    'req.ExeucteQuery returns a HttpJob. The returned HttpJob is used as the sender filter.
    Wait For (req.ExecuteQuery(cmd, 0, MyName)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(result As DBResult)
        If j.JobName = "DBRequest" Then
            Dim result As DBResult = req.HandleJob(j)
            If result.Rows.Size>0 Then
                Dim i As Int=0
                If gg.IsInitialized Then
                    gg.RowsDelete
                End If
                DataGrid(True)
                For Each records() As Object In result.Rows
                    Dim NitCustomer As Long = records(result.Columns.Get("NIT"))
                    Dim NameCustomer As String = records(result.Columns.Get("Name"))
                    Dim CityCustomer As String=records(result.Columns.Get("City"))
                    Dim mm As Map
                    mm.Initialize
                    mm.Put ( "nit"   , NitCustomer )
                    mm.Put ( "name"   , NameCustomer )
                    mm.Put ( "city"   , CityCustomer )
                    gg.MapInsertRow ( i+1 , mm )
                Next
            Else
                Msgbox("Customer does not exist","Warning!!!")
            End If
           
        End If
    Else
        Msgbox("Network problem ","Warning!!!")
    End If
    Dim res As DBResult = Null
    Return res
End Sub
I have tried using LIKE '\%? \%' As it says LucaMs, but the same error comes out:

ResponseError. Reason: java.sql.SQLException: Invalid parameter index 1., Response: <html>
<head>
<meta http-equiv = "Content-Type" content = "text / html; charset = utf-8" />
<title> Error 500 java.sql.SQLException: Invalid parameter index 1. </ title>
</ head>
<body> <h2> HTTP ERROR 500 </ h2>
<p> Problem accessing / rdc. Reason:
<pre> java.sql.SQLException: Invalid parameter index 1. </ pre> </ p> <hr> <a href="http://eclipse.org/jetty"> Powered by Jetty: // 9.4.z -SNAPSHOT </a> <hr />
</ body>
</ html>

I have tried as Erel says including the percentage signs in the parameter in this way by changing DBCommand and the SQL query with the LIKE operator in jRDC2

sql.scustomers=SELECT NIT,Name,City FROM Customers WHERE Name LIKE ?

B4X:
 Dim cmd As DBCommand = CreateCommand("scustomers",Array("%"&MyName&"%"))
The solution that Erel has given is the correct one, it must include the percentage symbols within the parameter.

Thank you very much and I hope this solution will serve several of the colleagues who use this great B4A application.
 
Top