User Tools

Site Tools


WRITE

Guide Section: Processing Object Data
EASAP Tree:
DATA PROCESSING or EVENT PROCESSING or PROCESSES DATABASE


WRITE performs a write action in the DATABASE specified in its Parent DATABASE.

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 Text: or File:

The SQL query must start with an INSERT, UPDATE or DELETE statement.

An Author using a DATABASE must be proficient with SQL and have an understanding of the content of their database.


The Parameters: parameter

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.

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


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


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


DELETE Statement

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 destructive, 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 for 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, object references allowed
File:File containing text of SQL query that begins with an INSERT, UPDATE, or DELETE statement, object references allowed
Parameters:Comma-separated list of LIST's, each LIST will replace a ? character in the SQL query
Delimiter:Character to delimit any object references in Text: or File: (default→%)
Encoding:Text encoding used in File: (default→US-ASCII)
Diagnostics:The name of an new explicit SCALAR which, if set, will contain the contents of any database error message. Setting this disables the effect of the On Error: parameter under DATABASE ACTION.
Error Occurred:The name of an new explicit SCALAR which, if set, will contain a status string to indicate whether the query was successful, either ERROR or NO ERROR. Setting this disables the effect of DATABASE ACTION→On Error:
Empty as Null:Treat any 'empty single quote pair' or 'two adjacent commas' as a database NULL value, 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.