Differences

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

--- transformations:sqlcommand [2017/05/02 02:48] – dmitry
+++ transformations:sqlcommand [2026/09/22 14:12] (current) – [Create table settings] Typo roberto
@@ Line 1: / Line 1: @@
-===== SQL Command =====
+{{ transformations:SQLCommandAction.png}}
+====== DATABASE COMMAND ======
+Category: Workflow / External\\
-Performs one of these 4 commands:
+\\
+=====Description=====
+This action performs commands on a specified database.
+\\
+=====Capabilities=====
+  * Custom command
   * Create database table
   * Delete database table
   * Delete all rows
-  * Custom command
+  * List tables
+  * List table fields
-==== Create database table ====
+\\
-Creates a new database table. The table name can be specified either explicitly, or using a [[:parameters|project parameter]].
+=====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
-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.
+\\
+=====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:  //Custom command//, //Create table//, //Delete table//, //Delete all rows//, //List tables//, or //List table fields//.|
+|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.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
-==== Delete database table ====
+\\
-Deletes a database table and all data in it. The table name can be specified either explicitly, or using a [[:parameters|project 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};  ''
-Be careful when deleting database tables as this operation can't be undone. All information in the target database table is lost forever.
+\\
+====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). In //Advanced// mode, click the pencil icon next to a column to change its data type.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
+\\
+===Column data type settings===
+**Description:**  In //Advanced// mode, the pencil icon next to a selected column opens the //Column data type// dialog.
+^Settings ^Description ^
+|Standard data type / Custom definition|Select //Standard data type// to choose one of the database's data types, or //Custom definition// to write the column definition yourself.|
+|Data type|//Standard data type// only. Select the data type.|
+|Can't be null|//Standard data type// only. Adds ''NOT NULL'' to the column definition, so the column won't accept null (empty) values.|
+|Must be unique|//Standard data type// only. Adds ''UNIQUE'' to the column definition, so the database won't accept duplicate values in the column.|
+|Definition|//Custom definition// only. Enter the part of the ''CREATE TABLE'' statement that follows the column name, e.g. ''DECIMAL(12,2) DEFAULT 0 NOT NULL'' or ''INT PRIMARY KEY''. EasyMorph adds the column name and passes the text to the database as is.|
-==== Delete all rows ====
+\\
-Makes a database table empty by removing all data (rows) from it. The table structure (i.e. field names and types) remains the same. The table name can be specified either explicitly, or using a [[:parameters|project 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]].\\
-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.
+\\
+====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 ====
+\\
-Sends a free-form SQL query to the database, specified by [[:connectors|connector]].
+====List tables settings====
+**Description:**  This command creates a two-column dataset consisting of the Schema name and Table name of all tables in the target database.
-The query can include [[:parameters]] wrapped in braces. Parameters will be replaced with their values during runtime. For instance:
+^Settings ^Description ^
+|Only for schema|Optionally check this to only list the tables of a certain schema. Enter the schema name or use the schema picker to select it.|
-  LOAD DATA INFILE '{FileName}' INTO TABLE {TableName};
+\\
+====List table fields settings====
-The query can include multiple statements, if this is supported by the database connector.
+**Description:**  This command creates a three-column dataset consisting of the Column name, Column data type, and any Column annotations of all fields in the target table.
+^Settings ^Description ^
-** Use cases **
+|Table name<sup>*</sup>|Select the database table to retrieve details from.|
-  * Export data into a database by instructing the database to bulk load a text file (e.g. previously generated by EasyMorph)
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
-  * Trigger stored procedures
-** See also **
+\\
-  * [[http://easymorph.com/learn/export-database.html|Tutorial: Exporting data into a databse]]
+=====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]]