JDBC Lookup

[Former Member]

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

[Shabarish_Nair · Accepted solution]

>

> 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

[Former Member · Accepted solution]

Hi,

Thanks for your response.

in that case, should i create two CC for file(sender & receiver) and one for JDBC is it.

Also let me know, should i include the MI name for the JDBC service.

What CC should i specify in receiver agreement

Regards,

Nithiyanandam

Edited by: Nithiyanandam A.U. on May 18, 2009 1:18 PM

[Former Member · Accepted solution]

Hi,

As Like normal file to file scenario you need do the configuration.JDBC Communication channel used in UDF for DB connectivity purpose.

No need to mention MI in JDBC service.

Here Business service, Communication channel used to identify the DB using JDBC adapter.

In UDF query is used to featch the data from DB table.

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

Here LoginMaster_1 is a table contains URole,UName,UPassword fields.

Regards,

Prakasu.M

Edited by: prakasu on May 18, 2009 4:54 PM

[Former Member · Accepted solution]

Hi,

>> In that case, should i create two CC for file(sender & receiver) and one for JDBC is it.

You need to create three CC one for sender, one for receiver and one for JDBC lookup with type receiver.

>> Also let me know, should i include the MI name for the JDBC service.

It is not required to mentioned MI in JDBC Lookup service.

>> What CC should i specify in receiver agreement

You need to be specify recv communication channel in recv agreement

Sameer

[Former Member · Accepted solution]

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

[Former Member]

Hi,

please provide some inputs...

Regards,

Nithiyanandam

[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

[Former Member]

Hi,

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

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

Sameer

[Former Member]

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