Module hdbconnect::code_examples

Code examples for the usage of this database driver.

1. Database connections

Establish an authenticated connection to the database server (see also ConnectParams):

use hdbconnect::{Connection, IntoConnectParams};
let params = "hdbsql://my_user:my_passwd@the_host:2222".into_connect_params()?;
let mut connection = Connection::new(params)?;

2. Queries and other database calls

The most generic way to fire SQL statements without preparation is using Connection::statement(). This generic method can handle very different kinds of calls (queries, dml, procedures), and thus has the most complex return type, HdbResponse.

let query = "SELECT foo FROM bar";
let response = connection.statement(query)?; // HdbResponse

HdbResponse is a nested enum which covers all possible return values we can get from the database. You thus have to analyze it to come to the concrete response relevant for your call. You can do this either explicitly using match etc or with the adequate short-cut method, e.g.:

let response = connection.statement(query)?; // HdbResponse
let resultset = response.into_resultset()?; // ResultSet

You can do the same of course with HdbResponses obtained from the execution of prepared statements.

In many cases it will be more appropriate and convenient to use one of the specialized methods

• connection.query(...) // ResultSet
• connection.dml(...) // usize
• connection.exec(...) // ()

where each has an adequate simple result type:

let qry = "SELECT foo FROM bar";
let resultset = connection.query(qry)?; // ResultSet

With prepared statements, the code will look like this:

let stmt_str = "insert into TEST_PREPARE (F_STRING, F_INTEGER) values(?, ?)";
let mut stmt = connection.prepare(stmt_str)?;
stmt.add_batch(&("foo", 45_i32))?;
stmt.add_batch(&("bar", 46_i32))?;
stmt.execute_batch()?;

Or like this:

let stmt_str = "select NAME, CITY from TEST_TABLE where age > ?";
let mut stmt = connection.prepare(stmt_str)?;
stmt.add_batch(&(45_i32))?;
let resultset = stmt.execute_batch()?;

3. Resultset evaluation

Evaluating a resultset by iterating over the rows explicitly is possible, of course. Note that the row iterator returns HdbResult<Row>, not Row, because the resultset might need to fetch more rows lazily from the server, which can fail.

for row in resultset {
    let row = row?;
    // now you have a real row
}

Such a streaming-like behavior is especially appropriate for large resultsets. Iterating over the rows, while they are fetched on-demand from the server in smaller portions, makes it easy to write complex evaluations in an efficient and scalable manner.

let key_figure = resultset.into_iter()?.map(|r|{r?}).filter(...).fold(...);

You can retrieve the field values of a row individually, in arbitrary order. hdbconnect::Row provides for this a single method that is generalized by its return value, so you need to specify the target type explicitly:

use chrono::NaiveDateTime;
for row in resultset {
    let mut row:Row = row?;
    let f4: NaiveDateTime = row.field_into(3)?;
    let f1: String = row.field_into(0)?;
    let f3: i32 = row.field_into(2)?;
    let f2: Option<i32> = row.field_into(1)?;
}

A usually more convenient way is to convert the complete row into a normal rust value or tuple or struct:

#[derive(Deserialize)]
struct TestData {/* ...*/}
let qry = "select * from TEST_RESULTSET";
for row in connection.query(qry)? {
    let td: TestData = row?.try_into()?;
}

As hdbconnect uses serde for this conversion, you need to specify the type of your target variable explicitly.

Sometimes even more convenient is the option to convert the complete resultset in a single step. This option supports a variety of target data structures, depending on the concrete numbers of rows and columns.

• You can always, and most often want to, use a Vec of a struct or tuple that matches the fields of the resultset.

#[derive(Deserialize)]
struct MyRow {/* ...*/}

let result: Vec<MyRow> = connection.query(qry)?.try_into()?;

• If the resultset contains only a single line (e.g. because you specified TOP 1 in your select, or you qualified the full primary key), then you can choose to deserialize into a plain MyRow directly.

  let result: MyRow = connection.query(qry)?.try_into()?;

• If the resultset contains only a single column, then you can choose to deserialize into a Vec<field>, where field is a type that matches the field of the resultset. If a plain rust type is used, you don't even need to derive Deserialize:

  let result: Vec<u32> = connection.query(qry)?.try_into()?;

• If the resultset contains only a single value (one row with one column), then you can also deserialize into a plain field:

  let result: u32 = connection.query(qry)?.try_into()?;

4. Deserialization of field values

The deserialization of individual values provides flexibility without data loss:

• You can e.g. convert values from a nullable column into a plain field, provided that no NULL values are given in the resultset.

• Vice versa, you can use an Option<field> as target structure, even if the column is marked as NOT NULL.

• Source and target integer types can differ from each other, as long as the concrete values can be assigned without loss.

• You can convert numeric values on-the-fly into default String representations.

You should use this flexibility with some care though, errors are returned if the data violates the boundaries of the target values.

5. Binary Values

So far, specialization support is not yet in rust stable. Without that, you have to use serde_bytes::Bytes and serde_bytes::ByteBuf as lean wrappers around &[u8] and Vec<u8> to serialize into or deserialize from binary database types.

let raw_data: Vec<u8> = ...;
insert_stmt.add_batch(&(Bytes::new(&*raw_data)))?;

let bindata: ByteBuf = resultset.try_into()?; // single binary field
let first_byte = bindata[0];

6. LOBs Binary and Character LOBs can be treated like "normal" binary and String data, i.e. you can convert them with the methods described above into ByteBuf or String values.

But of course you often do not want to materialize the complete "Large Object", especially if you just want to stream it into a writer.

This can be easily accomplished as well:

    let mut resultset: hdbconnect::ResultSet = connection.query(query)?;
    let mut clob: CLOB = resultset.pop_row().unwrap().field_into_clob(1)?;
    io::copy(&mut clob, &mut writer)?;

While being read by io::copy(), the CLOB will continuously fetch more data from the database until the complete CLOB was passed over.