Differences

This shows you the differences between two versions of the page.

--- transformations:sqlcommand [2016/06/19 18:44] – created dmitry
+++ transformations:sqlcommand [2021/06/21 02:46] – craigt
@@ Line 1: / Line 1: @@
-===== 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]].\\
-The query can include multiple statements, if this is supported by the database connector.
+\\
+====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<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]].\\
-** Use cases **
+\\
-  * Export data into a database by instructing the database to load a text file (e.g. previously generated by EasyMorph)
+====Delete table settings====
-  * Truncate/drop/create tables
+**Description:**  Deletes a database table and all data in it.
-  * Trigger stored procedures
+^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]]