- Excel Desktop Client new !
The WRITE actions can take one of three forms:
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 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.
The basic form of an INSERT statement is as follows:
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:
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:
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.
|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.