How to call a MySQL stored proc using crate sqlx?

behai · September 8, 2023, 4:35am

Hi,

Please help me with a following question: how to call a MySQL stored procedure which returns a database using crate sqlx.

I have done a lot of searches, and no avail.

First, please let me present what I have done successfully (?) so far: I've been able to run a normal query and process the resultant data.

The database I used is Oracle Corporation MySQL test data.

And the table is the employees table, whom structure is:

CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_unicode_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And following is my test project:

Cargo.toml's dependencies:

...
[dependencies]
async-std = "1.12.0"
sqlx = { version = "0.7", default-features = false, features = ["runtime-async-std", "macros", "mysql", "time"]}
time = {version = "0.3.22", default-features = false, features = ["formatting", "macros"]}

src/main.rs:

use sqlx::{FromRow, Pool, MySql, Error, MySqlPool};
use sqlx::types::time::Date;
use time::macros::format_description;

use async_std::task;

#[derive(FromRow)]
struct Employee {
    emp_no: i32,
    birth_date: Date,
    first_name: String,
    last_name: String,    
    gender: String,
    hire_date: Date,
}

async fn connect() -> Result<Pool<MySql>, Error> {
    return MySqlPool::connect("mysql://root:pcb.2176310315865259@localhost:3306/employees").await;
}

async fn do_run_query() {
    let result = task::block_on(connect());

    match result {
        Err(err) => {
            println!("Cannot connect to database [{}]", err.to_string());
        }        

        Ok(pool) => {
            let query_result = sqlx::query_as::<_, Employee>("select * from employees where emp_no <= 10010")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, employee) in query_result.iter().enumerate() {
                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    &employee.emp_no,
                    &employee.birth_date.format(&format).unwrap(),
                    &employee.first_name,
                    &employee.last_name,
                    &employee.gender,
                    &employee.hire_date.format(&format).unwrap());
            }
        }
    }
}

fn main() {
    task::block_on(do_run_query());
}

The above code works. It does what I expected do to.

The stored procedure I have is extremely simple:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `get_employees`( 
pmLastName varchar(16), pmFirstName varchar(14) )
    READS SQL DATA
begin
  select * from employees e where (upper(e.last_name) like upper(pmLastName))
    and (upper(e.first_name) like upper(pmFirstName)) order by e.emp_no;
end$$
DELIMITER ;

Within MySQL Workbench, it can be called with:

call get_employees('%chi', '%ak');

Which results in 3 (three) records. (I have no problem of calling this stored procedure in Python or Delphi with parameters either.)

I could not find any example or documentation on how to call it. So willy-nill, instead of a query, as seen above, I pass in the stored procedure call:

            ...
            let query_result = sqlx::query_as::<_, Employee>("call get_employees('%chi', '%ak')")
                //.bind("%chi").bind("%ak")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());
            ...

The backtrace makes sense for me. But I just don't how to fix the code to correct call a stored procdure:

F:\rust\sqlx>set RUST_BACKTRACE=1

