Difference between revisions of "SQL Statement Nodes"

From PresenceWiki
Jump to: navigation, search
Line 48: Line 48:
 
'''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.
 
'''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 ===
+
==== Conditions Panel ====
  
 
http://www.international-presence.com/wikidocs/images/sql_dialog_3.png
 
http://www.international-presence.com/wikidocs/images/sql_dialog_3.png
Line 64: Line 64:
 
WHERE (SURNAME LIKE 'P%' OR SURNAME LIKE 'Q%') AND COUNTRY = 'UK'
 
WHERE (SURNAME LIKE 'P%' OR SURNAME LIKE 'Q%') AND COUNTRY = 'UK'
  
==== Dynamic Markers ====
+
===== 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.
 
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.
Line 72: Line 72:
 
WHERE (SURNAME LIKE ? OR SURNAME LIKE ?) AND COUNTRY = ?
 
WHERE (SURNAME LIKE ? OR SURNAME LIKE ?) AND COUNTRY = ?
  
=== Order and Groups ===
+
==== Order and Groups ====
  
 
This panel allows users to specify the sort-order of any results returned (the ORDER BY clause) and the columns that should be grouped in the GROUP BY clause of the SQL Statement.
 
This panel allows users to specify the sort-order of any results returned (the ORDER BY clause) and the columns that should be grouped in the GROUP BY clause of the SQL Statement.
Line 82: Line 82:
 
http://www.international-presence.com/wikidocs/images/sql_dialog_4.png
 
http://www.international-presence.com/wikidocs/images/sql_dialog_4.png
  
=== Generate SQL Statement ===
+
==== Generate SQL Statement ====
  
 
This panel creates the SQL statement based on the actions taken in the previous panels. You can use this panel to create a SELECT statement, an INSERT, an UPDATE or a DELETE.
 
This panel creates the SQL statement based on the actions taken in the previous panels. You can use this panel to create a SELECT statement, an INSERT, an UPDATE or a DELETE.
Line 92: Line 92:
 
If you want to verify the validity of your statement, click on the "Check Syntax" button. This will perform a syntax check against the database (which may not report errors).
 
If you want to verify the validity of your statement, click on the "Check Syntax" button. This will perform a syntax check against the database (which may not report errors).
  
=== Dynamic Markers Panel ===
+
==== Dynamic Markers Panel ====
  
 
This allows you to specify the values of any dynamic markers present in the query. These are identified by the ? character (without quotes).
 
This allows you to specify the values of any dynamic markers present in the query. These are identified by the ? character (without quotes).
Line 100: Line 100:
 
To set a value, either click on the ? (highlighted in red) in the left hand pane, or select the item from the list (main panel) and click "Edit". You will be presented with an input dialog where you can enter the value (usually a data reference such as :var{COLUMN_NAME} or a variable such as ${var}) and the data type.
 
To set a value, either click on the ? (highlighted in red) in the left hand pane, or select the item from the list (main panel) and click "Edit". You will be presented with an input dialog where you can enter the value (usually a data reference such as :var{COLUMN_NAME} or a variable such as ${var}) and the data type.
  
=== Results Test ===
+
==== Results Test ====
  
 
This panel allows you to test the results of your query or statement. Click the "Test" button to check that the results you receive are as expected.
 
This panel allows you to test the results of your query or statement. Click the "Test" button to check that the results you receive are as expected.

Revision as of 09:39, 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

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

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 = ?

Order and Groups

This panel allows users to specify the sort-order of any results returned (the ORDER BY clause) and the columns that should be grouped in the GROUP BY clause of the SQL Statement.

To add columns to either panel, drag them from the metadata tree on the left hand side of the screen.

Some databases require columns to be referenced by their alias (if specified) instead of the original column name. For this reason we have provided the "Reference columns by alias" for both the GROUP BY clause and the ORDER BY clause.

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

Generate SQL Statement

This panel creates the SQL statement based on the actions taken in the previous panels. You can use this panel to create a SELECT statement, an INSERT, an UPDATE or a DELETE.

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

Choose the type of Statement you want to generate and then click on the "Generate" button. The generated SQL statement will appear in the pane above.

If you want to verify the validity of your statement, click on the "Check Syntax" button. This will perform a syntax check against the database (which may not report errors).

Dynamic Markers Panel

This allows you to specify the values of any dynamic markers present in the query. These are identified by the ? character (without quotes).

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

To set a value, either click on the ? (highlighted in red) in the left hand pane, or select the item from the list (main panel) and click "Edit". You will be presented with an input dialog where you can enter the value (usually a data reference such as :var{COLUMN_NAME} or a variable such as ${var}) and the data type.

Results Test

This panel allows you to test the results of your query or statement. Click the "Test" button to check that the results you receive are as expected.

The results of the query (if it is a SELECT query) will be displayed in a table (as illustrated). Any messages from the database (errors or otherwise) can be viewed by clicking the "Database Messages" tab. To review the statement which was executed, click the "SQL Statement" tab.

See also: Database Resource