Reg:Error in JDBC Lookup Table

[Former Member]

Hi All,

My actual requirement is,A project ID is being triggered to PI which will pull the data from the database(SQL Server 2000) ,from different tables and it has to post to file.Receiver side I will be using the File Adapter.

For this I am using JDBC Lookup table through an User Defined function.

// code

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

//Build the Query String

Query = "Select proj_id, proj_short_name from PROJECT where proj_id = '"proj_id[0]"'";

try{

channel = LookupService.getChannel("bs_test" ,"cc_test");

//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 output

resultSet = accessor.execute(Query);

Iterator rows = null;

rows = resultSet.getRows();

while(rows.hasNext())

{

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

// String proj_id = rowMap.get("proj_id");

// String project_name = rowMap.get("proj_short_name");

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

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

//result.addValue(proj_id);

//result.addValue(project_short_name);

}

}

catch(Exception ex)

{

result.addValue(ex.getMessage());

}

finally{

try{

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

}

catch(Exception e)

{

result.addValue(e.getMessage());

}

//@@end

}

While testing in test tab of Message Mapping,I am getting the value as 'NULL' for the project id and am not able to see the other field.

Please suggest me in this.

Thanks in Advance,

Lavanya.B

[varun_k · Accepted solution]

Hi Lavanya Balanandham,

While you are testing in the Message Mapping you would get Null Value only.

So Complete all the required steps. Then test the Whole scenario and try.

Regards,

Varun

[Former Member · Accepted solution]

Hi All,

My actual requirement is,A project ID is being triggered to PI which will pull the data from the database(SQL Server 2000) ,from different tables and it has to post to file.Receiver side I will be using the File Adapter.

For this I am using JDBC Lookup table through an User Defined function.

// code

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

//Build the Query String

Query = "Select proj_id, proj_short_name from PROJECT where proj_id = '"proj_id[0]"'";

try{

channel = LookupService.getChannel("bs_test" ,"cc_test");

//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 output

resultSet = accessor.execute(Query);

Iterator rows = null;

rows = resultSet.getRows();

while(rows.hasNext())

{

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

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

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

}

}

catch(Exception ex)

{

result.addValue(ex.getMessage());

}

finally{

try{

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

}

catch(Exception e)

{

result.addValue(e.getMessage());

}

//@@end

}

While testing in test tab of Message Mapping,I am getting the value as 'NULL' for the project id and am not able to see the other field value.

Output:

<?xml version="1.0" encoding="UTF-8" ?>

<ns0:mt_test_recv xmlns:ns0="http://xxxx">

<proj_id>null</proj_id>

<proj_short_name />

</ns0:mt_test_recv>

Please suggest me in this.

Thanks in Advance,

Lavanya.B

Edited by: Lavanya Balanandham on Dec 5, 2008 6:51 AM

[Former Member · Accepted solution]

Change as below,

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

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

to

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

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

there was a space after proj_id and project_short_name .

Regards

Unni

[Former Member · Accepted solution]

HI

I have sent you a tested Code.

Run complete scenario and check your ID config as well. JDBC channel etc. if the channel is not getting connected then also it will return null.

Thanks

Gaurav

[Former Member]

The problem can be in:

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

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

this step. Kindly check. There is aspace after proj_id...this could be the problem.

Use something like this, and check..

result.addValue("" + rowMap.get("proj_id")); OR using String.valueOf( rowMap.get("proj_id") );

Regards