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

[Xypron]

Hello Noli,

you can find a description how to set up sqlite for ODBC at
http://www.ch-werner.de/sqliteodbc/html/index.html

For a DSN less connection the syntax is vendor specific.
The field dbq is specific to MS Access.

If you want to make a DSN less connection to sqlite,
use the following syntax:

Windows

table markets IN "ODBC"
  'Driver=SQLite3 ODBC Driver;Database=full-path-to-db;'
  'transp_demand' :
  J<- [ MARKET ], b ~ DEMAND;


UNIX:

table markets IN "ODBC"
  'Driver=SQLITE3;Database=full-path-to-db;'
  'transp_demand' :
  J<- [ MARKET ], b ~ DEMAND;


If you want to use a DSN (data source name) you need a command
like:

table markets IN "ODBC"
  'DNS=transp;UID=myuser;PWD=mypassword'
  'transp_demand' :
  J<- [ MARKET ], b ~ DEMAND;

If you want to make a DSN less connection use the following syntax

Windows

table markets IN "ODBC"
  'Driver=SQLite3 ODBC Driver;Database=full-path-to-db;'
  'transp_demand' :
  J<- [ MARKET ], b ~ DEMAND;


UNIX:

table markets IN "ODBC"
  'Driver=SQLITE3;Database=full-path-to-db;'
  'transp_demand' :
  J<- [ MARKET ], b ~ DEMAND;


Best regards

Xypron


Noli Sicad wrote:
> 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
> >
> >      
>