dbcli 0.1.0

Convert SQL query results to JSON without struct mapping, supporting MySQL/PostgreSQL/SQLite/Odbc
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

//! ODBC implementation of `execute_raw_sql`.
//!
//! Executes one or more SQL statements against any ODBC-compatible database
//! (e.g., Microsoft Access `.mdb` / `.accdb` files) and returns a
//! [`Vec<SqlResult>`] — one entry per statement.
//!
//! Because `odbc-api` is a synchronous API, the entire operation is wrapped in
//! [`tokio::task::spawn_blocking`] so it can be safely called from an async context
//! without blocking the Tokio runtime.

use crate::SqlResult;
use odbc_api::{ConnectionOptions, Environment};

/// Execute one or more SQL statements against an ODBC data source.
///
/// Supports multiple statements separated by `;`.
/// Each statement that produces a result set (SELECT, etc.) is automatically
/// converted to JSON via [`crate::to_json::odbc::to_json`] and returned as a
/// [`SqlResult::Query`]. Statements that do not produce rows (INSERT, UPDATE,
/// DELETE, CREATE, DROP, etc.) return a [`SqlResult::Execute`] with
/// `rows_affected: 0` (ODBC does not expose affected-row counts easily).
///
/// The entire ODBC operation runs inside [`tokio::task::spawn_blocking`] to
/// avoid blocking the async runtime.
///
/// # Arguments
///
/// * `connection_string` - An ODBC connection string, e.g.:
///   `"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\path\to\file.mdb;"`
/// * `sql` - SQL string; may contain multiple statements separated by `;`
///
/// # Returns
///
/// A [`Vec<SqlResult>`] with one entry per statement result, in execution order.
///
/// # Example
///
/// ```rust,no_run
/// # async fn run() -> anyhow::Result<()> {
/// let results = dbcli::execute::odbc::execute_raw_sql(
///     r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\data\my.mdb;",
///     "SELECT * FROM Users;",
/// ).await?;
///
/// for result in &results {
///     println!("{}", serde_json::to_string_pretty(result)?);
/// }
/// # Ok(())
/// # }
/// ```
pub async fn execute_raw_sql(
    connection_string: &str,
    sql: &str,
) -> anyhow::Result<Vec<SqlResult>> {
    // Clone into owned values so they can be moved into the blocking closure.
    let connection_string = connection_string.to_owned();
    let sql = sql.to_owned();

    tokio::task::spawn_blocking(move || {
        let env = Environment::new()?;
        let conn =
            env.connect_with_connection_string(&connection_string, ConnectionOptions::default())?;

        let mut results: Vec<SqlResult> = Vec::new();

        // Split on ';' and skip blank statements
        for statement in sql.split(';') {
            let statement = statement.trim();
            if statement.is_empty() {
                continue;
            }

            match conn.execute(statement, (), None)? {
                Some(mut cursor) => {
                    // Statement returned a result set — convert to JSON
                    let (data, columns) = crate::to_json::odbc::to_json(&mut cursor)?;
                    results.push(SqlResult::Query { data, columns });
                }
                None => {
                    // DML / DDL — no result set.
                    // ODBC does not provide affected-row counts in this path easily,
                    // so we report 0.
                    results.push(SqlResult::Execute { rows_affected: 0 });
                }
            }
        }

        Ok(results)
    })
    .await?
}