Data base lookup call mapping error

[Former Member]

Hi Expertts,

I have a RFC to FIle scenario, in between am using database lookup for getting some fields(OPIS product and Branded).while testing in the message mapping throwing the below error .Please find the below UDF code and the error.Please suggest me the solution that will help me a lot.

public void ProductMasterDBLookup(String[] MaterialNumber,ResultList result,Container container){

Connection con = null;

//CallableStatement cstmt = null;

//ResultSet rs=null;

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

try

{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("Systemname","JDBC 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);

Date currentDatetime = new Date(System.currentTimeMillis());

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

String myDate = formatter.format(currentDatetime);

if (accessor != null)

{

accessor = con.prepareCall("{call xav78.PKG_AV78220_PROS_EXTRACTS.get_material_data(?,?,?,?,?)}");

accessor.setString (1,MaterialNumber);

accessor.setString (2,myDate);

accessor.registerOutParameter(3,Types.INTEGER); //change to TYPES.VARCHAR if it is a STRING

accessor.registerOutParameter(4,Types.VARCHAR); //change to TYPES.VARCHAR if it is a STRING

accessor.registerOutParameter(5,Types.VARCHAR); //change to TYPES.VARCHAR if it is a STRING

resultSet = accessor.executeQuery();

while(resultSet.next())

{

//System.out.println("Inside while()" + resultSet.getString(1)+resultSet.getString(2));

myGlobal1 = accessor.getString(4);

myGlobal2 = accessor.getString(5);

}

}

}

catch(Exception e)

{

System.out.println("Print Exception: " + e);

}

Source code has syntax error:

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:119: cannot resolve symbol

symbol : class Connection

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

Connection con = null;

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:137: cannot resolve symbol

symbol : class SimpleDateFormat

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:137: cannot resolve symbol

symbol : class SimpleDateFormat

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:143: cannot resolve symbol

symbol : method setString (int,java.lang.String[])

location: class com.sap.aii.mapping.lookup.DataBaseAccessor

accessor.setString (1,MaterialNumber);

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:144: cannot resolve symbol

symbol : method setString (int,java.lang.String)

location: class com.sap.aii.mapping.lookup.DataBaseAccessor

accessor.setString (2,myDate);

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:145: cannot resolve symbol

symbol : variable Types

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

accessor.registerOutParameter(3,Types.INTEGER); //change to TYPES.VARCHAR if it is a STRING

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:146: cannot resolve symbol

symbol : variable Types

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

accessor.registerOutParameter(4,Types.VARCHAR); //change to TYPES.VARCHAR if it is a STRING

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:147: cannot resolve symbol

symbol : variable Types

location: class com.sap.xi.tf._MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_

accessor.registerOutParameter(5,Types.VARCHAR); //change to TYPES.VARCHAR if it is a STRING

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:148: cannot resolve symbol

symbol : method executeQuery ()

location: class com.sap.aii.mapping.lookup.DataBaseAccessor

resultSet = accessor.executeQuery();

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:150: cannot resolve symbol

symbol : method next ()

location: interface com.sap.aii.mapping.lookup.DataBaseResult

while(resultSet.next())

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:152: cannot resolve symbol

symbol : method getString (int)

location: interface com.sap.aii.mapping.lookup.DataBaseResult

myGlobal1 = resultSet.getString(4);

^

F:/usr/sap/ID4/DVEBMGS10/j2ee/cluster/server0/./temp/classpath_resolver/Map2f879280050d11dca4ee0015c5888624/source/com/sap/xi/tf/_MM_Z_CBT_RFC_PRODUCTMASTER_TO_Material_Set_.java:153: cannot resolve symbol

symbol : method getString (int)

location: interface com.sap.aii.mapping.lookup.DataBaseResult

myGlobal2 = resultSet.getString(5);

^

12 errors

Regards,

Raju

[Former Member]

Hi Raju,

Could you please tell me the query for executing the stored procedure from UDF.

[former_member189418]

Hi Raju,

I observed two points from ur code.

1. We dont have <b>Connection</b> class in com.sap.aii.mapping.lookup.* API.

2. We can straight away call the SQL Statement using <b>execute</b> method of the DataBaseAccessor.

I think u r mixing the general java JDBC code with this one. correct me if i"m wrong.

Anil

[Former Member]

Hi Anil,

am not sure wheather <b>connection</b> class exist in the import file or not , if not please suggest me how to execute from JDBC without <b>connection</b> class.Can u edit my code and revert me back if u have better idea, it will be great help to me coz i am not java expert.

Raju

[former_member189418]

Hi Raju,

I'm not sure wot u r trying to do with this DBLookup and hence cant code it.

But definetly the following blog will help u.

<a href="/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler:///people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

Hope this helps.

Anil

[Former Member]

Hi Anil,

The main intension of writing this Database lookup code is to execute stored procedure , we need to get the response from the JDBC stored procedure.So that i used connection class here.But u r right the errors seems to be some thing wrong with <b>import file</b>.Please help if u can.

Thanks,

Raju

[Former Member]

Hi ,

Write following code in your UDF

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

Query = <your query>;

try{

channel =LookupService.getChannel(<comm channel name>,< business system>);

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(Query);

if(!(resultSet.equals(null)))

{

Iterator rows = resultSet.getRows();

if (rows.hasNext()) {

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

<your variable>.add(rowMap.get(<db field name>)+ "");

}

catch(Exception ex)

{

result.addValue(ex.getMessage());

}

finally

{

try

{

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

}

catch(Exception e)

{

result.addValue(e.getMessage());

}

}

Thanks ,

Suvarna

Pls award pts if it helps .

[Former Member]

Hi Suvarna,

Please note that i am executing stored procedure through database lookup , which i need response from JDBC, in ur code not mentioned how to execute stored procedure.please go through my code and suggest to rectify the errors, i will definitely give points if it helps me.

Thanks ,

Raju

[Former Member]

Hi ,

If there is not solid reason to use the stored procedure then pls write a query and you can use the code given by me as is .

if you want to use the code given by you i will send you the list of corrections .

Thanks

Suvarna

[Former Member]

Hi,

Actually i need to execute the stored procedure , coz i am sending RFC structure to file in betn i need some fields from JDBC , so its manditory to use stored procedure for getting response , with the combination of JDBC response and RFC input i will map to target structure.any idea abt rectifying the code.

Thanks,

Raju

[former_member189418]

Hi Raju,

I observed two points from ur code.

1. We dont have <b>Connection</b> class in com.sap.aii.mapping.lookup.* API.

2. We can straight away call the SQL Statement using <b>execute</b> method of the DataBaseAccessor.

I think u r mixing the general java JDBC code with this one. correct me if i"m wrong.

Anil