35 public static void main(String[] args) {
38 if (args.length > 0) {
43 File workingDirectory =
new File(System.getProperty(
"user.dir"),
"Transport9");
44 workingDirectory.mkdir();
47 String strAccessConn = args[0] + GAMSGlobals.FILE_SEPARATOR +
"apifiles"
48 + GAMSGlobals.FILE_SEPARATOR +
"Data"
49 + GAMSGlobals.FILE_SEPARATOR +
"transport.accdb";
55 opt.defines(
"gdxincname", db.
getName());
56 opt.setAllModelTypes(
"xpress" );
59 System.out.println(
"x(" + rec.getKey(0) +
"," + rec.getKey(1) +
"): level=" + rec.getLevel() +
" marginal=" + rec.getMarginal());
61 writeDataToAccess(ws, strAccessConn, t9.
OutDB());
69 Class.forName(
"net.ucanaccess.jdbc.UcanaccessDriver");
72 String url = net.ucanaccess.jdbc.UcanaccessDriver.URL_PREFIX + location;
73 Connection c = DriverManager.getConnection(url,
"",
"");
76 readSet(c, db,
"SELECT Plant FROM Plant",
"i", 1,
"canning plants");
77 readSet(c, db,
"SELECT Market FROM Market",
"j", 1,
"markets");
80 readParameter(c, db,
"SELECT Plant, Capacity FROM Plant",
"a", 1,
"capacity of plant i in cases");
81 readParameter(c, db,
"SELECT Market,Demand FROM Market",
"b", 1,
"demand at market j in cases");
82 readParameter(c, db,
"SELECT Plant,Market,Distance FROM Distance",
"d", 2,
"distance in thousands of miles");
85 }
catch (ClassNotFoundException e) {
86 System.err.println(
"Error: Failed to find a driver for the database.");
89 }
catch (SQLException e) {
90 System.err.println(
"Error: Failed to retrieve data from the database.");
97 static void readSet(Connection c,
GAMSDatabase db, String queryString, String setName,
int setDimension, String setExplanatoryText)
throws SQLException {
98 Statement st = c.createStatement();
100 ResultSet rs = st.executeQuery(queryString);
101 ResultSetMetaData rsmd = rs.getMetaData();
103 if (rsmd.getColumnCount() != setDimension) {
104 System.err.println(
"Error: Number of fields in select statement does not match setDimemsion.");
109 GAMSSet set = db.
addSet(setName, setDimension, setExplanatoryText);
111 String[] keys =
new String[setDimension];
114 for (
int idx=0; idx < setDimension; idx++)
115 keys[idx] = rs.getString(idx+1);
116 set.addRecord( keys );
121 static void readParameter(Connection c,
GAMSDatabase db, String queryString, String parName,
int parDimension, String parExplanatoryText)
throws SQLException {
122 Statement st = c.createStatement();
124 ResultSet rs = st.executeQuery(queryString);
125 ResultSetMetaData rsmd = rs.getMetaData();
127 int numberOfColumns = rsmd.getColumnCount();
128 if (numberOfColumns != (parDimension+1)) {
129 System.err.println(
"Error: Number of fields in select statement does not match parDimension.");
136 String[] keys =
new String[parDimension];
139 for (
int idx=0; idx < parDimension; idx++)
140 keys[idx] = rs.getString(idx+1);
141 parameter.addRecord( keys ).setValue( Double.valueOf(rs.getString(numberOfColumns)) );
146 static void writeVariable(Connection c,
GAMSDatabase db, String varName, String ... domains)
throws SQLException {
149 if ( domains.length != var.getDimension() ) {
150 System.err.println(
"Error: Number of column names does not match the dimension of the variable.");
155 Statement st = c.createStatement();
157 String sql =
"create table " + varName +
"(";
158 for (String dom : domains)
159 sql += dom +
" varchar(64), ";
160 sql +=
"lvl double)";
162 st.executeUpdate(sql);
165 sql =
"insert into " + varName +
"(";
166 for (String dom : domains)
168 sql +=
"lvl) values (";
169 for (String key : rec.getKeys())
170 sql +=
"'" + key +
"', ";
171 sql += rec.getLevel() +
")";
173 st.executeUpdate(sql);
181 Class.forName(
"net.ucanaccess.jdbc.UcanaccessDriver");
184 String url = net.ucanaccess.jdbc.UcanaccessDriver.URL_PREFIX + location;
185 Connection c = DriverManager.getConnection(url,
"",
"");
188 writeVariable(c, db,
"x",
"i",
"j");
191 }
catch (ClassNotFoundException e) {
192 System.err.println(
"Error: Failed to find a driver for the database.");
195 }
catch (SQLException e) {
196 System.err.println(
"Error: Failed to write data back to the database.");
202 static String model =
204 " i canning plants \n"+
208 " a(i) capacity of plant i in cases \n"+
209 " b(j) demand at market j in cases \n"+
210 " d(i,j) distance in thousands of miles \n"+
211 " Scalar f freight in dollars per case per thousand miles /90/; \n"+
213 "$if not set gdxincname $abort 'no include file name for data file provided' \n"+
214 "$gdxin %gdxincname% \n"+
215 "$load i j a b d \n"+
218 " Parameter c(i,j) transport cost in thousands of dollars per case ; \n"+
220 " c(i,j) = f * d(i,j) / 1000 ; \n"+
223 " x(i,j) shipment quantities in cases \n"+
224 " z total transportation costs in thousands of dollars ; \n"+
226 " Positive Variable x ; \n"+
229 " cost define objective function \n"+
230 " supply(i) observe supply limit at plant i \n"+
231 " demand(j) satisfy demand at market j ; \n"+
233 " cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; \n"+
235 " supply(i) .. sum(j, x(i,j)) =l= a(i) ; \n"+
237 " demand(j) .. sum(i, x(i,j)) =g= b(j) ; \n"+
239 " Model transport /all/ ; \n"+
241 " Solve transport using lp minimizing z ; \n"+
243 " Display x.l, x.m ; \n"+