Mapping JDBC lookup: Passing values to multiple fields at target

[Former Member]

Hi all

i my scenario i am using mapping JDBC lookup ,and it is working sucessfully.

but i need to select 4 fields from lookups select query and passing it to 3 different target field at target MT.

if i use Global container object methods,than only 1 field i can store inglobal container variable..

and return it 1 the target fields...

BUT how do i pass 4 fields ,result of the select query to 4 different fields at target MT..

query :"

Select BPNO,emp,BENR,bacepack from Ztable where BPNO='"+BPNO[0]+"'";

Regards

AjayP

[GabrielSagaya · Accepted solution]

User-Defined Function

Description

Imports java.sql.*;

public String UOMLookupJDBC(String a,Container container){

//write your code here

String url = "jdbc:microsoft:sqlserver://10.100.100.1:1433;DatabaseName=TESTDB";

String user = "TESTUSER";

String pass = "TESTPASS";

Connection con = null;

Statement stmt = null;

ResultSet rset = null;

String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

String expStr = "";

String sql = "";

try{

Class.forName( driver );

con = DriverManager.getConnection(url, user, pass );

stmt = con.createStatement();

sql = "Select BPNO,emp,BENR,bacepack from Ztable where BPNO='"BPNO[0]"'";

sql = "SELECT CODE FROM Material03 WHERE MEINS = '"+ a +"'";

rset = stmt.executeQuery ( sql );

while(rset.next()){

expStr = rset.getString(1);

expStr = expStr","rset.getString(2);

expStr = expStr","rset.getString(3);

expStr = expStr","rset.getString(4);

}

if(rset != null)

rset.close();

if(stmt != null )

stmt.close();

if(con != null )

con.close();

}catch(Exception e) {

e.getMessage();

e.printStackTrace();

}

return expStr;

[Former Member · Accepted solution]

Dear Gabriel Sagaya & Ramu

Concatenation fullfill only the requirment of getting all 4 values as a result from UDF of JDBC lookups..

But it does'nt solve the problem of mapping these 4 values to 4 different fields of target MT.

Regards

AjayP

[former_member181962 · Accepted solution]

HI Ajay,

Once you get the value returned, put that in a global container variable.

YOu can take substring of that variable to map to your four variables.

Regards,

Ravi

[Former Member · Accepted solution]

Dear Ravi

BUT using Global Container ,if i use

globalContainer_obj1.setparameter.(BPNO);

globalContainer_obj2.setparameter.(emp);

globalContainer_obj3.setparameter.(benr);

globalContainer_obj4.setparameter.(bacepack);

the final value of "bacepack " get is over written in all 4 target fields...

i am able to use GlobalContainer only for 1 field...

as mentioned in first post...

Regards,

AjayP

[former_member181962 · Accepted solution]

YOu have to use the set parameter using two arguments (Name, value pairs).

globalContainer_obj1.setParameter.("BPNO",BPNO);

globalContainer_obj2.setParameter.("emp",emp);

globalContainer_obj3.setParameter.("benr",benr);

globalContainer_obj4.setParameter.("bacepack",bacepack);

To retrieve these values, you should use the getParameter function as below.

bpno = globalContainer_obj1.getParameter.("BPNO");

emp = globalContainer_obj1.getParameter.("emp");

benr = globalContainer_obj1.getParameter.("benr");

bacepack = globalContainer_obj1.getParameter.("bacepack");

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi Global container solved th problem.

Regards,

AjayP

[Former Member]

Hi,

I have to fetch 3 values and populate it to the 3 fields in the target. The UDF am using is as attached. This UDF is for fetching one value. Kindly tell me about the changes I will have to make to fetch 3 values instead of 1 value.

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

// Query to retrieve the PROP value for the particular source value passed.

Query ="Select PROP from TANKS where ID='" + ID[0] + "' ";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("<Business Service>","<Communication Channel>");

//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("PROP"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

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

}

catch(Exception e){

result.addValue(e.getMessage());

}}

[Former Member]

.

[Former Member]

Hi,

Use UDF to concat all strings.

Thanks,

RamuV