Rust 连接和操作 达梦数据库

[dependencies]
odbc-api = { version = "8.1.4", features = ["derive"] }
env_logger = "0.11"
anyhow = "1.0.100"
encoding_rs = "0.8"

main.rs

#![allow(warnings)]
use anyhow::{Context, Result};
use odbc_api::{ConnectionOptions, Environment, Cursor};
use encoding_rs::GBK;

/// DaMeng Database Demo
/// Demonstrates comprehensive database operations including:
/// - Connection management
/// - Table creation and schema operations
/// - CRUD operations (Create, Read, Update, Delete)
/// - Transaction handling
/// - Various data types
/// - Error handling

fn main() -> Result<()> {
    // Initialize logging
    env_logger::init();

    println!("=== DaMeng Database Comprehensive Demo ===\n");

    // Create ODBC environment
    let env = Environment::new()
        .context("Failed to create ODBC environment")?;

    // Display available data sources and drivers
    display_odbc_info(&env)?;

    // DaMeng database connection string
    let connection_string = "
        Driver={DM8 ODBC DRIVER};
        Server=localhost;
        Port=5236;
        UID=SYSDBA;
        PWD=Z@xc;
        Schema=DMHR;
    ";

    // Schema 就算 数据库 名称

    // Establish connection
    let conn = env
        .connect_with_connection_string(connection_string, ConnectionOptions::default())
        .context("Failed to connect to DaMeng database")?;

    println!("✓ Successfully connected to DaMeng database");

    // Get database information
    display_database_info(&conn)?;

    // Run comprehensive demo
    run_demo(&conn)?;

    println!("\n=== Demo completed successfully ===");

    Ok(())
}

/// Display ODBC data sources and drivers
fn display_odbc_info(env: &Environment) -> Result<()> {
    println!("--- Available Data Sources ---");
    match env.data_sources() {
        Ok(sources) => {
            for data_source in sources {
                println!("{:#?}", data_source);
            }
        }
        Err(e) => println!("Failed to retrieve data sources: {:?}", e),
    }

    println!("\n--- Available Drivers ---");
    match env.drivers() {
        Ok(drivers) => {
            for driver_info in drivers {
                println!("{:#?}", driver_info);
            }
        }
        Err(e) => println!("Failed to retrieve drivers: {:?}", e),
    }

    println!();
    Ok(())
}

/// Display database management system information
fn display_database_info(conn: &odbc_api::Connection) -> Result<()> {
    println!("\n--- Database Information ---");
    
    let dbms_name = conn
        .database_management_system_name()
        .context("Failed to get DBMS name")?;
    println!("Database Management System Name: {}", dbms_name);

    let db_name = conn
        .current_catalog()
        .context("Failed to get database name")?;
    println!("Current Database: {}", db_name);

    Ok(())
}

/// Run comprehensive database demo
fn run_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Demo 1: Table operations
    println!("\n=== Demo 1: Table Operations ===");
    table_operations_demo(conn)?;

    // Demo 2: Insert operations with different data types
    println!("\n=== Demo 2: Insert Operations ===");
    insert_demo(conn)?;

    // Demo 3: Select operations
    println!("\n=== Demo 3: Select Operations ===");
    select_demo(conn)?;

    println!("\n=== Demo 3: view_demo Operations ===");
    view_demo(conn)?;

    println!("\n=== Demo 3: function_demo Operations ===");
    function_demo(conn)?;


    println!("\n=== Demo 3: procedure_demo Operations ===");
    procedure_demo(conn)?;

    // Demo 4: Update operations
    println!("\n=== Demo 4: Update Operations ===");
    update_demo(conn)?;

    // Demo 5: Delete operations
    println!("\n=== Demo 5: Delete Operations ===");
    delete_demo(conn)?;

    // Demo 6: Transaction demo
    println!("\n=== Demo 6: Transaction Demo ===");
    transaction_demo(conn)?;

    // Demo 7: Batch operations
    println!("\n=== Demo 7: Batch Operations ===");
    batch_operations_demo(conn)?;

    // Cleanup: Drop test table
    // println!("\n=== Cleanup ===");
    // drop_table(conn, "EMPLOYEES")?;

    Ok(())
}

