[FIXED] Malformed function or procedure escape syntax at offset 1

Issue

I executed a code in eclipse and my objective is to call a stored procedure in postgres database. I tried with values passed from servlet and got this error: " Malformed function or procedure escape syntax at offset 1."

so now I tried hardcoding the values . Still the same problem. What is offset? please help me to resolve the error. Iv already checked the order and datatype of fields.They are correct.

public int  dispCustomer3(Cust cc){
    con=dbCon.getConnection();
    //PreparedStatement ps3=null;
    System.out.println("inside update function ");
    CallableStatement callableStatement =null;
    try {
        callableStatement=con.prepareCall("{SELECT fn_UpdateCustomer(37, 'Test_Customer56','Test_Customer56','Requirement','Customer_Location',NULL,2,2,NULL,'Customer_Contact_Info','Account_Contact_Info','01-02-2016','01-04-2016',5,'Comments',1,2);}");
        /*callableStatement.setInt(1,cc.getCustId());
        callableStatement.setString(2,cc.getShortName());
        callableStatement.setString(3,cc.getStatus_name() );
        callableStatement.setString(4,cc.getRequirement());
        callableStatement.setString(5,cc.getCustomer_location());
        callableStatement.setInt(6,cc.getDemo_location_type_id());
        callableStatement.setInt(7, cc.getDeployment_type_id());
        callableStatement.setInt(8, cc.getRequested_by_id());
        callableStatement.setInt(9, cc.getPilot_resource_id());
        callableStatement.setString(10, cc.getCustomer_contact_info());
        callableStatement.setString(11, cc.getAccount_contact_info());
        callableStatement.setDate(12, cc.getDemo_planned_on());
        callableStatement.setDate(13, cc.getDemo_actual_on());
        callableStatement.setInt(14, cc.getStatus_id());
        callableStatement.setString(15, cc.getComments());*/
        callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
        callableStatement.executeUpdate();



    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{dbCon.closeConnection(con);}
    return 1;



}
}

Solution

When using CallableStatement you should CALL your procedure, rather than SELECT. Something like:

callableStatement=con.prepareCall("{CALL fn_UpdateCustomer(37, 'Test_Customer56','Test_Customer56','Requirement','Customer_Location',NULL,2,2,NULL,'Customer_Contact_Info','Account_Contact_Info','01-02-2016','01-04-2016',5,'Comments',1,2)}");

The error that you get, Malformed function or procedure escape syntax at offset 1 refers to the SELECT keyword.

Answered By - Sevle
Answer Checked By - David Marino (JavaFixing Volunteer)