help-glpk
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From: Aly Megahed
Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel
Date: Tue, 20 Jul 2010 19:31:29 -0400 (EDT)


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" <address@hidden>
To: address@hidden
Cc: address@hidden
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" <address@hidden>
> To: address@hidden
> Cc: address@hidden
> 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: address@hidden
> > 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





reply via email to

[Prev in Thread] Current Thread [Next in Thread]