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]

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.
Handle	Statically ensures that Connection can only be used after ResultSet was consumed to avoid runtime errors.
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.
Options	Runtime configuration.
PreparedStatement	ODBC prepared statement.
ResultSet	Iterator over result set rows.
Row	Represents SQL table row of Column objects.
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
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.