This discussion is archived
8 Replies Latest reply: May 8, 2012 11:10 PM by Sudipto Desmukh RSS

ADF BC connection to SQL Server with parameterized variable for View Object

930700 Newbie
Currently Being Moderated
Hi everyone

I'm developing a BPM Application using Oracle BPM 11.1.1.5.0 and JDeveloper 11.1.1.5.0

Following the thread from the link below, since this is a new topic, I decided to open a new thread.

Re: ADF method call to fetch data from DB before the initiator page loads

what I'm trying to do is to get a record from a database and show it to the user on the initiator UI using ADF BC and not the Database Adapter.
I have been able to work with ADF BC and View Objects to get all the rows and show them to the user in a table.

However, when I try to run the same query in the parameterized form to just return a single row, I hit a wall.
In short, My problem is like this:

I have an Application Module which has an entity object and a view object.
My database is SQL Server 2008.
when I try to create a new read only view object to return a single row I face the problem.
The query I have in the query section of my View Object is like this:
select *
from dbo.Employee
where EmployeeCode= 99
which works fine.
However when I define a bind variable, input_code for example, and change the query to the following it won't validate.
select *
from dbo.Employee
where EmployeeCode= :input_code
It just keeps saying
>
incorrect syntax near ":"
>
I don't know if this has to do with my DB not being Oracle or I'm doing something wrong.
Can someone help me with this problem please?

thanks in advance
  • 1. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    Dimitar Dimitrov Expert
    Currently Being Moderated
    The problem is that you are using "Oracle Named" style for parameter binding in the SQL statement. However, this binding style is Oracle specific. You should use "JDBC Positional" binding style with MS SQL Server:
    select *
    from dbo.Employee
    where EmployeeCode= ?
    (The binding style is specified in the query edit pane of the VO definition dialog).

    If you specify "JDBC Positional" binding style, another field called "Bind Positions" will be displayed in the pane of the VO bind variable definitions. You will be able to specify there the parameter position(s) in the VO query that the particular bind variable is bound to. (The index of the bind position is 0-based).

    Dimitar
  • 2. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    930700 Newbie
    Currently Being Moderated
    Thank you :)

    You were right, and now it's working.
    I have one other thing however, I wonder if you could help me with that as well.

    I wanted to pass the userName of the task creator to the db to get the query results.

    I could do this before using the expression
     #{bindings.creator.inputValue} 
    However since the bind variable in the view object accepts groovy expressions only, that doesn't work here anymore.

    I tried the value
    adf.context.securityContext.userName
    but it just returns "anonymous". I just want the ID/username the user logins to the bpm workspace with.

    Can you help me with that please?

    Thanks
  • 3. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    Sudipto Desmukh Expert
    Currently Being Moderated
    this should work to find logged in user in BPM workspace -
    How to get the logged in user name of  BPM worklist.

    Edited by: Sudipto Desmukh on May 8, 2012 5:31 PM
  • 4. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    Dimitar Dimitrov Expert
    Currently Being Moderated
    I wanted to pass the userName of the task creator to the db to get the query results.
    Use the iterator binding's built-in operation ExecuteWithParams to supply values to VO's bind variables and to execute the query. Use an appropriate EL expression to supply the task creator's name to your bind variable. (Unfortunately, I am not familiar with BPM taskflows and I do not know what the correct expression is).

    If you want to execute the query at a button click, then drag and drop the ExecuteWithParams operation as a button on the page.

    If you want to execute the query only once before entering the page, then create a Method Call Activity (that is bound to the ExecuteWithParams operation) and place it before the page's View Activity in the taskflow.

    Have a look at this video for a relevant example:
    Setting Parameter for Before Page Loads

    Dimitar
  • 5. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    Dimitar Dimitrov Expert
    Currently Being Moderated
    I tried the value adf.context.securityContext.userName but it just returns "anonymous"
    <tt>adf.context.securityContext.userName</tt> returns the user authenticated by the WebLogic container. It will return "anonymous" if the user is not authenticated yet by the WebLogic server. You have to setup ADF Security for your application in order to force the user to authenticate, and then adf.context.securityContext.userName will be returning the authenticated username.

    Dimitar
  • 6. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    930700 Newbie
    Currently Being Moderated
    Hi
    This all seems complicated to me so I think my simplest way would be to use the java code in the aformentioned thread by Sudipto:
    public String getUserLogin() throws WorkflowException,
    BPMIdentityException,
    BPMUnsupportedAttributeException {
    String userId = "";
    IWorkflowServiceClient wfSvcClient;
    ITaskQueryService queryService;
    IWorkflowContext wfContext;
    
    // Get username of User Login
    String contextStr = ADFWorklistBeanUtil.getWorklistContextId();
    wfSvcClient = WorkflowService.getWorkflowServiceClient();
    queryService = wfSvcClient.getTaskQueryService();
    wfContext = queryService.getWorkflowContext(contextStr);
    userId = wfContext.getUser();
    
    return userId;
    } 
    I don't know where and how I should put the code to be called when the view object loads though.
    I have created a class in the java class section of my view object which has two methods related to the bind variable input_empCode:
    getinput_empcode and setinput_empcode , as expected.

    however any code i try to put in those two would not be called at all!
    for example:
        public Integer getinput_empCode() {
            //return (Integer)getNamedWhereClauseParam("input_empCode");
            return 1444;
        }
    and
        public void setinput_empCode(Integer value) {
            //setNamedWhereClauseParam("input_empCode", value);
            setNamedWhereClauseParam("input_empCode", 666);
        }
    It just keeps filled with the values in the bind variable section.

    I don't know what I'm doing wrong here.
    would you show me the way?
    Thanks again
  • 7. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    Sudipto Desmukh Expert
    Currently Being Moderated
    You should use the code which I suggested in a backingBean ... maybe you can get the user and pass it to a VO through a AM method.

    regarding the other query I am confused , I guess its better to start a new thread since this thread is having different issues and is becoming confusing :)
  • 8. Re: ADF BC connection to SQL Server with parameterized variable for View Object
    930700 Newbie
    Currently Being Moderated
    Yea you'r right, I'll start a new thread about it :)
    thanks


    Edit: Here's the follow up thread:

    Passing the logged in username to the View Link bind variable

    Edited by: luke on May 12, 2012 2:54 AM

Legend

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