tokio-rs / rdbc Goto Github PK

• rust error

Connection.prepare(sql);
create_statement.execute_query(&arg_array);

Err(General("Couldn\'t convert the value `Date(\"\\\'2019-12-12\\\'\")` to a desired type"))

• mysql data row

date   `2019-12-12 00:00:00`

so i check the code. maybe you need add a mothed support the date. and i use this code ,query date success.

//just like 

/// Result set from executing a query against a statement
pub trait ResultSet {
    /// get meta data about this result set
    fn meta_data(&self) -> Result<Box<dyn ResultSetMetaData>>;

    /// Move the cursor to the next available row if one exists and return true if it does
    fn next(&mut self) -> bool;

    fn get_i8(&self, i: u64) -> Result<Option<i8>>;
    fn get_i16(&self, i: u64) -> Result<Option<i16>>;
    fn get_i32(&self, i: u64) -> Result<Option<i32>>;
    fn get_i64(&self, i: u64) -> Result<Option<i64>>;
    fn get_f32(&self, i: u64) -> Result<Option<f32>>;
    fn get_f64(&self, i: u64) -> Result<Option<f64>>;
    fn get_string(&self, i: u64) -> Result<Option<String>>;
    fn get_bytes(&self, i: u64) -> Result<Option<Vec<u8>>>;

    fn get_date(&self, i: u64) -> Result<Option<String>>; // this code impl to support date
}

impl <'a>MySQLResultSet<'a>{
    fn get_my_date(&self, i: u64) -> Result<Option<my::Value>, Error> {
        match &self.row {
            Some(Ok(row)) => row
                .get_opt(i as usize)
                .expect("we will never `take` the value so the outer `Option` is always `Some`")
                .map(|v| Some(v))
                .map_err(value_to_rdbc_err),
            _ => Ok(None),
        }
    }
}


//get_date impl
impl<'a> rdbc::ResultSet for MySQLResultSet<'a> {
fn get_date(&self, i: u64) -> Result<Option<String>, Error> {
         let date=self.get_my_date(i)?;
         if date.is_some(){
             let v=date.unwrap();
            return Ok(Option::from(v.as_sql(true)));
         }else{
             return Ok(None);
         }
    }
}

The initial PoC of RDBC was pretty naive and copied from ODBC/JDBC and is not idiomatic Rust code. This issue has been created to discuss how to create an idiomatic Rust set of traits using async for the project.

Specific issues with the current traits:

• Not async
• Data access does not use generics and instead has one accessor method per datatype e.g. get_i32()
• There are ownership issues when trying to create more than one prepared statement per connection
• There is no support for batch or columnar, but that can potentially be addressed later

Currently, it is necessary to statically link to all possible RDBC driver implementations that need to be used at runtime but one of the main benefits of ODBC/JDBC is that new drivers can be loaded at runtime, so we need a similar mechanism here.

https://www.reddit.com/r/rust/comments/enphc2/an_update_on_rust_database_connectivity_rdbc_and/

Turn rust-cli into a real CLI, maybe using the following code for inspiration.

https://github.com/apache/arrow/blob/master/rust/datafusion/src/bin/repl.rs

Hi,

Would be cool have support for other DBMS not strictly purely SQL based DBMS, the API defined so far are generic enough that could be used also with some NoSql DBMS, the missing part at this stage I think is the support for more advanced types, in the specific I think would be enough to add a collection type so adding to ResultSet:

    fn get_vec_i8(&self, i: u64) -> Result<Option<Vec<i8>>>;
    fn get_vec_i16(&self, i: u64) -> Result<Option<Vec<i16>>>;
    fn get_vec_i32(&self, i: u64) -> Result<Option<Vec<i32>>>;
    fn get_vec_i64(&self, i: u64) -> Result<Option<Vec<i64>>>;
    fn get_vec_f32(&self, i: u64) -> Result<Option<Vec<f32>>>;
    fn get_vec_f64(&self, i: u64) -> Result<Option<Vec<f64>>>;
    fn get_vec_string(&self, i: u64) -> Result<Option<Vec<String>>>;
    fn get_vec_bytes(&self, i: u64) -> Result<Option<Vec<Vec<u8>>>>;

