Sample ObjectHandler to report all the exceptions at once during an excel import

To use this, set the "Object Handler Class" at the datasource definition to "xxra.customobjects.ExcelImportBulkErrorHandler"
package xxra.customobjects;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import com.rapapp.platform.client.mdata.RPCModelData;
import com.rapapp.platform.client.rpc.RAException;
import com.rapapp.platform.server.RAObject;
import com.rapapp.platform.server.RAObjectAttribute;

/**
 * Use this handler to throw all the data type validations from an excel import as a single message to the user
 * Note: this handler also converts the string value "NULL" into misChar, misNum & misDate per the data type of the attribute
 */
public class ExcelImportBulkErrorHandler extends AbstractObjectHandler {
	private static final String OTHERS = ": Others: ";
	private static final String OPEN_BRACKET = "(";
	private static final String CLOSE_BRACKET = ")";
	private static final String INVALID_SIZE = ": Invalid Size [";
	private static final String MUST_BE = " must be < ";
	private static final String BR = "<br/>";
	private static final String COMMA_SPACE = ", ";
	private static final String INVALID_DATA = "Invalid Data";
	private static final String NULL = "NULL";
	private static final String LINE = "[Line ";
	private static final String CLOSE_SQUARE_BRACKET = "]";
	private static final String INVALID_DATE = ": Invalid Date [";
	private static final String INVALID_NUMBER = ": Invalid Number [";
	private static final String SPACE = " ";
	Double misNum = null;
	String misChar = null;
	Timestamp misDate = null;

	private void initNULL(Connection con) throws SQLException {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery("SELECT 9.99E125, chr(0), TO_DATE('1','j') FROM DUAL");
			rs.next();
			misNum = rs.getDouble(1);
			misChar = rs.getString(2);
			misDate = rs.getTimestamp(3);
		} finally {
			DbUtils.closeQuietly(rs);
			DbUtils.closeQuietly(st);
		}
	}

	@Override
	public void handleBeforeProcessRows(Connection con, HandlerContext context, List rows) throws Exception {
		if (rows == null || rows.size() == 0)
			return;

		RAObject object = context.getScriptUtil().getCurrentRAObject();
		List<RAObjectAttribute> attrs = object.getAttrList();
		RAObjectAttribute attr = null;
		int count = rows.size();
		RPCModelData row = null;
		int attrCount = attrs.size();
		Object value = null;
		StringBuilder allErrors = new StringBuilder(), sb = new StringBuilder();
		for (int i = 0; i < count; i++) {
			row = (RPCModelData) rows.get(i);

			for (int a = 0; a < attrCount; a++) {
				try {
					attr = attrs.get(a);
					value = row.get(attr.getAttributeCode());
					if (value != null) {
						if (value instanceof String && NULL.equalsIgnoreCase((String) value)) {
							if (misNum == null) {
								initNULL(con);
							}
							if (attr.isDouble()) {
								value = misNum;
							} else if (attr.isDate()) {
								value = misDate;
							} else {
								value = misChar;
							}
							row.set(attr.getAttributeCode(), value);
						}
						if (attr.isDouble() && !(value instanceof Double)) {
							if (sb.length() != 0) {
								sb.append(COMMA_SPACE);
							}
							sb.append(attr.getAttributeName()).append(INVALID_NUMBER).append(value)
									.append(CLOSE_SQUARE_BRACKET).append(SPACE);
						} else if (attr.isDate() && !(value instanceof Date)) {
							if (sb.length() != 0) {
								sb.append(COMMA_SPACE);
							}
							sb.append(attr.getAttributeName()).append(INVALID_DATE).append(value)
									.append(CLOSE_SQUARE_BRACKET).append(SPACE);
						} else if (attr.isString() && attr.getMaxLength() != null
								&& attr.getMaxLength().longValue() > 0
								&& attr.getMaxLength().longValue() < ((String) value).length()) {
							if (sb.length() != 0) {
								sb.append(COMMA_SPACE);
							}
							sb.append(attr.getAttributeName()).append(INVALID_SIZE).append(value).append(SPACE)
									.append(OPEN_BRACKET).append(((String) value).length()).append(CLOSE_BRACKET)
									.append(CLOSE_SQUARE_BRACKET).append(MUST_BE)
									.append(attr.getMaxLength().longValue()).append(SPACE);
						}
					}
				} catch (Exception e) {
					sb.append(attr.getAttributeName()).append(OTHERS).append(e.getMessage()).append(SPACE);
				}
			}
			if (sb.length() > 0) {
				allErrors.append(BR).append(LINE).append(i).append(CLOSE_SQUARE_BRACKET).append(SPACE).append(sb);
				sb.setLength(0);
			}
		}
		if (allErrors.length() > 0) {
			allErrors.insert(0, BR);
			allErrors.append("<br/><br/>You must correct the above errors and re-upload the corrected file.<br/><br/>");
			throw new RAException(INVALID_DATA, allErrors.toString());
		}
	}
}

Location

101 California Street, Suite 2710
San Francisco, CA 94111


440 N. Wolfe Rd.
Sunnyvale, CA 94085


Office 11, 5th Floor, Building 9,
Mindspace IT Park
Hyderabad 500081

p: +1 (844) AT CloudIO (844-282-5683)
f: +1 (650) 300-5247 | e: sales@cloudio.io
© 2009, 2017 CloudIO Inc. | Terms of Use | Privacy Policy