Writing Values back to a database

[amegahed3]

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, 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

 

[markros]

The answer you received in T-SQL forum at MSDN applies here as well. Remove extra ( from the code.

PluralSight Learning Library

 

[amegahed3]

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

 

[markros]

You're welcome

PluralSight Learning Library