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

[Noli Sicad]

Hi,

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
and output of the models runs 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

~~~~~~~
Running transp_odbc_sqlite3.mod

>C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m 
>"transp_odbc_sqlite3.mod"
GLPSOL: GLPK LP/MIP Solver, v4.42
Parameter(s) specified in the command line:
 --cover --clique --gomory --mir -m transp_odbc_sqlite3.mod
Reading model section from transp_odbc_sqlite3.mod...
Reading data section from transp_odbc_sqlite3.mod...
104 lines were read
Reading plants...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT PLANT, CAPA AS CAPACITY FROM transp_capa
Reading markets...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT MARKET, DEMAND FROM transp_demand
Display statement at line 61
b['New York'] = 325
b[Chicago] = 300
b[Topeka] = 275
Reading dist...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
SELECT LOC1, LOC2, DIST FROM transp_dist
Display statement at line 72
d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8
d['San Diego','New York'] = 2.5
d['San Diego',Chicago] = 1.8
d['San Diego',Topeka] = 1.4
Generating cost...
Generating supply...
Generating demand...
Model has been successfully generated
GLPK Simplex Optimizer, v4.42
6 rows, 6 columns, 18 non-zeros
Preprocessing...
5 rows, 6 columns, 12 non-zeros
Scaling...
 A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
Problem data seem to be well scaled
Constructing initial basis...
Size of triangular part = 5
      0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
*     4: obj =  1.561500000e+002  infeas = 0.000e+000 (0)
*     5: obj =  1.536750000e+002  infeas = 0.000e+000 (0)
OPTIMAL SOLUTION FOUND
Time used:   0.0 secs
Memory used: 0.1 Mb (133563 bytes)
Writing result...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3
DELETE FROM transp_result
INSERT INTO transp_result VALUES (?,?,?)
Model has been successfully processed
>Exit code: 0    Time: 0.210


Running the transp_odbc_xls.mod

>C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m 
>"transp_odbc_xls.mod"
GLPSOL: GLPK LP/MIP Solver, v4.42
Parameter(s) specified in the command line:
 --cover --clique --gomory --mir -m transp_odbc_xls.mod
Reading model section from transp_odbc_xls.mod...
Reading data section from transp_odbc_xls.mod...
96 lines were read
Reading plants...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT PLANT, CAPA AS CAPACITY FROM [transp_capa$]
Reading markets...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT * FROM [transp_demand$]
Display statement at line 55
b[Chicago] = 300
b['New York'] = 325
b[Topeka] = 275
Reading dist...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT * FROM [transp_dist$]
Display statement at line 64
d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8
d['San Diego',Chicago] = 2.5
d['San Diego','New York'] = 1.8
d['San Diego',Topeka] = 1.4
Generating cost...
Generating supply...
Generating demand...
Model has been successfully generated
GLPK Simplex Optimizer, v4.42
6 rows, 6 columns, 18 non-zeros
Preprocessing...
5 rows, 6 columns, 12 non-zeros
Scaling...
 A: min|aij| = 1.000e+000  max|aij| = 1.000e+000  ratio = 1.000e+000
Problem data seem to be well scaled
Constructing initial basis...
Size of triangular part = 5
      0: obj =  0.000000000e+000  infeas = 9.000e+002 (0)
*     4: obj =  1.662750000e+002  infeas = 0.000e+000 (0)
*     7: obj =  1.332000000e+002  infeas = 0.000e+000 (0)
OPTIMAL SOLUTION FOUND
Time used:   0.0 secs
Memory used: 0.1 Mb (133559 bytes)
Writing result...
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
INSERT INTO [transp_result$] VALUES (?,?,?)
Model has been successfully processed
>Exit code: 0    Time: 0.311