-2

In the below code I want to call one stored procedures and execute one Query. I am facing error at statement.executeUpdate(); Please help in fixing it. I am not sure where it going wrong.

public void Dbexe() {

    Connection connection;
    connection = DatabaseConnection.getCon();
     CallableStatement stmt;
    try {
        stmt = connection.prepareCall("{CALL optg.Ld_SOpp}");
        stmt.executeUpdate();
        stmt.close();
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

     System.out.println("Stored Procedure executed");

     //PreparedStatement statement = null;
    // ResultSet rs = null;

    try{


     PreparedStatement statement;
    try {
        statement = connection.prepareStatement("MERGE INTO OPTG.R_VAL AS TARGET USING" + 
              ........... +
             "");

         statement.executeUpdate(); //Here the exception is thrown  
         statement.close();

         connection.commit();
         connection.close();


    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }   

    // statement = connection.prepareStatement(query);

     //statement.close();

    }

    finally{

        System.out.println("Data is copied to the  Table");

            }    

 }
SysDragon
  • 9,692
  • 15
  • 60
  • 89
123HIS
  • 103
  • 3
  • 5
  • 19
  • 1
    The problem seems to be in the sql sentence you're trying to execute. I mean, is an error from DB2, not java. Probably we gonna need the whole sql statement to identify where the error is. – dic19 Aug 30 '13 at 12:53
  • 1
    Hi, U r right.. the problem was in Query. I corrected. Thank u. – 123HIS Aug 30 '13 at 16:22

2 Answers2

5

Little off-topic: You should use CallableStatement instead if you want to call a store procedure (see documentation):

CallableStatement callableStatement = connection.prepareCall("{call opptymgmt.Load_SiebelOpportunity}");
ResultSet rs = callableStatement.executeQuery();

I would also suggest you check this topic How to properly clean up JDBC resources in Java?. It was very helpful to me.

Update: based on this stack trace:

com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=MERGE INTO OPPTYMGMT.REVENUE_VALIDAT;BEGIN-OF-STATEMENT;<variable_set>, DRIVER=4.7.85

The problem seems to be in the sql sentence you're trying to execute. I mean, is an error from DB2, not java. You should check your sql statement.

Community
  • 1
  • 1
dic19
  • 17,821
  • 6
  • 40
  • 69
  • Hi, thanks for the reply. Im getting error at statement.executeUpdate(); – 123HIS Aug 30 '13 at 11:57
  • com.ibm.db2.jcc.am.mo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=MERGE INTO OPPTYMGMT.REVENUE_VALIDAT;BEGIN-OF-STATEMENT;, DRIVER=4.7.85 – 123HIS Aug 30 '13 at 12:18
  • Can u please check whether I have closed connection, callablestatement, prepared statments properly or not? – 123HIS Aug 30 '13 at 12:26
0

I got it working in this method:

 PreparedStatement myStmt = conn.prepareStatement(sqlQuery);
    myStmt.setInt(1, id); //position of parameter (1,2,3....) , value
    
     ResultSet rs = myStmt.executeQuery();
       
    while (rs.next()) {
        int jobId = rs.getInt("jobId"); ....... }
Shameel Perayil
  • 86
  • 1
  • 11