Topic
8 replies Latest Post - ‏2013-10-18T08:03:50Z by AlexFleischer
SystemAdmin
SystemAdmin
2130 Posts
ACCEPTED ANSWER

Pinned topic unexpected outcome for DBUpdate

‏2013-03-28T08:03:53Z |
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
Updated on 2013-04-04T14:26:25Z at 2013-04-04T14:26:25Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2130 Posts
    ACCEPTED ANSWER

    Re: unexpected outcome for DBUpdate

    ‏2013-04-04T14:26:25Z  in response to 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
    • oplteachinginclass
      oplteachinginclass
      2 Posts
      ACCEPTED ANSWER

      Re: unexpected outcome for DBUpdate

      ‏2013-07-30T23:40:50Z  in response to SystemAdmin

      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

  • EXRE_Aric_Johnson
    EXRE_Aric_Johnson
    3 Posts
    ACCEPTED ANSWER

    Re: unexpected outcome for DBUpdate

    ‏2013-08-06T01:41:34Z  in response to SystemAdmin

    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. 

    • DBRE
      DBRE
      3 Posts
      ACCEPTED ANSWER

      Re: unexpected outcome for DBUpdate

      ‏2013-08-06T09:20:35Z  in response to EXRE_Aric_Johnson

      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.

      Attachments

      • EXRE_Aric_Johnson
        EXRE_Aric_Johnson
        3 Posts
        ACCEPTED ANSWER

        Re: unexpected outcome for DBUpdate

        ‏2013-08-06T13:19:37Z  in response to DBRE

        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.

        Updated on 2013-08-06T13:21:52Z at 2013-08-06T13:21:52Z by EXRE_Aric_Johnson
        • oplteachinginclass
          oplteachinginclass
          2 Posts
          ACCEPTED ANSWER

          Re: unexpected outcome for DBUpdate

          ‏2013-08-12T15:41:50Z  in response to EXRE_Aric_Johnson

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

          Updated on 2013-08-12T16:24:39Z at 2013-08-12T16:24:39Z by oplteachinginclass
      • AndrewBullock
        AndrewBullock
        12 Posts
        ACCEPTED ANSWER

        Re: unexpected outcome for DBUpdate

        ‏2013-10-11T15:51:08Z  in response to DBRE

        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