PostgreSQL PreparedStatement with ILIKE not returning anything

Databases
1

Good evening night owls,

I’m pulling my beard on this one. I’ve done it before but for some alien reason I cannot for the life of me figure it out…!
I’m querying my Postgres database with an ILIKE in a Prepared Statement.
If I’m NOT using a Prepared Statement it all works great but as soon as I turn it into a Prepared Statement it no longer returns any rows and no error message.

This is what DOES work:

    sql = "SELECT " _
    + "equipment.""name""," _
    + "equipment.""id""," _
    + "equipment.vendor," _
    + "equipment.serialnumber," _
    + "equipment.model," _
    + "equipment.status," _
    + "equipment.notes," _
    + "equipment.verification," _
    + "equipment.purchasedate," _
    + "equipment_status.status_name " _
    + "FROM equipment INNER JOIN equipment_status ON equipment.status = equipment_status.""id"" " _
    + "WHERE (equipment.""name""||' '||equipment.vendor ILIKE '%apple%') " _
    + "ORDER BY equipment.""name"" " + order
   rs = Session.pgDB.SQLSelect(sql)

This is what I want to use but it does not work.

    sql = "SELECT " _
    + "equipment.""name""," _
    + "equipment.""id""," _
    + "equipment.vendor," _
    + "equipment.serialnumber," _
    + "equipment.model," _
    + "equipment.status," _
    + "equipment.notes," _
    + "equipment.verification," _
    + "equipment.purchasedate," _
    + "equipment_status.status_name " _
    + "FROM equipment INNER JOIN equipment_status ON equipment.status = equipment_status.""id"" " _
    + "WHERE (equipment.""name""||' '||equipment.vendor ILIKE $1) " _
    + "ORDER BY equipment.""name"" " + order
    
    ps = PostgreSQLPreparedStatement(Session.pgDB.Prepare(sql))
    ps.Bind(0, "%" + Trim(searchText) + "%")
    rs = ps.SQLSelect(sql)

Any SQL gurus see my beard pulling error in there?

Appriciate any input :slight_smile:

2

If you’re binding the parameters separately like your example, don’t pass anything into the prepared statement’s SQLSelect. All you’d pass in there are your parameter values if you didn’t want to .Bind them separately…

3

Travis,
Thank you! I knew there was something “easy” I missed! That works great!
You’ve saved my beard :wink:

4

I don’t think that’s intentional! :wink:

5

It was :wink:
A beard takes you a long way, haha.