Crate odbc_iter

Expand description

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");

§Runtime statistics (with “statistics” feature)

If enabled, function odbc_iter::statistics() will provide runtime statistics that can be Displayed.

ODBC statistics: connections: open: 5, queries: preparing: 2, executing: 1, fetching: 2, done: 5, failed: 0

Note that they are not strongly synchronised so things may be observed counted twice.

!

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 Aliases§

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.

Crate odbc_iterCopy item path