F:\rust\sqlx>target\debug\learn_sqlx.exe
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: ColumnNotFound("emp_no")', src\main.rs:32:41
stack backtrace:
   0: std::panicking::begin_panic_handler
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca/library\std\src\panicking.rs:578
   1: core::panicking::panic_fmt
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca/library\core\src\panicking.rs:67
   2: core::result::unwrap_failed
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca/library\core\src\result.rs:1687
   3: enum2$<core::result::Result<alloc::vec::Vec<learn_sqlx::Employee,alloc::alloc::Global>,enum2$<sqlx_core::error::Error> > >::unwrap<alloc::vec::Vec<learn_sqlx::Employee,alloc::alloc::Global>,enum2$<sqlx_core::error::Error> >
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\core\src\result.rs:1089
   4: learn_sqlx::do_run_query::async_fn$0
             at .\src\main.rs:30
   5: async_std::task::builder::impl$1::poll::closure$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\builder.rs:199
   6: async_std::task::task_locals_wrapper::impl$0::set_current::closure$0<async_std::task::builder::impl$1::poll::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,enum2$<core::task::poll::Poll<tuple$<> > > >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\task_locals_wrapper.rs:60
   7: std::thread::local::LocalKey<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> > >::try_with<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> >,async_std::task::task_locals_wrapper::im
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:252
   8: std::thread::local::LocalKey<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> > >::with<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> >,async_std::task::task_locals_wrapper::impl$0
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:228
   9: async_std::task::task_locals_wrapper::TaskLocalsWrapper::set_current<async_std::task::builder::impl$1::poll::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,enum2$<core::task::poll::Poll<tuple$<> > > >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\task_locals_wrapper.rs:55
  10: async_std::task::builder::impl$1::poll<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\builder.rs:197
  11: futures_lite::future::impl$12::poll<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,enum2$<async_executor::impl$5::run::async_fn$0::async_block_env$0<tuple$<>,async_std::task::builder::SupportTaskLoc
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\futures-lite-1.13.0\src\future.rs:526
  12: async_executor::impl$5::run::async_fn$0<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> > >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-executor-1.5.1\src\lib.rs:243
  13: async_executor::impl$11::run::async_fn$0<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> > >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-executor-1.5.1\src\lib.rs:447
  14: async_io::driver::block_on<tuple$<>,enum2$<async_executor::impl$11::run::async_fn_env$0<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> > > > >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-io-1.13.0\src\driver.rs:146
  15: async_global_executor::reactor::block_on::closure$0<enum2$<async_executor::impl$11::run::async_fn_env$0<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> > > >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-global-executor-2.3.1\src\reactor.rs:3
  16: async_global_executor::reactor::block_on<enum2$<async_executor::impl$11::run::async_fn_env$0<tuple$<>,async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> > > >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-global-executor-2.3.1\src\reactor.rs:12
  17: async_global_executor::executor::block_on::closure$0<async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-global-executor-2.3.1\src\executor.rs:26
  18: std::thread::local::LocalKey<async_executor::LocalExecutor>::try_with<async_executor::LocalExecutor,async_global_executor::executor::block_on::closure_env$0<async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,tup
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:252
  19: std::thread::local::LocalKey<async_executor::LocalExecutor>::with<async_executor::LocalExecutor,async_global_executor::executor::block_on::closure_env$0<async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,tuple$<
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:228
  20: async_global_executor::executor::block_on<async_std::task::builder::SupportTaskLocals<enum2$<learn_sqlx::do_run_query::async_fn_env$0> >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-global-executor-2.3.1\src\executor.rs:26
  21: async_std::task::builder::impl$0::blocking::closure$0::closure$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\builder.rs:171
  22: async_std::task::task_locals_wrapper::impl$0::set_current::closure$0<async_std::task::builder::impl$0::blocking::closure$0::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\task_locals_wrapper.rs:60
  23: std::thread::local::LocalKey<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> > >::try_with<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> >,async_std::task::task_locals_wrapper::im
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:252
  24: std::thread::local::LocalKey<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> > >::with<core::cell::Cell<ptr_const$<async_std::task::task_locals_wrapper::TaskLocalsWrapper> >,async_std::task::task_locals_wrapper::impl$0
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:228
  25: async_std::task::task_locals_wrapper::TaskLocalsWrapper::set_current<async_std::task::builder::impl$0::blocking::closure$0::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\task_locals_wrapper.rs:55
  26: async_std::task::builder::impl$0::blocking::closure$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\builder.rs:168
  27: std::thread::local::LocalKey<core::cell::Cell<usize> >::try_with<core::cell::Cell<usize>,async_std::task::builder::impl$0::blocking::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >,tuple$<> >
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:252
  28: std::thread::local::LocalKey<core::cell::Cell<usize> >::with<core::cell::Cell<usize>,async_std::task::builder::impl$0::blocking::closure_env$0<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >,tuple$<> >
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\std\src\thread\local.rs:228
  29: async_std::task::builder::Builder::blocking<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\builder.rs:161
  30: async_std::task::block_on::block_on<enum2$<learn_sqlx::do_run_query::async_fn_env$0>,tuple$<> >
             at C:\Users\behai\.cargo\registry\src\index.crates.io-6f17d22bba15001f\async-std-1.12.0\src\task\block_on.rs:33
  31: learn_sqlx::main
             at .\src\main.rs:53
  32: core::ops::function::FnOnce::call_once<void (*)(),tuple$<> >
             at /rustc/90c541806f23a127002de5b4038be731ba1458ca\library\core\src\ops\function.rs:250
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

F:\rust\sqlx>

Thank you and best regards,

...behai.

drmason13 · September 8, 2023, 5:33am

This is an old bug in sqlx by the looks of it:

github.com/launchbadge/sqlx

[MySQL] Can't get by column name when calling stored procedure