As well adding a sort of nested type

   // ResultSet
   fn get_vec_value(&self, i: u64) -> Result<Option<Vec<Value>>>;
   fn get_vec_value(&self, i: u64) -> Result<Option<Value>>;
  // Nested Type
  trait Value {
     //Same signatures of ResultSet for access values
  }

Another important part i think is the named access to values, often there is also a name associate to a value and may not be there at all a position.
So maybe would be cool to have also the relative methods:

    fn get_named_i8(&self, name: &str) -> Result<Option<i8>>;
    // And all the other types combinations.

If you are interested to this I can also write a PR for this cases.
Regards

So far I've been mimicking ODBC/JDBC in using 1-based indexes in ResultSet and ResultSetMetaData.

I did this to ease the translation of existing ODBC/JDBC code but it feels dirty.

Realistically we will want idiomatic Rust ways of accessing this API using iterators so maybe this low-level API can remain ugly ... I am interested to hear what others think.

I see most of the type returned from traits functions are Rc<Refcell>. What is the reason behind this? Shouldn't the user wrap it himself if he needs it?

Use https://docs.rs/tiberius/0.3.2/tiberius/

Hi, there is DataType::Integer. I was wondering if it's meant to signify 32-bit integers, (int in MySQL and integer in Postgres). Is there a plan for having a data type for 64-bit integers?

It would also be very helpful if there were a correspondence table between the data types of this library, standard SQL and commonly used DB's such as Postgres, MySQL and Sqlite.

Converts many single INSERT statements into one INSERT statement with many values.

Example: INSERT INTO table VALUES (1,'yes'),(2,'no),(3,'maybe'),(4,'not sure')

https://dzone.com/articles/performant-batch-inserts-using-jdbc

https://www.journaldev.com/2494/jdbc-batch-insert-update-mysql-oracle

It looks like the last commit was 15 months ago. Might be worth being explicit about the maintenance status for this project if the intention is that not much more work on it will be done?

all of database,the transaction is supported？
Will it support serde serialize query result(https://github.com/serde-rs/json)?

BEGIN;
COMMIT;
ROLLBACK;

I really get sore eyes when I see Box<dyn Something> in rust code. It's not an idiomatic rust to use them, they bypass type safety and they hurt performance.

I understand that the API is oriented around trait objects so as to be able to link your compiled program against any driver at runtime. There certainly are cases where people would want to build apps that are compiled-once and which work with any database at runtime, but there are also cases where people would want to use RDBC API for accessing just one particular database. It's obvious that in the second case performance is harmed, and more importantly, type safety is lost. Also, API in the current state doesn't allow for downcasting or any other method of accessing concrete types which I found to be of use, although rarely, for some driver specific behavior when using JDBC. I think backdoors are always a nice to have feature in any API.

I would like to propose an alternative which would include best of both worlds. This means that API would use generics instead of trait objects like this:

pub trait Driver: Sync + Send {
    type C: Connection
    fn connect(&self, url: &str) -> Result<Self::C>;
}
pub trait Connection {
    type S: Statement;
    fn create(&mut self, sql: &str) -> Result<Self::S>;
    fn prepare(&mut self, sql: &str) -> Result<Self::S>;
}
pub trait Statement {
    type RS: ResultSet;
    fn execute_query(&mut self, params: &[Value]) -> Result<RS>;
    fn execute_update(&mut self, params: &[Value]) -> Result<u64>;
}

This API would support native compilation for any driver used, but the limitation is that the driver would have to be known in compile time. To handle that limitation, the trick is to make a driver-agnostic concrete implementation of the API which would wrap trait objects, it would hide the boxed API and expose it as generic. This way the user would only have to choose whether he wants his code compiled against native implementation or driver-agnostic implementation of the API.

PS: Compiling this project is broken. It complains that it cannot compile sqlparser

Last commit was 13 months ago, and I can't find RDBC database drivers except the ones written by @andygrove.

It should be possible to get meta-data for a result set, providing column names and data types.

Until native Rust drivers exist for all databases, it would be useful to be able to wrap ODBC drivers in the RDBC API.

I made a reasonable start on this [1] but could use some help.

[1] https://github.com/andygrove/rdbc/tree/master/rdbc-odbc

I suggest we have explicit being/commit/rollback functions as well as the ability to set autocommit mode when creating connections.

I would expect that it's possible to create multiple prepared statements for a single connection at once like following:

fn check<'a>(
    conn: &'a mut (dyn Connection + 'static),
) -> (
    Rc<RefCell<dyn Statement + 'a>>,
    Rc<RefCell<dyn Statement + 'a>>,
) {
    let a = conn.prepare("SELECT name, id FROM users").unwrap();
    let b = conn.prepare("SELECT name, id FROM posts").unwrap();

    (a, b)
}

