List of usage examples for java.sql ResultSet getByte
byte getByte(String columnLabel) throws SQLException;
ResultSet object as a byte in the Java programming language. From source file:com.flexive.core.storage.genericSQL.GenericEnvironmentLoader.java
/** * {@inheritDoc}// ww w . jav a 2s .c o m */ @Override public List<FxType> loadTypes(Connection con, FxEnvironment environment) throws FxLoadException { Statement stmt = null; PreparedStatement ps = null; String curSql; ArrayList<FxType> result = new ArrayList<FxType>(20); try { final Map<Long, List<FxStructureOption>> typeOptions = loadAllTypeOptions(con); // 1 2 3 4 ps = con.prepareStatement("SELECT TYPESRC, TYPEDST, MAXSRC, MAXDST FROM " + TBL_STRUCT_TYPERELATIONS + " WHERE TYPEDEF=?"); // 1 2 3 4 5 6 curSql = "SELECT ID, NAME, PARENT, STORAGE_MODE, CATEGORY, TYPE_MODE, " + //7 8 9 10 11 12 "LANG_MODE, TYPE_STATE, SECURITY_MODE, TRACKHISTORY, HISTORY_AGE, MAX_VERSIONS," + //13 14 15 16 17 18 19 20 21 "REL_TOTAL_MAXSRC, REL_TOTAL_MAXDST, CREATED_BY, CREATED_AT, MODIFIED_BY, MODIFIED_AT, ACL, WORKFLOW, ICON_REF, " + // 22 23 24 25 "MULTIPLE_CONTENT_ACLS, INSUPERTYPEQUERY, DEFACL, AUTO_VERSION" + " FROM " + TBL_STRUCT_TYPES + " ORDER BY NAME"; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(curSql); ResultSet rsRelations; final Map<Long, FxString[]> labels = Database.loadFxStrings(con, TBL_STRUCT_TYPES, "description"); while (rs != null && rs.next()) { try { final long id = rs.getLong(1); ps.setLong(1, id); ArrayList<FxTypeRelation> alRelations = new ArrayList<FxTypeRelation>(10); rsRelations = ps.executeQuery(); while (rsRelations != null && rsRelations.next()) alRelations.add(new FxTypeRelation(new FxPreloadType(rsRelations.getLong(1)), new FxPreloadType(rsRelations.getLong(2)), rsRelations.getInt(3), rsRelations.getInt(4))); long parentId = rs.getLong(3); FxType parentType = id == 0 ? null : new FxPreloadType(parentId); final long defaultInstanceACLId = rs.getInt(24); final ACL defaultInstanceACL; if (!rs.wasNull()) defaultInstanceACL = environment.getACL(defaultInstanceACLId); else defaultInstanceACL = null; FxType _type = new FxType(id, environment.getACL(rs.getInt(19)), defaultInstanceACL, environment.getWorkflow(rs.getInt(20)), rs.getString(2), getTranslation(labels, id, 0, false), parentType, TypeStorageMode.getById(rs.getInt(4)), TypeCategory.getById(rs.getInt(5)), TypeMode.getById(rs.getInt(6)), LanguageMode.getById(rs.getInt(7)), TypeState.getById(rs.getInt(8)), rs.getByte(9), rs.getBoolean(22), rs.getBoolean(23), rs.getBoolean(10), rs.getLong(11), rs.getLong(12), rs.getBoolean(25), rs.getInt(13), rs.getInt(14), LifeCycleInfoImpl.load(rs, 15, 16, 17, 18), new ArrayList<FxType>(5), alRelations, FxSharedUtils.get(typeOptions, id, null)); long iconId = rs.getLong(21); if (!rs.wasNull()) _type.getIcon().setValue(new ReferencedContent(iconId)); result.add(_type); } catch (FxNotFoundException e) { throw new FxLoadException(LOG, e); } } } catch (SQLException exc) { throw new FxLoadException(LOG, "Failed to load all FxTypes: " + exc.getMessage(), exc); } finally { try { if (ps != null) ps.close(); } catch (SQLException e) { //ignore } Database.closeObjects(GenericEnvironmentLoader.class, null, stmt); } return result; }
From source file:mysql5.MySQL5PlayerDAO.java
@Override public PlayerCommonData loadPlayerCommonData(final int playerObjId) { PlayerCommonData cached = playerCommonData.get(playerObjId); if (cached != null) { log.debug("[DAO: MySQL5PlayerDAO] PlayerCommonData for id: " + playerObjId + " obtained from cache"); return cached; }//from w w w . java 2 s . c o m final PlayerCommonData cd = new PlayerCommonData(playerObjId); boolean success = false; Connection con = null; try { con = DatabaseFactory.getConnection(); PreparedStatement stmt = con.prepareStatement("SELECT * FROM players WHERE id = ?"); stmt.setInt(1, playerObjId); ResultSet resultSet = stmt.executeQuery(); log.debug("[DAO: MySQL5PlayerDAO] loading from db " + playerObjId); if (resultSet.next()) { success = true; cd.setName(resultSet.getString("name")); // set player class before exp cd.setPlayerClass(PlayerClass.valueOf(resultSet.getString("player_class"))); cd.setExp(resultSet.getLong("exp")); cd.setRecoverableExp(resultSet.getLong("recoverexp")); cd.setRace(Race.valueOf(resultSet.getString("race"))); cd.setGender(Gender.valueOf(resultSet.getString("gender"))); cd.setLastOnline(resultSet.getTimestamp("last_online")); cd.setNote(resultSet.getString("note")); cd.setQuestExpands(resultSet.getInt("quest_expands")); cd.setNpcExpands(resultSet.getInt("npc_expands")); cd.setAdvancedStigmaSlotSize(resultSet.getInt("advanced_stigma_slot_size")); cd.setTitleId(resultSet.getInt("title_id")); cd.setBonusTitleId(resultSet.getInt("bonus_title_id")); cd.setWarehouseSize(resultSet.getInt("warehouse_size")); cd.setOnline(resultSet.getBoolean("online")); cd.setMailboxLetters(resultSet.getInt("mailbox_letters")); cd.setDp(resultSet.getInt("dp")); cd.setDeathCount(resultSet.getInt("soul_sickness")); cd.setCurrentReposteEnergy(resultSet.getLong("reposte_energy")); cd.setCurrentEventExp(resultSet.getLong("event_exp")); cd.setBattleGroundPoints(resultSet.getInt("bg_points")); float x = resultSet.getFloat("x"); float y = resultSet.getFloat("y"); float z = resultSet.getFloat("z"); byte heading = resultSet.getByte("heading"); int worldId = resultSet.getInt("world_id"); PlayerInitialData playerInitialData = DataManager.PLAYER_INITIAL_DATA; boolean checkThis = World.getInstance().getWorldMap(worldId).isInstanceType(); // this helps to pretend an player loading error // if you have a better idea do it :) if (checkThis) { mr = null; } else { mr = World.getInstance().getWorldMap(worldId).getMainWorldMapInstance().getRegion(x, y, z); } if (mr == null && playerInitialData != null) { // unstuck unlucky characters :) LocationData ld = playerInitialData.getSpawnLocation(cd.getRace()); x = ld.getX(); y = ld.getY(); z = ld.getZ(); heading = ld.getHeading(); worldId = ld.getMapId(); } WorldPosition position = World.getInstance().createPosition(worldId, x, y, z, heading, 0); cd.setPosition(position); cd.setWorldOwnerId(resultSet.getInt("world_owner")); cd.setMentorFlagTime(resultSet.getInt("mentor_flag_time")); cd.setInitialGameStats(resultSet.getInt("initial_gamestats")); cd.setLastTransferTime(resultSet.getLong("last_transfer_time")); cd.setFatigue(resultSet.getInt("fatigue")); cd.setFatigueRecover(resultSet.getInt("fatigueRecover")); cd.setFatigueReset(resultSet.getInt("fatigueReset")); cd.setPassportStamps(resultSet.getInt("stamps")); cd.setPassportReward(resultSet.getInt("rewarded_pass")); cd.setLastStamp(resultSet.getTimestamp("last_stamp")); cd.setJoinRequestLegionId(resultSet.getInt("joinRequestLegionId")); cd.setJoinRequestState(LegionJoinRequestState.valueOf(resultSet.getString("joinRequestState"))); } else { log.info("Missing PlayerCommonData from db " + playerObjId); } resultSet.close(); stmt.close(); } catch (Exception e) { log.error("Could not restore PlayerCommonData data for player: " + playerObjId + " from DB: " + e.getMessage(), e); } finally { DatabaseFactory.close(con); } if (success) { if (CacheConfig.CACHE_COMMONDATA) { playerCommonData.put(playerObjId, cd); playerCommonDataByName.put(cd.getName().toLowerCase(), cd); } return cd; } return null; }
From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java
@SuppressWarnings("deprecation") private void assertNonExistingColumn(final ResultSet rs) throws Exception { int nonExistingColIndex = Integer.MAX_VALUE; String nonExistingColName = "col" + nonExistingColIndex; try {/*www. ja v a 2 s . co m*/ rs.getString(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getString(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBoolean(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getByte(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getShort(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getInt(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getLong(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getFloat(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDouble(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColIndex, 1); fail(); } catch (SQLException ignore) { } try { rs.getBigDecimal(nonExistingColName, 1); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBytes(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getDate(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTime(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColIndex, null); fail(); } catch (SQLException ignore) { } try { rs.getTimestamp(nonExistingColName, null); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getAsciiStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getUnicodeStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getBinaryStream(nonExistingColName); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColIndex); fail(); } catch (SQLException ignore) { } try { rs.getCharacterStream(nonExistingColName); fail(); } catch (SQLException ignore) { } }
From source file:com.flexive.ejb.beans.structure.TypeEngineBean.java
/** * Load a type from the given (and open!) connection * * @param con an open and valid connection * @param id if of the type to load/*from w ww . ja v a 2 s. co m*/ * @return FxType * @throws FxLoadException on errors * @throws FxNotFoundException on errors */ private FxType loadType(Connection con, long id) throws FxLoadException, FxNotFoundException { Statement stmt = null; PreparedStatement ps = null; String curSql; FxEnvironment environment = CacheAdmin.getEnvironment(); try { // load structure options for a given type final List<FxStructureOption> options = loadTypeOptions(con, id, "ID", TBL_STRUCT_TYPES_OPTIONS); // 1 2 3 4 ps = con.prepareStatement("SELECT TYPESRC, TYPEDST, MAXSRC, MAXDST FROM " + TBL_STRUCT_TYPERELATIONS + " WHERE TYPEDEF=?"); // 1 2 3 4 5 6 curSql = "SELECT ID, NAME, PARENT, STORAGE_MODE, CATEGORY, TYPE_MODE, " + //7 8 9 10 11 12 "LANG_MODE, TYPE_STATE, SECURITY_MODE, TRACKHISTORY, HISTORY_AGE, MAX_VERSIONS," + //13 14 15 16 17 18 19 20 "REL_TOTAL_MAXSRC, REL_TOTAL_MAXDST, CREATED_BY, CREATED_AT, MODIFIED_BY, MODIFIED_AT, ACL, WORKFLOW, " + // 21 22 23 24 "MULTIPLE_CONTENT_ACLS, INSUPERTYPEQUERY, DEFACL, AUTO_VERSION" + " FROM " + TBL_STRUCT_TYPES + " WHERE ID=" + id; stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(curSql); ResultSet rsRelations; if (rs != null && rs.next()) { try { ps.setLong(1, rs.getLong(1)); List<FxTypeRelation> alRelations = new ArrayList<FxTypeRelation>(10); rsRelations = ps.executeQuery(); while (rsRelations != null && rsRelations.next()) alRelations.add(new FxTypeRelation(new FxPreloadType(rsRelations.getLong(1)), new FxPreloadType(rsRelations.getLong(2)), rsRelations.getInt(3), rsRelations.getInt(4))); final long defaultInstanceACLId = rs.getInt(23); final ACL defaultInstanceACL; if (!rs.wasNull()) defaultInstanceACL = environment.getACL(defaultInstanceACLId); else defaultInstanceACL = null; return new FxType(rs.getLong(1), environment.getACL(rs.getInt(19)), defaultInstanceACL, environment.getWorkflow(rs.getInt(20)), rs.getString(2), Database.loadFxString(con, TBL_STRUCT_TYPES, "description", "id=" + rs.getLong(1)), new FxPreloadType(rs.getLong(3)), TypeStorageMode.getById(rs.getInt(4)), TypeCategory.getById(rs.getInt(5)), TypeMode.getById(rs.getInt(6)), LanguageMode.getById(rs.getInt(7)), TypeState.getById(rs.getInt(8)), rs.getByte(9), rs.getBoolean(21), rs.getBoolean(22), rs.getBoolean(10), rs.getLong(11), rs.getLong(12), rs.getBoolean(24), rs.getInt(13), rs.getInt(14), LifeCycleInfoImpl.load(rs, 15, 16, 17, 18), new ArrayList<FxType>(5), alRelations, options); } catch (FxNotFoundException e) { throw new FxLoadException(LOG, e); } } throw new FxNotFoundException(LOG, "FxType with id " + id + " does not exist!"); } catch (SQLException exc) { throw new FxLoadException(LOG, "Failed to load FxType: " + exc.getMessage(), exc); } finally { try { if (ps != null) ps.close(); } catch (SQLException e) { //ignore } Database.closeObjects(TypeEngineBean.class, null, stmt); } }
From source file:org.cloudgraph.rdb.service.RDBDataConverter.java
private Object convertFrom(ResultSet rs, int columnIndex, int sourceType, Property property) throws SQLException { Object result = null;/* w w w . j a v a 2s . c om*/ if (!property.getType().isDataType()) throw new IllegalArgumentException("expected data type property, not " + property.toString()); DataType targetDataType = DataType.valueOf(property.getType().getName()); switch (targetDataType) { case String: case URI: case Month: case MonthDay: case Day: case Time: case Year: case YearMonth: case YearMonthDay: case Duration: result = rs.getString(columnIndex); break; case Date: java.sql.Timestamp ts = rs.getTimestamp(columnIndex); if (ts != null) result = new java.util.Date(ts.getTime()); break; case DateTime: ts = rs.getTimestamp(columnIndex); if (ts != null) { // format DateTime String for SDO java.util.Date date = new java.util.Date(ts.getTime()); result = DataConverter.INSTANCE.getDateTimeFormat().format(date); } break; case Decimal: result = rs.getBigDecimal(columnIndex); break; case Bytes: if (sourceType != Types.BLOB) { result = rs.getBytes(columnIndex); } else if (sourceType == Types.BLOB) { Blob blob = rs.getBlob(columnIndex); if (blob != null) { long blobLen = blob.length(); // for debugging // Note: blob.getBytes(columnIndex, blob.length()); is // somehow truncating the array // by something like 14 bytes (?!!) even though // blob.length() returns the expected length // using getBinaryStream which is preferred anyway InputStream is = blob.getBinaryStream(); try { byte[] bytes = IOUtils.toByteArray(is); long len = bytes.length; // for debugging result = bytes; } catch (IOException e) { throw new RDBServiceException(e); } finally { try { is.close(); } catch (IOException e) { log.error(e.getMessage(), e); } } } } break; case Byte: result = rs.getByte(columnIndex); break; case Boolean: result = rs.getBoolean(columnIndex); break; case Character: result = rs.getInt(columnIndex); break; case Double: result = rs.getDouble(columnIndex); break; case Float: result = rs.getFloat(columnIndex); break; case Int: result = rs.getInt(columnIndex); break; case Integer: result = new BigInteger(rs.getString(columnIndex)); break; case Long: result = rs.getLong(columnIndex); break; case Short: result = rs.getShort(columnIndex); break; case Strings: String value = rs.getString(columnIndex); if (value != null) { String[] values = value.split("\\s"); List<String> list = new ArrayList<String>(values.length); for (int i = 0; i < values.length; i++) list.add(values[i]); // what no Java 5 sugar for this ?? result = list; } break; case Object: default: result = rs.getObject(columnIndex); break; } return result; }
From source file:org.voltdb.HsqlBackend.java
public VoltTable runDML(String dml) { dml = dml.trim();//ww w . j a v a 2 s . co m String indicator = dml.substring(0, 1).toLowerCase(); if (indicator.equals("s") || // "s" is for "select ..." indicator.equals("(")) { // "(" is for "(select ... UNION ...)" et. al. try { Statement stmt = dbconn.createStatement(); sqlLog.l7dlog(Level.DEBUG, LogKeys.sql_Backend_ExecutingDML.name(), new Object[] { dml }, null); sqlLog.debug("Executing " + dml); ResultSet rs = stmt.executeQuery(dml); ResultSetMetaData rsmd = rs.getMetaData(); // note the index values here carefully VoltTable.ColumnInfo[] columns = new VoltTable.ColumnInfo[rsmd.getColumnCount()]; for (int i = 1; i <= rsmd.getColumnCount(); i++) { String colname = rsmd.getColumnLabel(i); String type = rsmd.getColumnTypeName(i); //LOG.fine("Column type: " + type); if (type.equals("VARCHAR")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.STRING); else if (type.equals("TINYINT")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.TINYINT); else if (type.equals("SMALLINT")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.SMALLINT); else if (type.equals("INTEGER")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.INTEGER); else if (type.equals("BIGINT")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.BIGINT); else if (type.equals("DECIMAL")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.DECIMAL); else if (type.equals("FLOAT")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.FLOAT); else if (type.equals("TIMESTAMP")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.TIMESTAMP); else if (type.equals("VARBINARY")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.VARBINARY); else if (type.equals("CHARACTER")) columns[i - 1] = new VoltTable.ColumnInfo(colname, VoltType.STRING); else throw new ExpectedProcedureException( "Trying to create a column in Backend with a (currently) unsupported type: " + type); } VoltTable table = new VoltTable(columns); while (rs.next()) { Object[] row = new Object[table.getColumnCount()]; for (int i = 0; i < table.getColumnCount(); i++) { if (table.getColumnType(i) == VoltType.STRING) row[i] = rs.getString(i + 1); else if (table.getColumnType(i) == VoltType.TINYINT) row[i] = rs.getByte(i + 1); else if (table.getColumnType(i) == VoltType.SMALLINT) row[i] = rs.getShort(i + 1); else if (table.getColumnType(i) == VoltType.INTEGER) row[i] = rs.getInt(i + 1); else if (table.getColumnType(i) == VoltType.BIGINT) row[i] = rs.getLong(i + 1); else if (table.getColumnType(i) == VoltType.DECIMAL) row[i] = rs.getBigDecimal(i + 1); else if (table.getColumnType(i) == VoltType.FLOAT) row[i] = rs.getDouble(i + 1); else if (table.getColumnType(i) == VoltType.VARBINARY) row[i] = rs.getBytes(i + 1); else if (table.getColumnType(i) == VoltType.TIMESTAMP) { Timestamp t = rs.getTimestamp(i + 1); if (t == null) { row[i] = null; } else { // convert from millisecond to microsecond granularity row[i] = new org.voltdb.types.TimestampType(t.getTime() * 1000); } } else { throw new ExpectedProcedureException( "Trying to read a (currently) unsupported type from a JDBC resultset."); } if (rs.wasNull()) { // JDBC returns 0/0.0 instead of null. Put null into the row. row[i] = null; } } table.addRow(row); } stmt.close(); rs.close(); return table; } catch (Exception e) { if (e instanceof ExpectedProcedureException) { throw (ExpectedProcedureException) e; } sqlLog.l7dlog(Level.TRACE, LogKeys.sql_Backend_DmlError.name(), e); throw new ExpectedProcedureException("HSQLDB Backend DML Error ", e); } } else { try { Statement stmt = dbconn.createStatement(); sqlLog.debug("Executing: " + dml); long ucount = stmt.executeUpdate(dml); sqlLog.debug(" result: " + String.valueOf(ucount)); VoltTable table = new VoltTable(new VoltTable.ColumnInfo("", VoltType.BIGINT)); table.addRow(ucount); return table; } catch (SQLException e) { // glorious hack to determine if the error is a constraint failure if (e.getMessage().contains("constraint")) { sqlLog.l7dlog(Level.TRACE, LogKeys.sql_Backend_ConvertingHSQLExtoCFEx.name(), e); final byte messageBytes[] = e.getMessage().getBytes(); ByteBuffer b = ByteBuffer.allocate(25 + messageBytes.length); b.putInt(messageBytes.length); b.put(messageBytes); b.put(e.getSQLState().getBytes()); b.putInt(0); // ConstraintFailure.type try { FastSerializer.writeString("HSQL", b); } catch (IOException e1) { e1.printStackTrace(); } b.putInt(0);//Table size is 0 b.rewind(); throw new ConstraintFailureException(b); } else { sqlLog.l7dlog(Level.TRACE, LogKeys.sql_Backend_DmlError.name(), e); throw new ExpectedProcedureException("HSQLDB Backend DML Error ", e); } } catch (Exception e) { // rethrow an expected exception sqlLog.l7dlog(Level.TRACE, LogKeys.sql_Backend_DmlError.name(), e); throw new ExpectedProcedureException("HSQLDB Backend DML Error ", e); } } }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testDataTypes() throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select * from " + dataTypeTableName + " order by c1"); ResultSetMetaData meta = res.getMetaData(); // row 1/*from w w w . jav a 2 s . c o m*/ assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // getXXX returns 0 for numeric types, false for boolean and null for other assertEquals(0, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(0d, res.getDouble(3), floatCompareDelta); assertEquals(null, res.getString(4)); assertEquals(null, res.getString(5)); assertEquals(null, res.getString(6)); assertEquals(null, res.getString(7)); assertEquals(null, res.getString(8)); assertEquals(0, res.getByte(9)); assertEquals(0, res.getShort(10)); assertEquals(0f, res.getFloat(11), floatCompareDelta); assertEquals(0L, res.getLong(12)); assertEquals(null, res.getString(13)); assertEquals(null, res.getString(14)); assertEquals(null, res.getString(15)); assertEquals(null, res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getString(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); // row 2 assertTrue(res.next()); assertEquals(-1, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(-1.1d, res.getDouble(3), floatCompareDelta); assertEquals("", res.getString(4)); assertEquals("[]", res.getString(5)); assertEquals("{}", res.getString(6)); assertEquals("{}", res.getString(7)); assertEquals("{\"r\":null,\"s\":null,\"t\":null}", res.getString(8)); assertEquals(-1, res.getByte(9)); assertEquals(-1, res.getShort(10)); assertEquals(-1.0f, res.getFloat(11), floatCompareDelta); assertEquals(-1, res.getLong(12)); assertEquals("[]", res.getString(13)); assertEquals("{}", res.getString(14)); assertEquals("{\"r\":null,\"s\":null}", res.getString(15)); assertEquals("[]", res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getTimestamp(17)); assertEquals(null, res.getBigDecimal(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); assertEquals(null, res.getString(23)); // row 3 assertTrue(res.next()); assertEquals(1, res.getInt(1)); assertEquals(true, res.getBoolean(2)); assertEquals(1.1d, res.getDouble(3), floatCompareDelta); assertEquals("1", res.getString(4)); assertEquals("[1,2]", res.getString(5)); assertEquals("{1:\"x\",2:\"y\"}", res.getString(6)); assertEquals("{\"k\":\"v\"}", res.getString(7)); assertEquals("{\"r\":\"a\",\"s\":9,\"t\":2.2}", res.getString(8)); assertEquals(1, res.getByte(9)); assertEquals(1, res.getShort(10)); assertEquals(1.0f, res.getFloat(11), floatCompareDelta); assertEquals(1, res.getLong(12)); assertEquals("[[\"a\",\"b\"],[\"c\",\"d\"]]", res.getString(13)); assertEquals("{1:{11:12,13:14},2:{21:22}}", res.getString(14)); assertEquals("{\"r\":1,\"s\":{\"a\":2,\"b\":\"x\"}}", res.getString(15)); assertEquals("[{\"m\":{},\"n\":1},{\"m\":{\"a\":\"b\",\"c\":\"d\"},\"n\":2}]", res.getString(16)); assertEquals("2012-04-22 09:00:00.123456789", res.getString(17)); assertEquals("2012-04-22 09:00:00.123456789", res.getTimestamp(17).toString()); assertEquals("123456789.0123456", res.getBigDecimal(18).toString()); assertEquals("abcd", res.getString(19)); assertEquals("2013-01-01", res.getString(20)); assertEquals("2013-01-01", res.getDate(20).toString()); assertEquals("abc123", res.getString(21)); assertEquals("abc123 ", res.getString(22)); byte[] bytes = "X'01FF'".getBytes("UTF-8"); InputStream resultSetInputStream = res.getBinaryStream(23); int len = bytes.length; byte[] b = new byte[len]; resultSetInputStream.read(b, 0, len); for (int i = 0; i < len; i++) { assertEquals(bytes[i], b[i]); } // test getBoolean rules on non-boolean columns assertEquals(true, res.getBoolean(1)); assertEquals(true, res.getBoolean(4)); // test case sensitivity assertFalse(meta.isCaseSensitive(1)); assertFalse(meta.isCaseSensitive(2)); assertFalse(meta.isCaseSensitive(3)); assertTrue(meta.isCaseSensitive(4)); // no more rows assertFalse(res.next()); }
From source file:com.alibaba.wasp.jdbc.TestJdbcResultSet.java
@Test public void testInt() throws SQLException { trace("test INT"); ResultSet rs; Object o;/* w ww.j a va 2 s . co m*/ stat = conn.createStatement(); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(31,-1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(32,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(33,1, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(34," + Integer.MAX_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(35," + Integer.MIN_VALUE + ", 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(36,0, 'testInt')"); stat.execute("INSERT INTO test (column1,column2,column3) VALUES(37,0, 'testInt')"); // this should not be read - maxrows=6 // MySQL compatibility (is this required?) rs = stat.executeQuery("SELECT column1,column2,column3 FROM test where column3='testInt' ORDER BY column1"); // MySQL compatibility assertEquals(1, rs.findColumn("column1")); assertEquals(2, rs.findColumn("column2")); ResultSetMetaData meta = rs.getMetaData(); assertEquals(3, meta.getColumnCount()); assertTrue(rs.getRow() == 0); rs.next(); trace("default fetch size=" + rs.getFetchSize()); // 0 should be an allowed value (but it's not defined what is actually // means) rs.setFetchSize(1); assertThrows(SQLErrorCode.INVALID_VALUE_2, rs).setFetchSize(-1); // fetch size 100 is bigger than maxrows - not allowed rs.setFetchSize(6); assertTrue(rs.getRow() == 1); assertEquals(2, rs.findColumn("COLUMN2")); assertEquals(2, rs.findColumn("column2")); assertEquals(2, rs.findColumn("Column2")); assertEquals(1, rs.findColumn("COLUMN1")); assertEquals(1, rs.findColumn("column1")); assertEquals(1, rs.findColumn("Column1")); assertEquals(1, rs.findColumn("colUMN1")); assertTrue(rs.getInt(2) == -1 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("column2") == -1 && !rs.wasNull()); assertTrue(rs.getInt("Column2") == -1 && !rs.wasNull()); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); o = rs.getObject("column2"); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); o = rs.getObject(2); trace(o.getClass().getName()); assertTrue(o instanceof Long); assertTrue(((Long) o).longValue() == -1); assertTrue(rs.getBoolean("Column2")); assertTrue(rs.getByte("Column2") == (byte) -1); assertTrue(rs.getShort("Column2") == (short) -1); assertTrue(rs.getLong("Column2") == -1); assertTrue(rs.getFloat("Column2") == -1.0); assertTrue(rs.getDouble("Column2") == -1.0); assertTrue(rs.getString("Column2").equals("-1") && !rs.wasNull()); assertTrue(rs.getInt("COLUMN1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt("Column1") == 31 && !rs.wasNull()); assertTrue(rs.getInt(1) == 31 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 2); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(!rs.getBoolean(2)); assertTrue(rs.getByte(2) == 0); assertTrue(rs.getShort(2) == 0); assertTrue(rs.getLong(2) == 0); assertTrue(rs.getFloat(2) == 0.0); assertTrue(rs.getDouble(2) == 0.0); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(rs.getInt(1) == 32 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 3); assertTrue(rs.getInt("COLUMN1") == 33 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == 1 && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 4); assertTrue(rs.getInt("COLUMN1") == 34 && !rs.wasNull()); assertTrue(rs.getInt("COLUMN2") == Integer.MAX_VALUE && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 5); assertTrue(rs.getInt("column1") == 35 && !rs.wasNull()); assertTrue(rs.getInt("column2") == Integer.MIN_VALUE && !rs.wasNull()); assertTrue(rs.getString(1).equals("35") && !rs.wasNull()); rs.next(); assertTrue(rs.getRow() == 6); assertTrue(rs.getInt("column1") == 36 && !rs.wasNull()); assertTrue(rs.getInt("column2") == 0 && !rs.wasNull()); assertTrue(rs.getInt(2) == 0 && !rs.wasNull()); assertTrue(rs.getInt(1) == 36 && !rs.wasNull()); assertTrue(rs.getString(1).equals("36") && !rs.wasNull()); assertTrue(rs.getString(2).equals("0") && !rs.wasNull()); assertTrue(!rs.wasNull()); // assertFalse(rs.next()); // assertEquals(0, rs.getRow()); // there is one more row, but because of setMaxRows we don't get it }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * {@inheritDoc}/*from w w w.j a va2 s . c o m*/ */ @Override public FxContentSecurityInfo getContentSecurityInfo(Connection con, FxPK pk, FxContent rawContent) throws FxLoadException, FxNotFoundException { PreparedStatement ps = null; try { switch (pk.getVersion()) { case FxPK.MAX: ps = con.prepareStatement(SECURITY_INFO_MAXVER); break; case FxPK.LIVE: ps = con.prepareStatement(SECURITY_INFO_LIVEVER); break; default: ps = con.prepareStatement(SECURITY_INFO_VER); ps.setInt(2, pk.getVersion()); } ps.setLong(1, pk.getId()); byte typePerm; long typeACL, contentACL, stepACL, previewACL; long previewId, typeId, ownerId, mandatorId; int version; final Set<Long> propertyPerms = new HashSet<Long>(); ResultSet rs = ps.executeQuery(); if (rs == null || !rs.next()) throw new FxNotFoundException("ex.content.notFound", pk); contentACL = rs.getLong(1); typeACL = rs.getLong(2); stepACL = rs.getLong(3); typePerm = rs.getByte(4); typeId = rs.getLong(5); previewId = rs.getLong(6); previewACL = rs.getLong(7); ownerId = rs.getLong(8); mandatorId = rs.getLong(9); version = rs.getInt(10); if (rs.next()) throw new FxLoadException("ex.db.resultSet.tooManyRows"); if ((typePerm & 0x02) == 0x02) { //use property permissions FxContent co = rawContent; try { if (co == null) { FxCachedContent cachedContent = CacheAdmin.getCachedContent(pk); if (cachedContent != null) co = cachedContent.getContent(); else { ContentStorage storage = StorageManager.getContentStorage(pk.getStorageMode()); StringBuilder sql = new StringBuilder(2000); co = storage.contentLoad(con, pk, CacheAdmin.getEnvironment(), sql); } } co = co.copy(); co.getRootGroup().removeEmptyEntries(); for (String xp : co.getAllPropertyXPaths()) { final FxPropertyAssignment pa = co.getPropertyData(xp).getPropertyAssignment(); if (pa.isSystemInternal()) continue; Long propACL = pa.getACL().getId(); propertyPerms.add(propACL); } } catch (FxInvalidParameterException e) { throw new FxLoadException(e); } } pk = new FxPK(pk.getId(), version); final List<Long> acls = contentACL == ACL.NULL_ACL_ID ? loadContentAclTable(con, pk) : Arrays.asList(contentACL); return new FxContentSecurityInfo(pk, ownerId, previewId, typeId, mandatorId, typePerm, typeACL, stepACL, acls, previewACL, Lists.newArrayList(propertyPerms), StorageManager.getLockStorage().getLock(con, pk)); } catch (SQLException e) { throw new FxLoadException(LOG, e, "ex.db.sqlError", e.getMessage()); } catch (FxLockException e) { throw new FxLoadException(e); } finally { Database.closeObjects(GenericHierarchicalStorage.class, ps); } }
From source file:edu.ku.brc.specify.conversion.AgentConverter.java
/** * Specify 5.x points at AgentAdress instead of an Agent. The idea was that to point at an Agent * and possibly a differnt address that represents what that person does. This was really * confusing so we are changing it to point at an Agent instead. * /*from w w w . j a v a 2s . c om*/ * So that means we need to pull apart these relationships and have all foreign keys that point * to an AgentAddress now point at an Agent and we then need to add in the Agents and then add * the Address to the Agents. * * The AgentAdress, Agent and Address (triple) can have a NULL Address but it cannot have a NULL * Agent. If there is a NULL Agent then this method will throw a RuntimeException. */ public boolean convertAgents(final boolean doFixAgents) { boolean debugAgents = false; log.debug("convert Agents"); BasicSQLUtils.removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); // Create the mappers here, but fill them in during the AgentAddress Process IdTableMapper agentIDMapper = idMapperMgr.addTableMapper("agent", "AgentID"); IdTableMapper agentAddrIDMapper = idMapperMgr.addTableMapper("agentaddress", "AgentAddressID"); agentIDMapper.setInitialIndex(4); if (shouldCreateMapTables) { log.info("Mapping Agent Ids"); agentIDMapper.mapAllIds("SELECT AgentID FROM agent ORDER BY AgentID"); } // Just like in the conversion of the CollectionObjects we // need to build up our own SELECT clause because the MetaData of columns names returned // FROM // a query doesn't include the table names for all columns, this is far more predictable List<String> oldFieldNames = new ArrayList<String>(); StringBuilder agtAdrSQL = new StringBuilder("SELECT "); List<String> agentAddrFieldNames = getFieldNamesFromSchema(oldDBConn, "agentaddress"); agtAdrSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); List<String> agentFieldNames = getFieldNamesFromSchema(oldDBConn, "agent"); agtAdrSQL.append(buildSelectFieldList(agentFieldNames, "agent")); log.debug("MAIN: " + agtAdrSQL); agtAdrSQL.append(", "); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); List<String> addrFieldNames = getFieldNamesFromSchema(oldDBConn, "address"); log.debug(agtAdrSQL); agtAdrSQL.append(buildSelectFieldList(addrFieldNames, "address")); GenericDBConversion.addNamesWithTableName(oldFieldNames, addrFieldNames, "address"); // Create a Map FROM the full table/fieldname to the index in the resultset (start at 1 not zero) HashMap<String, Integer> indexFromNameMap = new HashMap<String, Integer>(); agtAdrSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID INNER JOIN address ON agentaddress.AddressID = address.AddressID Order By agentaddress.AgentAddressID Asc"); // These represent the New columns of Agent Table // So the order of the names are for the new table // the names reference the old table String[] agentColumns = { "agent.AgentID", "agent.TimestampModified", "agent.AgentType", "agentaddress.JobTitle", "agent.FirstName", "agent.LastName", "agent.MiddleInitial", "agent.Title", "agent.Interests", "agent.Abbreviation", "agentaddress.Email", "agentaddress.URL", "agent.Remarks", "agent.TimestampCreated", // User/Security changes "agent.ParentOrganizationID" }; HashMap<Integer, AddressInfo> addressHash = new HashMap<Integer, AddressInfo>(); // Create a HashMap to track which IDs have been handled during the conversion process try { log.info("Hashing Address Ids"); Integer agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AddressID) FROM address ORDER BY AddressID"); // So first we hash each AddressID and the value is set to 0 (false) Statement stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rsX = stmtX .executeQuery("SELECT AgentAddressID, AddressID FROM agentaddress ORDER BY AgentAddressID"); conv.setProcess(0, agentCnt); int cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int addrId = rsX.getInt(2); addressHash.put(addrId, new AddressInfo(agentAddrId, addrId)); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Next we hash all the Agents and set their values to 0 (false) log.info("Hashing Agent Ids"); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(*) FROM agent ORDER BY AgentID"); rsX = stmtX.executeQuery( "SELECT AgentID, AgentType, LastName, Name, FirstName FROM agent ORDER BY AgentID"); conv.setProcess(0, agentCnt); cnt = 0; while (rsX.next()) { int agentId = rsX.getInt(1); agentHash.put(agentId, new AgentInfo(agentId, agentIDMapper.get(agentId), rsX.getByte(2), rsX.getString(3), rsX.getString(4), rsX.getString(5))); if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); conv.setProcess(0, 0); // Now we map all the Agents to their Addresses AND // All the Addresses to their Agents. // // NOTE: A single Address Record May be used by more than one Agent so // we will need to Duplicate the Address records later // log.info("Cross Mapping Agents and Addresses"); String post = " FROM agentaddress WHERE AddressID IS NOT NULL and AgentID IS NOT NULL"; agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID)" + post); stmtX = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String asSQL = "SELECT AgentAddressID, AgentID" + post; log.debug(asSQL); rsX = stmtX.executeQuery(asSQL); conv.setProcess(0, agentCnt); cnt = 0; // Needed to add in case AgentAddress table wasn't used. while (rsX.next()) { int agentAddrId = rsX.getInt(1); int agentId = rsX.getInt(2); // /////////////////////// // Add Address to Agent // /////////////////////// AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null) { String msg = "The AgentID [" + agentId + "] in AgentAddress table id[" + agentAddrId + "] desn't exist"; log.error(msg); tblWriter.logError(msg); } else { agentInfo.add(agentAddrId, agentAddrId); } if (cnt % 100 == 0) { conv.setProcess(0, cnt); } cnt++; } rsX.close(); stmtX.close(); //dumpInfo("beforeInfo.txt", addressHash); conv.setProcess(0, 0); // It OK if the address is NULL, but the Agent CANNOT be NULL log.info("Checking for null Agents"); agentCnt = BasicSQLUtils.getCount(oldDBConn, "SELECT COUNT(AgentAddressID) FROM agentaddress a where AddressID IS NOT NULL and AgentID is null"); // If there is a Single Record With a NULL Agent this would be BAD! if (agentCnt != null && agentCnt > 0) { showError("There are " + agentCnt + " AgentAddress Records where the AgentID is null and the AddressId IS NOT NULL!"); } // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has both an Address AND an Agent // //////////////////////////////////////////////////////////////////////////////// log.info(agtAdrSQL.toString()); Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); log.debug("AgentAddress: " + agtAdrSQL.toString()); // Create Map of column name to column index number int inx = 1; for (String fldName : oldFieldNames) { // log.info("["+fldName+"] "+inx+" ["+rsmd.getColumnName(inx)+"]"); indexFromNameMap.put(fldName, inx++); } Statement updateStatement = newDBConn.createStatement(); // Figure out certain column indexes we will need alter int agentIdInx = indexFromNameMap.get("agent.AgentID"); int agentTypeInx = indexFromNameMap.get("agent.AgentType"); int lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); int nameInx = indexFromNameMap.get("agent.Name"); int lastNameInx = indexFromNameMap.get("agent.LastName"); int firstNameInx = indexFromNameMap.get("agent.FirstName"); int recordCnt = 0; ResultSet rs = stmt.executeQuery(agtAdrSQL.toString()); while (rs.next()) { int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Deal with Agent FirstName, LastName and Name] String lastName = rs.getString(lastNameInx); String name = rs.getString(nameInx); namePair.second = StringUtils.isNotEmpty(name) && StringUtils.isEmpty(lastName) ? name : lastName; namePair.first = rs.getString(firstNameInx); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.setShowLogErrors(false); if (debugAgents) log.info(String.format("Map - agentAddressId (Old) %d to Agent -> New ID: %d", agentAddressId, agentInfo.getNewAgentId())); if (agentAddrIDMapper.get(agentAddressId) == null) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } else { log.debug(String.format("ERROR - agentAddressId %d Already mapped to New ID: %d", agentAddressId, agentInfo.getNewAgentId())); } agentAddrIDMapper.setShowLogErrors(true); } // Because of the old DB relationships we want to make sure we only add each agent // in one time // So start by checking the HashMap to see if it has already been added if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // It has not been added yet so Add it StringBuilder sqlStr = new StringBuilder(); sqlStr.append("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionId, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, "); sqlStr.append("Title, Interests, Abbreviation, Email, URL, Remarks, TimestampCreated, "); sqlStr.append("ParentOrganizationID, CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) { sqlStr.append(","); } if (i == 0) { if (debugAgents) log.info("Adding: " + agentColumns[i] + " New ID: " + agentInfo.getNewAgentId()); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (agentColumns[i].equals("agent.ParentOrganizationID")) { Object obj = rs.getObject(indexFromNameMap.get(agentColumns[i])); if (obj != null) { int oldId = rs.getInt(agentColumns[i]); Integer newID = agentIDMapper.get(oldId); if (newID == null) { log.error("Couldn't map ParentOrganizationID [" + oldId + "]"); } sqlStr.append(BasicSQLUtils.getStrValue(newID)); } else { sqlStr.append("NULL"); } } else if (agentColumns[i].equals("agent.LastName") || agentColumns[i].equals("LastName")) { int lastNameLen = 120; String lstName = namePair.second; lstName = lstName == null ? null : lstName.length() <= lastNameLen ? lstName : lstName.substring(0, lastNameLen); sqlStr.append(BasicSQLUtils.getStrValue(lstName)); } else if (agentColumns[i].equals("agent.FirstName") || agentColumns[i].equals("FirstName")) { sqlStr.append(BasicSQLUtils.getStrValue(namePair.first)); } else { inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ",0"); sqlStr.append(")"); try { if (debugAgents) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } //conv.addAgentDisciplineJoin(newAgentId, conv.getDisciplineId()); } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); System.exit(0); throw new RuntimeException(e); } } BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); if (recordCnt % 250 == 0) { log.info("AgentAddress Records: " + recordCnt); } recordCnt++; } // while BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); log.info("AgentAddress Records: " + recordCnt); rs.close(); stmt.close(); // //////////////////////////////////////////////////////////////////////////////// // This does the part of AgentAddress where it has JUST Agent // //////////////////////////////////////////////////////////////////////////////// log.info("******** Doing AgentAddress JUST Agent"); int newRecordsAdded = 0; StringBuilder justAgentSQL = new StringBuilder(); justAgentSQL.setLength(0); justAgentSQL.append("SELECT "); justAgentSQL.append(buildSelectFieldList(agentAddrFieldNames, "agentaddress")); justAgentSQL.append(", "); getFieldNamesFromSchema(oldDBConn, "agent", agentFieldNames); justAgentSQL.append(buildSelectFieldList(agentFieldNames, "agent")); justAgentSQL.append( " FROM agent INNER JOIN agentaddress ON agentaddress.AgentID = agent.AgentID WHERE agentaddress.AddressID IS NULL ORDER BY agentaddress.AgentAddressID ASC"); log.info(justAgentSQL.toString()); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(justAgentSQL.toString()); oldFieldNames.clear(); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentAddrFieldNames, "agentaddress"); GenericDBConversion.addNamesWithTableName(oldFieldNames, agentFieldNames, "agent"); indexFromNameMap.clear(); inx = 1; for (String fldName : oldFieldNames) { indexFromNameMap.put(fldName, inx++); } agentIdInx = indexFromNameMap.get("agent.AgentID"); lastEditInx = indexFromNameMap.get("agent.LastEditedBy"); agentTypeInx = indexFromNameMap.get("agent.AgentType"); recordCnt = 0; while (rs.next()) { byte agentType = rs.getByte(agentTypeInx); int agentAddressId = rs.getInt(1); int agentId = rs.getInt(agentIdInx); String lastEditedBy = rs.getString(lastEditInx); AgentInfo agentInfo = agentHash.get(agentId); // Now tell the AgentAddress Mapper the New ID to the Old AgentAddressID if (shouldCreateMapTables) { agentAddrIDMapper.put(agentAddressId, agentInfo.getNewAgentId()); } recordCnt++; if (!agentInfo.wasAdded()) { agentInfo.setWasAdded(true); BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); // Create Agent StringBuilder sqlStr = new StringBuilder("INSERT INTO agent "); sqlStr.append( "(AgentID, DivisionID, TimestampModified, AgentType, JobTitle, FirstName, LastName, MiddleInitial, Title, Interests, "); sqlStr.append("Abbreviation, Email, URL, Remarks, TimestampCreated, ParentOrganizationID, "); sqlStr.append("CreatedByAgentID, ModifiedByAgentID, Version)"); sqlStr.append(" VALUES ("); for (int i = 0; i < agentColumns.length; i++) { if (i > 0) sqlStr.append(","); if (i == 0) { if (debugAgents) log.info(agentColumns[i]); sqlStr.append(agentInfo.getNewAgentId()); sqlStr.append(","); sqlStr.append(conv.getCurDivisionID()); } else if (i == lastEditInx) { // Skip the field } else if (agentColumns[i].equals("agent.LastName")) { if (debugAgents) log.info(agentColumns[i]); int srcColInx = agentType != 1 ? nameInx : lastNameInx; String lName = BasicSQLUtils.getStrValue(rs.getObject(srcColInx)); sqlStr.append(lName); } else { if (debugAgents) log.info(agentColumns[i]); inx = indexFromNameMap.get(agentColumns[i]); sqlStr.append(BasicSQLUtils.getStrValue(rs.getObject(inx))); } } sqlStr.append("," + conv.getCreatorAgentIdForAgent(lastEditedBy) + "," + conv.getModifiedByAgentIdForAgent(lastEditedBy) + ", 0"); // '0' is Version sqlStr.append(")"); try { if (debugAgents) { log.info(sqlStr.toString()); } updateStatement.executeUpdate(sqlStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newAgentId = BasicSQLUtils.getInsertedId(updateStatement); if (newAgentId == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } newRecordsAdded++; } catch (SQLException e) { log.error(sqlStr.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } if (recordCnt % 250 == 0) { log.info("AgentAddress (Agent Only) Records: " + recordCnt); } } // while log.info("AgentAddress (Agent Only) Records: " + recordCnt + " newRecordsAdded " + newRecordsAdded); rs.close(); updateStatement.close(); conv.setProcess(0, BasicSQLUtils.getNumRecords(oldDBConn, "agent")); conv.setDesc("Adding Agents"); // Now Copy all the Agents that where part of an Agent Address Conversions stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); AgentInfo agentInfo = agentHash.get(agentId); if (agentInfo == null || !agentInfo.wasAdded()) { copyAgentFromOldToNew(agentId, agentIDMapper); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); //------------------------------------------------------------ // Now Copy all the Agents that where missed //------------------------------------------------------------ conv.setProcess(0); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery("SELECT AgentID FROM agent"); recordCnt = 0; while (rs.next()) { Integer agentId = rs.getInt(1); Integer newId = agentIDMapper.get(agentId); if (newId != null) { Integer isThere = BasicSQLUtils.getCount(newDBConn, "SELECT COUNT(*) FROM agent WHERE AgentID = " + newId); if (isThere == null || isThere == 0) { copyAgentFromOldToNew(agentId, agentIDMapper); } } else { tblWriter.logError("Mapping missing for old Agent id[" + agentId + "]"); } recordCnt++; if (recordCnt % 50 == 0) { conv.setProcess(recordCnt); } } conv.setProcess(recordCnt); if (doFixAgents) { fixAgentsLFirstLastName(); } //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- StringBuilder sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append( "(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append( "IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, PositionHeld, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); PreparedStatement pStmt = newDBConn.prepareStatement(sqlStr1.toString(), Statement.RETURN_GENERATED_KEYS); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrSQL = "SELECT a.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, a.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.JobTitle " + "FROM address AS a " + "INNER JOIN agentaddress AS aa ON a.AddressID = aa.AddressID WHERE aa.AgentAddressID = %d"; BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); int fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { HashMap<Integer, Integer> addrs = agentInfo.getAddrs(); for (Integer oldAgentAddrId : addrs.keySet()) { String adrSQL = String.format(addrSQL, oldAgentAddrId); rs = stmt.executeQuery(adrSQL); if (!rs.next()) { rs.close(); continue; } String lastEditedBy = rs.getString(14); String posHeld = rs.getString(15); if (posHeld != null && posHeld.length() > 32) { posHeld = posHeld.substring(0, 32); } String addr1 = rs.getString(2); String addr2 = null; if (addr1 != null && addr1.length() > 255) { addr1 = addr1.substring(0, 255); addr2 = addr1.substring(255); } pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, addr1); pStmt.setString(3, addr2); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setString(16, posHeld); pStmt.setInt(17, agentInfo.getNewAgentId()); pStmt.setInt(18, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(20, 0); pStmt.setInt(21, agentInfo.addrOrd); Integer newID = BasicSQLUtils.getInsertedId(pStmt); log.debug(String.format("Saved New Id %d", newID)); //agentInfo.addWrittenAddrOldId(addrInfo.getOldAddrId()); agentInfo.addrOrd++; rs.close(); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } //addrInfo.setWasAdded(true); } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } } log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close(); //------------------------------------------------------------------ // Step #2 - Now duplicate the addresses for the agents that had // already been written to the database //------------------------------------------------------------------ /*fixCnt = 0; for (AgentInfo agentInfo : agentHash.values()) { for (Integer oldAgentAddrId : agentInfo.getUnwrittenOldAddrIds()) { Integer oldAddrId = agentInfo.getAddrs().get(oldAgentAddrId); //AddressInfo addrInfo = addressHash.get(oldAddrId); System.out.println(String.format("%d %d", oldAgentAddrId, oldAddrId)); //duplicateAddress(newDBConn, addrInfo.getOldAddrId(), addrInfo.getNewAddrId(), agentInfo.getNewAgentId()); } } log.info(String.format("Duplicated %d new Addresses", fixCnt)); */ //---------------------------------------------------------------------------------------------------------------------------------- // Now loop through the Agents hash and write the addresses. If the address has already been written then it will need to be // duplicate in the second step. //---------------------------------------------------------------------------------------------------------------------------------- /*BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "address", BasicSQLUtils.myDestinationServerType); sqlStr1 = new StringBuilder("INSERT INTO address "); sqlStr1.append("(TimestampModified, Address, Address2, City, State, Country, PostalCode, Remarks, TimestampCreated, "); sqlStr1.append("IsPrimary, IsCurrent, Phone1, Phone2, Fax, RoomOrBuilding, AgentID, CreatedByAgentID, ModifiedByAgentID, Version, Ordinal)"); sqlStr1.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); pStmt = newDBConn.prepareStatement(sqlStr1.toString()); // 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 String addrOnlySQL = "SELECT aa.TimestampModified, a.Address, a.City, a.State, a.Country, a.Postalcode, a.Remarks, aa.TimestampCreated, aa.Phone1, aa.Phone2, aa.Fax, aa.RoomOrBuilding , aa.IsCurrent, a.LastEditedBy, aa.AgentID " + "FROM agentaddress AS aa " + "LEFT JOIN address AS a ON a.AddressID = aa.AddressID " + "WHERE a.addressID IS NULL AND aa.AgentID IS NOT NULL"; fixCnt = 0; rs = stmt.executeQuery(addrOnlySQL); while (rs.next()) { int agentId = rs.getInt(15); int newAgentId = agentIDMapper.get(agentId); String lastEditedBy = rs.getString(14); pStmt.setTimestamp(1, rs.getTimestamp(1)); pStmt.setString(2, rs.getString(2)); pStmt.setString(3, null); // Address 2 pStmt.setString(4, rs.getString(3)); pStmt.setString(5, rs.getString(4)); pStmt.setString(6, rs.getString(5)); pStmt.setString(7, rs.getString(6)); pStmt.setString(8, rs.getString(7)); pStmt.setTimestamp(9, rs.getTimestamp(8)); pStmt.setBoolean(10, rs.getByte(13) != 0); pStmt.setBoolean(11, rs.getByte(13) != 0); pStmt.setString(12, rs.getString(9)); pStmt.setString(13, rs.getString(10)); pStmt.setString(14, rs.getString(11)); pStmt.setString(15, rs.getString(12)); pStmt.setInt(16, newAgentId); pStmt.setInt(17, conv.getCreatorAgentIdForAgent(lastEditedBy)); pStmt.setInt(18, conv.getModifiedByAgentIdForAgent(lastEditedBy)); pStmt.setInt(19, 0); pStmt.setInt(20, 1); try { if (debugAgents) { log.info(sqlStr1.toString()); } if (pStmt.executeUpdate() != 1) { log.error("Error inserting address.)"); } else { fixCnt++; } } catch (SQLException e) { log.error(sqlStr1.toString()); log.error("Count: " + recordCnt); e.printStackTrace(); log.error(e); throw new RuntimeException(e); } } rs.close(); log.info(String.format("Added %d new Addresses", fixCnt)); pStmt.close();*/ stmt.close(); //dumpInfo("afterInfo.txt", addressHash); BasicSQLUtils.setIdentityInsertOFFCommandForSQLServer(newDBConn, "agent", BasicSQLUtils.myDestinationServerType); return true; } catch (SQLException ex) { log.error(ex); ex.printStackTrace(); System.exit(0); throw new RuntimeException(ex); } }