/// Demo: Create and drop tables
fn table_operations_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Drop table if exists
    let _ = drop_table(conn, "EMPLOYEES");

    // Create table with various data types
    let create_sql = r#"
        CREATE TABLE EMPLOYEES (
            ID INT PRIMARY KEY,
            NAME VARCHAR(50) NOT NULL,
            AGE INT,
            SALARY DECIMAL(10, 2),
            DEPARTMENT VARCHAR(50),
            HIRE_DATE DATE,
            IS_ACTIVE BIT,
            SCORE FLOAT,
            DESCRIPTION TEXT
        )
    "#;

    conn.execute(&create_sql, ())?;
    println!("✓ Created EMPLOYEES table");

    Ok(())
}

/// Demo: Insert records with different data types
fn insert_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Insert single record using direct SQL
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (1, '张三', 30, 8500.50, '技术部', '2023-01-15', 1, 95.5, '资深软件工程师，负责后端开发')",
        ())?;
    println!("✓ Inserted single record: ID=1, Name=张三");

    // Insert multiple records
    let employees = vec![
        (2i32, "李四", 28, 7500.00, "技术部", "2023-03-20", 1i16, 88.0, "初级开发工程师"),
        (3, "王五", 35, 12000.00, "管理部", "2022-06-10", 1i16, 92.5, "项目经理"),
        (4, "赵六", 25, 6000.00, "市场部", "2023-08-01", 0i16, 85.0, "市场专员"),
        (5, "钱七", 32, 9500.00, "技术部", "2022-11-15", 1i16, 90.0, "高级开发工程师"),
    ];

    for emp in &employees {
        let sql = format!(
            "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
             VALUES ({}, '{}', {}, {}, '{}', '{}', {}, {}, '{}')",
            emp.0, emp.1, emp.2, emp.3, emp.4, emp.5, emp.6, emp.7, emp.8
        );
        conn.execute(&sql, ())?;
    }
    println!("✓ Inserted {} additional records", employees.len());

    // Verify total count
    let count_sql = "SELECT COUNT(*) FROM EMPLOYEES";
    if let Some(cursor) = conn.execute(count_sql, ())? {
        let mut buffer = odbc_api::buffers::RowVec::<(i32,)>::new(1);
        let mut block_cursor = cursor.bind_buffer(&mut buffer)?;
        if let Some(batch) = block_cursor.fetch()? {
            for row in batch.iter() {
                println!("✓ Total records in EMPLOYEES: {}", row.0);
            }
        }
    }

    Ok(())
}

/// Demo: Select and query operations
fn select_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Select all records
    println!("\n--- All Employees ---");
    let select_all_sql = "SELECT ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE FROM EMPLOYEES ORDER BY ID";
    
    if let Some(cursor) = conn.execute(select_all_sql, ())? {
        display_query_results(cursor)?;
    }

    // Select with WHERE clause
    println!("\n--- Active Employees in Technical Department ---");
    let filter_sql = r#"
        SELECT ID, NAME, SALARY, DEPARTMENT 
        FROM EMPLOYEES 
        WHERE DEPARTMENT = '技术部' AND IS_ACTIVE = 1
        ORDER BY SALARY DESC
    "#;
    
    if let Some(cursor) = conn.execute(filter_sql, ())? {
        display_query_results(cursor)?;
    }

    // Select with aggregate functions
    println!("\n--- Department Statistics ---");
    let stats_sql = r#"
        SELECT 
            DEPARTMENT,
            COUNT(*) as EMPLOYEE_COUNT,
            AVG(SALARY) as AVG_SALARY,
            MAX(SALARY) as MAX_SALARY,
            MIN(SALARY) as MIN_SALARY
        FROM EMPLOYEES
        GROUP BY DEPARTMENT
        ORDER BY AVG_SALARY DESC
    "#;
    
    if let Some(cursor) = conn.execute(stats_sql, ())? {
        display_query_results(cursor)?;
    }

    // Select with LIMIT
    println!("\n--- Top 3 Highest Paid Employees ---");
    let top_sql = r#"
        SELECT ID, NAME, SALARY, DEPARTMENT
        FROM EMPLOYEES
        ORDER BY SALARY DESC
        FETCH FIRST 3 ROWS ONLY
    "#;
    
    if let Some(cursor) = conn.execute(top_sql, ())? {
        display_query_results(cursor)?;
    }

    Ok(())
}


