Re: [Help-glpk] Problems with Connecting GLPK to Excel

[aly]

Hello Xypron,

I know I've been asking a lot lately, but your replies have been SO useful. I'm 
sure they are useful to many other people too, either now or in the future!!

Now I have the following questions/concerns (the first two are detailed, while 
the last 3 are general):


1. I was not able to do the update part in your example. What's that "diff 
X[p]"?? As it gives me a syntax error??

2. Is there a way not ti write back zero valued variables?? I tried to insert 
an "if" condition in the SQL statement of writing values back to Access, but 
with no luck.

3. Would the GLPK syntax (including the model, reading and writing to Access) 
work directly on AMPL (as I read that GLPK is part of AMPL) or I do have to do 
many modifications?

4. Is there a way to connect GLPK to MS SQL 64 bit?

5. Is there a way to connect to MySQL under Unix?

Thanks a bunch for the continuous support!!

Aly

----- Original Message -----
From: "glpk xypron" <address@hidden>
To: address@hidden
Cc: address@hidden
Sent: Thursday, July 29, 2010 2:03:19 PM GMT -05:00 US/Canada Eastern
Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel

Hello Aly,

you will need two table OUT statements, one for updating and
one for inserting (see example below).

Some none standard extensions like MySQL REPLACE can do both.

Best regards

Xypron


# products
set P;
# periods
set T;
# demand (product, period, quantity)
set D, dimen 3;
# periods with demand
set X{p in P} := setof{(p, t, d) in D} t;
# stock (we will use some dummy value in this example)
param s{P,T} := Uniform01();
solve;
# create table for our test
table CRE {(p,t,d) in D} OUT
  'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
  'DROP TABLE IF EXISTS tbl;' # not supported by Access
  'CREATE TABLE tbl ('
  '  p TEXT(10),'
  '  t INT,'
  '  d FLOAT,'
  '  s FLOAT,'
  '  PRIMARY KEY (p(10), t) );'
  'INSERT INTO tbl (p, t, d, s)'
  '  VALUES (?, ?, ?, 0);' :
  p, t, d;
# update only existing records
table UPD { (p, t, d) in D } OUT
  'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
  'UPDATE tbl'
  '  SET s = ?'
  '  WHERE p = ? AND t = ?' :
  s[p,t], p, t;
# insert missing records
table INS {p in P, t in T diff X[p]} OUT
  'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
  'INSERT INTO tbl (p, t, d, s)'
  '  VALUES (?, ?, 0, ?);' :
  p, t, s[p,t];

data;

set P := p1 p2 p3;
set T := 1 2 3 4 5;
set D :=
  p1 2 43
  p1 4 16
  p2 5 13
  p3 1  7
  p3 4 18;
end;


-------- Original-Nachricht --------
> Datum: Thu, 29 Jul 2010 04:33:50 -0400 (EDT)
> Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel

