Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Unanswered: Writing values back to a database

    Hi All,

    I'm trying to write values back to an access database from an open source mathematical programming language (called glpk) using ODBC. I succeeded in doing so in most variables except one. The problem is described below:

    The following code gets an error when writing the data:

    \begin code:


    table x3_iii {j in TF, k in C, p in P, t in T: x3[j,k,p,t]>0} OUT 'ODBC'
    'FileDSN=.\d2.dsn'

    'UPDATE ((Channels a INNER JOIN ChannelPeriodProducts b'
    'ON a.ChannelID = b.ChannelID)'
    'INNER JOIN TransformerTypes d'
    'on a.OriginFacilityID = d.FacilityID'
    'SET b.Quantity = ?'
    'WHERE d.TransformerTypeID = ?'
    'AND a.OriginFacilityID =?'
    ' AND b.ProductID = ?'
    ' AND b.PeriodID = ?':
    x3[j, k, p, t], j, k, p, t;


    \end code


    Note that the sql is on multiple lines, because of a maximum strings limit in the glpk (but the way I used in the code is fine and worked without problems for writing other variables).

    Now, let me describe what I am basically trying to do: I'm trying to write the values for the variable x3[j,k,p,t] in its field "Quantity" in the table 'ChannelPeriodProducts'. The indices for that variable are j,k,p,t.

    The 'ChannelPeriodProducts' table has a "ChannelID", "PeriodID", "ProductID" and "Quantity" fields. The index t is corresponding to "PeriodID", the index p is corresponding to the field "ProductID", and the variable x3[j,k,p,t] itself, as I said, is corresponding to the field "Quantity".

    The 'Channels' table has a "ChannelID", "OriginFacilityID" and "DestinationFacilityID" fields. The index k is corresponding to the field "DestinationFacilityID". The "ChannelID" field in it is corresponding to the "ChannelID" in the 'ChannelPeriodProducts' table.

    Finally, the 'TransformerTypes' table has a "FacilityID" and a "TransformerTypeID" fields. The index k is corresponding to the field "TransformerTypeID". The "FacilityID" field in this 'TransformerTypes' table is corresponding to the 'DestinationFacilityID' field in the Channels table.

    Note that there are existing records in all tables, with the "Quantity" field in these records in the table 'ChannelPeriodProducts' empty (as it is to be filled by the output of the model).

    Hope it is clear. I'd REALLY appreciate any help telling me how I can modify the above code in order to do what I want to do as explained above, or even provide me with a different code that does what I want to do!

    And to describe the error I get:
    The whole statement is re-written when executed (which happens whenever any SQL statement is executed through glpk) but then it aborts with an error from the ODBC driver saying that the writing failed.

    I know that that probably doesn't help much in debugging, but it is what happens.

    By the way, what makes me believe that it is an SQL query problem is that other SQL statements worked perfectly when executed from glpk.


    N.B: I understand that this forum is NOT concerned with Access in specific, and that I'm writing these values to Access, but I do believe that the SQL of my case should be a bit general, and also given that I didn't get a solution from Access forums or even from glpk experts, I'd really appreciate your input here!!


    Thanks a lot,

    Aly

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by amegahed3 View Post
    By the way, what makes me believe that it is an SQL query problem is that other SQL statements worked perfectly when executed from glpk.
    Yeah, but your error message seems to indicating that the query is never being executed on the database, and that the failure is occuring before that step.

    The obvious....:
    You have two left parenthesis, and only one right parenthesis?
    And this is not standard SQL:
    "UPDATE (Channels a INNER JOIN ChannelPeriodProducts b'
    'ON a.ChannelID = b.ChannelID)".
    You can't update a subquery (that I have ever heard of), in SQL Server, MS Access, or any other database I know. You need to update a single, specific object.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Lose both parentheses for your first INNER JOIN, and then move it right after 'SET b.Quantity = ?' while preceding it with FROM.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Aug 2010
    Posts
    6
    Well, after MANY trials and asking different people, I was able to get the correct code. Here it is for anyone's reference:

    table x3_iii {j in TF, k in C, p in P, t in T: x3[j,k,p,t]>0} OUT 'ODBC'
    'FileDSN=.\d2.dsn'


    'UPDATE ((Channels a INNER JOIN ChannelPeriodProducts b'
    'ON a.ChannelID = b.ChannelID)'
    'INNER JOIN TransformerTypes d'
    'on a.OriginFacilityID = d.FacilityID)'
    'INNER JOIN Customers cc'
    'on a.DestinationFacilityID=cc.FacilityID'

    'SET b.Quantity = ?'

    'WHERE d.TransformerTypeID = ?'
    'AND cc.FacilityID =?'
    ' AND b.ProductID = ?'
    ' AND b.PeriodID = ?':
    x3[j, k, p, t], j, k, p, t;


    Best,

    Aly

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So it was the missing parenthesis?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2010
    Posts
    6
    Well there were 2 things:
    1. The missing parenthesis.
    2. Adding the table of 'Customers' which was necessary to achieve what I wanted to do!

    Thanks.

    Aly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •