Used for easier work with databases. The component executes one action with the specified table. It can be used for select, adding, updating, and removal of entries using the specified condition. A list of variables is used for exchanging data with the entries. Each variable equals a field in the table. When setting a variable for a field, field type is to be specified. Field type determines the direction of data exchange between the variable and the field.

Data Source

List of available data sources.

Caching

The description of the Caching Tab see in the article Caching in components working with data servers.

Select Table

Specifies the table in the data source, which the action is to be executed for.

Action

Sets the action to be executed for the specified table. The following actions are possible:

Select Data - Get one entry from the table. The values are returned into the result-type fields. For entry selection, fields of the key, autokey, and search types are used.

Insert Data - Add one entry to the table. For fields of the autokey type, maximum field values are returned.

Update Data - Update several existing entries in the table. For entry selection, fields of the key and search types are used.

Delete Data - Remove several entries from the table. For entry selection, fields of the key, autokey, and search types are used.

Insert or Update Data - Update existing entries in the table or add a new one. For entry selection, fields of the search types are used. If some entries are found, they will be updated. If there are no entries, a new entry will be added, like with Update Data.

Field, Type, Variable

Sets the correspondence list for table fields and variables, as well as field types. The following field types are used:

Unused - The field is not used when exchanging data.

Key - The variable is used for selection by key field.

Autokey  - The variable is used to get automatic counter value.

Search - The variable is used for selection by variable field.

Update - The variable contains a value stored in the field. If an expression in the Variable column can be put in parentheses, it is substituted as a part of SQL expression, not as a variable value. For the format for Insert or Update Data, see the note.

Result - The variable is used to store the field value upon action execution.

Remarks

●For the Insert or Update Data action, the / symbol can be used as a separator of expressions used in Insert and Update

Syntax

Insert expression/Update expression.

Examples:

 (1)/(MSGCOUNT+1)

 StartNo/(MSGCOUNT+1)

 MsgNo/()

 

●If there is no separator, the same expression is used both for Insert and for Update.

●If there is no need to change field value when updating, leave empty expression or empty parentheses after the separator.

---

Detailed description of the actions

The actions used in the component are converted into one or several SQL requests. Below are some examples of how this works.

Assume we have a table named CLIENT, which stores client's name and surname, his e-mail address, date of registration, and number of messages received from that client.

The examples below show what requests will be executed for each action.

---

Select Data

To parse data from the table into variables, the Select Data action is used. Assume you need to find out identifier and registration date of a registered client based on his e-mail address.

Assigning fields to values

The component will execute the following request:

SELECT ID, REGDATE FROM CLIENT WHERE EMAIL='<%SenderEmail%>';

The request result will be placed into variables assigned to fields to be obtained in the request, i.e. Id and RegDate.

---

Insert Data

To add data about a client, the Insert Data is to be used. Assume you need to parse client's name and surname from a message into variables, the current date is to be written to the REGDATE field, and "1" is to be written into the MSGCOUNT field. Besides, it is necessary to get identifier of the new entry about a client.

Assigning fields to values:

The component will execute the following requests:

INSERT INTO CLIENT (REGDATE, FIRSTNAME, LASTNAME, EMAIL, MSGCOUNT)

VALUES('<%RegDate%>', '<%FirstName%>', '<%LastName%>','<%Email%>', 1);

SELECT MAX(ID) FROM CLIENT

WHERE FIRSTNAME='<%FirstName%>' AND LASTNAME='<%LastName%>' AND EMAIL='<%Email%>';

The result of the last request will be stored in variable of the autokey type, i.e. Id.

---

Update Data

To update data in the table entries, the Update Data action is used. Assume you need to increase message counter for a client with given e-mail address by 1.

Assigning fields to values:

The component will execute the following request:

UPDATE CLIENT SET MSGCOUNT=(MSGCOUNT+1)WHERE EMAIL='<%Email%>';

---

Delete Data

To delete, the Delete Data action is to be executed. Assume you need to delete entries about clients named John.

Assigning fields to variables:

The component will execute the following request:

DELETE FROM CLIENT WHERE FIRSTNAME='<%FirstName%>';

---

Insert or Update Data

To add entries with checking for existence, use the Insert or Update Data action. Assume you need to increase the message counter for a client with given name, surname, and e-mail address, and if there is no such client in the table, to create a new entry. Identifier of the found or new client is to be stored in the Id variable.

Assigning fields to variables:

The components will execute the following requests:

SELECT ID FROM CLIENT

WHERE FIRSTNAME='<%FirstName%>' AND LASTNAME='<%LastName%>' AND EMAIL='<%Email%>';

If no identifier is found, the component will execute the new entry adding request:

INSERT INTO CLIENT (REGDATE, FIRSTNAME, LASTNAME, EMAIL, MSGCOUNT)

VALUES('<%RegDate%>', '<%FirstName%>', '<%LastName%>','<%Email%>', 1);

Otherwise, the existing entry update request will be executed:

UPDATE CLIENT SET MSGCOUNT=MSGCOUNT+1

WHERE ID=[found with SELECT ID];

Finally, the request for getting autokey field will be executed:

SELECT MAX(ID) FROM CLIENT;

The result of the last request will be stored in the autokey type variable, i.e. Id.

Remarks

To use this component, you must have Microsoft Data Access Components 2.5 or newer installed on your computer. For more details about MSDAC visit the Universal Data Access web-site.

See also

Database Access

Database Iterator

Processors