Replace strings with "null" value as real null values rusqlite

I need to convert strings that have the value null to actual null values in a SQLite DB using rusqlite. I'm not in control of these string values. I've found the Null struct, but I'm not sure how to concisely pass it as a parameter, but then use the string value when it's not null.

sqlite.execute(
    "INSERT INTO people (name) VALUES (?1)",
    params![
        if person.name == "null" { &Null } else { person.name }
    ],
).unwrap();

But I get the error

`if` and `else` have incompatible types

expected `&rusqlite::types::Null`, found struct `std::string::String`rustc(E0308)

I could do something like this, but there has to be a better way.

if person.name == "null" {
    sqlite.execute(
        "INSERT INTO people (name) VALUES (?1)",
        params![
            &Null
        ],
    ).unwrap();
} else {
    sqlite.execute(
        "INSERT INTO people (name) VALUES (?1)",
        params![
            person.name
        ],
    ).unwrap();
}

I can only imagine how quickly something like this could get out of hand with multiple values in a more realistic setting. Is there some universal type I can covert the string type and the Null struct to? I could also make a SQL query to replace all these values once I'm done inserting, but I'd much rather do it right the first time around.

2 answers

  • answered 2022-01-23 04:06 Kevin Reid

    I'm not familiar with rusqlite, but in general, the tool for “might be null” in Rust is Option, and taking a look at rusqlite::ToSql confirms that there is a

    impl<T: ToSql> ToSql for Option<T>
    

    which does what you want. (It's undocumented, unfortunately, but following the “[src]” link shows its implementation.) To use it:

    if person.name == "null" { None } else { Some(person.name) }
    

  • answered 2022-01-23 07:29 Chayim Friedman

    In addition to what @KevinReid said, the params![] macro converts all values to &dyn ToSql. So, one way is to use dyn ToSql:

    sqlite
            .execute(
                "INSERT INTO people (name) VALUES (?1)",
                params![
                    if person.name == "null" { &Null as &dyn ToSql } else { &person.name },
                ],
            )
            .unwrap()
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum