Dynamic SQL

In cases when the code for an SQL statement is being constructed at runtime as a string, it might be difficult to express the SQL arguments as a tuple. The following approaches allow construction of SQL arguments dynamically with the SQL code and passing them to Sibyl for SQL execution.

Slice of Same Type Arguments

Quite often, when the SQL is built dynamically, argument values are provided out of the process. This way they most likely will arrive as strings. These strings can be collected into a vector and then passed to Sibyl as a slice of strings.

For the sake of clarity the example below does not show the actual process of building the SQL and the vector of arguments. One would have to feel in the gaps.

    // Assume that this was assembled dynamically from bits and pieces
    let sql = String::from("
        SELECT first_name, last_name, department_name, hire_date
          FROM hr.employees e
          JOIN hr.departments d
            ON d.department_id = e.department_id
         WHERE d.department_name IN (:department_name, :dn2, :dn3, :dn4, :dn5)
           AND d.department_id IN (
                    SELECT department_id
                      FROM hr.employees
                  GROUP BY department_id
                    HAVING Count(*) >= :min_employees )
           AND hire_date BETWEEN To_Date(:from_date,'MONTH DD, YYYY')
                             AND To_Date(:thru_date,'MONTH DD, YYYY')
      ORDER BY hire_date
    ");
    // Assume that values for them arrived from elsewhere, and were collected
    // while the above SQL was being constructed
    let mut args = Vec::new();
    args.push("Marketing".to_string());
    args.push("Purchasing".to_string());
    args.push("Human Resources".to_string());
    args.push("Shipping".to_string());
    args.push("IT".to_string());
    args.push("5".to_string());
    args.push("October 1, 2006".to_string());
    args.push("December 31, 2006".to_string());

    let stmt = session.prepare(&sql)?;

    let row = stmt.query_single(args.as_slice())?.expect("single row result");
    let first_name: &str = row.get(0)?;
    let last_name : &str = row.get(1)?;
    let dept_name : &str = row.get(2)?;
    let hire_date : Date = row.get(3)?;

Note that the example above uses only IN parameters. When some of them are OUT or INOUT the slice passed to Sibyl must be mutable, i.e. extracted via as_mut_slice(), and the strings at appropriate positions sized, i.e. created via with_capacity(), to accommodate the maximum length of the expected output.

While this woks, it has a certain, albeit minor, limitation - the SQL code has to expect only arguments of the same type and apply appropriate explicit conversion where appropriate. Sometimes this makes SQL a bit cluttered and somewhat more difficult to read.

Vec of Any Type

More strictly - a vector of any type that implements Sibyl's ToSql trait.

    let sql = String::from("
        SELECT first_name, last_name, department_name, hire_date
          FROM hr.employees e
          JOIN hr.departments d
            ON d.department_id = e.department_id
         WHERE d.department_name IN (:department_name, :dn2, :dn3, :dn4, :dn5)
           AND d.department_id IN (
                    SELECT department_id
                      FROM hr.employees
                  GROUP BY department_id
                    HAVING Count(*) >= :min_employees )
           AND hire_date BETWEEN :from_date AND :thru_date
      ORDER BY hire_date
    ");
    // Assume that the arguments where preparsed into the appropriate types
    let mut dept1 = "Marketing";
    let mut dept2 = "Purchasing";
    let mut dept3 = "Human Resources";
    let mut dept4 = "Shipping";
    let mut dept5 = "IT";
    let mut num_emp = 5;
    let mut date_from = Date::from_string("October   1, 2006", "MONTH DD, YYYY", &session)?;
    let mut date_thru = Date::from_string("December 31, 2006", "MONTH DD, YYYY", &session)?;

    let stmt = session.prepare(&sql)?;

    let mut args = Vec::<&mut dyn ToSql>::new();
    args.push(&mut dept1); // :department_name
    args.push(&mut dept2); // :dn2
    args.push(&mut dept3); // :dn3
    args.push(&mut dept4); // :dn4
    args.push(&mut dept5); // :dn5
    args.push(&mut num_emp as &mut dyn ToSql); // :min_employees
    args.push(&mut date_from); // :from_date
    args.push(&mut date_thru); // :thru_date

    let row = stmt.query_single(args)?.expect("single row result");
    let first_name: &str = row.get(0)?;
    let last_name : &str = row.get(1)?;
    let dept_name : &str = row.get(2)?;
    let hire_date : Date = row.get(3)?;

While this allows working with arguments that have appropriate types, it has a limitation on its own - in order to be able to accept arguments for OUT or INOUT parameters this method requires all arguments to be mutable. In a way it treats all of them as INOUT even when that is not actually needed.