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

[Chris Wolf]

Noli,

As mentioned in a separate message, if you want to connect to SQLite 
without defining a DSN, you can do this:


table plants IN "iODBC"
  'Driver=/usr/lib/libsqlite3odbc-0.83.dylib;Database=/Users/cwolf/glptest.db'
  'SELECT PLANT, CAPA AS CAPACITY'
  'FROM transp_capa' :
   I <- [ PLANT ], a ~ CAPACITY;



Note the following:

The correct syntax on MacOS is ...IN "iODBC"  (with letter 'i')
*not* ...IN "ODBC"   

The latter syntax is for the "unixOBDC" implementation which MacOS
does not use.

As with defining a DSN, the "Driver=" setting must point to the 
location of the SQLite ODBC driver (shared library), which on my system is 
"/usr/lib/libsqlite3odbc-0.83.dylib".  Also the "Database=" 
setting must point to a database file that you previously 
initialized with the transp.sql script (which I converted to
SQLite syntax and posted an hour ago, if this list accepts
attachments.)  In the above example, this would be done
via:

$ sqlite3 -init transp_sqlite.sql ~/glptest.db


Hope this helps,


Chris Wolf


Noli Sicad wrote:
> 
> Re: [Help-glpk] How to use Access mdb in Windows with ODBC in MathProg (GMPL)
> 
> Noli Sicad
> Sat, 13 Feb 2010 23:07:39 -0800
> 
> 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
>>