Top
Top
LibraryEASYProcess Reference ManualProcessesServicesDBWorkshopUpdate

Update Overview

An “Update” is a service that can be used to run a SQL update statement. This means the statement targets already existing records in a specified table and will update the values in certain columns of the table in the desired way.

The benefit of using an Update to an ExecuteUpdate (another service that can be used to run an update statement), is that you can enter the desired table and where clause and EASYProcess will build the SQL statement for you. This does not require that the developer be very familiar with the SQL syntax.

Update services build the query for you given the table and where clause. It follows the pattern:

UPDATE [Table] SET [Column1]=[Value1], [Column2]=[Value2], …

WHERE [WhereClause]

The drawback of the Update service is that EASYProcess will always use this pattern and requires that the WhereClause be predefined in the Keys. Sometimes a developer would like to build the update statement based on XSLT functions. A developer also might just feel more comfortable writing the statement themselves which gives more control. In these examples an ExecuteUpdate service might be a better fit.

Since EASYProcess is in charge of maintaining the query pattern in an Update service, it is able to do things for us, like parameterize 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.


Update Use Examples
Workshop:DBWorkshop
Service:Update
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.

FileName"FileName" is the name of the database table on which we would like to run the update.

KeysThe Keys create the WHERE Clause of the query. The keys are concatenated to form the following pattern: [AndOr1][Key1][AndOr2][Key2]... The AndOr values determine the order in which the keys are run. ANDs are always evaluated before ORs. This is similar to the way a BinaryDecision service works. This is the container node that contains all the Key nodes. It does not accept any input, but you can Right-Click to add new Keys (Key#0, Key#1, etc.). Each Key below this node is built into the query as a Where Clause.

KeyOnce EASYProcess builds each Key it will have the following format: [Column] [Operator] [Value]. Whether or not the Value is encased in single quotes is determined by the selected Type. "Key" is a container node which accepts no input but represents a single Key that can contain information related to a key. Each Key node is indexed.

AndOr"AndOr" is used when we are dealing with more than one key. Valid values are "And" and "Or". If this value is left blank it defaults to "And". The AndOr values determine the order in which the keys are run. ANDs are always evaluated before ORs.

ColumnThe column to be referenced from the table in the query.

OperatorThe operator to be used in the Key.

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

ValueThe value which we will compare against the "Column" of the this Key.

ValuesThe "Values" are all the updates against the table that will occur. This is the container node that contains all the "Value" nodes. It does not accept any input, but you can Right-Click to add new Values (Key#0, Key#1, etc.). Each Value below this node is built into the update statement.

ValueA "Value" specifies both the column name and the value to be updated in that column in the update statement. "Value" is a container node which accepts no input but represents a single Value that can contain information related to a Value. Each Value node is indexed.

ColumnThe column in the table the value will be updated.

TypeThis is the data type of the "Val". This determines whether the "Val" is encased in single quotes.

ValThis is the actual data which is updated in the specified column.

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

Outputs
NoOfRecordsUpdatedA numeric value is returned when the service completes that indicates the number of records updated.
QueryThe exact update statement used to update the records.

Update Use Examples

Basic Use Example

In this example the Update service is used to update existing records in a table (AddressBook). The where clause is defined by the Key in the service. Here, the where clause will become:

WHERE AddressId=[User’s Address Id]

In this particular table, the AddresId is the primary key, so this query will find only one record to update. However, in other use cases, the WhereClause could be used to filter down to multiple records to update, instead of just one.

Each of the “Values” specified in the Update service are the actual values that will be inserted into the filtered records. All the “Column” entries must be columns that already exist in the specified table (AddressBook).

Here is the final (parameterized) query that is created from this service.

UPDATE AddressBook SET

Name = @Name0,

Description = @Name1,

AddressLine1 = @Name2,

City = @Name3,

State = @Name4,

Zip = @Name5,

Phone = @Name6,

BranchPlant = @Name7

WHERE AddressId = @Name8


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