Hello Xypron,
I have been trying to do the "Insert" table as the one you described below, but
it doesn't want to work at all!!
I have the following:
set bc_dummy_set_2, dimen 5;
table bc_kptu_dummy_2 IN "ODBC"
'FileDSN=.\d2.dsn;READONLY=FALSE'
'CustomerBackorderPeriodPeriodProducts':
bc_dummy_set_2<-[FacilityID, ProductID, PeriodID, BackorderPeriodID, UnitCost];
set bc_existent{ k in C, p in P, t in T} := setof{(FacilityID, ProductID,
PeriodID, BackorderPeriodID, UnitCost) in bc_dummy_set_2} BackorderPeriodID;
/*line 6 */
table bq_insert { k in C, p in P, t in T, u in T diff bc_existent[k,p, t] }
OUT
'ODBC'
'FileDSN=.\d2.dsn'
'INSERT INTO CustomerBAckorderPeriodPeriodProducts (k,p,t,u,UnitCost,bq,
TotalCost, VariableID)'
'VALUES (?,?,?,?,0,?,0,0);':
k,p,t,u,bq[k,p,t,u];
Note that that table CustomerBackorderPeriodPeriodProducts has 7 fields, which
are the following:
a) 4 indices (defined on customers, products, periods, periods), the field
names are FacilityID, ProductID, PeriodID and BackorderPeriodID respectively,
b) The parameter UnitCost,
c) The variable bq, which has the field name Quantity,
d) 2 other fields (that are not used in this model), having the names TotalCost
and VariableID.
And I'm here trying to let it insert the non-existent records by inserting
values for each of the 4 indices and inserting the corresponding variable
value, and putting values of each of the parameter UnitCost in addition to the
two fields TotalCost and VariableID as zeros.
Also, please note that in "line 6" above in my set bc_existent, I wanted it to contain
all combinations of FacilityID, ProductID, PeriodID and BackorderPeriodID that have corresponding
parameter bc value, but I had to define it on only 3 of the parameters and put the fourth at the
end of the statement to not get a syntex error. Also, I got a syntax error of "u not
defined" whenever I tried to make it as (and I don't know why):
set bc_existent{ p in P, t in T, u in T} := setof{(FacilityID, ProductID,
PeriodID, BackorderPeriodID, UnitCost) in bc_dummy_set_2} FacilityID;
table bq_insert { k in C, p in P, t in T, u in T diff bc_existent[p, t, u] }
OUT
I'd appreciate your input a lot,
Thanks a lot!!!
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