1

I am pretty new to rust.

I need to source a external SQL file from within rust; here is what I tried, Please refer to the Comment and line break and the error I have mentioned:

async fn test_db(
    user_name: &str,
    password: &str,
    database: &str,
    authorization_script_path: &str,
    ) -> std::result::Result<(), Box<dyn std::error::Error>> {
       let connection_string = format!("mysql://{}:{}@localhost:3306", user_name, 
       password);
let connection_url: &str = &connection_string;
let pool = Pool::new(connection_url)?;
let mut connection = pool.get_conn()?;

let create_db_query = format!("CREATE DATABASE IF NOT EXISTS {}", database);
let result_create = connection.query_drop(create_db_query);
println!("{:?}", result_create.unwrap());

let use_db_query = format!("USE {}", database);
let _result_query = connection.query_drop(use_db_query);
-------------------------------------------------------------------------------------------
// here below I am trying to source a file using the `source` command 
let authoriztion_source_script_query = format!("source {}", &authorization_script_path);
let _result_authorization_script = connection.query_drop(authoriztion_source_script_query);
-------------------------------------------------------------------------------------------    

   return Ok(());
}

I use the follwing the following command to run my program cargo run -- -u root -p super_secret_password -d customera_authorization -A "C:\Users\<some_user_name>\Downloads\<Some_folder>\authorization.sql"

The -u flag is to pass a username for the mysql server. The -p flag is to pass a password for the mysql server. The -d flag is to pass a name of a database to be created The -A flag is to pass a path to the external sql file to be sourced.

when I check for the new database, I found that the DB has been created with the proper name but the SQL file is not sourced

The error I am getting is as follows: MySqlError { ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source C:\Users\<some_user_name>\Downloads\<Some_folder>\authorization.sql' at line 1 } Success

Note: The External SQL file has CREATE TABLE .... commands

I just want to source the external SQL file in one go.

Please guide me if possible for fixing this issue

Any help would be appreciated!!!

Thank you!

OddlyGhost
  • 128
  • 2
  • 14
  • 1
    which DB library are you using? Are you sure you *don't* have a syntax error in your sql file? – Holloway Nov 28 '22 at 11:11
  • I am using `mysql::prelude::*` and `mysql::*`, I am sure I do not have any syntax error in the SQL file as when I try to source it directly from command line it sources without any issues and I am able to see the tables in the DB – OddlyGhost Nov 28 '22 at 11:17
  • 1
    `source` is not SQL, it's an internal command from the [official mysql command-line client](https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html). – Álvaro González Nov 28 '22 at 11:30
  • @ÁlvaroGonzález Okay Understood!!, any Idea how can I call this command from rust or any other way to add multiple SQL statements from a file ? keeping in mind that I will be using this script into a Azure VM inside a MySQL subnet – OddlyGhost Nov 28 '22 at 11:38
  • 1
    I can only think of having a pure SQL file (either by preprocessing the file to rewrite it or by changing the code that generates it) or loading the file though the command-line utility (I can't speak Rust, but it'll have shell execution functions for sure). – Álvaro González Nov 28 '22 at 11:44
  • @ÁlvaroGonzález could you please provide me with the shell script ? I would try to do a work around – OddlyGhost Nov 28 '22 at 12:03
  • [How do I invoke a system command and capture its output?](https://stackoverflow.com/questions/21011330/how-do-i-invoke-a-system-command-and-capture-its-output) – Álvaro González Nov 28 '22 at 12:08

2 Answers2

1

Something like this exists and it's called include_sql - it's a macro that's not used directly but powers other crates into providing seamless integration with queries isolated into separate SQL files. For example there's include-postgres-sql, and ones for Oracle and SQLite are available too. There isn't one yet available for MySQL as of this writing.

So if the choice of the database isn't flexible, which I assume it isn't, your best bet appears to be parsing a .sql file with the standard tools.

Also there's rawsql that could help, especially if you have a bunch of queries in the same .sql file.

zarnoevic
  • 329
  • 3
  • 12
  • I have used a little different approach to fix this problem, that is by calling msql commands shell / cmd commands from within rust (the pre-requisit being installing the mysqsl client server edition), it does the job! Thank you for your insghts tho :) was helpful! – OddlyGhost Jan 09 '23 at 05:47
1

I have found another solution. sqlx for rust has a query_file! macro that directly sources a query from a file. sqlx supports PostgreSQL, MySQL, SQLite, and MSSQL.

Hope this helps :)

zarnoevic
  • 329
  • 3
  • 12