Insert null values

[franksl]

Hello,
Sorry for the silly question, I need to use execute_named() to execute an insert where some of the named fields must have a null value, can anybody show an example?
Thanks,
Frank

[kubo]

Use Option<ToSql>.

let mut stmt = conn
    .statement("insert into employees values (:name, :salary)")
    .build()?;

stmt.execute_named(&[("name", &"Allen"), ("salary", &None::<i32>)])?;

let employees: [(&str, Option<i32>); 3] = [
    ("Jones", Some(7566)),
    ("Martin", Some(7654)),
    ("Blake", None),
];
for emp in &employees {
    stmt.execute_named(&[("name", &emp.0), ("salary", &emp.1)])?;
}

[franksl]

Ok, thanks, should the type always match the column type when setting null values?
Suppose I have a table like this:
CREATE TABLE test (id INT PRIMARY KEY, ival INT, sval VARCHAR(100));

If I need to set ival and sval as null do I need to set the Option type as i32 and str like this?

let mut stmt = conn
    .statement("insert into test (id, ival, sval) values (:id, :ival, :sval)")
    .build()?;

stmt.execute_named(&[("id", &1), ("ival", &None::<i32>), ("sval", &None::<str>)])?;

Are columns types checked?
Thanks,
Frank

[kubo]

Ok, thanks, should the type always match the column type when setting null values?

Not always. For column types (number, date, timestamp, interval, ...) supporting implicit type conversion from string, &None::<&str> is available. For other column types (object, collection), the type must match the column type.