[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Help-glpk] Problems with Connecting GLPK to Excel
From: |
glpk xypron |
Subject: |
Re: [Help-glpk] Problems with Connecting GLPK to Excel |
Date: |
Wed, 21 Jul 2010 07:26:01 +0200 |
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" <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
>
>
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, aly, 2010/07/08
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, aly, 2010/07/16
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, aly, 2010/07/20
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, Aly Megahed, 2010/07/21
- Re: [Help-glpk] Problems with Connecting GLPK to Excel,
glpk xypron <=
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, glpk xypron, 2010/07/27
- Re: [Help-glpk] Problems with Connecting GLPK to Excel, aly, 2010/07/29