Action keywords cause an action to be taken – connecting to a database, querying a database, writing to a database or internalizing what has been read from a database. Querying a database and writing to a database require that informational keywords have been set so that Vensim knows which fields to extract information from.
:CONNECT database identifier
Identifies the database to connect to. The connect string is a database identifier along with other information. The easiest way to get a connect string is to use the menu item Insert>ODBC Connection while working in the text editor. You can also use a placeholder (such as DATA1) for the database identifier. In this case the first time it is encountered the user will be queried to select a data source. After that the selected data source will be associated with DATA1 until Vensim is closed.
The connect string is usually quite long and therefore often uses a continuation character which is a backslash \ right at the end of the line.
The :CONNECT keyword should be the first to appear. There can only be one active connection at a time. If a connection is already open it will be closed and the new connection open. If the :CONNECT keyword was missing or the connection could not be completed the remaining statements will not be able to execute. Whatever connection is open will automatically be closed after the file is processed.
:EXECUTE statement
Causes the specified SQL statement to be executed against the ODBC source specified in the most recent :CONNECT line. A long statement may be continued on the next line by using a backslash \ at the end of a line.
The statement is passed through directly as is without being parsed by Vensim except that if there are any :FIELD lines preceding the :EXECUTE line they will be added to the end of the statement separated by AND. If the statement has the word WHERE in it there will be a leading AND, otherwise Vensim will insert WHERE before the first :FIELD pair. See the example on storing scenario results below.
:INSERT tablename
Output Only: Specifies that the values will be put to the database using an INSERT statement (as opposed to an UPDATE statement). The insert statement will take the form
INSERT INTO tablename(field1,field2,field3…) VALUES(value1,valu2,value3…)
Where the field value combinations come from the :FIELD, :VAR, :SUB and possibly :TIME lines.
:INTERNALIZE
Input Only. Causes all data accessed so far to be internalized in the model. This is useful if you are reading from multiple data sources that might have conflicting sets of value for Lookups and Data variables. Without this statement each x,y (Time,value) pair associated with a variable is added to a list of pairs. The :INTERNALIZE statement will close all those lists and start building them again.
All data is automatically internalized after the completion of a .vdi file.
:SELECT tablename clause
Specifies the name of the table from which to extract information. The optional clause would typically be a WHERE qualifier that would restrict the dataset fetched of be used to determine the records updated. You can also let Vensim build the WHERE clause by specifying :FIELD lines.
For output :SELECT can only be used with :VARLOOKUP and :VARLIST. Everything else must use a :UPDATE or :INSERT command.
It is best to choose table names that do not contain any special characters or embedded spaces. If a table name does have special characters you should put double quotes " around it – though this may not work for all ODBC sources.
:UPDATE tablename clause
Output Only: Specifies the name of the table to write to and that the writing will be done using an UPDATE (as opposed to INSERT) statement. Update will change only the VALUE field, with everything else being part of the WHERE clause. The WHERE clause will be constructed from the :VAR, :SUB and :FIELD values and also, for time series and x,y pairs, the X or TIME value. Normally UPDATE would be used only to record constants or final values because it is much easier to delete and insert new time series values.
