Hello Aly, INSERT is to be used if you want to create an new record in your Access data base. This requires that the key columns are different.
> empty by default. What I'm trying to do is to let GLPK fill the results of > the variable iq_jpt in the field StorageQuantity, of course each value in its > corresponding record (according to the indices). The command to change a non key value in a record, is UPDATE, e.g. UPDATE TransformerTypeStoragePeriodProducts SET StorageQuantity = ? WHERE TransformerTypeID = '?' AND PeriodID = '?' AND ProductID = '?'; I suggest that you check that the UPDATE syntax works correctly inside MS Access first. Best regards Xypron -------- Original-Nachricht -------- > Datum: Tue, 20 Jul 2010 19:31:29 -0400 (EDT) > Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel > > > Actually, it is an Access not Excel file. That is my bad; since I didn't > change the email title. > > I'm sure of the table name in Access. Also, sure of the fields (columns) > names. File d2.dsn does not contain a line READONLY=TRUE. > > I knew about the bug and corrected it, thanks. > > Now, the only thing that could be causing the problem from what you > mentioned is : "A line with the exact values you try to INSERT should not > exist." > > What do you mean? because for sure, the records I want to have their > corresponding variable value written, are there (since these same records were > used to read the parameter values from). > > In order to make it more clear, here you are how that table looks in > Access: > > TransformerTypeID PeriodID ProductID UnitStorageCost StorageQuantity > TotalStorageCost StorageCapacity VariableID > 1 1 1 1 > > 100 0 > 1 1 2 2 > > 190 0 > 1 2 1 3 > 798 > 0 > 1 2 2 1 > > 684 0 > 2 1 1 5 > > 882 0 > 2 1 2 4 > > 788 0 > 2 2 1 3 > > 100 0 > 2 2 2 2 > > 100 0 > > As you can see, each record has a value for each of the 3 indices (the > first 3 fields) and each of the 2 parameter *UnitStorageCose and > StorageCapacity), and the default zero value for the extra field VariableID, > while the > variable field "StorageQuantity" and the extra field TotalStorageCost are > empty by default. What I'm trying to do is to let GLPK fill the results of > the variable iq_jpt in the field StorageQuantity, of course each value in its > corresponding record (according to the indices). > > So, what do you think the problem is? > > Aly > > > ----- Original Message ----- > From: "glpk xypron" <[email protected]> > To: [email protected] > Cc: [email protected] > Sent: Tuesday, July 20, 2010 10:38:11 AM GMT -05:00 US/Canada Eastern > Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel > > Hello Aly, > > please, check that the Excel file already contains a worksheet > "TransformerTypeStoragePeriodProducts". The first line of the > worksheet should contain the column names > TransformerTypeID, PeriodID, ProductID, UnitStorageCost, > StorageCapacity, StorageQuantity > > A line with the excact values you try to INSERT should not > exist. > > File .\d2.dsn should not contain a line READONLY=TRUE. > > A bug concerning SQL commands spread over multiple lines has > been reported. The fixed coding is included in WinGLPK 4.44.1. > http://lists.gnu.org/archive/html/bug-glpk/2010-07/msg00000.html > > Best regards > > Xypron > > > -------- Original-Nachricht -------- > > Datum: Tue, 20 Jul 2010 08:28:08 -0400 (EDT) > > Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel > > > Hi Xypron, > > > > Thanks a lot for that idea. It worked perfectly. > > > > Now, the problem I have is for writing values back to Access. The > writing > > fails, saying that VALUES <1,1,1,1,100,0>" failed. The driver reported > the > > following diagnostics whilst running SQLExecDirect model.txt:553: error > on > > writing data to table iq_jpt Model postsolving error. > > > > The code I use is: > > > > table iq_jpt {j in TF, p in P, t in T} OUT 'ODBC' > > 'FileDSN=.\d2.dsn;READONLY=FALSE' > > 'INSERT INTO [TransformerTypeStoragePeriodProducts$]' > > '(TransformerTypeID, PeriodID, ProductID,' > > 'UnitStorageCost, StorageQuantity, TotalStorage,' > > 'StorageCapacity, VariableID)' > > 'VALUES(?,?,?,?,?,?);' : > > j ~ TransformerTypeID, t ~ PeriodID, p ~ ProductID, hc[j,p,t] ~ > > UnitStorageCost, icap_jpt[j,p,t] ~ StorageCapacity, iq[j,p,t] ~ > StorageQuantity; > > > > > > N.B: hc and icap_jpt are parameters (read initially from the same table > > successfully), and iq is the variable I am trying to write. Also, note > that > > the table has 8 columns (3 indices, the 2 parameters, the one variable, > and > > two extra columns not used in this case). > > > > I tried erasing the line code of the column names, or modifying it > > (including only the indices and used parameters and variable) or trying > to just > > write the variable, with no luck!! > > > > Any help will be highly appreciated! > > > > Best, > > > > Aly > > > > ----- Original Message ----- > > From: "glpk xypron" <[email protected]> > > To: [email protected] > > Cc: [email protected] > > Sent: Friday, July 16, 2010 12:48:06 AM GMT -05:00 US/Canada Eastern > > Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel > > > > Hello Aly, > > > > GLPK only allows strings up to 100 characters. > > > > In the current release, SQL statements may be split over multiple > strings. > > A semicolon at the end of a string marks the end of the statement. > Spaces > > are automatically inserted between the strings. > > > > table ta {(i, j) in {i1 in 1..9} cross {i2 in 1..9}} OUT > > 'iODBC' 'DSN=glpk;UID=glpk;PWD=gnu' > > 'DELETE FROM sudoku_solution' > > 'WHERE ID = ' & id & ';' > > 'INSERT INTO sudoku_solution' > > '(ID, COL, LIN, VAL)' > > 'VALUES(?, ?, ?, ?);' : > > id ~ ID, i ~ COL, j ~ LIN, (sum{k in 1..9} x[i,j,k] * k) ~ VAL; > > > > Yesterday a bug was reported in the implementation of multiple string > > SQL statements. Please, apply the patch described in > > > > http://old.nabble.com/bug-in-glpk-4.44-in-glpsql.c-to29176967.html > > > > The statement you provided could be formatted as: > > > > table ti IN > > 'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu' > > 'SELECT' > > 'a.OriginFacilityID, d.TransformerTypeID, b.ProductID,' > > 'b.PeriodID, b.UnitCost' > > 'FROM Channels a' > > 'INNER JOIN ChannelPeriodProducts b' > > 'ON a.ChannelID = b.ChannelID, Channels a' > > 'INNER JOIN Facilities c1' > > 'ON a.OriginFacilityID = c1.FacilityID, Channel a' > > 'INNER JOIN Facilities c2' > > 'ON a.DestinationFacilityID = c2.FacilityID, Facilities c2' > > 'INNER JOIN TransformerTypes d' > > 'ON c2.FacilityID = d.FacilityID' > > 'WHERE (c2.FacilityType = "Transformer")' > > 'AND (c1.FacilityType => "Supplier");' : > > ... > > > > Please, check the syntax of your inner joins. > > > > Best regards > > > > Xypron > > > > > > -------- Original-Nachricht -------- > > > Datum: Thu, 15 Jul 2010 20:40:48 -0400 (EDT) > > > CC: [email protected] > > > Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel > > > > > Hi Xypron, > > > > > > Thanks a lot for the excellent support. I sincerely appreciate it. > > > > > > I tried the SQL idea you told me about. The problem now is that I get > > the > > > following error: "String Literal Too Long" for the "Where" statement!! > > > > > > > > > My SQL is: > > > > > > 'SELECT a.OriginFacilityID, a.DestinationFacilityID, b.ProductID, > > > b.PeriodID, b.UnitCost, d.TransformationTypeID' > > > 'FROM Channels a, ChannelPeriodProducts b, Facilities c, > > TransformerTypes > > > d' > > > 'WHERE a.ChannelID = b.ChannelID AND a.OriginFacilityID = c.FacilityID > > AND > > > c.FacilityType = "Supplier" AND a.DestinationFacilityID = c.FacilityID > > AND > > > c.FacilityType = "Transformer" AND a.DestinationFacilityID = > > > d.FacilityID': > > > > > > > > > I also tried the following alternative, and got the same error, now > for > > > the "FROM" statement: > > > > > > 'SELECT a.OriginFacilityID, d.TransformerTypeID, b.ProductID, > > b.PeriodID, > > > b.UnitCost ' > > > > > > 'FROM Channels a INNER JOIN ChannelPeriodProducts b ON a.ChannelID = > > > b.ChannelID, Channels a INNER JOIN Facilities c1 ON a.OriginFacilityID > = > > > c1.FacilityID , Channel a INNER JOIN Facilities c2 ON > > a.DestinationFacilityID = > > > c2.FacilityID, Facilities c2 INNER JOIN TransformerTypes d ON > > c2.FacilityID = > > > d.FacilityID ' > > > 'WHERE (c2.FacilityType = "Transformer") AND (c1.FacilityType = > > > "Supplier")': > > > > > > Is that error because GLPK can not handle long SQL statements?? And is > > > there a way to solve that problem? As I really would like to still > have > > that > > > query in GLPK (I do not want to manually create the table in Access > and > > then > > > call that table in GLPK) > > > > > > > > > Any help will be really appreciated. > > > > > > Best, > > > > > > Aly > > > > -- > > GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. > > Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl > > > > -- > > 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 > > > -- > 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 _______________________________________________ Help-glpk mailing list [email protected] http://lists.gnu.org/mailman/listinfo/help-glpk
