How to use uncertain sql statements in mysql library

[tgy3300]

sql( String ):   "SELECT * FROM `user` WHERE ( `age` > ? AND `age` < ? )"
sql_data( Vec<&Value> ):    [Number(20), Number(30)]

sql WHERE conditions are generated by parameters, that is, not fixed, sql_data corresponding to sql? Placeholders, also extracted from parameters Question: In the mysql library, how to use the above uncertain sql and sql_data for data query

[blackbeam]

Hi.

This looks like a query builder. Building a parametrized query is straight forward — just build the SQL string (sql) and the corresponding set of parameters (sql_data) and then execute the result.

Here is a snipped illustrating how you can build and execute a parametrized query:

/// This function drops all the resources specified in the drop_resource vector —
///     each element of the vector is basically just a resource identifier.
///
/// This function works faster than just dropping resources one by one because it splits input vector
/// into chunks and builds a batch query to drop the whole chunk at once.
pub fn drop_batch(mut tx: my::Transaction, drop_resource: Vec<DropResourceTask>) -> Result<()> {
    if drop_resource.is_empty() {
        return Ok(());
    }

    const BATCH_SIZE: usize = 2048;

    // different parts of our query
    const PROLOGUE: &str = "DELETE FROM resources WHERE id IN (";
    const FIRST_CHUNK: &str = "?";
    const NEXT_CHUNK: &str = ",?";
    const EPILOGUE: &str = ")";

    // preallocate query string and parameters
    let mut stmt = String::with_capacity(
        PROLOGUE.len()
            + FIRST_CHUNK.len()
            + (BATCH_SIZE - 1) * NEXT_CHUNK.len()
            + EPILOGUE.len(),
    );
    let mut params = Vec::<my::Value>::with_capacity(BATCH_SIZE);

    // build and execute a query for no more than BATCH_SIZE resources
    // TODO: you can do better using `Vec::chunks_exact`
    for (i, ev) in drop_resource.into_iter().enumerate() {
        if i % BATCH_SIZE == 0 {
            if i != 0 {
                stmt.push_str(EPILOGUE);
                tx.exec_drop(
                    &stmt,
                    replace(&mut params, Vec::with_capacity(BATCH_SIZE)),
                )?;
            }
            params.clear();
            stmt.clear();
            stmt.push_str(PROLOGUE);
            stmt.push_str(FIRST_CHUNK);
        } else {
            stmt.push_str(NEXT_CHUNK);
        }
        params.push(ev.res_id.into());
    }

    // cover the tail if any
    if !params.is_empty() {
        stmt.push_str(EPILOGUE);
        tx.exec_drop(&stmt, params)?;
    }

    Ok(tx.commit()?)
}