Top
Top
LibraryEASYProcess Reference ManualProcessesServicesDBWorkshopExecuteUpdate

ExecuteUpdate Overview

An “ExecuteUpdate” is a service that can be used to execute a SQL statement against a database.

The benefit of using an ExecuteUpdate to an Update or Delete service, is that you can type out your SQL statement and do complex inserts or deletes with unions and subqueries. The ExecuteUpdate will also be able to run multiple statements. This is useful when you want to break from the EASYProcess provided patterns of other services.

The drawback of an ExecuteUpdate is that EASYProcess isn’t aware of the pattern you are following. EASYProcess is in charge of maintaining the query pattern in services like Update or Delete, so it is able to do things for us, like parameterize the query. In ExecuteUpdate, the developer is responsible for the parameterization of the query.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks. These can happen when a value used in the query comes from the page in user entered data. It is good practice to parameterize all filter values, even the hard coded ones. This allows future developers to change the hard coded value to a dynamic one that could come from user input without having to first change the syntax of the query to use parameterization.

Even though the ExecuteUpdate gives the developer full control over the SQL statement that will execute, the ExecuteUpdate EASYProcess service does not expect a response. This means the service could be used for a SELECT statement, but the results will not be returned. If you want the control the ExecuteUpdate gives, but would like to write a SELECT statement, use RunQuery.


ExecuteUpdate Use Examples
Workshop:DBWorkshop
Service:ExecuteUpdate
Inputs
DbType"DbType" is the database type. This input is usually loaded from configuration variables.

ConnectionString"ConnectionString" is the connection string to the database. This input is usually loaded from configuration variables.

Query"Query" is the query we would like to execute.

ExecutionTimeout

ParametersThese are Parameters that are built by EASYProcess with the given input for each Parameter node. This is the container node that contains all the Parameter nodes. It does not accept any input, but you can Right-Click to add new Parameters (Parameter#0, Parameter#1, etc.). Each Parameter below this node can be used in our query.

ParameterThere must be a "@[Name]" existing somewhere in the "Query" which matches the assigned name of the parameter in order for EASYProcess to know where in the query this parameter must be placed. "Parameter" is a container node which accepts no input but represents a single Parameter that can contain Name, Value and DataType of the Parameter. Each Parameter node is indexed.

Name"Name" is the name we would like to assign to the parameter.

Value"Value" is the value we would like to assign to the parameter.

DataTypeThis is the data type of the "Value". This determines whether the "Value" is encased in single quotes.

ParametersXml"ParametersXml" is where we can pass parameters in XML format instead of through the EASYProcess-built parameters. This must have the following format:

CommandTimeout"CommandTimeout" is the timeout for query in milliseconds. After this time the service cancels execution of the query and throws an error.

Outputs
NoOfRecordsAffectedA numeric value is returned when the service completes that indicates the number of records affected (updated, deleted, inserted, etc).
QueryThe exact statement used to perform an action against the records.

ExecuteUpdate Use Examples

Insert Into Statement

In this example, the ExecuteUpdate is used to perform an Insert Into statement. The format for a statement like this is:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Multiple Statements in One ExecuteUpdate

Multiple SQL statements can be contained in one ExecuteUpdate service. In the example below, the following statements will run:

  • Update
  • Delete
  • Insert Into (multiple)


Powered by EASYProcess (© 2019 K-Rise Systems, Inc).