Skip to Content
avatar image
Former Member

JDBC Lookup

Hi All,

I am working on JDBC lookup scenario, I am following this blog

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

I had created file sender and receiver datatypes, MT, MI. Now that when I am trying to do the mapping I need to use the UDF, which returns the values when I query the DB,

For Example, I have two fields name and no and it has to return the age of a person how do I write the UDF.

I am not sure what details i should provide for the code attached to determine the channel

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME");

details for "DB-SYSTEM-NAME","DB-CHANNEL-NAME", please provide the details.

Regards

Nithiyanandam

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    May 18, 2009 at 10:56 AM

    >

    > Hi All,

    >

    > I am working on JDBC lookup scenario, I am following this blog

    >

    > /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

    >

    > I had created file sender and receiver datatypes, MT, MI. Now that when I am trying to do the mapping I need to use the UDF, which returns the values when I query the DB,

    >

    > For Example, I have two fields name and no and it has to return the age of a person how do I write the UDF.

    >

    > I am not sure what details i should provide for the code attached to determine the channel

    >

    > //Determine a channel, as created in the Configuration

    > channel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME");

    >

    > details for "DB-SYSTEM-NAME","DB-CHANNEL-NAME", please provide the details.

    >

    > Regards

    > Nithiyanandam

    "DB-SYSTEM-NAME" - The business system/service for the JDBC adapter in ID

    "DB-CHANNEL-NAME" - the JDBC adapter

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi All,

      As mentioned, I have done the mapping for two input fields (EMPNAME, EMPNUMBER) and I need to select the AGE based upon those fields. I have done all the configuration and used the following UDF. But while executing it I am getting the response as null where the fields are populated with the values.

      Could you please help out.

      /write your code here

      String Query = " ";

      Channel channel = null;

      DataBaseAccessor accessor = null;

      DataBaseResult resultSet = null;

      // Build the Query String

      //Query = "Select SEX from pdmlink80.EMPLOYEE where EMPNAME = '" + EMPNAME[0];

      Query = "Select AGE from EMPLOYEE where EMPNAME = '" + EMPNAME[0] + "' and EMPNUMBER = '" + EMPNUMBER[0] +"'";

      System.out.println("Query ="+Query );

      try{

      //Determine a channel, as created in the Configuration

      channel = LookupService.getChannel("jdbcservice","CC_JDBC_RX");

      //Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.

      accessor = LookupService.getDataBaseAccessor(channel);

      //Execute Query and get the values in resultset

      resultSet = accessor.execute(Query);

      for(Iterator rows = resultSet.getRows();rows.hasNext();){

      Map rowMap = (Map)rows.next();

      result.addValue((String)rowMap.get("AGE"));

      }

      }

      catch(Exception ex){

      result.addValue(ex.getMessage());

      }

      finally{

      try{

      if (accessor!=null) accessor.close();

      }

      catch(Exception e){

      result.addValue(e.getMessage());

      }

      }

      Regards,

      Nithiyanandam

  • avatar image
    Former Member
    May 18, 2009 at 10:58 AM

    Hi,

    // Build the Query String
    Query = "Select URole from  LoginMaster_1 where UName = '" + UName[0] + "' and UPassword = '" + Pwd[0] +"'";

    I have two fields name and no and it has to return the age of a person

    Instead of Uname,Upassword you need to give name and no.Instead of Urole you need to give age.

    DB-SYSTEM-NAME: Business system or service Name

    DB-CHANNEL-NAME: Communication channel name

    Regards,

    Prakasu.M

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 18, 2009 at 11:13 AM

    Hi,

    "DB-SYSTEM-NAME" - Specify your business system/service for the JDBC receiver adapter

    "DB-CHANNEL-NAME" - Specify your JDBC adapter channel name

    Sameer

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 19, 2009 at 06:16 AM

    Hi,

    please provide some inputs...

    Regards,

    Nithiyanandam

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      Check with mapping level.In Message mapping go to test tag and give input and excute the mapping.Look up find the value and give in the output.In case you are not receiving response then check the query in DB using like..sql plus.Incase you created table using some other user and using different user in JDBC receiver then that time also you can not get the response.

      Regards,

      Prakasu.M