Updating SQL Loader

The loader reads the file contents as a string and considers it to be an SQL statment that will update the database(e.g starts with INSERT, UPDATE or DELETE). The SQL statement is used to construct a new function that will execute the update as required.

The function that is returned after successfully autoloading the file requires at least one argument. That first argument must be a database connection, as returned by the jdbcconn loader. Any subsequent arguments are assumed to be parameters to the SQL update 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 that will update the database(e.g. starts with INSERT, UPDATE or DELETE). The statement must be valid for your particular database. As the SQL is executed as a prepared statement, you can specify variable parameters to the update by using a "?" character. 

UPDATE
  site_text
SET
  text = ?
WHERE
  site_id = 'lemeridien'
  AND
  language = 'japanese'
  OR
  property_id = ?

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 update for your database. Any variable parameters you want can be specified as "?". This example will assume you have created a file called site_text.update.sql. 
    UPDATE
  site_text
SET
  text = ?
WHERE
  site_id = 'lemeridien'
  AND
  language = 'japanese'
  OR
  property_id = ?
  3. To execute the SQL update within a script, we apply the autoloaded function and must supply the connection as an argument, followed by any parameters that are required. e.g. 
    images( localhost, "My paragraph of text. Whatever.", "p12345" );
    Which would send the following update to the database: 
    UPDATE
  site_text
SET
  text = 'My paragraph of text. Whatever.'
WHERE
  site_id = 'lemeridien'
  AND
  language = 'japanese'
  OR
  property_id = 'p12345'

Notes

  • The implementation of the function returned by this loader uses a prepared statement to execute the update. The prepared statement is cached between function applications which should result in faster execution when the update 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.