This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 16, 2012 12:32 PM by bip_monkey RSS

Another dynamic where clause question

bip_monkey Newbie
Currently Being Moderated
Hello All,

I have my VO in which the bindin style = 'Oracle Named'. I have no bind variables defined/declared in the VO

Code in my AM:
    public void initDetails(String primarykeyval) {    
            xxObjectVOImpl vo = getxxObjectVO1();                       
            System.out.println("I am in the AM  : primarykeyval value is "+ primarykeyval);
            vo.initQuery(primarykeyval);                        
        }
Code in my VOimpl to set and run the query dynamically:
    public void initQuery(String primarykeyval) {
        if ((primarykeyval != null) &&
            (!("".equals(primarykeyval.trim()))))
            {
            setWhereClause("PRIMARYKEY = :1");
            setWhereClauseParams(null); // Always reset
            setWhereClauseParam(0, primarykeyval);
            System.out.println("Query Executed " + getQuery());
            executeQuery();  
            }
}
What is happening is when the dynamic query runs in the VOimpl, it is setting the where clause as (PRIMARYKEY = :1). It looks like the value passed in the variable "primarykeyval" is not binding to the ":1".

Not sure why it is not taking in the value. Any ideas/suggestions as to how to solve it?

Thanks,
  • 1. Re: Another dynamic where clause question
    keerthioaf Expert
    Currently Being Moderated
    Hi ,

    Print the primary key value and see whether you are able to obtain the value inside the method

    Public void initQuery(String primarykeyval) {
    if ((primarykeyval != null) && (!("".equals(primarykeyval.trim()))))
    {

    Sysetm.out.println("Print your primary key value " +primarykeyval) ;

    }

    If its not null then binding should work , since you are printing the query using getQuery() ; copy and run the same query and
    see if the binding is taking place .

    Keerthi
  • 2. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    Keerthi,

    Thank you for the feedback. It is getting the primarykeyval in the initiquery.

    The SOP at vairous points inside the initquery shows the following:
    Public void initQuery(String primarykeyval) {
    //sop point1
    if ((primarykeyval != null) && (!("".equals(primarykeyval.trim()))))
    { 
    
    //sop point 2 as you suggested
    
    setWhereClauseParam.....
    //sop point3
    //sop to show the sql query
    executeQuery();
    }
    12/11/16 08:32:42 I am in the initquery 1 : 12345
    12/11/16 08:32:42 I am in the initquery 2 : 12345
    12/11/16 08:32:42 I am in the initquery 3 : 12345
    12/11/16 08:32:42 Query Executed SELECT * FROM (SELECT *
    FROM <<mytable>>) QRSLT WHERE (PRIMARYKEY = :1)

    As you can see the query is getting the literal "(PRIMARYKEY = :1)". Shouldn't it take the value and instead show "(PRIMARYKEY = 12345)" to successfully execute the query? When I say successfully execute the query, I am expecting the results to show in a table region of a forwarded page. The forwarded page is coming up....but with the message "No search conducted". That can't be true because if I take the same query and run it in TOAD with the value 12345, I get back results. What is that I am missing/doing wrong here?

    Thanks,

    Edited by: OAF_Monkey on Nov 16, 2012 9:06 AM
  • 3. Re: Another dynamic where clause question
    shreevat Pro
    Currently Being Moderated
    As you are added the where clause dynamically, you don't need to set the index again (you can simplify your syntax), so instead of

    setWhereClause("PRIMARYKEY = :1");
    setWhereClauseParams(null); // Always reset
    setWhereClauseParam(0, primarykeyval);


    USE

    setWhereClause("PRIMARYKEY = "+primarykeyval);
    setWhereClauseParams(null); // Always reset


    Thanks
    Shree
  • 4. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    Thank you for the tip....although that did'nt solve it.

    In my scenario, I have 2 pages. First page is a search and results page. Results are displayed in a table region with a button per row. When I click on a button of a specific row, I have the page forward to another page (page2). This initquery is in the "VOimpl" of the page2. What I am seeing is, when the 2nd page comes up (via the page forward), the URL (for the page2) is still pointing to the "page1PG" with reference to the search region of page1. Why is that? I would've expected it to show reference to "page2PG" in the URL. One thing that i have is, a new AM for page2 (don't know if this is right or wrong....but did it that way since I thoguth that each page should have its own root AM).

    Thanks,
  • 5. Re: Another dynamic where clause question
    Sushant Sharma Expert
    Currently Being Moderated
    Hi,

    Are you able to see your results on Page2 and the only issue is about URL ?

    Also have you created different AMs for page1 and page2?

    --Sushant                                                                                                                                                                                                                                                                                                                       
  • 6. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    Are you able to see your results on Page2 and the only issue is about URL ?
    No. I am not able to see the results on Page2. What I am tyring to do is display the results again in a table in the page2 (after the page forward happens). What I see is a table in the page2 but empty with the message "No search conducted." in the middle of the table. However, the title of the table region in page2 is what I have given in the "page2PG". Hence I know that it is getting the page2 correctly eventhough the URL still points to "Page1".

    Also have you created different AMs for page1 and page2?
    Yes. I have 2 diff. AMs for page1 and page2. Is this a wrong thing to do?

    Thanks,
  • 7. Re: Another dynamic where clause question
    Sushant Sharma Expert
    Currently Being Moderated
    Hi,

    Use same AM for both the pages.
    Every page should have a root AM but you can use same AM for multiple pages.

    --Sushant                                                                                                                                                                                                                                                                   
  • 8. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    If i try doing that...ie., moved the initdetails() method where the call to the initiquery() is from the AM of the page2 to AM of the page1. Now not able to get an instance of the 'voimpl' for page2. Hence getting a nice "null pointer exception" error message. Why wouldn't it get a handle to the "voimpl" instance in the AM of page1 but can get it in the AM of the page2? Whew....this learning experience is turning treacherous by the minute :-)

    Thanks,
  • 9. Re: Another dynamic where clause question
    Sushant Sharma Expert
    Currently Being Moderated
    Hi,

    No. You have to use single AM for both the pages and write all code of AM in single AMImpl.java

    Can you share your code to me at sushantsh51@gmail.com

    --Sushant                                                                                                                                                                                                                                                                                                                                                           
  • 10. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    Thank you for the feedback. It looks like the moment, I changed the AM on the page2 to point to AM of the page1, it loses its ability to instantiate an object of the voimpl of the VO for page2. I checked everything that i could think of....everything seems to be in order. The only change that I have done is point to a different AM than what it was using. Now that I have exhausted all ideas in my mind...I am gonna try the windows approach to try and solve it....shut down the jdeveloper and start it back up:-)

    Can't send you from work...will over the weekend.

    Thanks,
  • 11. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    ok...was able to fix that issue and now using the same AM as page1 (when i changed the AM on page2, thats the only thing that I had done...had to go back into (data model of) the AM of the page1 and add the instance of the VO related to Page2).

    By adding that instance to the AM, now able to reference the 'voimpl' instance in the initDetails before calling the initQuery.

    Now back to the original issue where the "executeQuery()" is not returning anything when page2 is rendered. So, using the AM of page2 or AM of page1, did not make any diff.

    Based on the fact that it did not matter which AM is used, I don't understand your justification behind the below statement:
    <<No. You have to use single AM for both the pages and write all code of AM in single AMImpl.java>>

    Could you pls. explain why a single AM 'has' to be used for both pages?

    Thanks,

    Edited by: OAF_Monkey on Nov 16, 2012 11:19 AM
  • 12. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    2 more things that I tried:

    1) In the "initQuery()" method, I tried to pass a string literal as shown below:
    //setWhereClauseParam(0, ukeyval);
    setWhereClauseParam(0, "12345"); --> I know for a fact that this record exists in the table as seen from TOAD.

    It still did not display the results in the page2 table region.

    2) Then edited the VO and in the sql statement step of the VO for page2. In the expert mode added the "where primarykeyval =12345" to the existing sql statement. That still did not display the results when page2 got rendered.

    Don't know why it wouldn't show results even when hardcoded value is passed in. Any ideas/suggestions/pointers from the gurus to help me understand?

    Thanks,
  • 13. Re: Another dynamic where clause question
    bip_monkey Newbie
    Currently Being Moderated
    hello any ideas/solutions...pls. help.

    Thanks,
  • 14. Re: Another dynamic where clause question
    shreevat Pro
    Currently Being Moderated
    Are you invoking the method to execute the query in the process request of page 2? Can you provide the contents?

    Thanks
    Shree
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points