can't use array as query parameters

[antonio-antuan]

Hi!
I can't find a way to use an array in queries. Here is an example:

#[tokio::main]
async fn main() {
    db::RB
        .link("postgres://user:password@0.0.0.0:5432/db")
        .await
        .expect("can't initialize pool");
    let guids = vec!["1", "2", "3"];  // absolutely the same result with `let guids = ("1", "2", "3")`
    let exists: Vec<String> = db::RB
        .fetch_prepare(
            "",
            r#"
        SELECT guid 
        FROM records
        WHERE guid = ANY($1)
        AND source_id = $2"#,
            &vec![json!(&guids), json!(&1)],
        )
        .await
        .unwrap();
}

Logs:

[2020-11-04T11:45:51Z INFO  rbatis::plugin::log] [rbatis] [] Query ==> 
            SELECT guid 
            FROM records
            WHERE guid = ANY($1)
            AND source_id = $2
[2020-11-04T11:45:51Z INFO  rbatis::plugin::log] [rbatis] [] Args  ==> [["1","2","3"],1]
[2020-11-04T11:45:51Z INFO  sqlx::query] /* SQLx ping */; rows: 0, elapsed: 614.743µs
[2020-11-04T11:45:51Z INFO  sqlx::query] SELECT guid FROM records …; rows: 0, elapsed: 1.014ms
    
    SELECT
      guid
    FROM
      records
    WHERE
      guid = ANY($1)
      AND source_id = $2
    
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: E("error returned from database: op ANY/ALL (array) requires array on right side")', src/main.rs:48:10
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

[zhuxiujia]

Hi!
I can't find a way to use an array in queries. Here is an example:

#[tokio::main]
async fn main() {
    db::RB
        .link("postgres://user:password@0.0.0.0:5432/db")
        .await
        .expect("can't initialize pool");
    let guids = vec!["1", "2", "3"];  // absolutely the same result with `let guids = ("1", "2", "3")`
    let exists: Vec<String> = db::RB
        .fetch_prepare(
            "",
            r#"
        SELECT guid 
        FROM records
        WHERE guid = ANY($1)
        AND source_id = $2"#,
            &vec![json!(&guids), json!(&1)],
        )
        .await
        .unwrap();
}

Logs:

[2020-11-04T11:45:51Z INFO  rbatis::plugin::log] [rbatis] [] Query ==> 
            SELECT guid 
            FROM records
            WHERE guid = ANY($1)
            AND source_id = $2
[2020-11-04T11:45:51Z INFO  rbatis::plugin::log] [rbatis] [] Args  ==> [["1","2","3"],1]
[2020-11-04T11:45:51Z INFO  sqlx::query] /* SQLx ping */; rows: 0, elapsed: 614.743µs
[2020-11-04T11:45:51Z INFO  sqlx::query] SELECT guid FROM records …; rows: 0, elapsed: 1.014ms
    
    SELECT
      guid
    FROM
      records
    WHERE
      guid = ANY($1)
      AND source_id = $2
    
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: E("error returned from database: op ANY/ALL (array) requires array on right side")', src/main.rs:48:10
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

You should read doc website https://rbatis.github.io/rbatis.io/#/README_EN?id=wrapper

Here is how to use wrapper

[antonio-antuan]

I think that I can't use wrapper because result data type is Vec:

let wrapper = db::RB
        .new_wrapper()
        .r#in("guid", &guids)
        .eq("source_id", 1)
        .check()
        .unwrap();
    let exists: Vec<String> = db::RB.fetch_by_wrapper("", &wrapper).await.unwrap();
    println!("{:?}", exists);

error[E0277]: the trait bound `std::string::String: rbatis::crud::CRUDEnable` is not satisfied
  --> src/main.rs:41:41
   |
41 |     let exists: Option<String> = db::RB.fetch_by_wrapper("", &wrapper).await.unwrap();
   |                                         ^^^^^^^^^^^^^^^^ the trait `rbatis::crud::CRUDEnable` is not implemented for `std::string::String`
   |

[zhuxiujia]

I think that I can't use wrapper because result data type is Vec:

let wrapper = db::RB
        .new_wrapper()
        .r#in("guid", &guids)
        .eq("source_id", 1)
        .check()
        .unwrap();
    let exists: Vec<String> = db::RB.fetch_by_wrapper("", &wrapper).await.unwrap();
    println!("{:?}", exists);

error[E0277]: the trait bound `std::string::String: rbatis::crud::CRUDEnable` is not satisfied
  --> src/main.rs:41:41
   |
41 |     let exists: Option<String> = db::RB.fetch_by_wrapper("", &wrapper).await.unwrap();
   |                                         ^^^^^^^^^^^^^^^^ the trait `rbatis::crud::CRUDEnable` is not implemented for `std::string::String`
   |

try use macro，Use macros for cleaner and cleaner generation

    #[py_sql(rbatis, "select * from biz_activity where delete_flag = 0
                  AND id in
                  for item in ids:
                      #{item}")]
    fn py_select_vec(rbatis:&Rbatis,ids: &Vec<String>) -> Vec<String> {
    }


    #[async_std::test]
    pub async fn test_vec() {
        fast_log::init_log("requests.log", 1000, log::Level::Info, true);
        //use static ref
        RB.link("mysql://root:123456@localhost:3306/test").await.unwrap();
        let a = py_select_vec(&RB,&vec!["1".to_string(), "2".to_string()]).await.unwrap();
        println!("{:?}", a);
    }

2020-11-04T21:29:28.816277200+08:00 INFO rbatis::plugin::log - [rbatis] [] Query ==> select * from biz_activity where delete_flag = 0 AND id in ? ?
2020-11-04T21:29:28.816423800+08:00 INFO rbatis::plugin::log - [rbatis] [] Args  ==> ["1","2"]

and .... if you use fetch_prepare method
the arg's length must equal to input arg's length
However, we do not recommend using concatenated raw strings and arguments directly。
for example :

RB.fetch_prepare(
                "",
                r#"
        SELECT guid
        FROM records
        WHERE guid = ANY($1,$2,$3)
        AND source_id = $2"#,
                vec![json!(&guids[0]),json!(&guids[1]),json!(&guids[2]), json!(&1)]
            )

[zhuxiujia]

Finally, you can use fast_log to disable logs from SQLX。
for example:

use fast_log::fast_log::LogAppender;
use fast_log::plugin::file::FileAppender;
use fast_log::plugin::console::ConsoleAppender;
use fast_log::filter::ModuleFilter;

pub fn init_log() {
    let mut appenders: Vec<Box<dyn LogAppender>> = vec![
        Box::new(FileAppender::new("requests.log"))
    ];
    if true {
        appenders.push(Box::new(ConsoleAppender {}));
    }
    fast_log::init_custom_log(appenders, 1000, log::Level::Info, Box::new(
        //NoFilter{}//not filter

        //Module
        ModuleFilter {
            //contains
            contains: None,
            //exclude_contains
            exclude_contains: Some(vec![
                "sqlx".to_string()
            ]),
        }
    ));
}

[antonio-antuan]

So now I see that arrays as parameters not supported.

[zhuxiujia]

So now I see that arrays as parameters not supported.

array param not support in prepare method。

maybe we need add fetch_prepare_wrapper method。
for now you can use rbatis-macro fix this