Originally posted by: SystemAdmin

Hi,
I am using the version 12.5 of IBM ILOG CPLEX
Optimization Studio (Build id 20121025-0121).

Following a lab exercise concerning database
connectivity I noticed an unexpected outcome.

The data connection is realized as follows:
DBConnection db("odbc","DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\\Documents and Settings\\Administrator\\Desktop\\Model_Dev_Lab_Source - verbessert\\COS125.labs\\Staffing\\skills.mdb//");
The aim is to write a result set
({string} hiredWorkers = {w | w in namesOfWorkers : hireWorker[w] == 1})

into the data base table new_hires via
hiredWorkers to DBUpdate(db,"insert into new_hires(hire_name) values(?)");
The result set contains 6 items. Nevertheless at
the end of the optimization run only the first
string can be found in the database table.

I would have expected all items to be written into
the table. As far as I remember this had been the
behaviour with previous versions.

You will find the concerned data attached.

Thank you in advance, best regards

Ulrich
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

Hi,

This issue is know and currently investigated internally. In the meantime you can set dbUpdateBatchSize to 1, it should allow you to avoid the issue
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: oplteachinginclass

Hello all,

I do have the same problem with weird data transfers from OPL to Access on DBupdate. Setting dbUpdateBatchSize = 1 in the settings does not solve the problem. Do you have any further suggestions for a workaround on that matter?

Regards

TeachingOplinClass

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: EXRE_Aric_Johnson

Just to confirm what opteachinginclass wrote, the dbUpdateBatchSize = 1 doesn't solve the issue. This is a real joke, I wasted whole day thinking it was my ODBC connection and it turns out that IBM is at fault (thats weird, Incomplete But Marketed). Btw, Im using 12.5.1 so it wasnt fixed between in this release. 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: DBRE

Just did the test.

If I add the following in the staffing.mod, it works fine:

execute {
  settings.dbUpdateBatchSize = 1;
  writeln("  settings.dbUpdateBatchSize = ",settings.dbUpdateBatchSize);
}

See the attached file.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: EXRE_Aric_Johnson

Not sure what the staffing.mod output looks like that is later being written to a DB. But my mod file has postprocessing tuples of the form:

tuple Solution {

string field1;

string field2;

string field3;

....

int field4;

float field5;

....

}

{Solution} solution;

Then when I write the solution tuple set to my access db in the dat file using DBUpdate, it doesnt write the string fields properly (in fact it doesnt write anything for most of the string fields) but it does write all of the int or float fields properly. This whole process used to work fine v12.2. Guess I just have to go back to old version.

Oh, I have tried this with the above execute statement as well changing this setting in the ops file.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: oplteachinginclass

Thanks to EXRE_Aric_Johnson for confirming the problem.

@DBRE: Please just use the oil example that ships with OPL 12.5.1. Run the model and inspect the table RESULTS in the ACCESS database afterwards. You will see that FLOATs and INTs are transfered properly and the fields for the two strings OIL and  GAS are nearly empty.

System admin posted in April that the error is known. Is their any current information when a bugfix will be available?

Going back to v12.2 is not an option for me as the class room computers are equipped with Win8. However, OPL < v12.5.1 does not work with Win8.....

In the meantime, I will avoid STRINGs in DBupdate-Statements and use INTs instead....

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: AndrewBullock

I've been struggling with this problem as well on 12.5.1.0 writing out over ODBC to MS Access 2007 on 32 bit. It only seems to write strings into the first record written out.

Whilst the "dbUpdateBatchSize = 1" works for trivial examples there is still a significant problem. When I try it with a larger problem it completely crashes IDE's Java or the calling application when using the .NET interface.

Further tests on the use of "dbUpdateBatchSize = 1" revealed that it will sometimes (but consistently) fail to write out any data to some tables (but not others) over ODBC to MS Access but will not report any kind of error. So I'd recommend being careful with using dbUpdateBatchSize=1 for ODBC and MS Access.

An update on the status of the original point raised by SystemAdmin on fixing the inability of OPL to write strings out successfully to MS access over ODBC would be approciated. Anyone?

Andrew

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

Andrew and I solved this issue.

Regards

 

#DecisionOptimization
#OPLusingCPLEXOptimizer