Syntax of the READ and WRITE Statements

READ and WRITE statements

In READ and WRITE statement you can specify the data source type, what data will be transferred, and in what mode. The syntax of the statements reflect these aspects.

Syntax

read-write-statement:

image/svg+xmlWRITE READ selection FROM TO FILE TABLE data-source IN DENSE REPLACE COLUMNS ROWS BACKUP MERGE INSERT MODE CHECKING FILTERING binding-tuple IN identifier , WHERE SUFFIX = element-expression IN set-expression ;

selection:

image/svg+xmlbinding-tuple IN identifier data-selection ,

Data sources

The data source of a READ or WRITE statement in AIMMS can be either

  • a File represented by either

    • a File identifier,

    • a string constant, or

    • a scalar string reference,

  • a TABLE represented by either

    • a DatabaseTable identifier,

    • an element parameter with a range that is a subset of the predeclared set AllDatabaseTables

Strings for file data sources refer either to an absolute path or to a relative path. All relative paths are taken relative to the project directory.

Examples

Assuming that UserSelectedFile is a File identifier, and UserFilename a string parameter, then the following statements illustrate the use of strings and File identifiers.

read from file "C:\Data\Transport\initial.dat" ;
read from file "data\initial.dat" ;
read from file UserFileName ;
read from file UserSelectedFile ;

Specifying a selection

The selection in a READ or WRITE statement determines which data you want to transfer from or to a text file, or database table. A selection is a list of references to sets, parameters, variables and constraints. During a WRITE statement, AIMMS accepts certain restrictions on each reference to restrict the amount of data written (as explained below). Note, however, that AIMMS does not accept all types of restrictions which are syntactically allowed by the syntax diagram of the READ and WRITE statements.

Default selection

If you do not specify a selection during a READ statement, AIMMS will transfer the data of all identifiers stored in the table or file that can be mapped onto identifiers in your model. If you do not specify a selection for a WRITE statement to a text

file, all identifiers declared in your model will be written. When writing to a database table, AIMMS will write data for all columns in the table as long as they can be mapped onto AIMMS identifiers.

Filtering the selection

You can apply the following filtering qualifiers on READ and WRITE statements to restrict the data selection:

  • the FILTERING or CHECKING clauses restrict the domain of all transferred data in both the READ and WRITE statements, and

  • an arbitrary logical condition can be imposed on each individual parameter and variable in a WRITE statement.

FILTERING versus CHECKING

You can use both the FILTERING and CHECKING clause to restrict the tuples for which data is transferred between a data source and AIMMS. During a WRITE statement there is no difference in semantics, and you can use both clauses interchangeably. During a READ statement, however, the FILTERING clause will skip over all data outside of the filtering domain, whereas the CHECKING clause will issue a runtime error when the data source contains data outside of the filtering domain. This is useful feature for catching typing errors in text data files.

Examples

The following examples illustrate filtering and the use of logical conditions imposed on index domains.

read Distance(i,j) from table RouteTable
     filtering i in SourceCities, (i,j) in Routes;

write Transport( (i,j) | Sum(k, Transport(i,k)) > MinimumTransport )
      to table RouteTable ;

Advanced filtering on records

If you need more advanced filtering on the records in a database table, you can use the database to perform this for you. You can

  • define views to create temporary tables when the filtering is based on a non-parameterized condition, or

  • use stored procedures with arguments to create temporary tables when the filtering is based on a parameterized condition.

The resulting tables can then be read using a simple form of the READ statement.

Merge, replace or backup mode

AIMMS allows you to transfer data from and to a file or a database table in merge mode, replace mode or insert mode. If you have not selected a mode in either a READ or WRITE statement, AIMMS will transfer the data in replace mode by default, with one exception: when reading from a case difference file that was generated by CaseCreateDifferenceFile function with diffTypes argument equal to elementReplacement, elementAddition or elementMultiplication, the file is always read in merge mode, so that the diffTypes can be applied in a sensible way.
When you are writing data to a text data file, AIMMS also supports a backup mode. The insert mode can speed up writing to databases.

Reading in merge mode

When AIMMS reads data in merge mode, it will overwrite existing elements for all read identifiers, and add new elements as necessary. It is important to remember that in this mode, if there is no data read for some of the existing elements, they keep their current value.

Writing in merge mode

When AIMMS writes data in merge mode, the semantics is dependent on the type of the data source.

  • If the data source is a text file, AIMMS will append the newly written data to the end of the file.

  • If the data source is a database table, AIMMS will merge the new values into the existing values, creating new records as necessary.

Reading in replace mode

When AIMMS reads data in replace mode, it will empty the existing data of all identifiers in the identifier selection, and then read in the new data.

Writing in replace mode

When AIMMS writes data in replace mode, the semantics is again dependent on the type of the data source.

  • If the data source is a text file, AIMMS will overwrite the entire contents of the file with the newly written data. Thus, if the file also contained data for identifiers that are not part of the current identifier selection, their data is lost by the WRITE statement.

  • If the data source is a database table, AIMMS will either empty all columns in the table that are mapped onto identifiers in the identifier selection (default, REPLACE COLUMNS mode), or will remove all records in the table not written by this write statement (REPLACE ROWS mode). The REPLACE COLUMNS and REPLACE ROWS modes are discussed in more detail in Database Table Restrictions).

