Android Question [SOLVED] Use correct SQL LIKE operator

Bladimir Silva Toro · May 15, 2018

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.

DonManfred · May 15, 2018

Bladimir Silva Toro said:
I appreciate your help.

Start with giving us all relevant Information.

What is the code you are using to call this??

Bladimir Silva Toro said:
Invalid parameter index 1.

Seems you are not giving any parameters.

LucaMs · May 15, 2018

Bladimir Silva Toro said:
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 '%?%'"

?

DonManfred · May 15, 2018

LucaMs said:
Maybe it is due to the use of parametrized query

He is using a parametrized Query, yes.
But it seems he is not using a parametrized request.

LucaMs · May 15, 2018

DonManfred said:
He is using a parametrized Query, yes.
But it seems he is not using a parametrized request.

I don't remember jRDC2 but, as you know, parameterized query can escape the parameter, so I would try using "\%".

Let me know if it works

Erel · May 15, 2018

Change the command to:

B4X:

SELECT NIT, Name, City FROM Customers WHERE Name LIKE ?

And include the percentage signs in the passed parameter.

Bladimir Silva Toro · May 15, 2018

Hello friends

Here is the query in the config file of jRDC2

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.

Android Question [SOLVED] Use correct SQL LIKE operator

Bladimir Silva Toro

Active Member

DonManfred

Expert

LucaMs

Expert

DonManfred

Expert

LucaMs

Expert

Erel

B4X founder

Bladimir Silva Toro

Active Member

Similar Threads