| Next revision | Previous revisionNext revisionBoth sides next revision |
| transformations:sqlcommand [2016/06/19 18:43] – created dmitry | transformations:sqlcommand [2021/06/21 02:46] – craigt |
|---|
| ===== SQL Command ===== | {{ transformations:SQLCommandAction.png}} |
| | ====== DATABASE COMMAND ====== |
| | Category: Workflow / External\\ |
| |
| Sends a free-form SQL query to a database, specified by a [[:connectors|connector]]. | \\ |
| | =====Description===== |
| | This action performs one of these 4 commands on a specified database: |
| | * Create database table |
| | * Delete database table |
| | * Delete all rows |
| | * Custom command |
| |
| The query can include [[:parameters]] wrapped in braces. Parameters will be replaced with their values during runtime. For instance: | \\ |
| | =====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 |
| |
| TRUNCATE TABLE "{TableName}" | \\ |
| | =====Action settings===== |
| | ^ Setting ^ Description ^ |
| | |Connector<sup>*</sup>|Select or create a connector to the database.| |
| | |Command|Select 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 run before timing out. Default is 1800.| |
| | <sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\ |
| |
| ** Use cases ** | \\ |
| * Export data into a database by instructing the database to load a text file (e.g. previously generated by EasyMorph) | ====Create table settings==== |
| * Truncate/drop/create tables | **Description:** Creates a new database table. |
| * Trigger stored procedures | 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<sup>*</sup>|Enter the name of the database table to create.| |
| | |If table already exists|Select 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 types|Select whether to use //Simple// or //Advanced// data type definitions in the field list.| |
| | |Field list|Select 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).| |
| | <sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\ |
| | |
| | \\ |
| | ====Delete table settings==== |
| | **Description:** Deletes a database table and all data in it. |
| | ^Settings ^Description ^ |
| | |Table name<sup>*</sup>|Enter the name of the database table to delete.| |
| | |If table doesn't exist|Select 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).| |
| | <sup>*</sup> Setting can be specified using a [[:parameters|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<sup>*</sup>|Enter the name of the database table to delete.| |
| | |If table doesn't exist|Select 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).| |
| | <sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\ |
| | |
| | \\ |
| | ====Custom command settings==== |
| | **Description:** Sends a free-form SQL query to the specified database. |
| | ^Settings ^Description ^ |
| | |Statement|Enter 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 ** | \\ |
| * [[http://easymorph.com/learn/export-data.html|Tutorial: Exporting data]] | =====See also===== |
| | * [[transformations:deletedbrows|Delete database rows]] |
| | * [[transformations:deletedbrowsbykeys|Delete matching database rows]] |
| | * [[transformations:updatedb|Update database table]] |
| | * [[:connectors|Information on connectors]] |
| | * [[http://easymorph.com/learn/export-database.html|Tutorial: Exporting data into a database]] |