> Hello Xypron,
> 
> Well, it works PERFETLY!!! Thanks a lot for that!
> 
> The only problem now in the same context happens when writing values back
> for some of the non-existent records. Those non-existent records are
> non-existent because the corresponding parameters are zero (and I don't input
> records that have corresponding zero parameter values). However, the
> corresponding variables for some of those records could be non-zero. An 
> example is
> the delivery quantity in a certain period. It could be non-zero even though
> the demand for that period is zero. That is because such delivery quantity
> will be stored to satisfy the demand in later periods for instance. 
> 
> So, there is no record corresponding to that period with zero demand in
> the table before solving the model (note that the table I'm dealing with here
> has a field for the index "periods", another for the parameter "demand"
> and a third for the variable "delivered quantity") and so, an error happens
> when the solver tries  to update the variable value for that period as it
> doesn’t find its corresponding record.
> 
> I hope it is clear!
> 
> 
> Any ideas on the best way to deal with that?
> 
> Best,
> 
> Aly
> 
> ----- Original Message -----
> From: "glpk xypron" <address@hidden>
> To: address@hidden, "xypron glpk" <address@hidden>
> Cc: address@hidden
> Sent: Tuesday, July 27, 2010 4:54:48 PM GMT -05:00 US/Canada Eastern
> Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel
> 
> Hello Aly,
> 
> the model below shows the correct syntax.
> 
> READONLY=FALSE is only needed for Excel not for Access.
> 
> In the table OUT statement a domain specifies over which
> entries you iterate.
> 
> The last SQL statement is executed once per domain
> entry.
> 
> All question marks in this last SQL statement are replaced
> by the variables given after the colon.
> 
> Best regards
> 
> Xypron
> 
> 
> 
> set TF := { 'tf1', 'tf2', 'tf3' };
> set P  := { 'p1', 'p2', 'p3'};
> set T  := { 't1', 't2'};
> param iq{TF, P, T} := Uniform01();
> 
> solve;
> 
> table t {j in TF, p in P, t in T} OUT 'ODBC'
>   'FileDSN=.\d2.dsn'
>   'UPDATE TransformerTypeStoragePeriodProducts'
>   '  SET StorageQuantity = ?'
>   '  WHERE TransformerTypeID = ?'
>   '    AND PeriodID = ?'
>   '    AND ProductID = ?' :
>   iq[j, p, t], j, t, p;
> 
> end;
> 
> 
> -------- Original-Nachricht --------
> > Datum: Tue, 27 Jul 2010 07:22:00 -0400 (EDT)
> > Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel
> 
> > Hi Xypron,
> > 
> > Well, I have really tried a lot, but still with no luck.
> > 
> > The following SQL syntax works fine in Access:
> > 
> > UPDATE TransformerTypeStoragePeriodProducts SET StorageQuantity = 5
> > WHERE TransformerTypeID = 1
> > AND PeriodID = 1
> > AND ProductID = 1;
> > 
> > Then, when I try to have this in GLPK:
> > 
> > table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
> >   'FileDSN=.\d2.dsn;READONLY=FALSE'
> > 
> > 'UPDATE TransformerTypeStoragePeriodProducts'
> > 'SET StorageQuantity = iq[j, p, t]'
> > 
> > 'WHERE TransformerTypeID = j'
> > 'AND PeriodID = t'
> > 'AND ProductID = p';
> > 
> > I get a syntax error saying that a semi colon is missing where expected.
> > 
> > So, I tried this:
> > 
> > table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
> >   'FileDSN=.\d2.dsn;READONLY=FALSE'
> > 
> > 'UPDATE TransformerTypeStoragePeriodProducts'
> > 'SET StorageQuantity = iq[j, p, t]'
> > 
> > 'WHERE TransformerTypeID = j'
> > 'AND PeriodID = t'
> > 'AND ProductID = p':
> > j ~ TransformerTypeID, p ~ ProductID, t ~ PeriodID, iq[j, p, t] ~
> > StorageQuantity;
> > 
> > And that one (and other similar ones) fails to write and the driver
> > reports the error 733: "error on writing data to table iq_jptyyy, model
> > postsolving error". One of the other ones for instance that gave the
> same error and
> > didn't work for instance is:
> > 
> > table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
> >   'FileDSN=.\d2.dsn;READONLY=FALSE'
> > 
> > 'UPDATE TransformerTypeStoragePeriodProducts'
> > 'SET StorageQuantity = iq[j, p, t]':
> > j ~ TransformerTypeID, p ~ ProductID, t ~ PeriodID, iq[j, p, t] ~
> > StorageQuantity;
> > 
> > 
> > 
> > Any ideas?
> > 
> > Thanks a lot for the continuous support, patience and help!! I certainly
> > appreciate it!!
> > 
> > Best,
> > 
> > Aly
> 
> -- 
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
> 
> -- 
> PhD Student
> 
> Rm. 407 Main Building
> H. Milton Stewart School of Industrial and Systems Engineering
> Georgia Institute of Technology
> 765 Ferst Dr., NW
> Atlanta, Georgia 30332-0205, USA
> 

-- 
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

-- 
PhD Student

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA