Execute SQL
Synopsis
This operator executes the specified SQL statement on the specified database.
Description
The Execute SQL operator executes the specified SQL statement on the specified SQL database. The SQL query can be specified through the query parameter. If the SQL query is in a file then the path of that file can be specified through the query file parameter. Please note that this operator cannot be used for loading data from databases. It can be used for executing SQL statements like CREATE or ADD etc. In order to load data from an SQL database, please use the Read Database operator. You need to have at least a basic understanding of databases, database connections and queries in order to use this operator properly. Please go through the parameters and the attached Example Process to understand the working of this operator.
Differentiation
Read Database
The Read Database operator is used for loading data from a database. The Execute SQL operator cannot be used for loading data from databases. It can be used for executing SQL statements like CREATE or ADD etc on the database.
Input
connection
This input port expects a Connection object if any. See the parameter connection entry for more information.
through
It is not compulsory to connect any object with this port. Any object connected at this port is delivered without any modifications to the output port. This operator can have multiple inputs. When one input is connected, another through input port becomes available which is ready to accept another input (if any). The order of inputs remains the same. The object supplied at the first through input port of the Execute SQL operator is available at the first through output port.
query file
The file that contains the SQL query which will be executed on the specified database. Long queries are usually stored in files. Storing queries in files can also enhance reusability.
Output
connection
This output port delivers the Connection object from the input port. If the input port is not connected the port delivers nothing.
through
The objects that were given as input are passed without changing to the output through this port. It is not compulsory to connect this port to any other port; the SQL command is executed even if this port is left without connections. The Execute SQL operator can have multiple outputs. When one output is connected, another through output port becomes available which is ready to deliver another output (if any). The order of outputs remains the same. The object delivered at the first through input port of the Execute SQL operator is delivered at the first through output port
Parameters
define connection
This parameter indicates how the database connection should be specified. It gives you four options: repository, predefined, url and jndi. The parameter is not visible if the connection input port is connected.
connection entry
This parameter is only available when the define connection parameter is set to repository. This parameter is used to specify a repository location that represents a connection entry. The connection can also be provided using the connection input port.
connection
This parameter is only available when the define connection parameter is set to predefined. This parameter is used for connecting to a database using a predefined connection. You can have many predefined connections. You can choose one of them using the drop down list. You can add a new connections or modify previous connections using the button next to the drop down list. You may also accomplish this by clicking on **Manage Database Connections... ** from the Tools menu in the main window. A new window appears. This window asks for several details e.g. Host, Port, Database system, schema, username and password. The Test button in this new window will allow you to check whether the connection can be made. Save the connection once the test is successful. After saving a new connection, it can be chosen from the drop down list of the connection parameter. You need to have a basic understanding of databases for configuring a connection.
database system
This parameter is only available when the define connection parameter is set to url. This parameter is used for selecting the database system in use.
database url
This parameter is only available when the define connection parameter is set to url. This parameter is used for defining the URL connection string for the database, e.g. 'jdbc:mysql://foo.bar:portnr/database'.
username
This parameter is only available when the define connection parameter is set to url. This parameter is used for specifying the username of the database.
password
This parameter is only available when the define connection parameter is set to url. This parameter is used for specifying the password of the database.
jndi name
This parameter is only available when the define connection parameter is set to jndi. This parameter is used for specifying the JNDI name for a data source.
query
This parameter is used for specifying the SQL query which will be executed on the specified database.
prepare statement
If checked, the statement is prepared, and '?' can be filled in using the parameters parameter.
parameters
This parameter specifies the Parameters to insert into '?' placeholders when the statement is prepared.