use ibm_db::{
create_environment_v3,
Statement,
ResultSetState::{Data, NoData},
};
use std::error::Error;
fn main() {
match test_data_types() {
Ok(()) => println!("\n✓ Data type test completed successfully"),
Err(e) => println!("\n✗ Data type test failed: {}", e),
}
}
fn test_data_types() -> Result<(), Box<dyn Error>> {
println!("\n========== DATA TYPE HANDLING TEST ==========\n");
let env = create_environment_v3().map_err(|e| e.unwrap())?;
let connection_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=SAMPLE;HOSTNAME=db.example.com;PORT=50000;UID=db_user;PWD=YourPassword123";
let conn = env.connect_with_connection_string(connection_string)?;
println!("✓ Connected to database\n");
let stmt = Statement::with_parent(&conn)?;
let create_sql = "CREATE TABLE IF NOT EXISTS test_types (
id INT,
name VARCHAR(50),
description VARCHAR(255),
salary DECIMAL(10,2),
is_active INT,
hire_date DATE
)";
match stmt.exec_direct(create_sql)? {
Data(s) => { let _ = s.close_cursor()?; }
NoData(_) => {}
}
println!("✓ Test table created\n");
println!("--- Test 1: Insert Various Data Types ---");
let test_records = vec![
(1, "John Doe", "Senior Developer", 85000.00, 1),
(2, "Jane Smith", "Project Manager", 78500.50, 1),
(3, "Bob Johnson", "Intern", 35000.00, 0),
(4, "Alice Williams", "QA Engineer", 62000.75, 1),
];
for (id, name, desc, salary, active) in test_records {
let sql = format!(
"INSERT INTO test_types (id, name, description, salary, is_active, hire_date) VALUES ({}, '{}', '{}', {}, {}, CURRENT_DATE)",
id, name, desc, salary, active
);
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct(&sql)? {
Data(s) => { let _ = s.close_cursor()?; }
NoData(_) => {}
}
println!("✓ Inserted: ID={}, Name='{}', Salary={}", id, name, salary);
}
println!("\n--- Test 2: Retrieve Different Data Types ---");
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct("SELECT id, name, salary, is_active FROM test_types WHERE is_active = 1")? {
Data(mut stmt) => {
println!("\nActive Employees:");
println!("{:<5} {:<15} {:<12} {:<8}", "ID", "Name", "Salary", "Active");
println!("{}", "-".repeat(45));
while let Some(mut cursor) = stmt.fetch()? {
let id: Option<i32> = cursor.get_data(1)?;
let name: Option<String> = cursor.get_data(2)?;
let salary: Option<f64> = cursor.get_data(3)?;
let is_active: Option<i32> = cursor.get_data(4)?;
if let (Some(id), Some(name), Some(salary), Some(active)) = (id, name, salary, is_active) {
println!("{:<5} {:<15} ${:<11.2} {}", id, name, salary, active);
}
}
let _ = stmt.close_cursor()?;
}
NoData(_) => println!("✗ Query returned no data"),
}
println!("\n--- Test 3: Insert and Handle NULL Values ---");
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct("INSERT INTO test_types (id, name) VALUES (5, 'Unknown Employee')")? {
Data(s) => { let _ = s.close_cursor()?; }
NoData(_) => {}
}
println!("✓ Inserted record with NULL values\n");
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct("SELECT id, name, salary, description FROM test_types WHERE id = 5")? {
Data(mut stmt) => {
while let Some(mut cursor) = stmt.fetch()? {
let id: Option<i32> = cursor.get_data(1)?;
let name: Option<String> = cursor.get_data(2)?;
let salary: Option<f64> = cursor.get_data(3)?;
let desc: Option<String> = cursor.get_data(4)?;
println!("Retrieved record:");
println!(" ID: {:?}", id);
println!(" Name: {:?}", name);
println!(" Salary: {:?} (NULL)", salary);
println!(" Description: {:?} (NULL)", desc);
}
let _ = stmt.close_cursor()?;
}
NoData(_) => println!("✗ Query returned no data"),
}
println!("\n--- Test 4: SQL Type Conversion ---");
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct("SELECT name, CAST(salary AS INT) as salary_int, UPPER(name) as upper_name FROM test_types WHERE id <= 2")? {
Data(mut stmt) => {
println!("\nType Conversions:");
println!("{:<15} {:<12} {:<15}", "Name", "Salary(INT)", "UpperCase");
println!("{}", "-".repeat(45));
while let Some(mut cursor) = stmt.fetch()? {
let name: Option<String> = cursor.get_data(1)?;
let salary_int: Option<i32> = cursor.get_data(2)?;
let upper: Option<String> = cursor.get_data(3)?;
if let (Some(name), Some(salary), Some(upper)) = (name, salary_int, upper) {
println!("{:<15} {:<12} {:<15}", name, salary, upper);
}
}
let _ = stmt.close_cursor()?;
}
NoData(_) => println!("✗ Query returned no data"),
}
println!("\n--- Test 5: Aggregate Functions ---");
let stmt = Statement::with_parent(&conn)?;
match stmt.exec_direct("SELECT COUNT(*) as cnt, AVG(salary) as avg_sal, MAX(salary) as max_sal, MIN(salary) as min_sal FROM test_types")? {
Data(mut stmt) => {
if let Some(mut cursor) = stmt.fetch()? {
let count: Option<i32> = cursor.get_data(1)?;
let avg: Option<f64> = cursor.get_data(2)?;
let max: Option<f64> = cursor.get_data(3)?;
let min: Option<f64> = cursor.get_data(4)?;
println!("\nAggregate Statistics:");
println!(" Total Records: {}", count.unwrap_or(0));
if let Some(avg_val) = avg {
println!(" Average Salary: ${:.2}", avg_val);
}
if let Some(max_val) = max {
println!(" Maximum Salary: ${:.2}", max_val);
}
if let Some(min_val) = min {
println!(" Minimum Salary: ${:.2}", min_val);
}
}
let _ = stmt.close_cursor()?;
}
NoData(_) => println!("✗ Query returned no data"),
}
println!("\n========== TEST COMPLETE ==========\n");
Ok(())
}