Cannot insert heterogeneous elements with .execute

[NoraCodes]

I have a database created by:

CREATE TABLE quotes (
    id          INTEGER PRIMARY KEY,
    quote       TEXT NOT NULL,
    author      VARCHAR(255),
    source      TEXT
);

and I want to run the following query:

const SQL_INSERT_QUOTE: &'static str =
" INSERT INTO quotes (quote, author, source) VALUES (?1, ?2, ?3)";

With the code:

pub fn add_quote(connection: &mut Connection, quote: &Quote) -> Result<(), Error> {
    let mut statement = connection.prepare(SQL_INSERT_QUOTE);
    statement.execute(&[&quote.quote, &quote.author, &quote.source])?;
    Ok(())
}

Here, quote.source is an Option<String>, while the others are String, so there's no way (that I can see) to insert it, because I can't access ToSql to make the elements of the array into &dyn ToSql, and the array has to be homogeneous.

What's the correct way to do this?

[thomcc]

Why can't you access ToSql? It's public, just on rusqlite::types::ToSql.

[gwenn]

See the example here:

conn.execute(
        "INSERT INTO person (name, time_created, data)
                  VALUES (?1, ?2, ?3)",
        &[&me.name as &ToSql, &me.time_created, &me.data],
    ).unwrap();

The first element must be cast: as &ToSql.

[gwenn]

This inconvenient syntax is related to https://github.com/jgallagher/rusqlite/issues/312#issuecomment-420028030

[NoraCodes]

Oh, thanks for the clarification! Perhaps it's possible to use a technique like Rocket uses and have a tosql! macro that takes heterogeneous implementers and produces a slice of &dyn ToSql? If this sounds OK I will make a PR.

[gwenn]

@NoraCodes We want to support parameter values specified as:
https://github.com/jgallagher/rusqlite/blob/master/src/statement.rs#L997
without memory allocation.
If you can do this and fix inconvenient syntax:
https://github.com/jgallagher/rusqlite/blob/master/src/statement.rs#L1002
Perfect.

[thomcc]

This would probably work.

macro_rules! to_sql {
    () => {
        $crate::NO_PARAMS
    };
    ($($param:expr),+ $(,)?) => {
        &[$(&$param as &dyn $crate::types::ToSql),+]
    };
}

I'm not sure about it doing &$param vs just $param. It probably doesn't matter though.

A version that supports named params too would be nice, but is a decent amount more work due to annoying macro expansion matching weirdness (I think it needs to be tt muncher, sadly).

It might be nice to export ToSql from the root at the same time too, it's always a pain to have to import it from rusqlite::types::ToSql instead of just rusqlite::ToSql.

[NoraCodes]

Great, thanks a lot!