Error in UDF

[former_member644654]

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

[former_member644654]

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

[Ryan-Crosby]

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

[former_member644654]

Hi Ryan,

Thank you for the response.

I've declared those fields globally in the Java Section where it is being filled with the values.

Is the rest of the code correct? Please suggest me if any other code is available for this requirement.

I've tried using a new code also but not sure if it works as it requires a JDBC communication channel to be created and I'm not able to understand where to call it in the Configuration scenario.

The code is like:

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)

{

}

}         

[Ryan-Crosby]

Hi Sreedhar,

What exactly is the syntax error that you get with the original code?  I'm not familiar with this second approach so I couldn't say if the code looks right or not... although that is rather interesting use of a FOR loop.

Regards,

Ryan Crosby

[Former Member]

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-mappin...

[former_member644654]

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?