SQL Runner
SQL Runner is used to execute an SQL Query or Script against a database and optionally store the results in a desired location.
Run SQL Script with Integer Parameters
The Run SQL Script with Integer Parameters action will run an SQL Script on an integer bounded range of data. This action requires that the source data be sequentially sorted on a integer stamp key. Whenever your Task runs, it will execute your SQL Script against the database specified in your Target Database Connection in accordance with the parameters that you have specified.
This Task requires you to provide a remote database Connection, an SQL Script to perform on the database and parameters for narrowing the range of data processed based on integer information.
Alias
All Tasks contain this field. Set an Alias name that is meaningful and memorable as it will appear in the list of Tasks on your Nominode.
Target Database Connection
SQL Database access information is stored in a Generic:Database Connection on your Nominode.
This link contains more information about creating a Connection on a Nominode:
The Generic:Database Connection has seven fields.
- Set the Alias field to something meaningful like "YourDatabase on YourSQLServer".
- Leave the Engine Type field set to the default value of "mysql".
- Set the Hostname field to the DNS name of the server running the software hosting your database.
- Set the Database Name field to the name of your database.
- Set the Username field to the name of the SQL user that has the desired access to your database.
- Set the Password field to the SQL user's password.
- Set the Port field to the port number that your database server is listening on. MySQL servers listen on port 3306 by default.
Once the Connection is created, you can select its Alias from the drop down list for the Target Database Connection field on this Task.
SQL Script
Type in an SQL Script to execute on the data that you want to process. In your Script, use the variables range_start_point and range_end_point which are automatically calculated and populated based on the values in the Range section of this Task. For example, this SQL Script will change the values in column_1 to Strawberry in a table named yourtable in your database for the rows where the value in your_integer_column is between range_start_point and range_end_point:
UPDATE yourtable
SET column_1 = 'Strawberry'
WHERE your_integer_column >= {{ range_start_point }}
AND your_integer_column < {{ range_end_point }}
The example above assumes that you are processing your data in ascending order. If you are processing your data in descending order, adjust the comparison operators in your SQL Script accordingly. For example:
UPDATE yourtable
SET column_1 = 'Strawberry'
WHERE your_integer_column <= {{ range_start_point }}
AND your_integer_column > {{ range_end_point }}
If you have previously saved your SQL Script as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
User Defined Parameters
If your SQL Script contains parameters, use this field to specify the parameter values. For example, yourvalue is a parameter in this SQL Script:
UPDATE yourtable
SET column_1 = {{ yourvalue }}
WHERE your_integer_column >= {{ range_start_point }}
AND your_integer_column < {{ range_end_point }}
To specify the value "Strawberry" for the yourvalue parameter when this Task runs, set this field to:
{"yourvalue": "Strawberry"}
If you have previously saved your User Defined Parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
Next Run Start Point
Type in the integer stamp where the processing of your sequentially sorted data should begin. Each time an increment of data is successfully processed, this field value will automatically be updated to the integer stamp of the next row after the last row processed to allow the next increment of processing or the next run of the Task to begin at that point. If there is no next row, then this field value will be automatically set to the last row's value plus one if you are processing your data in ascending order or minus one if you are processing your data in descending order. This field can be modified between Task runs, if desired.
When a Task starts, the Next Run Start Point is used to automatically set the initial value of the range_start_point variable used in your SQL Script. After the beginning of the Task run, the value of the range_start_point variable is then updated after each data increment is successfully processed during the Task run.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
End Point Query
Type in an SQL Query that returns the integer value where the processing of your sequentially sorted data should stop. Once a row with a greater integer stamp, if you are processing your data in ascending order, or a lesser integer stamp, if you are processing your data in descending order, is encountered, no more rows will be processed. For example, this SQL Query will set the End Point value to the maximum value in your_integer_column:
SELECT MAX(your_integer_column)
FROM yourtable
Using a MAX or MIN function in your End Point Query is typical for processing the latest information in a database on an ongoing basis.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode:
Process Increment
This Task will process your data in increments. Set a value in this field to determine the range of each increment. Choosing a smaller process increment will allow your Task to use less memory and temporary disk space on your Nominode. Smaller process increments also improve the recoverability of your Task if it is stopped, intentionally or otherwise, prior to completion, because the Next Run Start Point is automatically updated to the integer stamp of the next row after the last successfully processed row in the increment. The value in this field should be positive if you are processing your data in ascending order or negative if you are processing your data in descending order. Note that, for negative values, a smaller number is a larger process increment. For example, -100 is a larger process increment than -50.
When a Task starts, the Process Increment value is combined with the range_start_point value and the result is compared to the result of the End Point Query. If you are processing your data in ascending order, the lesser of the two results becomes the value of the range_end_point variable used in your SQL Script. If you are processing your data in descending order, the greater of the two results becomes the value of the range_end_point variable used in your SQL Script. During the Task run, after each data increment is successfully processed, the range_start_point value is updated to the current range_end_point value. Then the Process Increment value is combined with the new range_start_point value and the result is compared to the result of the End Point Query. If you are processing your data in ascending order, the lesser of the two results becomes the new value of the range_end_point variable. If you are processing your data in descending order, the greater of the two results becomes the new value of the range_end_point variable. If the range_start_point and the range_end_point values match, the task ends.
If you have previously saved your Range parameters as a Shared Object on your Nominode, you can select the Shared Object to populate this field.
This link contains more information about creating a Shared Object on a Nominode: