Skip to Content
author's profile photo Former Member
Former Member

JDBC Sender Adapter

Hi Folks,

We have come across a requirement where we need to fetch one record from the table and update that record only( Flag turned to Read) .

1. How can i fetch one record only, out of n records having FLAG value Unread .

--> Can i use Select top 1 ..? As the RDBMS we are interacting with is Oracle and oracle doesn't understand anything by TOP...

Kindly correct me if i am wrong...

Selecting a single record is not an issue as i can do the same thing using rownum in oracle .

Ex :

select * from EMPMASTER where flag = 'Y' and rownum < 2.

It will return me only 1 record with flag value 'Y'.

2. Now Issue is here, suppose i got the first record but how can i update that record only(To turn the flag Read)...because as per update syntax it will update all the records with flag unread irrespective of this thing that XI has picked that record or not.

or is it so that update will work only on the just read record.

I hope i have made the doubts clear.....Waiting for valuable responses..

Regards,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Oct 18, 2007 at 10:11 AM

    Hi santhosh,

    A work around i see is to use a stored proceedure, which will have the logic incorporated to update that corresponding row, and finally returning a result set..

    For this case there is no need to use an update statement..

    I'm also watchin this thread to see if any other simpler solutions are available 😊

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2007 at 12:26 PM

    Hi Santosh,

    I am not really sure about your issue but we have solved such a problem with a small BPM.

    1. Start BPM with collecting file with select info

    2. Transform to message with Select statement to select the required entries

    3. Transform step to message for update statement

    4. Update step on Database

    The advantage is that you can use the transform step in 3. to reduce results from step 2 for the update step

    There is a good blog here with a description about creating all steps for this issue.

    /people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

    regards

    Dirk

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 23, 2007 at 05:02 AM

    Hi Folks i solved it by filtering the oracle query further in both select as well as in update query..

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.