prisma
More examples?
I'm largely a noob to rust (coming from a c# background, been doing rust for 4 weeks) but I'm trying to start with the basics, so I'm doing something simple like dump part of a table to a CSV file. So far I can connect to the db and create a string representing a CSV header:
let stream = client.query("SELECT * FROM [master].[dbo].[MSreplication_options]", &[&1i32]).await?;
let rows = stream.into_first_result().await?;
let col_names: Vec<&str> = rows.first().unwrap().columns().iter().map(|c| c.name()).collect();
let file = File::create("test.csv")?;
let mut file = LineWriter::new(file);
file.write_all(col_names.join(",").as_ref())?;
No problem.
However when I try to read the values from each row, that's where I get lost. I noticed the get() method and tried to serialize from that, but I can't figure out how to get a string out of that. Any assistance? I know the below code doesn't work because it needs me to specify a type, but I'm not quite sure how.
for row in rows {
let mut rowtext: Vec<&str> = Vec::new();
for i in 0..(row.len() -1) {
rowtext.push(row.get(i).unwrap_or_default());
}
file.write_all(rowtext.join(",").as_ref())?;
}
Where I'm stuck is the rowtext.push() bit. I imagine that I'm in want of a superfish type representation here, but I can't figure it out from the documentation. Any help?
Tried a different approach with the later bit that works:
for row in rows {
let mut rowtext: Vec<String> = Vec::new();
for item in row.into_iter() {
let output = match item {
ColumnData::Binary(_val) => "binary data".into(),
ColumnData::Bit(val) => val.unwrap_or_default().to_string(),
ColumnData::Date(_val) => "don't know how to implement".into(),
ColumnData::DateTime(_val) => "don't know how to implement".into(),
ColumnData::DateTime2(_val) => "don't know how to implement".into(),
ColumnData::DateTimeOffset(_val) => "don't know how to implement".into(),
ColumnData::F32(val) => val.unwrap_or_default().to_string(),
ColumnData::F64(val) => val.unwrap_or_default().to_string(),
ColumnData::Guid(val) => val.unwrap_or_default().to_string(),
ColumnData::I16(val) => val.unwrap_or_default().to_string(),
ColumnData::I32(val) => val.unwrap_or_default().to_string(),
ColumnData::I64(val) => val.unwrap_or_default().to_string(),
ColumnData::Numeric(val) => val.unwrap().to_string(),
ColumnData::SmallDateTime(_val) => "don't know how to implement".into(),
ColumnData::String(val) => val.unwrap_or_default().as_ref().into(),
ColumnData::Time(_val) => "don't know how to implement".into(),
ColumnData::U8(val) => val.unwrap_or_default().to_string(),
ColumnData::Xml(val) => val.unwrap().as_ref().to_string(),
_ => "nada".into()
};
rowtext.push(output);
}
let data = format!("{}\n", rowtext.join(","));
file.write_all(data.as_ref())?;
}
Though I don't suppose there are any examples of how to properly convert those date fields into strings? I tried to do it through Chrono but I haven't been able to sort out how to convert from the tiberius date/time structs to the chrono date/time structs. Also, I just did an unwrap for the tiberius numeric and xml types, I don't suppose we could get a default implemented for them in case the database has a null value for that row?
Figured it out, but I can't help but wonder if there's a more idiomatic way of doing this?
for row in rows {
let mut rowtext: Vec<String> = Vec::new();
for item in row.into_iter() {
let output = match item {
ColumnData::Binary(_val) => "<binary data>".into(),
ColumnData::Bit(val) => val.unwrap_or_default().to_string(),
ColumnData::Date(ref val) => val.map(|date| {
let date = from_days(date.days() as i64, 1);
date.format("%Y-%m-%d").to_string()
}).unwrap_or_default(),
ColumnData::DateTime(ref val) => val.map(|dt| {
let datetime = NaiveDateTime::new(
from_days(dt.days() as i64, 1900),
from_sec_fragments(dt.seconds_fragments() as i64)
);
datetime.format("%Y-%m-%d %H:%M:%S").to_string()
}).unwrap_or_default(),
ColumnData::DateTime2(ref val) => val.map(|dt| {
let datetime = NaiveDateTime::new(
from_days(dt.date().days() as i64, 1),
NaiveTime::from_hms(0,0,0) + chrono::Duration::nanoseconds(dt.time().increments() as i64 * 10i64.pow(9 - dt.time().scale() as u32))
);
datetime.format("%Y-%m-%d %H:%M:%S").to_string()
}).unwrap_or_default(),
ColumnData::DateTimeOffset(ref val) => val.map(|dto| {
let date = from_days(dto.datetime2().date().days() as i64, 1);
let ns = dto.datetime2().time().increments() as i64 * 10i64.pow(9 - dto.datetime2().time().scale() as u32);
let time = NaiveTime::from_hms(0,0,0) + chrono::Duration::nanoseconds(ns) - chrono::Duration::minutes(dto.offset() as i64);
let naive = NaiveDateTime::new(date, time);
let dto: DateTime<Utc> = chrono::DateTime::from_utc(naive, Utc);
dto.format("%Y-%m-%d %H:%M:%S %z").to_string()
}).unwrap_or_default(),
ColumnData::F32(val) => val.unwrap_or_default().to_string(),
ColumnData::F64(val) => val.unwrap_or_default().to_string(),
ColumnData::Guid(val) => val.unwrap_or_default().to_string(),
ColumnData::I16(val) => val.unwrap_or_default().to_string(),
ColumnData::I32(val) => val.unwrap_or_default().to_string(),
ColumnData::I64(val) => val.unwrap_or_default().to_string(),
ColumnData::Numeric(val) => val.unwrap().to_string(),
ColumnData::SmallDateTime(ref val) => val.map(|dt| {
let datetime = NaiveDateTime::new(
from_days(dt.days() as i64, 1900),
from_mins(dt.seconds_fragments() as u32 * 60),
);
datetime.format("%Y-%m-%d %H:%M:%S").to_string()
}).unwrap_or_default(),
ColumnData::String(val) => val.unwrap_or_default().as_ref().into(),
ColumnData::Time(ref val) => val.map(|time| {
let ns = time.increments() as i64 * 10i64.pow(9 - time.scale() as u32);
let time = NaiveTime::from_hms(0, 0, 0) + chrono::Duration::nanoseconds(ns);
format!("{}", time.format("%H:%M:%S"))
}).unwrap_or_default(),
ColumnData::U8(val) => val.unwrap_or_default().to_string(),
ColumnData::Xml(val) => val.unwrap().as_ref().to_string(),
};
rowtext.push(output);
}
let data = format!("{}\n", rowtext.join(","));
file.write_all(data.as_ref())?;
}
However when I try to read the values from each row, that's where I get lost. I noticed the get() method and tried to serialize from that, but I can't figure out how to get a string out of that. Any assistance? I know the below code doesn't work because it needs me to specify a type, but I'm not quite sure how.
for row in rows { let mut rowtext: Vec<&str> = Vec::new(); for i in 0..(row.len() -1) { rowtext.push(row.get(i).unwrap_or_default()); } file.write_all(rowtext.join(",").as_ref())?; }Where I'm stuck is the rowtext.push() bit. I imagine that I'm in want of a superfish type representation here, but I can't figure it out from the documentation. Any help?
Hi @ArmoredDragon - this is on the right track, and as you mentioned you were likely getting an error that the type couldn't be determined, also depending on the actual types in that underlying table, the .get() method may return an error if you try to extract an &str from a column that is actually an int or a datetime2. In general I'd recommend getting each type separately from each row, and potentially packing them into a struct. This is also convenient as you can specify them by column name, and the fields of the struct allow the compiler to figure out which type you want to extract from the column.
create table pet (
name varchar(255) not null,
age int not null
)
use once_cell::sync::Lazy;
use std::env;
use tiberius::{Client, Config};
use tokio::net::TcpStream;
use tokio_util::compat::Tokio02AsyncWriteCompatExt;
static CONN_STR: Lazy<String> = Lazy::new(|| {
env::var("TIBERIUS_TEST_CONNECTION_STRING").unwrap_or_else(|_| {
"server=tcp:localhost,1433;IntegratedSecurity=true;TrustServerCertificate=true".to_owned()
})
});
struct Pet {
name: String,
age: i32,
}
#[tokio::main]
async fn main() -> anyhow::Result<()> {
let config = Config::from_ado_string(&CONN_STR)?;
let tcp = TcpStream::connect(config.get_addr()).await?;
tcp.set_nodelay(true)?;
let mut client = Client::connect(config, tcp.compat_write()).await?;
let rows = client
.query("select name, age from pet", &[])
.await?
.into_first_result()
.await?;
let mut pets = Vec::new();
for row in rows {
pets.push(Pet {
name: row
// example of how to use the `::<_>` to specify the type we want to get out of the column.
.try_get::<&str, _>("name")?
.ok_or_else(|| anyhow::anyhow!("Unexpected null"))?
.to_string(),
age: row
.try_get("age")?
.ok_or_else(|| anyhow::anyhow!("Unexpected null"))?,
})
}
Ok(())
}
That's a more elegant approach, thanks! My other approach wasn't entirely wasted fortunately as it taught me some more about rust. :)
Though I don't quite understand what anyhow is doing, does that induce a panic if we run into a null? The reason I was using unwrap_or_default was in the hope of inserting the type default in the event of a null. In real world scenarios, the table joins I end up doing frequently yield null values in some places that my existing code handles gracefully.
Hi @ArmoredDragon, good question, in this case the macro anyhow::anyhow! is just a shortcut to create an anyhow::Error which we can then short-circuit with using ? allowing us to convert the None case from try_get into an error. In this specific case this is somewhat equivalent to causing a panic as it will cause the program to exit, however this makes sense in the example because we know up front that both columns in the table are not null. If this was inside another function then it could be handled appropriately.
Assuming you have created a result set from a combination of joins where some columns may be null there are a few options to handle those potentially null columns:
Option 1: nullable fields in struct
struct Pet {
name: Option<String>,
age: Option<i32>,
}
for row in rows {
pets.push(Pet {
name: row
// example of how to use the `::<_>` to specify the type we want to get out of the column.
.try_get::<&str, _>("name")?
.map(ToString::to_string),
age: row
.try_get("age")?,
})
}
Option 2: default values
struct Pet {
name: String,
age: i32,
}
for row in rows {
pets.push(Pet {
name: row
// example of how to use the `::<_>` to specify the type we want to get out of the column.
.try_get::<&str, _>("name")?
.unwrap_or_else(|| "unknown")
.to_string(),
age: row
.try_get("age")?
.unwrap_or_else(|| 0),
})
}
The way I solved this was to create a Column trait that encapsulated the conversions for each type that I was interested in.
pub trait Column {
fn get(row: &Row, col_name: &str) -> Self;
}
impl Column for i32 {
fn get(row: &Row, col_name: &str) -> i32 {
row.try_get::<i32, _>(col_name)?.unwrap()
}
}
impl Column for Option<i64> {
fn get(row: &Row, col_name: &str) -> Option<i64> {
row.get::<i64, _>(col_name)
}
}
impl Column for String {
fn get(row: &Row, col_name: &str) -> String {
row.try_get::<&str, _>(col_name)?.unwrap().to_string()
}
}
impl Column for Option<String> {
fn get(row: &Row, col_name: &str) -> Option<String> {
match row.get::<&str, _>(col_name) {
Some(data) => { Some(data.to_string()) }
None => { None }
}
}
}
# etc for types like u8, i64 etc
The conversion of the rows fetched from the DB into the Pet object is thus cleaner:
let pets = rows
.into_iter()
.map(|r| Pet {
name: Column::get(&r, "name"),
age: Column::get(&r, "age"),
})
.collect();
This post helped quite a bit. I cannot use a predefined struct and use row.get due to the conversion.
As @1Dragoon, I just want to get a String for each possible type on arbitrary sql statements that a user can supply. And like the author, I tried to find a simple solution with row.get(...).unwrap_or(...) though I do understand that this is not possible because row.get is generic and the compiler needs some help to apply the conversion.
In my first iteration I tried to match against the current column_type which kinda worked, but not for numbers because the column_type was often Intn which then could yield an I8 or an I16, etc.
It wasnt clear to me that rows.into_iter converts to the ColumnData and matching against this type is what I needed.
So documentation could be improved a bit. Like the docs on the FromSql trait specify the actual server-types, but it is not clear how they are mapped by tiberius.
... 18 months and a lot of rust experience later :)
Assuming you have created a result set from a combination of joins where some columns may be null there are a few options to handle those potentially null columns:
Option 1: nullable fields in struct
Kind of an advanced topic I suppose, but I've heard of people doing zero copy stuff with mssql in other languages. Is there a way to do that in rust? Guessing that would manifest in the form of something like this:
struct Pet<'a> {
name: Option<Cow<'a, str>>,
age: Option<Cow<'a, u8>>,
}
Though I'm not sure who would own the strings at this point. Does the example you provided move the string to the caller, or does it provide a reference, copy, etc? And is there any way to just ask it for a Cow<T>?
Oh and also on the topic of performance, would it be more performant to iterate over the columns, and then match the column name and retrieve the data, rather than use Row::get() or Row::try_get(), which iterates over the entire row each time it is called? If so, is there a preferred way of doing this?
Curious on how you were able to parse out the Datetime functions as strings as I am unable to find documentation on the from_days/from_sec_fragments methods anywhere within chrono and tiberius. Please let me know the methodology behind this as it is currently an issue I'm facing.
In the version 0.12.2, enable the features: chrono, time, tds73. Cargo.toml file add the following dependencies:
[dependencies]
tiberius = { version = "0.12", features = ["chrono", "tds73", "time"]}
I can get the column value as the PrimitiveDateTime and Numeric type, like these:
use tiberius::time::time::PrimitiveDateTime;
use tiberius::numeric::Numeric;
//mssql datetime column
let dt = row.get::<PrimitiveDateTime, _>(dtcolidx);
//mssql numeric column
let nm = row.get::<Numeric, _>(nmcolidx);
//mssql varchar columns
let str = row.get::<&str, _>(strcolidx);
All the value is wrapped in Some(val).
Hello guys Does it fit here ? https://github.com/stevewillcock/tiberius-mappers Someone had tried?
use chrono::{DateTime, NaiveDateTime, Utc};
use rust_decimal::prelude::*;
use serde_json::{Map, Value};
use tiberius::{numeric::Decimal, ColumnType, Row};
use uuid::Uuid;
use crate::encode::encode64;
pub fn sql_row_to_json_serde(row: Row) -> Value {
let mut map = Map::new();
for column in row.columns() {
let column_name = column.name();
let column_type = column.column_type();
log::info!(
"column_name: {}, column_type: {:?}",
column_name,
column_type
);
let value = match column_type {
ColumnType::Bit => row
.try_get::<bool, _>(column_name)
.ok()
.flatten()
.map(Value::Bool)
.or(Some(Value::Bool(false))),
ColumnType::Int1 => row
.try_get::<u8, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(v.into()))
.or(Some(Value::Number(0.into()))),
ColumnType::Int2 => row
.try_get::<i16, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(v.into()))
.or(Some(Value::Number(0.into()))),
ColumnType::Int4 => row
.try_get::<i32, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(v.into()))
.or(Some(Value::Number(0.into()))),
ColumnType::Intn => row
.try_get::<i64, _>(column_name)
.or_else(|_| {
row.try_get::<i32, _>(column_name)
.map(|v| v.map(|i| i as i64))
})
.or_else(|_| {
row.try_get::<i16, _>(column_name)
.map(|v| v.map(|i| i as i64))
})
.ok()
.flatten()
.map(|v| Value::Number(v.into()))
.or(Some(Value::Number(0.into()))),
ColumnType::Int8 => row
.try_get::<i64, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(v.into()))
.or(Some(Value::Number(0.into()))),
ColumnType::Float4 => row
.try_get::<f32, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(serde_json::Number::from_f64(v as f64).unwrap()))
.or(Some(Value::Number(
serde_json::Number::from_f64(0.0).unwrap(),
))),
ColumnType::Float8 | ColumnType::Floatn => row
.try_get::<f64, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(serde_json::Number::from_f64(v).unwrap()))
.or(Some(Value::Number(
serde_json::Number::from_f64(0.0).unwrap(),
))),
ColumnType::Guid => row
.try_get::<Uuid, _>(column_name)
.ok()
.flatten()
.map(|uuid| Value::String(uuid.to_string())),
ColumnType::NVarchar
| ColumnType::NChar
| ColumnType::BigVarChar
| ColumnType::BigChar
| ColumnType::Text
| ColumnType::NText => row
.try_get::<&str, _>(column_name)
.ok()
.flatten()
.map(|s| Value::String(s.to_string()))
.or(Some(Value::String("".to_string()))),
ColumnType::Numericn | ColumnType::Decimaln => row
.try_get::<Decimal, _>(column_name)
.ok()
.flatten()
.map(|v| {
if let Some(f) = v.to_f64() {
Value::Number(serde_json::Number::from_f64(f).unwrap())
} else {
Value::String(v.to_string())
}
})
.or(Some(Value::Number(0.into()))),
ColumnType::Datetime
| ColumnType::Datetime2
| ColumnType::DatetimeOffsetn
| ColumnType::Datetime4
| ColumnType::Daten
| ColumnType::Timen
| ColumnType::Datetimen => row
.try_get::<NaiveDateTime, _>(column_name)
.ok()
.flatten()
.map(|dt| {
Value::String(DateTime::<Utc>::from_naive_utc_and_offset(dt, Utc).to_rfc3339())
})
.or(Some(Value::String("".to_string()))),
ColumnType::Null => Some(Value::String("".to_string())),
ColumnType::Money | ColumnType::Money4 => row
.try_get::<f64, _>(column_name)
.ok()
.flatten()
.map(|v| Value::Number(serde_json::Number::from_f64(v).unwrap()))
.or(Some(Value::Number(
serde_json::Number::from_f64(0.0).unwrap(),
))),
ColumnType::Bitn => row
.try_get::<bool, _>(column_name)
.ok()
.flatten()
.map(Value::Bool)
.or(Some(Value::Bool(false))),
ColumnType::BigVarBin | ColumnType::BigBinary | ColumnType::Image => row
.try_get::<&[u8], _>(column_name)
.ok()
.flatten()
.map(|bytes| Value::String(encode64(bytes))),
ColumnType::Xml | ColumnType::Udt => row
.try_get::<&str, _>(column_name)
.ok()
.flatten()
.map(|s| Value::String(s.to_string()))
.or(Some(Value::String("".to_string()))),
ColumnType::SSVariant => {
log::error!(
"SSVariant type handling not implemented for column '{}'",
column_name
);
None
}
};
map.insert(
column_name.to_string(),
value.unwrap_or_else(|| Value::String("".to_string())),
);
}
Value::Object(map)
}
