Guide Section: Processing Object Data | EASAP Tree: DATA PROCESSING Branch / EVENT PROCESSING Branch / PROCESSES Branch

WRITE performs a write action in the DATABASE specified in its Parent DATABASE object. The WRITE actions can take one of three forms:

• Insert new data into the database
• Update existing data with new data
• Delete existing data in the database

WRITE uses the Structured Query Language (SQL) to define a SQL Query in either the Text: or File: parameters. The SQL Query must start with an INSERT, UPDATE or DELETE statement.

Note: Authors using a DATABASE are expected to be proficient with SQL and to have a good understanding of the content of their database.

The parameter symbols (question mark - '?') within the SQL statement will take their data from the list objects specified in order in the Parameters: parameter, introducing an abstraction layer between the statement and the actual data. By parameterizing the SQL statement this way, you can insert multiple rows with a single object while making it easier to author and maintain.

Additional benefits of using the Parameters: parameter are avoiding parsing errors and unexpected behaviours around executing SQL statements made up of text that include a single quote sign ( ' ), other special characters or using inline DORs.

Note: There must be the same number of LIST's specified in the Parameters: parameter as there are ?’s in the SQL statement.

The basic form of an INSERT statement is as follows:

INSERT INTO tablename (columnames) VALUES (values)

Where tablename will be a table found in the database, columnnames will be a comma separated list of columns found in the specified table, and values will be a comma separated list of values to be inserted into the specified columns.

Additional SQL syntax, such as WHERE and SELECT clauses, can be included in your SQL query. To insert multiple rows with a single WRITE, you can use a parameterized SQL statement.

The basic form of an UPDATE statement is as follows:

UPDATE tablename SET columnname = value WHERE expression

Where tablename will be a table found in the database, columnname will be a column found in the specified table, and value will be the value to change the existing values to in the specified column. Also, a WHERE clause is shown above to demonstrate how to narrow the UPDATE down to only rows in the specified column in which the expression evaluates to a true value. Additional SQL syntax, such as SELECT clauses, can be included in your SQL query.

The basic form of a DELETE statement is as follows:

DELETE FROM tablename WHERE expression 

Where tablename will be a table found in the database and expression is logical expression that will cause a complete row across all columns to be deleted when it evaluates to a true value.

Note: Unlike the SQL used in a READ, the SQL used in a WRITE is potentially dangerous, especially the DELETE statement, because it makes changes to the database. For instance a DELETE statement without a WHERE clause will delete all data in the tables specified. Therefore, you should always test SQL used in a WRITE thoroughly on a test database before use on a production database.

WRITE
Optional Parameters:
Text:	Text of SQL query that begins with an INSERT, UPDATE, or DELETE statement. DORs allowed
File:	File containing text of SQL query that begins with an INSERT, UPDATE, or DELETE statement. DORs allowed
Parameters:	Comma-separated list of LIST's to replace ?’s used in the SQL query
Delimiter:	Character to act as delimiter in DORs in Text: or File: parameters. default→%.
Encoding:	Text encoding used in File: default→US-ASCII
Diagnostics:	An explicit scalar DOR which can optionally be set to contain the contents of a database error message. Setting this disables the effect of the On Error: parameter under DATABASE ACTION.
Error Occurred:	An explicit scalar DOR which can optionally be set to record whether the query was successful. It will be set to either ERROR or NO ERROR. Setting this disables the effect of the On Error: parameter under DATABASE ACTION.
Empty as Null:	default→FALSE or TRUE. Change sometext,'',sometext,,sometext to sometext,NULL,sometext,NULL,sometext in an INPUTBOX for the VALUES clause of the SQL query (ie. adjacent commas and empty single-quotes map to NULL).