User Tools

Site Tools


transformations:sqlcommand

DATABASE COMMAND

Category: Workflow / External


Description

This action performs one of these 4 commands on a specified database:

  • Create database table
  • Delete database table
  • Delete all rows
  • Custom command


Use cases

  • Export data into a database by instructing the database to bulk load a text file (e.g. previously generated by EasyMorph)
  • Trigger stored procedures


Action settings

Setting Description
Connector*Select or create a connector to the database.
CommandSelect the process to run on the database. Options: Create table, Delete table, Delete all rows, or Custom command.
Advanced (Timeout)Set the number of seconds for this process to complete before timing out. Default is 1800. If the action has not
completed within the set Timeout, the workflow stops and displays an error.

* Setting can be specified using a parameter.


Create table settings

Description: Creates a new database table. Field types are automatically suggested based on the input dataset but can be changed manually. Field names are taken from the input dataset. They are case-sensitive.

Settings Description
Table name*Enter the name of the database table to create.
If table already existsSelect how EasyMorph handles cases when the named table already exists in the target database.
Options: Fail (the workflow stops with an error), Do nothing (the action does nothing and proceeds to the next action),
Delete table (the original table is deleted), or Delete rows, keep table (all records in the table are deleted, but the table structure is kept).
Data typesSelect whether to use Simple or Advanced data type definitions in the field list.
Field listSelect the columns to insert into the new table and the data type of each column. Data type options will vary based on the "Data types" option selected (above).

* Setting can be specified using a parameter.


Delete table settings

Description: Deletes a database table and all data in it.

Settings Description
Table name*Enter the name of the database table to delete.
If table doesn't existSelect how EasyMorph will handle cases when the named table is not found in the database. Options: Fail (the workflow stops with an error) or Do nothing (the workflow does nothing and continues to the next action).

* Setting can be specified using a parameter.


Delete all rows settings

Description: Deletes a database table and all data in it. Note that some databases don't have a native command for truncation. In such cases truncation is performed with the help of a DELETE query. The operation is atomic meaning that it either deletes everything or, in case of a failure, nothing. The query can include multiple statements if this is supported by the database connector.

Settings Description
Table name*Enter the name of the database table to delete.
If table doesn't existSelect how EasyMorph will handle cases when the named table is not found in the database. Options: Fail (the workflow stops with an error) or Do nothing (the workflow does nothing and continues to the next action).

* Setting can be specified using a parameter.


Custom command settings

Description: Sends a free-form SQL query to the specified database.

Settings Description
StatementEnter the free-form SQL query to send to the database. The query can include parameters wrapped in braces, which will be replaced with their values during runtime.

Example: LOAD DATA INFILE '{FileName}' INTO TABLE {TableName};


See also

transformations/sqlcommand.txt · Last modified: 2021/06/22 10:38 by craigt