/// view_demo
fn view_demo(conn: &odbc_api::Connection) -> Result<()> {
    let top_sql = r#"
    SELECT * FROM "V_EMPLOYEE"
"#;

if let Some(cursor) = conn.execute(top_sql, ())? {
    display_query_results(cursor)?;
}
    Ok(())
}

/// view_demo
fn function_demo(conn: &odbc_api::Connection) -> Result<()> {
    let top_sql = r#"
select identity_card ,GET_SEX(identity_card) from DMHR.EMPLOYEE;
    "#;

if let Some(cursor) = conn.execute(top_sql, ())? {
    display_query_results(cursor)?;
}
    Ok(())
}

/// procedure_demo
/*
--创建测试表 test_tab
create table test_tab (id int primary key, name varchar(30));
--创建有参数存储过程 p_test
create or replace procedure p_test(i in int)
as j int;
begin
for j in 1 ..i loop
insert into test_tab values(j,'p_test'||j);
end loop;
end;
*/
/// 
/// 
fn procedure_demo(conn: &odbc_api::Connection) -> Result<()> {
    let top_sql = r#"
    p_test(3);
    "#;

if let Some(cursor) = conn.execute(top_sql, ())? {
    display_query_results(cursor)?;
}
    Ok(())
}





/// Demo: Update operations
fn update_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Update single record
    let update_sql = "UPDATE EMPLOYEES SET SALARY = SALARY * 1.1 WHERE ID = 1";
    conn.execute(update_sql, ())?;
    println!("✓ Updated salary for employee ID=1 (10% increase)");

    // Verify update
    let verify_sql = "SELECT ID, NAME, SALARY FROM EMPLOYEES WHERE ID = 1";
    if let Some(cursor) = conn.execute(verify_sql, ())? {
        println!("Updated record:");
        display_query_results(cursor)?;
    }

    // Bulk update
    let bulk_update_sql = "UPDATE EMPLOYEES SET IS_ACTIVE = 0 WHERE DEPARTMENT = '市场部'";
    if let Some(_cursor) = conn.execute(bulk_update_sql, ())? {
        println!("✓ Updated records (set market department employees to inactive)");
    }

    Ok(())
}



/// Demo: Delete operations
fn delete_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Count before delete
    let count_before = count_employees(conn)?;
    println!("Records before delete: {}", count_before);

    // Delete specific record
    let delete_sql = "DELETE FROM EMPLOYEES WHERE ID = 4";
    conn.execute(delete_sql, ())?;
    println!("✓ Deleted employee ID=4");

    // Verify delete
    let count_after = count_employees(conn)?;
    println!("Records after delete: {}", count_after);

    Ok(())
}

/// Demo: Transaction handling
fn transaction_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Begin transaction using set_autocommit
    conn.set_autocommit(false)?;
    println!("✓ Transaction started (autocommit disabled)");

    // Insert records within transaction
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (10, '临时员工1', 22, 4000.00, '临时部', '2024-01-01', 1, 75.0, '临时工')",
        ())?;
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (11, '临时员工2', 23, 4200.00, '临时部', '2024-01-02', 1, 78.0, '临时工')",
        ())?;
    println!("✓ Inserted 2 records in transaction");

    // Verify records exist
    let count_sql = "SELECT COUNT(*) FROM EMPLOYEES WHERE ID >= 10";
    if let Some(cursor) = conn.execute(count_sql, ())? {
        let mut buffer = odbc_api::buffers::RowVec::<(i32,)>::new(1);
        let mut block_cursor = cursor.bind_buffer(&mut buffer)?;
        if let Some(batch) = block_cursor.fetch()? {
            for row in batch.iter() {
                println!("Records in transaction: {}", row.0);
            }
        }
    }

    // Rollback transaction
    conn.rollback()?;
    println!("✓ Transaction rolled back");

    // Re-enable autocommit
    conn.set_autocommit(true)?;

    // Verify records are gone
    let count_after_rollback = count_employees(conn)?;
    println!("Records after rollback: {}", count_after_rollback);

    Ok(())
}

