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

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.

Lose both parentheses for your first INNER JOIN, and then move it right after 'SET b.Quantity = ?' while preceding it with FROM.

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

So it was the missing parenthesis?

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