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

[Noli Sicad]

Hi Xypron,

Thanks again for providing this detailed instruction for SQLite3 ODBC
driver for MathProg.

In Windows XP

table plants IN "ODBC"
  'DRIVER=SQLite3 ODBC
Driver;Database=C:\Down2\glpk-4.42\examples\sql\transp.db3'
  'SELECT PLANT, CAPA AS CAPACITY'
  'FROM transp_capa' :
   I <- [ PLANT ], a ~ CAPACITY;

The above implementation in accordance to what you suggested seems
still needs 4 steps to accomplish the needed sqlite connection and
reading of the table.

These are the things that I have to do:
1. A pop up dialog will appear "SQLite3 ODBC Driver Connect"
2. Need to click "browse button" in the "Database Name" to locate
transp.db3 (full path in the text box is the correct full path of the
database and transp.db3)
3. Need to find the transp.db3 and click it.
4. Then OK.

I need to do all these steps in all the tables (3 tables for the
transportation model) including the results table.

It seems that this is not what we want.

Thanks.

Regards, Noli

On 2/16/10, Xypron <address@hidden> wrote:
> 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
>>>
>>>
>>
>
>