[Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg

[Noli Sicad]

Hi,

(Sorry if you got it twice. I am resenting it, I think the first one
fails. It since it does not appear in the mail archive help glpk. This
is shorter version)

I managed to implement transportation model sql (i.e. transp_odbc.mod
in ..glpk-4.4.2/examples/sql) using ODBC in Access, Excel and SQLite
and compare the results to the original transp.mod found in the
../glpk-4.42/examples folder).

Result of the original transp.mod

Variables;result
x[Seattle,New-York] = 50
x[Seattle,Chicago] =300
x[Seattle,Topeka] = 0
x[San-Diego,New-York] = 275
x[San-Diego,Chicago] = 0
x[San-Diego,Topeka]= 275

Access and Excel results (transp_odbc_mdb.mod and transp_odbc_xls.mod)

LOC1    LOC2    QUANTITY
Seattle Chicago 300.00
San Diego       New York        325.00
San Diego       Topeka  275.00


SQLite result (transp_odbc_sqlite3.mod)
LOC1    LOC2    QUANTITY
Seattle New York        50
Seattle Chicago 300
San Diego       New York        275
San Diego       Topeka  275

As you can see in the results, Access and Excel results are different
to the original transp.mod model but SQLite result is exactly the same
as the original transp.mod.

Reason for this variation of results in Access and Excel (I thought)
probably Microsoft ODBC drivers are not complaint to the SQL 92
standard (http://msdn.microsoft.com/en-us/library/ms711756%28VS.85%29.aspx).
No 2 primary keys are allowed in Access tables just an example. The
Excel ODBC driver does not support the use of SQL statements DELETE,
UPDATE, or ALTER (
http://www.stata.com/support/faqs/data/odbc_excel.html)

I suspected that the matrix formulation of the access and excel models
are entirely different from that of the original transp.mod model and
SQLite model (i.e. transp_odbc_sqlite3.mod) because SQL querying by
Microsoft ODBC drivers for Access and Excel. However, when converted
the 3 models into CPLEX format, the 3 models are exactly the same,
just ordering of the variables and constraints (see converted models below).

Anyway, BEWARE of using Access and Excel using OBDC (SQL statement) in MathProg.

I recommend using SQLite OBDC in MathProg (FYI, Android and Iphone use
SQLite :-)).

Anybody got opinion why Access and Excel model yielded different results?

Regards, Noli

~~~~~

\* Problem: transp_odbc_sqlite3 *\

Minimize
 cost: + 0.225 x(Seattle,'New_York') + 0.153 x(Seattle,Chicago)
 + 0.162 x(Seattle,Topeka) + 0.225 x('San_Diego','New_York')
 + 0.162 x('San_Diego',Chicago) + 0.126 x('San_Diego',Topeka)

Subject To
 supply(Seattle): + x(Seattle,'New_York') + x(Seattle,Chicago)
 + x(Seattle,Topeka) <= 350
 supply('San_Diego'): + x('San_Diego','New_York')
 + x('San_Diego',Chicago) + x('San_Diego',Topeka) <= 600
 demand('New_York'): + x(Seattle,'New_York') + x('San_Diego','New_York')
 >= 325
 demand(Chicago): + x(Seattle,Chicago) + x('San_Diego',Chicago) >= 300
 demand(Topeka): + x(Seattle,Topeka) + x('San_Diego',Topeka) >= 275

\* Problem: transp_odbc_xls *\
Minimize
 cost: + 0.225 x('San_Diego',Chicago) + 0.162 x('San_Diego','New_York')
 + 0.126 x('San_Diego',Topeka) + 0.153 x(Seattle,Chicago)
 + 0.225 x(Seattle,'New_York') + 0.162 x(Seattle,Topeka)

Subject To
 supply('San_Diego'): + x('San_Diego',Chicago)
 + x('San_Diego','New_York') + x('San_Diego',Topeka) <= 600
 supply(Seattle): + x(Seattle,Chicago) + x(Seattle,'New_York')
 + x(Seattle,Topeka) <= 350
 demand(Chicago): + x('San_Diego',Chicago) + x(Seattle,Chicago) >= 300
 demand('New_York'): + x('San_Diego','New_York') + x(Seattle,'New_York')
 >= 325
 demand(Topeka): + x('San_Diego',Topeka) + x(Seattle,Topeka) >= 275

\* Problem: transp_odbc_mdb *\

Minimize
 cost: + 0.225 x(Seattle,'New_York') + 0.153 x(Seattle,Chicago)
 + 0.162 x(Seattle,Topeka) + 0.162 x('San_Diego','New_York')
 + 0.225 x('San_Diego',Chicago) + 0.126 x('San_Diego',Topeka)

Subject To
 supply(Seattle): + x(Seattle,'New_York') + x(Seattle,Chicago)
 + x(Seattle,Topeka) <= 350
 supply('San_Diego'): + x('San_Diego','New_York')
 + x('San_Diego',Chicago) + x('San_Diego',Topeka) <= 600
 demand('New_York'): + x(Seattle,'New_York') + x('San_Diego','New_York')
 >= 325
 demand(Chicago): + x(Seattle,Chicago) + x('San_Diego',Chicago) >= 300
 demand(Topeka): + x(Seattle,Topeka) + x('San_Diego',Topeka) >= 275