As is the case with most interface functions, @ODBC can export data as well as import it. Specifically, you can use the @ODBC function in the data and/or calc sections of a model to export set members and attribute values to ODBC data sources. In order to export solutions with @ODBC, you place calls to @ODBC in the data section of your model. These @ODBC export instructions are executed each time your model is solved.

Syntax Form 1

The first form of syntax for using @ODBC to export data is:

@ODBC( ['data_source'[, 'table_name'[, 'column_name_1'[,…,

  'column_name_n']]]]) = object_list;

The object_list is a list, optionally separated by commas, containing model objects (i.e., attributes, sets, or variables) that are to be exported to the ODBC data source. Object_list may contain up to one set and/or multiple set attributes. All set attributes in object_list must be defined on the same set. If object_list contains a set, then all attributes in object_list must be defined on this set. The data_source argument is the name of the ODBC data source containing the data table that will receive the exported values. The table_name argument is the name of the data table within the data source that will receive the data. Finally, the column_name arguments are the names of the receiving columns, or fields, in the data table table_name. Set attributes and primitive sets require one receiving column name each. Derived sets require one receiving column name for each dimension of the set. Thus, a two-dimensional derived set would require two receiving columns in a data table.

If the data_source argument is omitted, the model's title is used in its place (see the discussion of the TITLE statement). If table_name is omitted, the name of any set in the object_list is used in its place. If there is no set in object_list, then the name of the set on which the attributes in object_list are defined is used.

If the column_name arguments are omitted, LINGO will choose default names based on whether the corresponding object in object_list is either a set attribute, a primitive set, or a derived set. When the object to be initialized is a set attribute or a primitive set, LINGO will use the name of the object as the default column name. When the object is a derived set, LINGO will generate one default column name for each dimension of the derived set, with each name being the same as the parent set that the given dimension is derived from. As an example, a two-dimensional set named LINKS derived from the two primitive sets SOURCE and DESTINATION would default to being initialized from the two columns titled SOURCE and DESTINATION.

Keep in mind that set members are exported as text, while set attributes are exported as double precision floating point values.

Some examples of using @ODBC to export data values to an ODBC data source are:

Example 1:        @ODBC( 'TRANSPORTATION', 'LINKS', 'VOLUME') = VOLUME;

LINGO sends the values of the VOLUME attribute to the column also titled VOLUME in the data table LINKS in the ODBC data source TRANSPORTATION.

Example 2:        @ODBC() = NUMBER_WORKING;

All arguments to the @ODBC function have been omitted and will default to the model's title for the data source, the attributes parent set for the data table, and the attribute's name for the column name. So, assuming we have used the TITLE statement to name this model SCHEDULING, and the attribute NUMBER_WORKING is defined on the set SCHEDULES, then LINGO exports the attribute NUMBER_WORKING to the column also titled NUMBER_WORKING in the data table SCHEDULES in the ODBC data source SCHEDULING.

Syntax Form 2

The first form of syntax will generally be sufficient for most database export operations.  However, there may be times when you need to export only portions of the attributes, or you need to export quantities computed from the attribute values.  Our second form of syntax uses the @WRITEFOR reporting function to handle these more general cases:

@ODBC( 'data_source', 'table_name', 'column_name_1'[,…,

  'column_name_n']) = @WRITEFOR( setname

      [ ( set_index_list) [ | conditional_qualifier]] : output_obj_1[,…, output_obj_n]);

@WRITEFOR functions like any other set looping function in that, as a minimum, you will need to specify the set to loop over.  Optionally, you may also specify an explicit set index list and a conditional qualifier.  If a conditional qualifier is used, it is tested for each member of the looping set and output will not occur for any members that don't pass the test.  It's this feature of being able to base output on the results of a condition that distinguish this second style of syntax.

The list of output objects, of course, specifies what it is you want to output.  As with the first form of syntax, the output objects may be labels, set members and variable values.  However, you have additional latitude in that the output objects may now consist of complex expressions of the variable values (e.g., you could compute the ratio of two variables).  This is a useful feature when you need to report statistics and quantities derived from the variable values.  By placing these calculations in the data section, as opposed to the model section, you avoid adding unnecessary complications to the constraints of the model.

In general, you can do everything in the second form of syntax that you can do in the first, and more.  However, the first form has an advantage in that it can be very concise.

Some examples of using @WRITEFOR for ODBC exports follow:

Example 1:        @ODBC( 'TRANSPORTATION',

'SOLUTION', 'FROM', 'TO', 'VOLUME') =

 @WRITEFOR( LINKS( I, J) | VOLUME( I, J) #GT# 0:

  WAREHOUSE( I), CUSTOMER( J), VOLUME( I, J));

In this example, we exploit the ability to specify a conditional expression to weed zero shipments out of the export.  The nonzero values of the VOLUME attribute are sent to the SOLUTION table in the TRANSPORTATION data source.   The shipping warehouse set name is placed in column FROM, the receiving customer set name goes to column TO, and the shipping volume for the arc in placed in the VOLUME column.

Example 2:        @ODBC( 'STAFFREP', 'STATS', 'RATIO') =

  @WRITEFOR( DEPARTMENTS( D): ON_DUTY( D) / NEEDS( D));

Here, we make use of @WRITEFOR's ability to perform computations to compute a ratio of two variables.  Specifically, the ratio of on-duty staff to staffing needs by department is placed into the column RATIO of table STATS in data source STAFFREP.