Writing in insert mode

Writing in insert mode is only applicable when writing to databases. Essentially, what it does is writing the selected data to a database table using SQL INSERT statements. In other words, it expects that the selection of the data that you write to the table doesn’t match any existing primary keys in the database table. If it does, AIMMS will raise an error message about duplicate keys being written. Functionally, the insert mode is equivalent to the replace rows mode, with the non-existing primary keys restriction. Especially when writing to database tables which already contain a lot of rows, the speed advantage of the insert mode becomes more visible.

Writing in backup mode

When you are transferring data to a text file, AIMMS supports writing in backup mode in addition to the merge and replace modes. The backup mode lets you write out files which can serve as a text backup to a (binary) AIMMS case file. When writing in backup mode, AIMMS

  • skips all identifiers on the identifier list which possess a nonempty definition (and, consequently, cannot be read in from a datafile),

  • skips all identifiers for which the property NoSave has been set, and

  • writes the contents of all remaining identifiers in such an order that, upon reading the data from the file, all domain sets are read before any identifiers defined over such domain sets.

Backup mode is not supported during a READ statement, or when writing to a database.

Writing data in a dense mode

Writing in dense mode is only applicable when writing to databases. Data in AIMMS is stored for non-default values only, and, by default, AIMMS only writes these non-default values to a database. In order to write the default values as well to the database table at hand, you can add the dense keyword before most of the WRITE modes discussed above. This will cause AIMMS to write all possible values, including the defaults, for all tuple combinations considered in the WRITE statement. Care should be taken that writing in dense mode does not lead to an excessive amount of records being stored in the database. The mode combination merge and dense is not allowed, because it is ambiguous whether or not a non-default entry in the database should be overwritten by a default value of AIMMS. For the same reason, the mode combination insert and dense is also not allowed.

Replacing sets

Whenever elements in a domain set have been removed by a READ statement in replace mode, AIMMS will not cleanup all identifiers defined over that domain. Instead, it will leave it up to you to use the CLEANUP statement to remove the inactive data that may have been created.

Domain filtering

For every READ and WRITE statement you can indicate whether or not you want domain filtering to take place during the data transfer. If you want domain filtering to be active, you must indicate the list of indices, or domain conditions to be filtered in either a FILTERING of CHECKING clause. In case of ambiguity which index position in a parameter you want to have filtered you must specify indices in the set or parameter reference.

Example

The following READ statements are not accepted because both Routes and Distance are defined over Cities \(\times\) Cities, and it is unclear to which position the filtered index i refers.

read Routes   from table RouteTable filtering i ;
read Distance from table RouteTable filtering i ;

This ambiguity can be resolved by explicitly adding the relevant indices as follows.

read (i,j) in Routes from table RouteTable filtering i ;
read Distance(i,j)   from table RouteTable filtering i ;

Semantics of domain filtering

When you have activated domain filtering on an index or index tuple, AIMMS will limit the transfer of data dependent on further index restrictions.

  • During a READ statement only the data elements for which the value of the given index (tuple) lies within the specified set are transfered. If no further index restriction has been specified, transfer will take place for all elements of the corresponding domain set.

  • During a WRITE statement only those data elements are transferred for which the index (tuple) is contained in the AIMMS set given in the (optional) IN clause. If no set has been specified, and the data source is a database table, the transfer is restricted to only those tuples that are already present in the table. When the data source is a text file

    the latter type of domain filtering is not meaningful and therefore ignored by AIMMS.

READ example

In the following two READ statements the data transfer for elements associated with i and (i,j), respectively, is further restricted through the use of the sets SourceCities and Routes.

read Distance(i,j) from table RouteTable filtering i in SourceCities ;
read Distance(i,j) from table RouteTable filtering (i,j) in Routes ;

WRITE example

In the following two WRITE statements, the values of the variable Transport(i,j) are written to the database table RouteTable for those tuples that lie in the AIMMS set SelectedRoutes, or for which records in the table RouteTable are already present, respectively.

write Transport(i,j) to table RouteTable filtering (i,j) in SelectedRoutes ;
write Transport(i,j) to table RouteTable filtering (i,j) ;

The FILTERING clause in the latter WRITE statement would have been ignored by AIMMS when the data source was a text data file.

Writing selected suffices using the WHERE clause

Using the WHERE clause of the WRITE statement you can instruct AIMMS, for all identifiers in the identifier selection, to write the data of either a specified suffix or a set of suffices to file, rather than their level values. The WHERE clause can only be specified during a WRITE statement to a FILE, and the corresponding set or element expression must refer to a subset of, or element in, the predefined set AllSuffixNames.

Example

The following WRITE statement will write the values of the .Violation suffix of to the file ViolationsReport.txt for all variables in the project.

write AllVariables to file "ViolationsReport.txt" where suffix = 'Violation';