How to query the executed sql statement

[tgy3300]

use serde_json::{json as sjson, Value};
use std::fmt::Debug;
use serde::Deserialize;
use mysql::{
    prelude::{FromRow, Queryable},
    PooledConn,
};

fn get_info<T: FromRow + Debug>(
    conn: &mut PooledConn,
    sql: String,
    sql_data: Vec<&Value>,
) -> Result<Option<T>, Box<dyn std::error::Error + Send + Sync + 'static>> {
    let s = conn.exec_first(sql, sql_data)?;
    Ok(s)
}

#[derive(FromRow, Deserialize, Default, Debug)]
struct Company {
    id: i64,
    name: String,
    status: i16,
    err_info: Option<String>,
    account_id: i64,
    create_at: i64,
    update_at: i64,
}

fn main() {
    let s = sjson!({ "name": { "$like": "洋200科" } });
    let (sql_where, sql_data) = get_where_sql(&s, None);
    let sql = format!("SELECT * FROM `company` WHERE {}", sql_where);
    
    let mut client = get_client();
    let res: Company = get_info(&mut client, sql, sql_data).unwrap().unwrap();
    println!("-------{:#?}----", res);
}

Question: How do I query the sql statements executed by exec_first

[blackbeam]

Hi.

Sorry but I couldn't parse the meaning of the question. What does it mean for an "executed SQL statement" to be "queried"?

If you need query log consider looking into MySql documentation — this is from the top of google results.

[tgy3300]

The interaction with the mysql database is carried out through sql statements

let s = conn.exec_first(sql, sql_data)?;

The parameters passed to the exec_first method here are sql and sql_data. Now I want to know what the complete sql statement generated by these two parameters is, so as to facilitate the viewing and debugging of errors

[blackbeam]

Oh, I see.

Prepared statements does not work In a way that "complete SQL statement" is constructed from sql and sql_data.

• On the client side you can just print both sql and sql_data - they are transferred to the server as is.
• On the server side - the only thing I know is that SHOW FULL PROCESSLIST will show you the statement with all the placeholders filled in, but I don't believe it's practically useful in your case. Consider digging into MySQL logging docs

[tgy3300]

let mut val: Vec<String> = Vec::new();
val.push("200key".to_string());
        
let s = conn.exec_first("SELECT * FROM `company` WHERE `name` LIKE '%?%'", val)?;

A combination like the above will report an error: ``Err value: DriverError { Statement takes 0 parameters but 1 was supplied }

So I want to see what the sql statement exec_first is like

[blackbeam]

"Prepared statements" is not a trivial template language where all the ? replaced by whatever you gave in the params. Please consult the relevant section of MySql documentation.

Things you should note:

1. Not all SQL statements can be prepared (DDL for example).
2. It is only possible to use the ? placeholder where a single value is expected.
3. The value of the ? parameter is whatever could be stored in a single mysql column.

This version should work:

conn.exec_first("SELECT * FROM `company` WHERE `name` LIKE ?", ("%200key%",))?;

[evbo]

@blackbeam I know it's not this repo's job to document mysql, but your above example is so helpful and I too was confused about how to mix prepared statements with LIKE.

For what it's worth, consider adding your example to the readme on prepared statements, as the current example is a bit trivial and it almost never hurts listing more than just 1 example ;)