Difference between revisions of "SQL Statement Nodes"

From PresenceWiki
Jump to: navigation, search
(SQL Statement Nodes)
(The SQL Statement Editor)
Line 12: Line 12:
  
 
http://www.international-presence.com/wikidocs/images/sql_dialog_1.png
 
http://www.international-presence.com/wikidocs/images/sql_dialog_1.png
 +
 +
==== Main Options ====
  
 
'''Live Server Database''' This is the Database Resource that the Presence server should connect to.
 
'''Live Server Database''' This is the Database Resource that the Presence server should connect to.
Line 28: Line 30:
  
 
If the "Record each time this statement modifies any records" checkbox is selected a new entry will be made into the Activity Archive each time this Node executes. Regardless of whether it is checked, errors should always be recorded.
 
If the "Record each time this statement modifies any records" checkbox is selected a new entry will be made into the Activity Archive each time this Node executes. Regardless of whether it is checked, errors should always be recorded.
 +
 +
==== Columns Panel ====
 +
 +
http://www.international-presence.com/wikidocs/images/sql_dialog_2.png
 +
 +
 +
When this pane displays it will first attempt to connect to the database in order to query the database metadata (the underlying structure of the database including schemas, tables and columns):
 +
 +
Here you can include or exclude database table names and schema names to restrict the database structure tree. Clicking Okay will cause the structure tree to be loaded into the left hand pane. Selecting a column will bring up extra information in the panel below the tree.
 +
 +
To include columns in your query just drag individual columns or entire tables into the list panel to the right.
 +
 +
'''Automatically Create Aliases''' - when dragging columns an alias will automatically be assigned to the column name. In the final query, columns will be referenced "AS" the alias. Select a column and click the "Change Alias" button to over-ride the automatic alias generated. To remove the alias, select the column and click "Remove Alias".
 +
 +
'''Discard Duplicate rows''' This will cause the final SQL statement to use the "DISTINCT" keyword, if a SELECT statement is to be generated.
 +
 +
'''Only get records between ... and ...''' This will cause the Presence server engine to discard all results outside this range. Leave the second field blank if you just want to skip the first n records but include all the rest.
 +
 +
=== Conditions Panel ===
 +
 +
This panel provides an intuitive drag-and-drop interface for creating the conditions for the "WHERE" part of your SQL Query. To add a condition to the WHERE clause, drag a column name from the metadata tree and drop it into the main panel. By default the "AND" operator will be used when multiple columns are added, for example:
 +
 +
WHERE SURNAME LIKE 'P%' AND COUNTRY = 'UK'
 +
 +
Using the "Switch to OR Group" will change this to be
 +
 +
WHERE SURNAME LIKE 'P%' OR COUNTRY = 'UK'
 +
 +
Additionally you can create AND / OR subgroups (nested conditions) to build more complicated queries such as:
 +
 +
WHERE (SURNAME LIKE 'P%' OR SURNAME LIKE 'Q%') AND COUNTRY = 'UK'
 +
 +
==== Dynamic Markers ====
 +
 +
Dynamic Markers are useful when you don't know what the value specified in a query will be. This may be because you are including data from an earlier query, or a dynamic variable. The actual value of the dynamic marker will be substituted by Presence when the Task runs.
 +
 +
To specify a Dynamic Marker value, use a question mark (?) instead of a literal value. For example, the above example conditional clause could be rewritten as:
 +
 +
WHERE (SURNAME LIKE ? OR SURNAME LIKE ?) AND COUNTRY = ?
  
  

Revision as of 06:10, 12 January 2010

SQL Statement Nodes

SQL Statement Nodes allow you to run SQL statements against a JDBC-compliant data source such as Oracle, Microsoft SQL Server, MySQL, etc.

To create a new SQL Statement Node, right click in your Task View and choose "Create New > SQL Statement" from the pop-up menu.

Please note that SQL Statement Nodes are reusable. This means that once created an SQL Statement Node can be re-used in other Tasks. Changes to one instance of the SQL Statement will affect all other references, and a warning dialog is shown when this is attempted.

The SQL Statement Editor

The first tab viewed when launching the SQL Editor is the "Main" tab, which prompts the user to enter a name and description for the query and to select the Database Resource:

http://www.international-presence.com/wikidocs/images/sql_dialog_1.png

Main Options

Live Server Database This is the Database Resource that the Presence server should connect to.

Test Database This is the database that the SQL editor will connect to for testing. It may be the same as the Live Server Database.

Username / Password This is the username and password that should be used to connect to the database.

Transaction Options

  • Auto-commit all transactions: Individual updates, deletes etc will be committed as soon as they are executed, providing no roll-back functionality.
  • Commit transactions after all statements run: In cases where multiple statements are ran, all will be committed once they have all run. If one statement fails, none will be committed.
  • Commit transactions later in Task: Updates are left uncommitted. They will be automatically committed after a successful Task completion and automatically rolled back if the Task fails. Alternatively you can use the Roll Back Transactions and Commit Transactions Nodes to do this manually according to Task logic.

Auditing

If the "Record each time this statement modifies any records" checkbox is selected a new entry will be made into the Activity Archive each time this Node executes. Regardless of whether it is checked, errors should always be recorded.

Columns Panel

http://www.international-presence.com/wikidocs/images/sql_dialog_2.png


When this pane displays it will first attempt to connect to the database in order to query the database metadata (the underlying structure of the database including schemas, tables and columns):

Here you can include or exclude database table names and schema names to restrict the database structure tree. Clicking Okay will cause the structure tree to be loaded into the left hand pane. Selecting a column will bring up extra information in the panel below the tree.

To include columns in your query just drag individual columns or entire tables into the list panel to the right.

Automatically Create Aliases - when dragging columns an alias will automatically be assigned to the column name. In the final query, columns will be referenced "AS" the alias. Select a column and click the "Change Alias" button to over-ride the automatic alias generated. To remove the alias, select the column and click "Remove Alias".

Discard Duplicate rows This will cause the final SQL statement to use the "DISTINCT" keyword, if a SELECT statement is to be generated.

Only get records between ... and ... This will cause the Presence server engine to discard all results outside this range. Leave the second field blank if you just want to skip the first n records but include all the rest.

Conditions Panel

This panel provides an intuitive drag-and-drop interface for creating the conditions for the "WHERE" part of your SQL Query. To add a condition to the WHERE clause, drag a column name from the metadata tree and drop it into the main panel. By default the "AND" operator will be used when multiple columns are added, for example:

WHERE SURNAME LIKE 'P%' AND COUNTRY = 'UK'

Using the "Switch to OR Group" will change this to be

WHERE SURNAME LIKE 'P%' OR COUNTRY = 'UK'

Additionally you can create AND / OR subgroups (nested conditions) to build more complicated queries such as:

WHERE (SURNAME LIKE 'P%' OR SURNAME LIKE 'Q%') AND COUNTRY = 'UK'

Dynamic Markers

Dynamic Markers are useful when you don't know what the value specified in a query will be. This may be because you are including data from an earlier query, or a dynamic variable. The actual value of the dynamic marker will be substituted by Presence when the Task runs.

To specify a Dynamic Marker value, use a question mark (?) instead of a literal value. For example, the above example conditional clause could be rewritten as:

WHERE (SURNAME LIKE ? OR SURNAME LIKE ?) AND COUNTRY = ?



See also: Database Resource