Difference between revisions of "SQL Statement Nodes"

From PresenceWiki
Jump to: navigation, search
 
(SQL Statement Nodes)
Line 5: Line 5:
 
To create a new SQL Statement Node, right click in your Task View and choose "Create New > SQL Statement" from the pop-up menu.
 
To create a new SQL Statement Node, right click in your Task View and choose "Create New > SQL Statement" from the pop-up menu.
  
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.
+
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 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
  
 +
'''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.
  
  

Revision as of 16:32, 11 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

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.



See also: Database Resource