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

[aly]

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