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

[glpk xypron]

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