1 2 Previous Next 16 Replies Latest reply on Dec 6, 2012 6:23 AM by 976640 Go to original post
      • 15. Re: Update multiple rows using WHERE CURRENT OF?
        AlbertoFaenza
        973637 wrote:
        Hello All,

        Thank you all for responding on this issue. Based on the conversation, i am modifying my code and will explain exact need.

        Some points to be noted-

        1. I don' anything to be returned by function. I am good to with void.
        2. The "value" will be set based on the "name" we are passing.
        3. Since this table is common to all thread or program, so make what a particular thread selects, it updates on same value.

        And here is the reason behind need for this-
        We are using JPA as ORM. This uses a table (a common table) where it puts all the entities to be persisted with their last value used.
        In my example MyTable is that table used by JPA.
        as i describe it
         1, Test1, 10
        2, Test2, 20
        3, Test3, 30
        4, Test4, 40
        Test1, Test2... are name of entities. So if i say Test1 has "value" 10, means if a thread comes to insert a row in Test1, the uidpk of Test1 will be used
        by that thread must 11. That thread is responsible to use next available uidpk from Mytable and update the same. So Thread used value as 11 for Test1,
        It will update the same on MyTable as well. If we execute
         select value from mytable where name='Test1'
        will return 11. And so on. Depending on application thread can insert more than one row at a time.

        If you are still with me, a big thank you for this.

        So point is, if application uses JPA and why i should write such code. To answer this some application are using Non-JPA technology. Application which are using Non-JPA technology can also insert row in Test1, Test2... and there i need to select last value used for uidpk and update the value used and make sure no other thread get dirty value.

        Hope i am clear now.

        Thanks,
        Arfeen.
        It sounds something where you can use sequence instead of updating a table. Much safer IMHO.
        I suggest to use one sequence for each entity and get sequence.nextval (i.e. table1seq.nextval) any time you need to insert a row in your entity.

        Regards.
        Al
        • 16. Re: Update multiple rows using WHERE CURRENT OF?
          976640
          Thanks AI for the response.
          But can not use Sequences as i mentioned some application are using JPA keys to insert rows in common table and we can't let them use sequences because of design. So the application which are based on non-JPA must use same JPA generated keys.

          Thanks,
          Arfeen.
          1 2 Previous Next