This fails because to create a prepared statement it is required to borrow the connection mutably, which implies that you can only create one prepared statement per connection at one point in time.
This disallows common patterns like a prepared statement cache.

Error message:

error[E0499]: cannot borrow `*conn` as mutable more than once at a time
  --> src/main.rs:16:13
   |
9  | fn check<'a>(
   |          -- lifetime `'a` defined here
...
15 |     let a = conn.prepare("SELECT name, id FROM users").unwrap();
   |             ---- first mutable borrow occurs here
16 |     let b = conn.prepare("SELECT name, id FROM posts").unwrap();
   |             ^^^^ second mutable borrow occurs here
17 | 
18 |     (a, b)
   |     ------ returning this value requires that `*conn` is borrowed for `'a`

Any reason for not mentioning MariaDB as one of the supported databases?

Some databases support queries that return multiple result sets. This could be modeled roughly like this pseudo code?

trait Statement {
  fn execute_query() -> impl Stream<ResultSet>;
}

trait ResultSet {
  fn get_meta_data() -> ResultSetMetaData;
  fn get_rows() -> impl Stream<Row>;
}

RDBC currently supports positional parameters with ? syntax but it would be good to have the option of using named parameters as well using :name syntax.

See the proposal in blog post [1] and some feedback in an HN thread [2].

[1] https://andygrove.io/2020/01/rust-database-connectivity-rdbc/
[2] https://news.ycombinator.com/item?id=22027134

We will need to run docker-compose to spin up Postgres and MySQL

IMHO borrow_mut() does not looks good in terms of rust.

let conn = connect_postgres()?;
let mut conn = conn.borrow_mut();
let stmt = conn.prepare("SELECT a FROM b WHERE c = ?")?;
let mut stmt = stmt.borrow_mut();

What if there will be another approach like:

• Having generic trait for implementing single connection or connection pool
• Connection (or pool) is immutable.
• It can produce sessions
• Session is used to execute one ore more queries (you can think of session as a transaction)
• When session goes out of scope it is closed or reused for next calls

let cnx = connect_postgres()?;
let mut session = cnx.start_session()?;
let stmt = conn.prepare("SELECT a FROM b WHERE c = ?")?;
// ...

We need the ability to retrieve DATE, TIME, TIME_WITH_TIMEZONE, TIMESTAMP, and TIMESTAMP_WITH_TIMEZONE data types from query result sets, as defined by ANSI SQL [1].

We should probably use the chrono crate? [2]

[1] https://crate.io/docs/sql-99/en/latest//chapters/02.html#sql-data-types
[2] https://docs.rs/chrono/0.4.10/chrono/

https://news.ycombinator.com/item?id=22027134

Add support for named parameters and positional parameters.

We need an enum describing all available/supported data types. It could be based on https://docs.oracle.com/javase/8/docs/api/java/sql/JDBCType.html

Can this be done using generics?

Allow the driver the ability to fetch the last generated key from the database.

In MySQL it would use SELECT LAST_INSERT_ID()