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 - currentlyDECIMAL
columns need to be cast toDOUBLE
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 Display
ed.
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
OdbcType
so they can be bound to query parameterStructs
Connection
can only be used after ResultSet
was consumed to avoid runtime
errors.Option<Value>
.Column
objects.Value
type.Enums
Statement
state used to represent a statement compiled into an access plan. A statement will
enter this state after a call to Statement::prepared
ValueRow
to given type.ValueRow
to given type.Traits
ResultSet
for given Item
type.
Configuration can be attached to Handle
and will be cloned per query so it can store per query state.Row
type representing ODBC cursor used internally by ResultSet
iterator to any other type returned as Item
that implements it.ValueRow
type used internally by ResultSet
iterator to any
other type returned as Item
that implements it.Functions
Type Definitions
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.