Module odbc_api::parameter

Passing parameters to statement

In a nutshell

• () -> No parameter
• &a -> Single input parameter
• &mut a -> Input Output parameter
• Out(&mut a) -> Output parameter
• (&a,&b,&c) -> Fixed number of parameters
• &[a] -> Arbitrary number of parameters
• a.into_parameter() -> Convert idiomatic Rust type into something bindable by ODBC.

Passing a single parameter

ODBC allows you to bind parameters to positional placeholders. In the simples case it looks like this:

use odbc_api::Environment;

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
let year = 1980;
if let Some(cursor) = conn.execute("SELECT year, name FROM Birthdays WHERE year > ?;", &year)? {
    // Use cursor to process query results.
}

All types implementing the Parameter trait can be used.

Annotating a parameter with an explicit SQL DataType

In the last example we used a bit of domain knowledge about the query and provided it with an i32. Each Parameter type comes with a default SQL Type as which it is bound. In the last example this spared us from specifing that we bind year as an SQL INTEGER (because INTEGER is default for i32). If we want to, we can specify the SQL type independent from the Rust type we are binding, by wrapping it in WithDataType.

use odbc_api::{Environment, parameter::WithDataType, DataType};

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
let year = WithDataType{
   value: 1980,
   data_type: DataType::Varchar {length: 4}
};
if let Some(cursor) = conn.execute("SELECT year, name FROM Birthdays WHERE year > ?;", &year)? {
    // Use cursor to process query results.
}

In that case it is likely that the driver manager converts our anotated year into a string which is most likely being converted back into an integer by the driver. All this converting can be confusing, but it is helpful if we do not know what types the parameters actually have (i.e. the query could have been entered by the user on the command line.). There is also an option to query the parameter types beforhand, but my advice is not trust the information blindly if you cannot test this with your driver beforehand.

Passing a fixed number of parameters

To pass multiple but a fixed number of parameters to a query you can use tuples.

use odbc_api::Environment;

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
let too_old = 1980;
let too_young = 2000;
if let Some(cursor) = conn.execute(
    "SELECT year, name FROM Birthdays WHERE ? < year < ?;",
    (&too_old, &too_young),
)? {
    // Use cursor to congratulate only persons in the right age group...
}

Passing an abitrary number of parameters

Not always do we know the number of required parameters at compile time. This might be the case if the query itself is generated from user input. Luckily slices of parameters are supported, too.

use odbc_api::Environment;

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
let params = [1980, 2000];
if let Some(cursor) = conn.execute(
    "SELECT year, name FROM Birthdays WHERE ? < year < ?;",
    &params[..])?
{
    // Use cursor to process query results.
}

Output and Input/Output parameters

Mutable references are treated as input/output parameters. To use a parameter purely as an output parameter you may wrapt it into out. Consider a Mircosoft SQL Server with the following stored procedure:

CREATE PROCEDURE TestParam
@OutParm int OUTPUT
AS
SELECT @OutParm = @OutParm + 5
RETURN 99
GO

We bind the return value as the first output parameter. The second parameter is an input/output bound as a mutable reference.

use odbc_api::{Environment, Out, Nullable};

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;

let mut ret = Nullable::<i32>::null();
let mut param = Nullable::<i32>::new(7);

conn.execute(
    "{? = call TestParam(?)}",
    (Out(&mut ret), &mut param))?;

assert_eq!(Some(99), ret.into_opt());
assert_eq!(Some(7 + 5), param.into_opt());

Passing the type you absolutly think should work, but does not.

Sadly not every type can be safely bound as something the ODBC C-API understands. Most prominent among those is a Rust string slice (&str).

use odbc_api::Environment;

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
// conn.execute("SELECT year FROM Birthdays WHERE name=?;", "Bernd")?; // <- compiler error.

Alas, not all is lost. We can still make use of the crate::IntoParameter trait to convert it into something that works.

use odbc_api::{Environment, IntoParameter};

let env = unsafe {
    Environment::new()?
};

let mut conn = env.connect("YourDatabase", "SA", "<YourStrong@Passw0rd>")?;
if let Some(cursor) = conn.execute(
    "SELECT year FROM Birthdays WHERE name=?;",
    &"Bernd".into_parameter())?
{
    // Use cursor to process query results.
};

Conversion for &str is not too expensive either. Just an integer more on the stack. Wait, the type you wanted to use, but that I have conviniently not chosen in this example still does not work? Well, in that case please open an issue or a pull request. crate::IntoParameter can usually be implemented entirely in safe code, and is a suitable spot to enable support for your custom types.

Structs

Out	Wraps a mutable reference. Use this wrapper in order to indicate that a mutable reference should be bound as an output parameter only, rather than an input / output parameter.
VarChar	A mutable buffer for character data which can be used as either input parameter or ouput buffer. It can not be used for columar bulk fetches, but if the buffer type is stack allocated in can be utilized in row wise bulk fetches.
VarCharRef	Binds a byte array as a VarChar input parameter.
WithDataType	Annotates an instance of an inner type with an SQL Data type in order to indicate how it should be bound as a parameter to an SQL Statement.

Traits

InputParameter	Extend the crate::handles::HasDataType trait with the guarantee, that the bound parameter buffer contains at least one element.
Output	Guarantees that there is space in the output buffer for at least one element.
Parameter	Implementers of this trait can be used as individual parameters of in a crate::ParameterCollection. They can be bound as either input parameters, output parameters or both.

Type Definitions

VarChar32	A stack allocated VARCHAR type able to hold strings up to a length of 32 bytes (including the terminating zero).
VarChar512	A stack allocated VARCHAR type able to hold strings up to a length of 512 bytes (including the terminating zero).
VarCharMut	Wraps a slice so it can be used as an output parameter for character data.