SECTION 10.7 STANDARD AND BUILT-IN TABLE HANDLERS 199
The first string tells AMPL that data transfers using this table declaration should employ
the standard ODBC handler. Subsequent strings then provide directions to that handler.
The second string identifies the external database file that is to be read or written upon
execution of read table table-name or write table table-name commands. There
are several possibilities, depending on the form of the connection-spec and the configura-
tion of ODBC on your computer.
If the connection-spec is a filename of the form name.ext, where ext is a 3-letter
extension associated with an installed ODBC driver, then the named file is the database
file. This form can be seen in a number of our examples, where filenames of the forms
name.mdb and name.xls refer to Access and Excel files, respectively.
Other forms of connection-spec are more specific to ODBC, and are explained in
online documentation. Information about your computer’s configuration of ODBC
drivers, data source names, file data sources, and related entities can be examined and
changed through the Windows ODBC control panel.
The third string normally gives the name of the relational table, within the specified
file, that is to be read or written upon execution of read table or write table com-
mands. If the third string is omitted, the name of the relational table is taken to be the
same as the table-name of the containing table declaration. For writing, if the indi-
cated table does not exist, it is created; if the table exists but all of the table
declaration’s data-specs have read/write status OUT, then it is overwritten. Otherwise,
writing causes the existing table to be modified; each column written either overwrites an
existing column of the same name, or becomes a new column appended to the table.
Alternatively, if the third string has the special form
"SQL=sql-query"
the table declaration applies to the relational table that is (temporarily) created by a state-
ment in the Structured Query Language, commonly abbreviated SQL. Specifically, a rela-
tional table is first constructed by executing the SQL statement given by sql-query, with
respect to the database file given by the second string in the table declaration’s string-
list. Then the usual interpretations of the table declaration are applied to the con-
structed table. All columns specified in the declaration should have read/write status IN,
since it would make no sense to write to a temporary table. Normally the sql-query is a
SELECT statement, which is SQL’s primary device for operating on tables to create new
ones.
As an example, if you wanted to read as data for diet.mod only those foods having
a cost of $2.49 or less, you could use an SQL query to extract the relevant records from
the Foods table of your database:
table cheapFoods IN "ODBC" "diet.mdb"
"SQL=SELECT * FROM Foods WHERE cost <= 2.49":
FOOD <- [FOOD], cost, f_min, f_max;
Then to read the relevant data for parameter amt, which is indexed over nutrients and
foods, you would want to read only those records that pertained to a food having a cost of