SQL Loader

The loader reads the SQL file contents as a string and considers it to be an SQL query. The SQL statement is compiled into a function, which we will call the "SQL function", to distinguish it from the "SQL file". The SQL function executes the SQL query as required.

The SQL function requires at least one argument, which must be a database connection, as returned by the jdbcconn loader. Any subsequent arguments are assumed to be parameters to the SQL query and may be of any valid type(integer, string, date, etc).

File Format

The format of an SQL file is simply any valid SQL statement (starting with SELECT) for the database you intend to query. As the SQL query is executed as a prepared statement, you can specify variable parameters to the query by using a "?" character. 

SELECT
  *
FROM
  images
WHERE
  site_id = ?
  AND
  language = ?
  OR
  some_other_field = 'a value'

Use

  1. If there isn't already one available, create a jdbcconn file in your inventory. This example will assume you have created a file called localhost.jdbcconn
  2. Create an sql file in your inventory, containing the SQL query for your database. Any variable parameters you want can be specified as "?". This example will assume you have created a file called images.sql. 
    SELECT
  *
FROM
  images
WHERE
  site_id = ?
  AND
  language = ?
  3. To execute the SQL query within a script, we apply the SQL function and must supply the connection as an argument, followed by any parameters that are required. e.g. 
    images( localhost, "lemeridien", "french" );
    Which would send the following query to the database: 
    SELECT
  *
FROM
  images
WHERE
  site_id = 'lemeridien'
  AND
  language = 'french'

Notes

  • The implementation of the SQL function returned by this loader uses a prepared statement to execute the SQL query. The prepared statement is cached between SQL function applications which should result in faster execution when the SQL query is run multiple times. Before execution the first argument is checked to see if the connection has changed, if it has the prepared statement is created for the new connection.