Question - Trying to use like operator with wildcards within query_as

[sakshatshinde]

Am I doing this right? This works for = operator but doesn't for like with the % wildcards

Getting the following error: InvalidBindIndex(1)

pub fn get_data<T, X>(
    s: &AppState,
    id: &X,
    col: &str,
    table: &str,
    area: DbArea,
    operator: &str,
) -> Vec<T>
where
    T: RowValue,
    X: ToSql,
{
    // https://stackoverflow.com/questions/44315421/what-parts-of-a-sql-query-are-allowed-to-be-parameterized/44315579#44315579
    let sql = match operator {
        "=" => format!("SELECT * FROM {table} where {col} {operator} :1"),
        "like" => format!("SELECT * FROM {table} where {col} {operator} '%:1%'"),
        "in" => format!("SELECT * FROM {table} where {col} {operator} (:1)"),
        _ => format!("SELECT * FROM {table} where {col} {operator} :1"),
    };

    let conn = match area {
           // some matching
    };

    let rows = conn
        .query_as::<T>(&sql, &[id])
        .inspect_err(|err| error!("Failed in db::get_data() with Error - {:?}", err));

    let mut data = Vec::new();

    if let Ok(rows) = rows {
        for result in rows {
            if let Ok(l_data) = result {
                data.push(l_data);
            }
        }
    }

    data
}

[kubo]

@sakshatshinde

Getting the following error: InvalidBindIndex(1)

That's because you put :1 between single quotation marks. Oracle think it as a part of a string, not a bind variable.

The following code will work. In the case, id should start with or end with % or both.

        "like" => format!("SELECT * FROM {table} where {col} {operator} :1"),

If you intend that rows where the col column contains id should be fetched when operator is like,

        "like" => format!("SELECT * FROM {table} where instr({col}, :1) > 0"),

Otherwise, construct %:1% in SQL, though it fetches all rows where col isn't null when id is null or an empty string.

        "like" => format!("SELECT * FROM {table} where {col} {operator} '%' || :1 || '%'"),

[sakshatshinde]

I just wanted to grab the rows if the given substring (id) exists in the col.

Example row

1 | I have an apple | 2 | I have a banana |

select * from table where col like '%apple%'

So it should return the 1st row.

I think the last option would work for me.

[kubo]

FYI.

        "in" => format!("SELECT * FROM {table} where {col} {operator} (:1)"),

It doesn't work as you might expect. You cannot pass multiple values to a single bind paramater. See #25

[kubo]

I just wanted to grab the rows if the given substring (id) exists in the col.

In that case,

        "like" => format!("SELECT * FROM {table} where instr({col}, :1) > 0"),

[sakshatshinde]

Thank you instr did the trick!