Skip to Content

Error in UDF

Hi All,

I'm trying to write a udf for DB Lookup, however I'm getting syntax error. Though I'm able to execute Java Code individually. Please let me know if there is any error in the code.

import java.sql.*;

import oracle.sql.*;

import oracle.jdbc.driver.*;

public void DBLookupSeqNo(String strSeqNo,ResultList result,Container container){

String strProcessFlag = "Y";

String strSelQuery="";

String strUpdateQuery="";

String strCount="";

String Process_End_Timstm="";

ResultSet rs =null;

PreparedStatement pstmtSel=null ;

PreparedStatement pstmtUpd =null;

Connection conn =null;

// Build the Query String

strSelQuery ="select count(*) as COUNT from gif_file_processing where file_seq_no ='"+strSeqNo+"' AND interface_id = '"+Interface_ID+"'" ;

strUpdateQuery ="UPDATE gif_file_processing SET run_flag ='"+strProcessFlag+"', run_id = '"+Run_ID+"', process_start_time = TO_TIMESTAMP ( '"+Process_Start_Timstm+"' , 'YYYY-MM-DD HH24:MI:SS.FF'),process_end_time = TO_TIMESTAMP ( '"+Process_End_Timstm+"' , 'YYYY-MM-DD HH24:MI:SS.FF'), input_cnt = '0', output_cnt = '0', error_cnt = '0' where file_seq_no ='"+strSeqNo+"' AND interface_id = '"+Interface_ID+"'" ;

Process_End_Timstm=(new java.sql.Timestamp(System.currentTimeMillis())).toString();

//Connect to DB Execute Query and get the values in resultset

try{

Class.forName( "oracle.jdbc.driver.OracleDriver" );

conn = DriverManager.getConnection("jdbc:oracle:thin:@10.11.12.13:8080:SANDBOX", "user", "pwd");

trace.addInfo(" < ---------- Connection Establised---------- > " );

pstmtSel = conn.prepareStatement(strSelQuery);

pstmtUpd = conn.prepareStatement(strUpdateQuery);

rs = pstmtSel.executeQuery();

trace.addInfo(" < ---------- Select Query Executed---------- > " );

while (rs.next())

{

strCount=rs.getString("COUNT");

if (strCount.equalsIgnoreCase("1"))

{

pstmtUpd.executeUpdate();

trace.addInfo(" < ---------- Update Query Executed ---------- > ");

conn.commit();

} else {

trace.addWarning("Multiple Rows fetched.Cannot progress with update");

}

}

}catch(Exception e){

trace.addWarning("Error in DB Lookup while updating sequence number details and exception message is:" +e);

String errDesc="Error in JDBC Adapter Connection while updating sequence number details";

strErrorMsg=prepErrorMsg.ErrorMsg(Component_ID,Transaction_ID,Project_ID,Interface_ID,"900043",errDesc,"2","OPR",e.getMessage(),fieldDelimiter);

} finally {

rs.close();

pstmtSel.close();

pstmtUpd.close();

conn.close();

}

The requirement for this code:

1) Access DB Table from Message Mapping

2) Validate the Table

3) Insert Records in DB Table

Limitations:

1) We are not supposed to use BPM

2) Version PI 7.0 so no chance of using Graphical JDBC LookUp.

Thanks in Advance,

Regards,

Sreedhar

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on May 24, 2012 at 12:57 PM

    Not sure if follow code would work for UDFs:

    Class.forName( "oracle.jdbc.driver.OracleDriver" );

    However, you could use JDBC connection pool and DataSource object for getting access to JDBC API.

    Please refer this blog with detailed instructions for PI 7.0.

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

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Alex,

      First of all I Thank you for the response. I've modified my UDF but not sure if it is correct.

      String Query1 = "";

      String Query2="";

      Channel channel = null;

      DataBaseAccessor accessor = null;

      DataBaseResult resultSet = null;

      //Query String

      Query1="Select Query to get Sequence number from DB";

      Query2= "Query to Update DB Status field";

      try{

      //Channel created in the Configuration

      channel = LookupService.getChannel("BusinessSystem","JDBC_Channel");

      //Execute Query

      accessor = LookupService.getDataBaseAccessor(channel);

      resultSet = accessor.execute(Query1);

      for(Iterator rows = resultSet.getRows();rows.hasNext();){

      Object element=rows.next();

      if(((String)element).equals(SequenceNumber))

      {}

      else

      accessor.execute(Query2);

      }

      }

      catch(Exception ex)

      {

      }

      finally{

      try{

      if (accessor!=null)

      {accessor.close();

      }

      }

      catch(Exception e)

      {

      }

      }

      I've gone through the link provided by you.

      Could you suggest me how to use JDBC connection pool and DataSource object for getting access to JDBC API as I don't have access to the Visual Administrator

      Is there any other approach through which I can achieve my requirement?

  • Posted on May 24, 2012 at 12:57 PM

    Hi Sreehar,

    I copy/pasted a piece of this code into Eclipse and right away I see that you reference three variables that are not declared when you build your query string:

    Run_ID, Interface_ID & Process_Start_Timstm

    It's not going to compile until you declare these first.

    Regards,

    Ryan Crosby

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 12, 2012 at 05:19 AM

    Hi Ryan,

    Thank you for your support. I could execute the UDF successfully after importing the OJDBC.jar in imported archives. Now my updation of the DB Table is being done.

    Regards,

    Sreedhar, Av

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.