java.sql.SQLException: Missing IN or OUT parameter at index::4 error occurs with preparestatement

Question

strInsertQuery="INSERT INTO SYNPACKAGEFREEUSAGEMAPPING (PACKAGEID,SERVICEID,PRIORITY,MAPPINGID,ATTRIBUTEVALUE,FREEUSAGE,UOM,PARAMSTR1,UNITCREDITPOLICYDETAILID) VALUES (?,?,?,?,?,?,?,?,?)";
newPstmt=newCon.prepareStatement(strInsertQuery);
newPstmt.setString(1,strProductId);
              newPstmt.setString(2,strUPGServiceId);
              newPstmt.setInt(3,0);
              if(FieldMappingrs.next())
              {
               newPstmt.setLong(4,FieldMappingrs.getLong(1));
              }
              newPstmt.setString(5,ucpDetailData.getCreditattributevalue());
              for(Iterator itrColUnitCreditDetail=ucpData.iterator();itrColUnitCreditDetail.hasNext();)
       {

        unitCreditData = (UnitCreditDetailData)itrColUnitCreditDetail.next();
        if(ucpDetailData.getUnitcreditpolicydetailid().equals(unitCreditData.getUnitcreditpolicydetailid()))
        {
         debugLog(PackageConstant.PACKAGE_MODULE_NAME,"ServicePackageSessionBean:createFreeUsageServiceDetails() unitCreditData "+ unitCreditData);
                newPstmt.setDouble(6,unitCreditData.getEndvalue());
               }

               debugLog(PackageConstant.PACKAGE_MODULE_NAME,"Insert record successfull"+ ucpDetailData);
              }
              newPstmt.setString(7,ucpDetailData.getUom());
              newPstmt.setString(8,ucpDetailData.getCreditattribute());
              newPstmt.setString(9,ucpDetailData.getUnitcreditpolicydetailid());

              newPstmt.executeUpdate();

Answer 1

Well, look at this:

if(FieldMappingrs.next())
{
    newPstmt.setLong(4,FieldMappingrs.getLong(1));
}

So you're conditionally setting parameter 4.

What do you want to insert if FieldMappingsrs.next() returns false? (I suspect this is what is happening.) Try setting it to an appropriate value in an else block.

Answer 2

Your problem is just at this block

  if(FieldMappingrs.next()) {
      newPstmt.setLong(4,FieldMappingrs.getLong(1));
  }

As you see if .next() returns false you won't be passing the parameter at index 4.

I suggest to, if you can pass to that table 0 instead of null use this

  if(FieldMappingrs.next()) {
      newPstmt.setLong(4,FieldMappingrs.getLong(1));
  } else {
      newPstmt.setLong(4,0);
  }

Or if it is required value, just throw an exception.