Module hdbconnect::code_examples

source ·
Expand description

Code examples.

§Contents

§Database connections

To establish an authenticated connection to a HANA database server, you need to provide connection parameters (ConnectParams) and optionally some connection configuration ConnectionConfiguration.

The connection parameters define how to establish an authenticated connection (TCP or TLS) to a server. The second parameter allows influencing the behavior and some performance characteristics of the connection.

Connection::new and Connection::with_configuration take as first parameter an object that implements IntoConnectParams.

A frequent pattern starts with a URL (see url for a full description of supported URLs) and adds user and password programmatically:

use hdbconnect::{Connection, ConnectionConfiguration, ConnectParamsBuilder};
let connection1 = Connection::new(
    ConnectParamsBuilder::from("hdbsqls://myhdb:30715?use_mozillas_root_certificates")?
        .with_dbuser("myName")
        .with_password("mySecret")
)?;

// with non-default configuration:
let connection2 = Connection::with_configuration(
    ConnectParamsBuilder::from("hdbsqls://myhdb:30715?use_mozillas_root_certificates")?
        .with_dbuser("myName")
        .with_password("mySecret"),
    &ConnectionConfiguration::default()
        .with_auto_commit(false)
        .with_read_timeout(Some(std::time::Duration::from_secs(300)))
)?;

§Queries and other database calls

§Generic method: Connection::statement() and HdbResponse

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

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

HdbResponse covers all possible return values you can get from the database. You thus have to analyze it to understand the concrete response to your call. (Or you use the respective short-cut method that fits to your statement, see below).

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.

§More specific methods with more convenient return values

In many cases it will be more appropriate and convenient to send your database command with one of the more specialized methods

which convert the database response directly into a simpler result type:

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

§Prepared statements

With prepared statements you can use parameters in a database statement, and provide one or more sets of these parameters in separate API calls before executing the statement. A parameter set is provided as a reference to a rust value that implements serde’s Serialize, and the serialized field structure must be convertible into the expected parameter value types.

Using a prepared statement could look like this:

#[derive(Serialize)]
struct Values{
   s: &'static str,
   i: i32,
};
let v1 = Values{s: "foo", i:45};
let v2 = Values{s: "bar", i:46};

let mut stmt = connection.prepare("insert into COUNTERS (S_KEY, I_VALUE) values(?, ?)")?;
stmt.add_batch(&v1)?;
stmt.add_batch(&v2)?;
stmt.execute_batch()?;

Or like this:

let mut stmt = connection.prepare("select NAME, CITY from PEOPLE where iq > ? and age > ?")?;
stmt.add_batch(&(100_u8, 45_i32))?;
let resultset = stmt.execute_batch()?.into_resultset()?;

§Iterating over rows

When iterating over the rows, the result set will implicitly fetch all outstanding rows from the server.

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

Such a streaming-like behavior is especially appropriate for large result sets.

Iterating over the rows allows writing complex evaluations in an efficient and scalable manner.

ⓘ
let key_figure = resultset.map(|r|{r.unwrap()}).map(...).fold(...).collect(...);

§Result set evaluation with try_into()

While it is possible to iterate over the rows of a resultset and then retrieve each value in each row individually, this driver offers a much more convenient way - the method try_into() allows assigning the resultset directly to some appropriate rust data type of your choice!

try_into() is available on HdbValue, Row, and ResultSet, and is based on the deserialization part of serde. It uses return type polymorphism, which means that you specify explicitly the desired type of the return value, and serde will do its best to get your data filled in.

§Explicitly evaluating a single row

You can retrieve the field values of a row individually, one after the other:

for row in resultset {
    let mut row:Row = row?;
    let f1: String = row.next_try_into()?;
    let f2: Option<i32> = row.next_try_into()?;
    let f3: i32 = row.next_try_into()?;
    let f4: chrono::NaiveDateTime = row.next_try_into()?;
}

§Direct conversion of entire rows

Convert the complete row into a normal rust value or tuple or struct with reasonably matching fields:

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

§Direct conversion of entire result sets

Even more convenient is the option to convert the complete result set in a single step. Depending on the concrete numbers of rows and columns, this option supports a variety of target data structures.

§Matrix-structured result sets

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

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

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

§Single-line result sets

If the result set 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 also deserialize directly into a plain MyRow.

#[derive(Deserialize)]
struct MyRow {/* ...*/}
let result: MyRow = connection.query(qry)?.try_into()?;

§Single-column result sets

If the result set 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 result set.

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

§Single-value result sets

If the result set 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()?;

§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 result set.

  • 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.

§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: serde_bytes::ByteBuf = resultset.try_into()?; // single binary field
let first_byte = bindata[0];

§LOBs

Character and binary LOBs can be treated like “normal” String and binary data, i.e. you can convert them with the methods described above into String or serde_bytes::ByteBuf values (see serde_bytes for serde’s specialties regarding bytes).

§Streaming LOBs to the database

Avoid materializing the complete “Large Object” by handing over a reader that provides the data. An internal buffer will be filled by reading from the reader. If the internal buffer has reached the value of the connection’s lob write size, data are sent to the database and the buffer can be filled anew.

  use std::sync::{Arc, Mutex};
  let am_reader = Arc::new(Mutex::new(reader));
  insert_stmt.execute_row(vec![
      HdbValue::STR("streaming2"),
      HdbValue::SYNC_LOBSTREAM(Some(am_reader)),
  ]).unwrap();

§Streaming LOBs from the database

Avoid materializing the complete “Large Object” by converting the HdbValue into the corresponding Lob object (one of hdbconnect::{BLob, CLob, NCLob}) and reading from it incrementally. When the internal buffer is empty, new data will be read from the database in chunks of the connection’s lob read size.

In this example the NCLob will, while being read, continuously fetch more data from the database until it is completely transferred:

use hdbconnect::{Connection, HdbResult, IntoConnectParams, ResultSet};
use hdbconnect::types::NCLob;
let mut nclob: NCLob = resultset.into_single_value()?.try_into_nclob()?;
std::io::copy(&mut nclob, &mut writer)?;