|
GET ODBC SUBSCRIPT('database','field','tablewhere','prefix') GET SUBSCRIPT elements from a database via ODBC |
Scroll Prev Top Next More |
When used in a subscript equation this populates the subscript with values taken from the database. The query is made using ODBC and can be made agains any database that support ODBC.
Restrictions: Must appear directly following the : and not be followed by anything other than a mapping.
GET ODBC SUBSCRIPT is invoked when a model is checked. This will happen automatically when a .mdl file is open but only in response to an explicit model check (Ctrl+T or Model>Check Model) for .vmf or .vpm files. This function is not invoked as part of the simulation process.
All of the arguments to GET ODBC SUBSCRIPT must be literals (enclosed in single quotes ') or String Variables. 'database' names a database either with the complete ODBC descriptor (which tends to be quite long) or with an simple name. In the latter case, or if the descriptor is not valid, Vensim will query for a data source. Thereafter that data source will be used to databases named with the same descriptor until Vensim is closed and reopened. 'field' specified the field name containing the subscript elements. 'tablewhere' specifies the table name, optionally followed (after a space) by WHERE and a valid where clause, and the possibly an order statement. 'prefix' is used to modify the contents of the file. This is useful when some entries in th file are numeric and it can prevent the necessity to use quotation marks and it can also be helpful to in insuring that all elements have unique names.
The select statement generated has the form SELECT field from table so a WHERE clause would appear exactly as for a normal SELECT statement. An ORDER BY clause might follow that if order is important (see example with no WHERE clause just an ORDER BY clause).
Only Subscripts have a named ODBC function. For constants and data their use is specified via a .vdi file loaded during the simulation process as described in that chapter.
NOTES
| • | Changes in String Variables from .cin files (or from Venapp Commands) will not be used in determining any of the arguments. |
| • | The order of the subscript elements is that returned by the query. It might change if no ORDER BY clause is included. |
See also: GET DIRECT SUBSCRIPT, ODBC Database Connections
Units: Subscripts are not part of units checking.
Example
| FromODBC : GET ODBC SUBSCRIPT( |
| 'DSN=MS Access Database;DBQ=C:\Users\Public\Vensim\odbc\odbctest.mdb;DefaultDir=C:\Users\Public\Vensim\odbc;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;', |
| 'Name', |
| 'SUBDEF ORDER BY NAME', |
'ODBC')
This should work with the example Access database installed in its default location (under Windows Vista/7).
Availability:DSS only, not available on the Macintosh. Models using this will work in the Model Reader (and on the Macintosh) but no database query will be made.
