[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Help-glpk] Beware of using Access and Excel tables with ODBC (SQL state
From: |
Noli Sicad |
Subject: |
[Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg |
Date: |
Fri, 12 Feb 2010 05:53:17 +0700 |
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
- [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Noli Sicad, 2010/02/11
- [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg,
Noli Sicad <=
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Andrew Makhorin, 2010/02/11
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Noli Sicad, 2010/02/11
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Noli Sicad, 2010/02/11
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Noli Sicad, 2010/02/12
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Andrew Makhorin, 2010/02/12
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Noli Sicad, 2010/02/12
- Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Andrew Makhorin, 2010/02/12
Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg, Andrew Makhorin, 2010/02/11