Need udf for jdbc look up

[allamudi_loordh]

hi Friends,

i need udf for my jdbc look up. In my currecnt scenario i am paaing purchaercode to DB and DB will return some value.i am using below udf still i am facing error.

String SAP_SOLDTO_CUST_ID = "";

Channel channel = null;

            DataBaseAccessor   accessor = null;

            DataBaseResult   resultSet = null;

String Query="Select PURFAC.SAP_SOLDTO_CUST_ID From GCDB.PURFAC INNER JOIN GCDB.BUSUNT ON PURFAC.BUS_UNIT_CODE = BUSUNT.BUS_UNIT_CODE  where BUSUNT.SAP_CO_CODE = purchasercode";

          // Query="Select SAP_UOM_CODE From GCDB.SELLUT Where CTRT_PRCE_UOM_CODE=GCDB.SELLUT-SELL_UNIT";

            try{

            //Determine a channel, as created in the Configuration

            channel = LookupService.getChannel("DBName","JDBCReceiver");            

            //Get a database accessor for the channel.

            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();

           SAP_SOLDTO_CUST_ID=((String)rowMap.get("GCDB.PURFAC.SAP_SOLDTO_CUST_ID"));

            }

            }   

            catch(Exception e)    

            {

            }

            finally{

try{

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

}

catch(Exception ex){

                                                ex.getMessage();

}}

return SAP_SOLDTO_CUST_ID;

Please can anybody correct this code.As i am getting some Sql syntax error because of  sax parser exception.

Regards,

Loordh.

[baskar_gopalakrishnan2]

Just few cents regarding quotes inside the string in java.

>String Query="Select PURFAC.SAP_SOLDTO_CUST_ID From GCDB.PURFAC INNER JOIN GCDB.BUSUNT ON PURFAC.BUS_UNIT_CODE = BUSUNT.BUS_UNIT_CODE  where BUSUNT.SAP_CO_CODE = purchasercode";

This should be as follows...

String Query="Select PURFAC.SAP_SOLDTO_CUST_ID From GCDB.PURFAC INNER JOIN GCDB.BUSUNT ON PURFAC.BUS_UNIT_CODE = BUSUNT.BUS_UNIT_CODE  where BUSUNT.SAP_CO_CODE = \"purchasercode\" ";

or if you want single quote for purchasercode value in the above query then

String Query="Select PURFAC.SAP_SOLDTO_CUST_ID From GCDB.PURFAC INNER JOIN GCDB.BUSUNT ON PURFAC.BUS_UNIT_CODE = BUSUNT.BUS_UNIT_CODE  where BUSUNT.SAP_CO_CODE = \'purchasercode\' ";

Refer this link to learn about patterns using inside the string

http://www.javacoffeebreak.com/faq/faq0050.html

[allamudi_loordh]

hi Bhaskr/Raj,

Thanks for your reply.

AS source side i am having muttliple records coming means , each record contains purchaser code.

So when i am calling method do i need to use remove context function before passing  purchaser code.

I am using result.addValue so obviously Purchaser code is going as String[] Purchasercode into udf.

in the above udf what should i put in place of purchasercode as it is going array.

String Query="Select PURFAC.SAP_SOLDTO_CUST_ID From GCDB.PURFAC INNER JOIN GCDB.BUSUNT ON PURFAC.BUS_UNIT_CODE = BUSUNT.BUS_UNIT_CODE  where BUSUNT.SAP_CO_CODE = \"purchasercode\" ";

Thanks,

Loordh.

[baskar_gopalakrishnan2]

Source side you get more than one purchase code? Are they same or different values? If different use removecontext or parse all the values of purchase code and store in the java vector class or String array.

How to make query?

Use for loop and  call the query inside by passing the string array index which corresponds to each value of purchase code.

>in the above udf what should i put in place of purchasercode as it is going array.

Example:
String[] values = select query returns multiple values

resultList.addValue(values);

Message was edited by: Baskar Gopalakrishnan

[prateek]

In the first look I see that purchasercode in your statement below should be in quotes ""

BUSUNT.SAP_CO_CODE = purchasercode"

As the Query itself is in quotes, you have to provide an escape character there. Therefore it will look like below: Just replace your statement by this:

BUSUNT.SAP_CO_CODE = '"purchasercode"'"

Regards,

Prateek Raj Srivastava

[baskar_gopalakrishnan2]

If your version is 7.1 and above, you can use jdbc graphical lookup already comes in this version. You dont need to write UDF for jdbc lookup. But if the version is below you might have to do only UDF.

Refer my thread answer in this link

http://scn.sap.com/thread/1875401

Also refer these links

For 7.0 -  http://scn.sap.com/people/william.li/blog/2007/03/30/using-jdbc-connection-pool-in-xi-message-mappin...

For 7.1 http://scn.sap.com/people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-s...