[−][src]Crate odbc_iter

odbc-iter is a Rust high level database access library based on odbc crate that uses native ODBC drivers to access a variety of databases.

With this library you can:

connect to any database supporting ODBC standard (e.g. via unixodbc library and ODBC database driver),
run one-off, prepared or parametrized queries,
iterate result set via standard Iterator interface,
automatically convert rows into:
- tuples of Rust standard types,
- custom type implementing a trait,
- vector of dynamically typed values,
create thread local connections for multithreaded applications.

Things still missing:

support for DECIMAL types - currently DECIMAL columns need to be cast to DOUBLE on the query (PR welcome),
rest of this list - please open issue in GitHub issue tracker for missing functionality, bugs, etc..

Example usage

Connect and run one-off queries with row type conversion

use odbc_iter::{Odbc, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");
let mut connection = Odbc::connect(&connection_string)
    .expect("failed to connect to database");

// Handle statically guards access to connection and provides query functionality
let mut db = connection.handle();

// Get single row single column value
println!("{}", db.query::<String>("SELECT 'hello world'").expect("failed to run query")
    .single().expect("failed to fetch row"));

// Iterate rows with single column
for row in db.query::<String>("SELECT 'hello world' UNION SELECT 'foo bar'")
    .expect("failed to run query") {
    println!("{}", row.expect("failed to fetch row"))
}
// Prints:
// hello world
// foo bar

// Iterate rows with multiple columns
for row in db.query::<(String, i8)>(
    "SELECT 'hello world', CAST(24 AS TINYINT) UNION SELECT 'foo bar', CAST(32 AS TINYINT)")
    .expect("failed to run query") {
    let (string, number) = row.expect("failed to fetch row");
    println!("{} {}", string, number);
}
// Prints:
// hello world 24
// foo bar 32

// Iterate rows with dynamically typed values using `ValueRow` type that can represent
// any row
for row in db.query::<ValueRow>("SELECT 'hello world', 24 UNION SELECT 'foo bar', 32")
    .expect("failed to run query") {
    println!("{:?}", row.expect("failed to fetch row"))
}
// Prints:
// [Some(String("hello world")), Some(Tinyint(24))]
// [Some(String("foo bar")), Some(Tinyint(32))]

Using prepared statements and parametrized queries

use odbc_iter::{Odbc, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");
let mut connection = Odbc::connect(&connection_string)
    .expect("failed to connect to database");

// Handle statically guards access to connection and provides query functionality
let mut db = connection.handle();

// Allocate `PreparedStatement` on given connection
let prepared_statement = db
    .prepare("SELECT 'hello world' AS foo, CAST(42 AS INTEGER) AS bar, CAST(10000000 AS BIGINT) AS baz")
    .expect("prepare prepared_statement");

// Use `?` as placeholder for value
let parametrized_query = db
    .prepare("SELECT ?, ?, ?")
    .expect("prepare parametrized_query");

// Database can infer schema of prepared statement
println!("{:?}", prepared_statement.schema());
// Prints:
// Ok([ColumnType { datum_type: String, odbc_type: SQL_VARCHAR, nullable: false, name: "foo" },
// ColumnType { datum_type: Integer, odbc_type: SQL_INTEGER, nullable: true, name: "bar" },
// ColumnType { datum_type: Bigint, odbc_type: SQL_EXT_BIGINT, nullable: true, name: "baz" }])

// Execute prepared statement without binding parameters
let result_set = db
    .execute::<ValueRow>(prepared_statement)
    .expect("failed to run query");

// Note that in this example `prepared_statement` will be dropped with the `result_set`
// iterator and cannot be reused
for row in result_set {
    println!("{:?}", row.expect("failed to fetch row"))
}
// Prints:
// [Some(String("hello world")), Some(Integer(42)), Some(Bigint(10000000))]

// Execute parametrized query by binding parameters to statement
let mut result_set = db
    .execute_with_parameters::<ValueRow, _>(parametrized_query, |q| {
        q
            .bind(&"hello world")?
            .bind(&43)?
            .bind(&1_000_000)
    })
    .expect("failed to run query");

// Passing `&mut` reference so we don't lose access to `result_set`
for row in &mut result_set {
    println!("{:?}", row.expect("failed to fetch row"))
}
// Prints:
// [Some(String("hello world")), Some(Integer(43)), Some(Bigint(1000000))]

// Get back the statement for later use dropping any unconsumed rows
let parametrized_query = result_set.close().expect("failed to close result set");

// Bind new set of parameters to prepared statement
let mut result_set = db
    .execute_with_parameters::<ValueRow, _>(parametrized_query, |q| {
        q
            .bind(&"foo bar")?
            .bind(&99)?
            .bind(&2_000_000)
    })
    .expect("failed to run query");

for row in &mut result_set {
    println!("{:?}", row.expect("failed to fetch row"))
}
// Prints:
// [Some(String("foo bar")), Some(Integer(99)), Some(Bigint(2000000))]

Using thread local connection

use odbc_iter::{Odbc, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");

// `connection_with` can be used to create one connection per thread
let result = odbc_iter::thread_local::connection_with(&connection_string, |mut connection| {
    // Provided object contains result of the connection operation
    // in case of error calling `connection_with` again will result
    // in new connection attempt
    let mut connection = connection.expect("failed to connect");

    // Handle statically guards access to connection and provides query functionality
    let mut db = connection.handle();

    // Get single row single column value
    let result = db.query::<String>("SELECT 'hello world'")
        .expect("failed to run query").single().expect("failed to fetch row");

    // Return connection back to thread local so it can be reused later on along
    // with the result of the query that will be returned by the `connection_with` call
    // Returning `None` connection is useful to force new connection attempt on the
    // next call
    (Some(connection), result)
});

println!("{}", result);
// Prints:
// hello world

Converting column values to `chrono` crate's date and time types (with "chrono" feature)

use odbc_iter::{Odbc, ValueRow};
use chrono::NaiveDateTime;

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");
let mut connection = Odbc::connect(&connection_string)
    .expect("failed to connect to database");

// Handle statically guards access to connection and provides query functionality
let mut db = connection.handle();

// Get `chrono::NaiveDateTime` value
println!("{}", db.query::<NaiveDateTime>("SELECT CAST('2019-05-03 13:21:33.749' AS DATETIME2)")
    .expect("failed to run query").single().expect("failed to fetch row"));
// Prints:
// 2019-05-03 13:21:33.749

Query JSON column from MonetDB (with "serde_json" feature)

use odbc_iter::{Odbc, Value};

// Connect to database using connection string
let connection_string = std::env::var("MONETDB_ODBC_CONNECTION")
    .expect("MONETDB_ODBC_CONNECTION environment not set");
let mut connection = Odbc::connect(&connection_string)
    .expect("failed to connect to database");

// Handle statically guards access to connection and provides query functionality
let mut db = connection.handle();

// Get `Value::Json` variant containing `serde_json::Value` object
println!("{}", db.query::<Value>(r#"SELECT CAST('{ "foo": 42 }' AS JSON)"#)
    .expect("failed to run query").single().expect("failed to fetch row"));
// Prints:
// {"foo":42}

Serializing `Value` and `ValueRow` using `serde` to JSON string (with "serde" feature)

use odbc_iter::{Odbc, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");
let mut connection = Odbc::connect(&connection_string)
    .expect("failed to connect to database");

// Handle statically guards access to connection and provides query functionality
let mut db = connection.handle();

// Get `ValueRow` (or just single `Value`) that implements `serde::Serialize` trait
let row = db.query::<ValueRow>("SELECT 'hello world', CAST(42 AS INTEGER), CAST(10000000 AS BIGINT)")
    .expect("failed to run query").single().expect("failed to fetch row");

println!("{}", serde_json::to_string(&row).expect("failed to serialize"));
// Prints:
// ["hello world",42,10000000]

UTF-16 databases (e.g. SQL Server)

With SQL Server NVARCHAR data cannot be passed via query text (N"foo") as query text itself is encoded as Rust String and hence UTF-8 and not UTF-16 as expected by SQL Server.

To correctly query NVARCHAR columns as String connection has to be configured like this:

use odbc_iter::{Odbc, Settings, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");

let mut connection = Odbc::connect_with_settings(&connection_string, Settings {
    utf_16_strings: true,
}).expect("failed to connect to database");

To correctly insert NVARCHAR column value, the String has to be cast to UTF-16 and bound as &[u16]:

use odbc_iter::{Odbc, Settings, ValueRow};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");

let mut connection = Odbc::connect_with_settings(&connection_string, Settings {
    utf_16_strings: true,
}).expect("failed to connect to database");

let mut db = connection.handle();

let utf_16_string = "Fóó".encode_utf16().collect::<Vec<u16>>();
let data: ValueRow = db.query_with_parameters("SELECT ? AS val", |q| q.bind(&utf_16_string))
    .expect("failed to run query")
    .single()
    .expect("fetch data");

Alternatively the provided StringUtf16 type can be bound (implementes Deserialize and custom Debug):

use odbc_iter::{Odbc, Settings, ValueRow, StringUtf16};

// Connect to database using connection string
let connection_string = std::env::var("DB_CONNECTION_STRING")
    .expect("DB_CONNECTION_STRING environment not set");

let mut connection = Odbc::connect_with_settings(&connection_string, Settings {
    utf_16_strings: true,
}).expect("failed to connect to database");

let mut db = connection.handle();

let utf_16_string = StringUtf16::from("Fóó");
let data: ValueRow = db.query_with_parameters("SELECT ? AS val", |q| q.bind(&utf_16_string))
    .expect("failed to run query")
    .single()
    .expect("fetch data");

Re-exports

pub use odbc_type::StringUtf16;

Modules

odbc_type	Extra types that represent SQL data values but with extra from/to implementations for `OdbcType` so they can be bound to query parameter
thread_local

Structs

BindError	Error that can happen when binding values to parametrized queries.
Binder	Controls binding of parametrized query values.
Column	Represents SQL table column which can be converted to Rust native type.
ColumnType	Description of column type, name and nullability properties used to represent row schema.
Connection	Database connection.
DefaultConfiguration	Default configuration that allows converting rows to types supported by this crate.
Handle	Statically ensures that `Connection` can only be used after `ResultSet` was consumed to avoid runtime errors.
NaiveDate	ISO 8601 calendar date without timezone. Allows for every proleptic Gregorian date from Jan 1, 262145 BCE to Dec 31, 262143 CE. Also supports the conversion from ISO 8601 ordinal and week date.
NaiveDateTime	ISO 8601 combined date and time without timezone.
NaiveTime	ISO 8601 time without timezone. Allows for the nanosecond precision and optional leap second representation.
NullableValue	Wrapper type that can be used to display nullable column value represented as `Option<Value>`.
Odbc	ODBC environment entry point.
OdbcError	ODBC library initialization and connection errors.
PreparedStatement	ODBC prepared statement.
ResultSet	Iterator over result set rows.
Row	Represents SQL table row of `Column` objects.
Settings	Runtime settings configured per connection.
SplitQueriesError	Error splitting SQL script into single queries.
SqlDataTypeMismatch	This error can be returned if database provided column type does not match type requested by client
UnsupportedSqlDataType	This error can be returned if database provided column of type that currently cannot be mapped to `Value` type.

Enums

ColumnConvertError	Error type that represents different problems when converting column values to specific types.
DataAccessError	Errors related to data access of query result set.
DatumAccessError	Errors related to datum access of ODBC cursor.
DatumType	Types of values that column can be converted to.
Prepared	`Statement` state used to represent a statement compiled into an access plan. A statement will enter this state after a call to `Statement::prepared`
QueryError	Errors related to execution of queries.
ResultSetError	Error crating ResultSet iterator.
RowConvertError	Errors that may happen during conversion of `ValueRow` to given type.
RowConvertTupleError	Errors that my arise when converting rows to tuples.
Value	Representation of every supported column value.
ValueConvertError	Error type that represents different problems when converting column values to specific types.
ValueRowConvertError	Errors that may happen during conversion of `ValueRow` to given type.
ValueRowConvertTupleError	Errors that my arise when converting rows to tuples.

Traits

AsNullable
Configuration	Data access configuration that can be used to configure data retrieval and conversion configured per `ResultSet` for given `Item` type. Configuration can be attached to `Handle` and will be cloned per query so it can store per query state.
Datelike	The common set of methods for date component.
Timelike	The common set of methods for time component.
TryFromColumn	Column values can be converted to types implementing this trait.
TryFromRow	This traits allow for conversion of `Row` type representing ODBC cursor used internally by `ResultSet` iterator to any other type returned as `Item` that implements it.
TryFromValue	Column values can be converted to types implementing this trait.
TryFromValueRow	This traits allow for conversion of `ValueRow` type used internally by `ResultSet` iterator to any other type returned as `Item` that implements it.

Functions

split_queries

Split SQL script into list of queries. Each query needs to be terminated with semicolon (";"). Lines starting with two dashes ("--") are skipped.

Type Definitions

Executed	`Statement` state used to represent a statement with a result set cursor. A statement is most likely to enter this state after a `SELECT` query.
SqlDate
SqlSsTime2
SqlTime
SqlTimestamp
ValueRow	Row of dynamic nullable column values.