Connecting AMPL to MySQL

[Graham Edwards]

Hi

I am trying to use the example on http://ampl.github.io/tables/mysql.html.  The connection string I am using in the run file is:

param ConnectionStr symbolic = "DRIVER={MySQL ODBC 5.3 ANSI Driver; version 5.3}; DATABASE=test;"

but I get the following error message:

Error reading table dietFoods with table handler odbc:
Could not connect to "DRIVER={MySQL ODBC 5.3 ANSI Driver; version 5.3}; DATABASE=test;".

I have attached a screenshot of the ODBC Drivers section

Thanks in advance, Graham

[AMPL Google Group]

Hi Graham,

I would suggest you to try with the following connection strings:
- "DRIVER={MySQL ODBC Driver; version 5.3}; DATABASE=test;"
- "DRIVER=MySQL ODBC 5.3 ANSI Driver; DATABASE=test;"

Additionally, in case none of the above connection strings works, could you please add the option "verbose" to your table declaration (see, e.g., http://ampl.github.io/tables/mysql.html#troubleshooting), which should provide a more helpful error message, and send us the output?

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:543873532-3027#}

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To post to this group, send email to am...@googlegroups.com.
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[Graham Edwards]

Thanks Filipe

I still had a problem with these strings however I noticed I had looked at the 64 bit ODBC data sources.  The 32 bit driver version is 5.2 - when I use this instead of 5.3 the connection is successful and the data is retrieved successfully from MySQL. 

However an "ampl.exe has stopped working" message appears when AMPL attempts to solve.  I have inserted the "log_file" option in the run file but nothing is written to the log file.

The versions of AMPL and CPLEX I am using are:

    AMPL Version 20171122 (MS VC++ 10.0, 32-bit) and CPLEX 12.6.0.0

The .mod files contains:

set NUTR;

set FOOD;

param cost {FOOD} > 0;

param f_min {FOOD} >= 0;

param f_max {j in FOOD} >= f_min[j];

param n_min {NUTR} >= 0;

param n_max {i in NUTR} >= n_min[i];

param amt {NUTR,FOOD} >= 0;

var Buy {j in FOOD} >= f_min[j], <= f_max[j];

minimize total_cost:  sum {j in FOOD} cost[j] * Buy[j];

subject to diet {i in NUTR}:

   n_min[i] <= sum {j in FOOD} amt[i,j] * Buy[j] <= n_max[i];

And the .run file contains:

model diet.mod;

param ConnectionStr symbolic = "DRIVER=MySQL ODBC 5.2 ANSI Driver; SERVER=localhost; UID=root; PASSWORD=***; DATABASE=dietdb";

table dietFoods "ODBC" (ConnectionStr) "Foods":

   FOOD <- [FOOD], cost IN, f_min IN, f_max IN,

   Buy OUT, Buy.rc ~ BuyRC OUT, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;

table dietNutrs IN "ODBC" (ConnectionStr) "Nutrients": NUTR <- [NUTR], n_min, n_max;

table dietAmts IN "ODBC" (ConnectionStr) "Amounts": [NUTR, FOOD], amt;

read table dietFoods;

read table dietNutrs;

read table dietAmts;

display FOOD;

                  

option solver cplexamp;

option log_file 'testlogfile.txt';

solve;

#write table dietFoods;

Best regards, Graham

[AMPL Google Group]

Hi Graham,

If you load the data from a .dat file instead of loading from the database, does this error still occur? Are there any error messages printed to the console?

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:543873532-3027#}

On Mon, Mar 19, 2018 at 3:45 PM UTC, <am...@googlegroups.com> wrote:

Thanks Filipe



I still had a problem with these strings however I noticed I had looked at the 64 bit ODBC data sources. The 32 bit driver version is 5.2 - when I use this instead of 5.3 the connection is successful and the data is retrieved successfully from MySQL.



However an "ampl.exe has stopped working" message appears when AMPL attempts to solve. I have inserted the "log_file" option in the run file but nothing is written to the log file.



The versions of AMPL and CPLEX I am using are:



AMPL Version 20171122 (MS VC++ 10.0, 32-bit) and CPLEX 12.6.0.0


The .mod files contains:


set NUTR;
set FOOD;

param cost {FOOD} > 0;
param f_min {FOOD} >= 0;
param f_max {j in FOOD} >= f_min[j];

param n_min {NUTR} >= 0;

param n_max {i in NUTR} >= n_min;

param amt {NUTR,FOOD} >= 0;

var Buy {j in FOOD} >= f_min[j], <= f_max[j];

minimize total_cost: sum {j in FOOD} cost[j] * Buy[j];

subject to diet {i in NUTR}:

n_min <= sum {j in FOOD} amt[i,j] * Buy[j] <= n_max;

[Graham Edwards]

Hi Filipe

Apologies for the delay in responding but I was not available yesterday,

After changing to the .dat file there is no problem.  I then changed back to the MySQL import and it is working!  I do not know what is now different but clearly this is good news for me.  If I experience the problem again I will let you know, but many thanks for looking into the "problem".

Best regards, Graham

[Graham Edwards]

Hi Filipe

A supplementary question if I may - given that AMPL can import data from a table in a MySQL database, can AMPL also import data from a View?

Thanks and best regards, Graham

[AMPL Google Group]

Hi Graham,

AMPL can import data from a MySQL View just like it imports data from MySQL tables.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:543873532-3027#}

On Wed, Mar 21, 2018 at 2:52 PM UTC, <am...@googlegroups.com> wrote:

Hi Filipe

Apologies for the delay in responding but I was not available yesterday,

After changing to the .dat file there is no problem. I then changed back to the MySQL import and it is working! I do not know what is now different but clearly this is good news for me. If I experience the problem again I will let you know, but many thanks for looking into the "problem".

Best regards, Graham

On Wed, Mar 21, 2018 at 2:52 PM UTC, <am...@googlegroups.com> wrote:

Hi Filipe

A supplementary question if I may - given that AMPL can import data from a table in a MySQL database, can AMPL also import data from a View?

Thanks and best regards, Graham

On Mon, Mar 19, 2018 at 10:39 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

Hi Graham,

If you load the data from a .dat file instead of loading from the database, does this error still occur? Are there any error messages printed to the console?

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com