opened 09:59AM - 10 Mar 22 UTC

mickaelmagniez

Hi When calling a stored procedure, i can't retrieve column by name : ```… sqlx::query("call fetch()").map(|row: MySqlRow| { row.get("id") }).fetch_all(&pool) ``` is not working, and with debugger i can see `row.column_names` is empty but get by index works ``` sqlx::query("call fetch()").map(|row: MySqlRow| { row.get(0) }).fetch_all(&pool) ``` Stored procedure looks like ``` DELIMITER $$ drop procedure if exists fetch_n; CREATE PROCEDURE fetch_n() BEGIN select id from records; END $$ ``` Best regards

It looks like using column indexes [0], [1] instead of column names is the workaround.

I think means you'd have to map to an Employee struct manually.

behai · September 9, 2023, 3:28am

Good afternoon drmason13,

Thank you for your reply and the link. I think the column names are the problem. This is what I have managed to come up with so far:

pub async fn do_run_stored_proc() {
    let result: Result<sqlx::Pool<sqlx::MySql>, sqlx::Error> = task::block_on(connect());

    match result {
        Err(err) => {
            println!("Cannot connect to database [{}]", err.to_string());
        }        

        Ok(pool) => {
            let query_result = sqlx::query("call get_employees(?, ?)")
                .bind("%chi").bind("%ak")
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, row) in query_result.iter().enumerate() {

                let emp_no: i32 = row.get(0);
                let birth_date: Date = row.get(1);
                let first_name: String = row.get(2);
                let last_name: String = row.get(3);
                let gender: String = row.get(4);
                let hire_date: Date = row.get(5);

                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    emp_no,
                    birth_date.format(&format).unwrap(),
                    first_name,
                    last_name,
                    gender,
                    hire_date.format(&format).unwrap());
            }
        }
    }
}

I am still trying to figure out how to map to the Employee struct manually... I am still a newbie to Rust. The documentation is a bit hard to follow.

Thank you and best regards,

...behai.

behai · September 9, 2023, 3:57am

Hi,

I think I have figured out how to manually map to Employee struct:

pub async fn do_run_stored_proc() {
    let result: Result<sqlx::Pool<sqlx::MySql>, sqlx::Error> = task::block_on(connect());

    match result {
        Err(err) => {
            println!("Cannot connect to database [{}]", err.to_string());
        }        

        Ok(pool) => {
            let query_result = sqlx::query("call get_employees(?, ?)")
                .bind("%chi").bind("%ak")
                .map(|row: sqlx::mysql::MySqlRow| { 
                    Employee {
                        emp_no: row.get(0),
                        birth_date: row.get(1),
                        first_name: row.get(2),
                        last_name: row.get(3),
                        gender: row.get(4),
                        hire_date: row.get(5)
                    }
                })
                .fetch_all(&pool).await.unwrap();

            println!("Number of Employees selected: {}", query_result.len());

            let format = format_description!("[day]/[month]/[year]");

            for (rindex, employee) in query_result.iter().enumerate() {
                println!("{}. No.: {}, Birth Date: {}, First Name: {}, Last Name: {}, Gender: {}, Hire Date: {}", 
                    rindex+1,
                    &employee.emp_no,
                    &employee.birth_date.format(&format).unwrap(),
                    &employee.first_name,
                    &employee.last_name,
                    &employee.gender,
                    &employee.hire_date.format(&format).unwrap());
            }
        }
    }
}

Thank you and best regards,

...behai.

drmason13 · September 9, 2023, 7:43am

Nice one, that's what I had in mind.

By the way, using like (%) in mySQL will do a case insensitive match, so you don't need the Upper() song and dance in that stored procedure

https://dev.mysql.com/doc/refman/8.1/en/case-sensitivity.html

Which was way too low of an answer on this SO question!

You're welcome! Happy to help.

system · December 8, 2023, 7:43am

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.

Topic		Replies	Views
Issue with create method with sqlx-crud crate help	1	136	March 15, 2024
Help Needed with `sqlx::query!` Macro and `DATABASE_URL` in a Rust Discord Bot help	5	1936	March 13, 2024
Mysql crate row mapping help	5	2364	December 31, 2020
How should one use `sqlx` with `Newtype` pattern? help	2	2972	June 17, 2022
Crate mysql - access a database help	3	829	June 5, 2022

How to call a MySQL stored proc using crate sqlx?

Related Topics