A: Running SQL Queries

Harmony provides Dino with extensions and convenience classes to execute arbitrary SQL queries and return dynamic objects. Using the techniques outlined in Parameter Declarations and the Trestle Environment, you can collect parameter values from the user and then embed them directly into the SQL query via string.Format, or more reliably, use parameter substitution.

Using parameter substitution is much more reliable than using string.Format, because the parameter is supplied in its native type rather than converted to a string. While SQL is very adept at converting strings to the correct type for queries, it's always better if we can supply the correct type ourselves. Parameter substitution is also safer because it can avoid SQL injection attacks, where a user asked for a customer name supplies a string of executable SQL like "DELETE FROM table_x" rather than "ACME Corp".

/**
Command function demonstrating direct SQL query.
*/
def cmdSelectLogs(intLimitTo) {
    $$
    <param name="intLimitTo"
           value="10"
	   help="Maximum number of records returned" />
    $$
    from Aim.Data import DbFactory;
	
    var query = string.Format(
        $$
        SELECT
	    log.name,
	    log.date_created,
	    log.created_by,
	    e.entry
        FROM
	    system_log AS log
        INNER JOIN
	    system_log_entry AS e ON e.system_log_id = log.id
        WHERE 
            entry LIKE '%Apple%'
        ORDER BY
	    log.date_created
        LIMIT {0}
        $$, intLimitTo);

    return DbFactory.GetObjects(query);
}

When using parameterized SQL queries as shown next, we don't need to do the string.Format, because the framework will do the job of finding the @param_name entries in the string, and replacing them with the values we supply when it prepares the SQL statement. Another advantage is that we don't have to surround string values with single quotes. The statement preparation is very reliable and can handle strings with embedded single quotes, for example a search for the name O'Reilly. This is harder to handle with string.Format, because we would need to "clean" our strings before adding them to the query string.

/**
Command function demonstrating direct SQL query using db parameters.
*/
def cmdSelectLogsUsingDbParams(strSearchText, intLimitTo) {
    $$
    <param name="strSearchText"
           value="%Apple%"
	   help="Search text to search in log entry. Use % for wildcard." />
    <param name="intLimitTo"
	   value="15"
	   help="Maximum number of records returned" />
    $$
    from Aim.Data import DbFactory;
	
    var query =
    $$
    SELECT
        log.name,
	log.date_created,
	log.created_by,
	e.entry
    FROM
	system_log AS log
    INNER JOIN
	system_log_entry AS e ON e.system_log_id = log.id
    WHERE 
        entry LIKE @search
    ORDER BY
	log.date_created
    LIMIT 
        @limit
    $$;
    var db = DbFactory.Current.Create();
    var cmd = db.GetCommand(query);
    // Add parameters. Note that the name does not need the @ before it
    db.AddParameter(cmd, 'search', strSearchText);
    db.AddParameter(cmd, 'limit', intLimitTo);
	
    return db.GetObjects(cmd);
}

Post-Processing Query Results

You are not limited to returning only what the database decrees. Since GetObjects returns a set of dynamic objects, we can loop through them and do post-processing, such as filtering out unwanted records, or adding new fields based on some criteria that was possibly too complex to incorporate into a SQL query.

In this case, our objects will have fields called name, date_created, created_by, and entry that we can inspect after we get the objects back. Whatever columns you select, each object will have property names that are the same. If you use an alias (e.g. SELECT name AS LogName), then the objects will have a property with the alias name.

/**
Command function demonstrating post-processing of SQL results.
*/
def cmdSelectLogsWithPostProcess(strSearchText, intLimitTo) {
    $$
    <param name="strSearchText"
	   value="%Apple%"
	   help="Search text to search in log entry. Use % for wildcard." />
    <param name="intLimitTo"
	   value="15"
	   help="Maximum number of records returned" />
    $$
    from Aim.Data import DbFactory;
	
    var query =
    $$
    SELECT
	log.name,
	log.date_created,
	log.created_by,
	e.entry -- we will look at this after the fact
    FROM
	system_log AS log
    INNER JOIN
	system_log_entry AS e ON e.system_log_id = log.id
    WHERE 
        entry LIKE @search
    ORDER BY
	log.date_created
    LIMIT
        @limit
    $$;
	
    var db = DbFactory.Current.Create();
    var cmd = db.GetCommand(query);
    // Add parameters. Note that the name does not need the @ before it
    db.AddParameter(cmd, 'search', strSearchText);
    db.AddParameter(cmd, 'limit', intLimitTo);
	
    var results = db.GetObjects(cmd);
    // Loop thru the results and add new fields
    each r in results {
        // Always add the field(s) before any conditional statement  
        // so that all results have the same fields
	r.is_windows = false;
	if r.entry.Contains('Win') {
	    // Change the field value inside conditional statement
	    r.is_windows = true;
	}
    }
	
    return results;
}