• AEP Online Help Index

Simple Database Access

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. Every of variables equal field in the table. When setting a variable for a field, field type is to be specified. Field's type determines the direction of data exchange between the variable and the field.

Select Datasource

Opens the datasource selection dialogue.

Select Table

Specifies the table in the datasource, 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:

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.

Unused

The field is not used when exchanging data.

NB: 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/()

Notes:

• 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.

Field	Type	Description
ID	COUNTER	Client identifier
REGDATE	DATETIME	Registration date
FIRSTNAME	TEXT	Client name
LASTNAME	TEXT	Client surname
EMAIL	TEXT	Client e-mail address
MSGCOUNT	NUMBER	Number of received messages

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:

Field	Type	Expression
ID	Result	Id
REGDATE	Result	RegDate
FIRSTNAME	Unused
LASTNAME	Unused
EMAIL	Search	SenderEmail
MSGCOUNT	Unused

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:

Field	Type	Expression
ID	Autokey	Id
REGDATE	Update	RegDate
FIRSTNAME	Key	FirstName
LASTNAME	Key	LastName
EMAIL	Key	SenderEmail
MSGCOUNT	Update	(1)

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:

Field	Type	Expression
ID	Unused
REGDATE	Unused
FIRSTNAME	Unused
LASTNAME	Unused
EMAIL	Key	SenderEmail
MSGCOUNT	Update	(MSGCOUNT+1)

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:

Field	Type	Expression
ID	Unused
REGDATE	Unused
FIRSTNAME	Search	FirstName
LASTNAME	Unused
EMAIL	Unused
MSGCOUNT	Unused

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:

Field	Type	Expression
ID	Autokey	Id
REGDATE	Update	RegDate/()
FIRSTNAME	Search	FirstName
LASTNAME	Search	LastName
EMAIL	Search	Email
MSGCOUNT	Update	(1)/(MSGCOUNT+1)

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.

Note: To use this component, you should have Microsoft Data Access Components 2.5 or newer installed on your machine. For detailed information about MSDAC, visit the Universal Data Access web-site.

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

See also:

Database Access Database Iterator Processors