Re: [Help-glpk] How to use Access mdb in Windows with ODBC in MathProg (GMPL)

[Noli Sicad]

Hello Xypron,

Thanks for this tip. I applies as well to Excel but not on sqlite3.

I tried it with sqlite3 ODBC driver and it seems not to work.

table markets IN "ODBC"
  'DRIVER={SQLite3 ODBC Driver};dbq=transp.db3'
  'transp_demand' :
  J <- [ MARKET ], b ~ DEMAND;

It is still looking for the Database Name or the file transp.db3.

I tried this below but still does not work

table markets IN "ODBC"
  'DRIVER={SQLite3 ODBC Driver};DNS=transp.db3'
  'transp_demand' :
  J <- [ MARKET ], b ~ DEMAND;

Any tip on sqlite3, not setting up ODBC. Sqlite3 is really good since
it cross platform format and ODBC driver is open source as well.

I will send you the otago.mdb later for the bug fixing.

Thanks again.

Regards, Noli



On 2/14/10, glpk xypron <address@hidden> wrote:
> Hello Noli,
>
> it is not necessary to set up a ODBC data source, to use
> a MS Access data base. You can use the following syntax:
>
> table ti IN 'ODBC'
>   'DRIVER={Microsoft Access Driver (*.mdb)};dbq=glpk.mdb'
>   'SELECT * FROM sudoku'
>   'WHERE ID = ' & id :
>   fields <- [COL, LIN], givens ~ VAL;
>
> where glpk.mdb is the MS Access data base.
>
> Best regards
>
> Xypron
>
>
> -------- Original-Nachricht --------
>> Datum: Thu, 11 Feb 2010 06:09:16 +0700
>> Von: Noli Sicad <address@hidden>
>> An: "address@hidden" <address@hidden>
>> Betreff: [Help-glpk] How to use Access mdb in Windows with ODBC in
>> MathProg     (GMPL)
>
>> Hi,
>>
>> I am posting "How to use Access mdb in Windows with ODBC in MathProg
>> (GMPL)" as part of how to .. in MathProg / GLPK. Probably this will
>> encourage glpk/mathprog users as well to post "How to .. in MathProg /
>> GLPK.
>>
>> Anyway, here is my "How to use Access mdb in Windows with ODBC in
>> MathProg (GMPL)"
>>
>> Transp_odbc_mdb.mod as an example
>>
>> Setup ODBC for Access mdb in Windows XP (e.g. transp.mdb)
>>
>> 1. Click Start -> Settings -> Control Panel -> Adminstrative Tools (2
>> clicks)
>> 2. Data Sources (ODBC) (2x clicks) -> Select MS Access Database then click
>> Add
>> 3. Select MS Access Database driver (*.mdb) and click Finish
>> 3. Type 'glpk' in Data Source Name, Description: "Access Mdb for
>> MathProg" then OK
>> 4.  Click 'Configure', then Click 'Advanced'
>> 5. Login Name: glpk
>> 6. Password: gnu  then Click OK
>> 7. Click Select.. in Database, then locate the your Access, mdb (e.g.
>> transp.mdb). Better put it in your model folder (e.g. .../sql)
>>
>> Running the transp_odbc_mdb.mod using the transp.mdb
>> 8. Download transp_odbc_mdb.rar in this email, unzip it and put it in
>> glpk4.42/examples/sql folder
>> 9. Run transport_odbc_mdb.mod in GUSEK and see your results of the model
>>
>> Tip / note: In creating tables in Access, no 2 primary keys are
>> allowed.  The solution this problem is just ignored if tables require
>> 2 primary keys.  Don't put any key in that table as in the case of the
>> transp.sql script presented by Xypron and Andrew (I think).
>>
>> See the result of the run below.
>>
>> I hope this would put a nail to this Access (*.mdb) database question
>> in MathProg :-).
>>
>> Next,  How to use sqlite and spatialite in Windows with ODBC in MathProg
>> (GMPL)
>>
>> Andrew, would it be possible to include this example in the glpk/gmlp
>> in the next release?
>>
>> Noli
>>
>> ~~~~~~~~~~~
>> >C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m
>> "transp_odbc_mdb.mod"
>> GLPSOL: GLPK LP/MIP Solver, v4.42
>> Parameter(s) specified in the command line:
>>  --cover --clique --gomory --mir -m transp_odbc_mdb.mod
>> Reading model section from transp_odbc_mdb.mod...
>> Reading data section from transp_odbc_mdb.mod...
>> 95 lines were read
>> Reading plants...
>> Connected to ACCESS 04.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 ACCESS 04.00.0000 -
>> C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
>> SELECT MARKET, DEMAND FROM transp_demand
>> Reading dist...
>> Connected to ACCESS 04.00.0000 -
>> C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
>> SELECT LOC1, LOC2, DIST FROM transp_dist
>> 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.734750000e+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 ACCESS 04.00.0000 -
>> C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
>> DELETE FROM transp_result
>> INSERT INTO transp_result VALUES (?,?,?)
>> Model has been successfully processed
>> >Exit code: 0    Time: 0.412
>
> --
> NEU: Mit GMX DSL über 1000,- ¿ sparen!
> http://portal.gmx.net/de/go/dsl02
>