Top
Top
LibraryEASYProcess Reference Manual 5ProcessesServicesDBWorkshopInsert

Insert Overview

An “Insert” is a service that can be used to run a SQL Insert statement. This means the statement will create new records in the specified table.

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

Insert services build the query for you given the table and values. It follows the pattern:

INSERT INTO [Table] ([Column1], [Column2], ...)

WHERE ([Value1], [Value2], ...)

The drawback of the Update service is that EASYProcess will always use this pattern and requires that the Values be predefined. Sometimes a developer would like to build the Insert 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 SQL statement pattern in an Insert 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.


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

ValuesThe "Values" are all the inserts 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 insert statement.

ValueA "Value" specifies both the column name and the value to be inserted in that column in the insert 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 inserted into.

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 inserted into 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
NoOfRecordsInsertedA numeric value is returned when the service completes that indicates the number of records inserted.
QueryThe exact insert statement used to insert the records.

Insert Use Examples

Basic Use Example

In this example, we are inserting a new record into an existing table, EPUserSessions. The columns specified are all existing columns in the table.

Note: you must insert values into all columns that cannot be null in the table. For example, the primary key of the table can never be null.

This table’s primary key is the UserSessionId which we are inserting a unique key into

.


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