/// Demo: Batch operations
fn batch_operations_demo(conn: &odbc_api::Connection) -> Result<()> {
    // Batch insert
    println!("✓ Starting batch insert");
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (20, '批量员工1', 26, 5000.00, '测试部', '2024-01-10', 1, 80.0, '批量插入测试')",
        ())?;
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (21, '批量员工2', 27, 5200.00, '测试部', '2024-01-11', 1, 82.0, '批量插入测试')",
        ())?;
    conn.execute(
        "INSERT INTO EMPLOYEES (ID, NAME, AGE, SALARY, DEPARTMENT, HIRE_DATE, IS_ACTIVE, SCORE, DESCRIPTION) \
         VALUES (22, '批量员工3', 28, 5400.00, '测试部', '2024-01-12', 1, 84.0, '批量插入测试')",
        ())?;
    println!("✓ Batch inserted 3 records");

    // Display batch results
    let select_sql = "SELECT ID, NAME, SALARY, DEPARTMENT FROM EMPLOYEES WHERE ID >= 20 ORDER BY ID";
    if let Some(cursor) = conn.execute(select_sql, ())? {
        display_query_results(cursor)?;
    }

    // Cleanup batch records
    let delete_sql = "DELETE FROM EMPLOYEES WHERE ID >= 20";
    if let Some(_cursor) = conn.execute(delete_sql, ())? {
        println!("✓ Cleaned up 3 batch records");
    }

    Ok(())
}

/// Helper: Count employees
fn count_employees(conn: &odbc_api::Connection) -> Result<i32> {
    let count_sql = "SELECT COUNT(*) FROM EMPLOYEES";
    if let Some(cursor) = conn.execute(count_sql, ())? {
        let mut buffer = odbc_api::buffers::RowVec::<(i32,)>::new(1);
        let mut block_cursor = cursor.bind_buffer(&mut buffer)?;
        if let Some(batch) = block_cursor.fetch()? {
            for row in batch.iter() {
                return Ok(row.0);
            }
        }
    }
    Ok(0)
}

/// Helper: Drop table
fn drop_table(conn: &odbc_api::Connection, table_name: &str) -> Result<()> {
    let drop_sql = format!("DROP TABLE IF EXISTS {}", table_name);
    match conn.execute(&drop_sql, ()) {
        Ok(_) => {
            println!("✓ Dropped table {}", table_name);
            Ok(())
        }
        Err(e) => {
            println!("Note: Table {} may not exist or could not be dropped: {:?}", table_name, e);
            Ok(())
        }
    }
}

/// Helper: Display query results using text buffer
fn display_query_results(mut cursor: impl Cursor) -> Result<()> {
    use odbc_api::buffers::TextRowSet;
    use encoding_rs::{GBK, UTF_8};
    
    let num_cols = cursor.num_result_cols()?;
    
    // 达梦数据库通常使用 GBK 编码
    let max_str_len = 1024; // 为中文预留更多空间
    let batch_size = 250;
    let mut buffer = TextRowSet::for_cursor(batch_size, &mut cursor, Some(max_str_len))?;
    
    // Bind buffer and fetch
    if let Ok(mut block_cursor) = cursor.bind_buffer(&mut buffer) {
        let mut row_count = 0;
        while let Some(batch) = block_cursor.fetch()? {
            for row_index in 0..batch.num_rows() {
                let mut values = Vec::new();
                for col_index in 0..num_cols {
                    let text = batch.at(col_index as usize, row_index as usize);
                    if let Some(text_bytes) = text {
                        // 找到实际的字符串长度（去除空字节）
                        let actual_len = text_bytes.iter()
                            .position(|&b| b == 0)
                            .unwrap_or(text_bytes.len());
                        let trimmed = &text_bytes[..actual_len];
                        
                        // 尝试 GBK 编码（达梦数据库常用）
                        let (decoded, _, had_errors) = GBK.decode(trimmed);
                        if had_errors {
                            // 如果 GBK 解码失败，尝试 UTF-8
                            if let Ok(utf8_str) = std::str::from_utf8(trimmed) {
                                values.push(utf8_str.to_string());
                            } else {
                                // 都失败时使用转义格式
                                values.push(format!("[BINARY: {:?}]", trimmed));
                            }
                        } else {
                            values.push(decoded.to_string());
                        }
                    } else {
                        values.push("NULL".to_string());
                    }
                }
                println!("{:?}", values);
                row_count += 1;
                
                if row_count >= 20 {
                    println!("... (showing first 20 rows)");
                    return Ok(());
                }
            }
        }
        
        if row_count == 0 {
            println!("No results found");
        }
    }

    Ok(())
}