private void addColumnMutation(
String schemaName, String tableName, PColumn column, PreparedStatement colUpsert)
throws SQLException {
colUpsert.setString(1, schemaName);
colUpsert.setString(2, tableName);
colUpsert.setString(3, column.getName().getString());
colUpsert.setString(
4, column.getFamilyName() == null ? null : column.getFamilyName().getString());
colUpsert.setInt(5, column.getDataType().getSqlType());
colUpsert.setInt(
6,
column.isNullable() ? ResultSetMetaData.columnNullable : ResultSetMetaData.columnNoNulls);
if (column.getMaxLength() == null) {
colUpsert.setNull(7, Types.INTEGER);
} else {
colUpsert.setInt(7, column.getMaxLength());
}
if (column.getScale() == null) {
colUpsert.setNull(8, Types.INTEGER);
} else {
colUpsert.setInt(8, column.getScale());
}
colUpsert.setInt(9, column.getPosition() + 1);
colUpsert.setInt(10, ColumnModifier.toSystemValue(column.getColumnModifier()));
colUpsert.execute();
}
public static void setNumeric(PreparedStatement stm, int i, Number value) throws SQLException {
if (value != null) {
stm.setObject(i, value, Types.NUMERIC);
} else {
stm.setNull(i, Types.NUMERIC);
}
}
public void setColumnObject(PreparedStatement pst, String value, int index) throws SQLException {
if (value.equalsIgnoreCase("NULL")) {
pst.setNull(index, sqlType);
} else {
pst.setLong(index, Long.parseLong(value));
}
}
public static void setTimestamp(PreparedStatement stm, int i, Date date, boolean isNull)
throws SQLException {
if (isNull) {
stm.setNull(i, Types.TIMESTAMP);
} else {
stm.setTimestamp(i, new Timestamp(date.getTime()));
}
}
public static void setDate(PreparedStatement stm, int i, Date date, boolean isNull)
throws SQLException {
if (isNull) {
stm.setNull(i, Types.DATE);
} else {
stm.setDate(i, new java.sql.Date(date.getTime()));
}
}
public void deleteISOLanguageByCode2Idx(
CFSecurityAuthorization Authorization, String argISO6391Code) {
final String S_ProcName = "deleteISOLanguageByCode2Idx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
final String sql = "CALL sp_delete_iso_lang_by_code2idx( ?, ?, ?, ?, ?" + ", " + "?" + " )";
if (stmtDeleteByCode2Idx == null) {
stmtDeleteByCode2Idx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
if (argISO6391Code != null) {
stmtDeleteByCode2Idx.setString(argIdx++, argISO6391Code);
} else {
stmtDeleteByCode2Idx.setNull(argIdx++, java.sql.Types.VARCHAR);
}
resultSet = stmtDeleteByCode2Idx.executeQuery();
if (resultSet.next()) {
int deleteFlag = resultSet.getInt(1);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
@Override
public HTTPResponse insert(HTTPRequest request, HTTPResponse response) {
Key key = Key.create(request, response);
Connection connection = getConnection();
String sql =
"insert into response(uri, vary, status, headers, payload, mimeType, cachetime) values (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement statement = null;
try {
JdbcUtil.startTransaction(connection);
invalidate(key, connection);
statement = connection.prepareStatement(sql);
statement.setString(1, key.getURI().toString());
statement.setString(2, key.getVary().toJSON());
statement.setInt(3, response.getStatus().getCode());
statement.setString(4, response.getHeaders().toJSON());
InputStream inputStream = null;
if (response.hasPayload() && response.getPayload().isAvailable()) {
statement.setString(6, response.getPayload().getMimeType().toString());
inputStream = response.getPayload().getInputStream();
statement.setBinaryStream(5, inputStream);
} else {
statement.setNull(5, Types.BLOB);
statement.setNull(6, Types.VARCHAR);
}
statement.setTimestamp(7, new Timestamp(DateTimeUtils.currentTimeMillis()));
try {
statement.executeUpdate();
} finally {
IOUtils.closeQuietly(inputStream);
}
connection.commit();
return getImpl(connection, key);
} catch (SQLException e) {
JdbcUtil.rollback(connection);
JdbcUtil.close(connection);
throw new DataAccessException(e);
} finally {
JdbcUtil.endTransaction(connection);
JdbcUtil.close(statement);
}
}
public CFSecurityISOLanguageBuff[] readBuffByCode2Idx(
CFSecurityAuthorization Authorization, String ISO6391Code) {
final String S_ProcName = "readBuffByCode2Idx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
final String sql = "CALL sp_read_iso_lang_by_code2idx( ?, ?, ?, ?, ?" + ", " + "?" + " )";
if (stmtReadBuffByCode2Idx == null) {
stmtReadBuffByCode2Idx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
if (ISO6391Code != null) {
stmtReadBuffByCode2Idx.setString(argIdx++, ISO6391Code);
} else {
stmtReadBuffByCode2Idx.setNull(argIdx++, java.sql.Types.VARCHAR);
}
resultSet = stmtReadBuffByCode2Idx.executeQuery();
List<CFSecurityISOLanguageBuff> buffList = new LinkedList<CFSecurityISOLanguageBuff>();
while (resultSet.next()) {
CFSecurityISOLanguageBuff buff = unpackISOLanguageResultSetToBuff(resultSet);
buffList.add(buff);
}
int idx = 0;
CFSecurityISOLanguageBuff[] retBuff = new CFSecurityISOLanguageBuff[buffList.size()];
Iterator<CFSecurityISOLanguageBuff> iter = buffList.iterator();
while (iter.hasNext()) {
retBuff[idx++] = iter.next();
}
return (retBuff);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void deleteISOLanguageByCode2Idx(
CFSecurityAuthorization Authorization, String argISO6391Code) {
final String S_ProcName = "deleteISOLanguageByCode2Idx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_iso_lang_by_code2idx( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ " )";
if (stmtDeleteByCode2Idx == null) {
stmtDeleteByCode2Idx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByCode2Idx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByCode2Idx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
if (argISO6391Code != null) {
stmtDeleteByCode2Idx.setString(argIdx++, argISO6391Code);
} else {
stmtDeleteByCode2Idx.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtDeleteByCode2Idx.executeUpdate();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* 根据数据库连接和表和相关列，做数据库的插入操作
*
* @param conn
* @param tablename
* @param colnames
* @throws SQLException
*/
public void doInsertDB(Connection conn, String tablename, String[] colnames) throws SQLException {
StringBuilder insertsql = new StringBuilder();
insertsql.append("insert into ").append(tablename);
StringBuilder lstr = new StringBuilder(), rstr = new StringBuilder();
lstr.append("(").append(colnames[0]);
rstr.append("(?");
for (int i = 1; i < colnames.length; i++) {
lstr.append(",").append(colnames[i]);
rstr.append(",?");
}
lstr.append(")");
rstr.append(")");
insertsql.append(lstr).append("values").append(rstr);
PreparedStatement ps = null;
try {
// System.out.println(insertsql.toString()) ;
ps = conn.prepareStatement(insertsql.toString());
for (int i = 0; i < colnames.length; i++) {
Object tmpo = GDB.prepareObjVal(this.getValue(colnames[i]));
if (tmpo != null) {
ps.setObject(i + 1, tmpo);
} else {
int sqlt =
this.belongToDT
.getColumn(colnames[i])
.getJdbcType(); // JavaColumnInfo.Class2SqlType(c)
if (sqlt == java.sql.Types.BLOB) // for sqlserver driver
// NullPointer error
ps.setObject(1 + i, new byte[0]);
else ps.setNull(1 + i, sqlt);
}
}
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}
}
public void doUpdateDB(Connection conn, String tablename, String uniquecol, String[] cols)
throws SQLException {
StringBuilder upsql = new StringBuilder();
upsql.append("update ").append(tablename).append(" set ");
upsql.append(cols[0]).append("=?");
for (int i = 1; i < cols.length; i++) {
upsql.append(",").append(cols[i]).append("=?");
}
upsql.append(" where ").append(uniquecol).append("=?");
String[] allcols = new String[cols.length + 1];
System.arraycopy(cols, 0, allcols, 0, cols.length);
allcols[cols.length] = uniquecol;
PreparedStatement ps = null;
try {
// System.out.println(upsql.toString());
ps = conn.prepareStatement(upsql.toString());
for (int i = 0; i < allcols.length; i++) {
Object tmpo = GDB.prepareObjVal(this.getValue(allcols[i]));
if (tmpo != null) {
ps.setObject(i + 1, tmpo);
} else {
int sqlt =
this.belongToDT
.getColumn(allcols[i])
.getJdbcType(); // JavaColumnInfo.Class2SqlType(c)
if (sqlt == java.sql.Types.BLOB) // for sqlserver driver
// NullPointer error
ps.setObject(1 + i, new byte[0]);
else ps.setNull(1 + i, sqlt);
}
}
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}
}
public MutationState createTable(CreateTableStatement statement, byte[][] splits)
throws SQLException {
PTableType tableType = statement.getTableType();
boolean isView = tableType == PTableType.VIEW;
if (isView && !statement.getProps().isEmpty()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.VIEW_WITH_TABLE_CONFIG)
.build()
.buildException();
}
connection.rollback();
boolean wasAutoCommit = connection.getAutoCommit();
try {
connection.setAutoCommit(false);
TableName tableNameNode = statement.getTableName();
String schemaName = tableNameNode.getSchemaName();
String tableName = tableNameNode.getTableName();
PrimaryKeyConstraint pkConstraint = statement.getPrimaryKeyConstraint();
String pkName = null;
Set<String> pkColumns = Collections.<String>emptySet();
Iterator<String> pkColumnsIterator = Iterators.emptyIterator();
if (pkConstraint != null) {
pkColumns = pkConstraint.getColumnNames();
pkColumnsIterator = pkColumns.iterator();
pkName = pkConstraint.getName();
}
List<ColumnDef> colDefs = statement.getColumnDefs();
List<PColumn> columns = Lists.newArrayListWithExpectedSize(colDefs.size());
PreparedStatement colUpsert = connection.prepareStatement(INSERT_COLUMN);
int columnOrdinal = 0;
Map<String, PName> familyNames = Maps.newLinkedHashMap();
boolean isPK = false;
for (ColumnDef colDef : colDefs) {
if (colDef.isPK()) {
if (isPK) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.PRIMARY_KEY_ALREADY_EXISTS)
.setColumnName(colDef.getColumnDefName().getColumnName().getName())
.build()
.buildException();
}
isPK = true;
}
PColumn column = newColumn(columnOrdinal++, colDef, pkConstraint);
if (SchemaUtil.isPKColumn(column)) {
// TODO: remove this constraint?
if (!pkColumns.isEmpty()
&& !column.getName().getString().equals(pkColumnsIterator.next())) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.PRIMARY_KEY_OUT_OF_ORDER)
.setSchemaName(schemaName)
.setTableName(tableName)
.setColumnName(column.getName().getString())
.build()
.buildException();
}
}
columns.add(column);
if (colDef.getDataType() == PDataType.BINARY && colDefs.size() > 1) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.BINARY_IN_ROW_KEY)
.setSchemaName(schemaName)
.setTableName(tableName)
.setColumnName(column.getName().getString())
.build()
.buildException();
}
if (column.getFamilyName() != null) {
familyNames.put(column.getFamilyName().getString(), column.getFamilyName());
}
}
if (!isPK && pkColumns.isEmpty()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.PRIMARY_KEY_MISSING)
.setSchemaName(schemaName)
.setTableName(tableName)
.build()
.buildException();
}
List<Pair<byte[], Map<String, Object>>> familyPropList =
Lists.newArrayListWithExpectedSize(familyNames.size());
Map<String, Object> commonFamilyProps = Collections.emptyMap();
Map<String, Object> tableProps = Collections.emptyMap();
if (!statement.getProps().isEmpty()) {
if (statement.isView()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.VIEW_WITH_PROPERTIES)
.build()
.buildException();
}
for (String familyName : statement.getProps().keySet()) {
if (!familyName.equals(QueryConstants.ALL_FAMILY_PROPERTIES_KEY)) {
if (familyNames.get(familyName) == null) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.PROPERTIES_FOR_FAMILY)
.setFamilyName(familyName)
.build()
.buildException();
}
}
}
commonFamilyProps = Maps.newHashMapWithExpectedSize(statement.getProps().size());
tableProps = Maps.newHashMapWithExpectedSize(statement.getProps().size());
Collection<Pair<String, Object>> props =
statement.getProps().get(QueryConstants.ALL_FAMILY_PROPERTIES_KEY);
// Somewhat hacky way of determining if property is for HColumnDescriptor or
// HTableDescriptor
HColumnDescriptor defaultDescriptor =
new HColumnDescriptor(QueryConstants.DEFAULT_COLUMN_FAMILY_BYTES);
for (Pair<String, Object> prop : props) {
if (defaultDescriptor.getValue(prop.getFirst()) != null) {
commonFamilyProps.put(prop.getFirst(), prop.getSecond());
} else {
tableProps.put(prop.getFirst(), prop.getSecond());
}
}
}
for (PName familyName : familyNames.values()) {
Collection<Pair<String, Object>> props = statement.getProps().get(familyName.getString());
if (props.isEmpty()) {
familyPropList.add(
new Pair<byte[], Map<String, Object>>(familyName.getBytes(), commonFamilyProps));
} else {
Map<String, Object> combinedFamilyProps =
Maps.newHashMapWithExpectedSize(props.size() + commonFamilyProps.size());
combinedFamilyProps.putAll(commonFamilyProps);
for (Pair<String, Object> prop : props) {
combinedFamilyProps.put(prop.getFirst(), prop.getSecond());
}
familyPropList.add(
new Pair<byte[], Map<String, Object>>(familyName.getBytes(), combinedFamilyProps));
}
}
// Bootstrapping for our SYSTEM.TABLE that creates itself before it exists
if (tableType == PTableType.SYSTEM) {
PTable table =
new PTableImpl(
new PNameImpl(tableName),
tableType,
MetaDataProtocol.MIN_TABLE_TIMESTAMP,
0,
QueryConstants.SYSTEM_TABLE_PK_NAME,
null,
columns);
connection.addTable(schemaName, table);
}
for (PColumn column : columns) {
addColumnMutation(schemaName, tableName, column, colUpsert);
}
Integer saltBucketNum = (Integer) tableProps.remove(PhoenixDatabaseMetaData.SALT_BUCKETS);
if (saltBucketNum != null
&& (saltBucketNum <= 0 || saltBucketNum > SaltingUtil.MAX_BUCKET_NUM)) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.INVALID_BUCKET_NUM)
.build()
.buildException();
}
PreparedStatement tableUpsert = connection.prepareStatement(CREATE_TABLE);
tableUpsert.setString(1, schemaName);
tableUpsert.setString(2, tableName);
tableUpsert.setString(3, tableType.getSerializedValue());
tableUpsert.setInt(4, 0);
tableUpsert.setInt(5, columnOrdinal);
if (saltBucketNum != null) {
tableUpsert.setInt(6, saltBucketNum);
} else {
tableUpsert.setNull(6, Types.INTEGER);
}
tableUpsert.setString(7, pkName);
tableUpsert.execute();
final List<Mutation> tableMetaData = connection.getMutationState().toMutations();
connection.rollback();
MetaDataMutationResult result =
connection
.getQueryServices()
.createTable(tableMetaData, isView, tableProps, familyPropList, splits);
MutationCode code = result.getMutationCode();
switch (code) {
case TABLE_ALREADY_EXISTS:
connection.addTable(schemaName, result.getTable());
if (!statement.ifNotExists()) {
throw new TableAlreadyExistsException(schemaName, tableName);
}
break;
case NEWER_TABLE_FOUND:
// TODO: add table if in result?
throw new NewerTableAlreadyExistsException(schemaName, tableName);
case UNALLOWED_TABLE_MUTATION:
throw new SQLExceptionInfo.Builder(SQLExceptionCode.CANNOT_MUTATE_TABLE)
.setSchemaName(schemaName)
.setTableName(tableName)
.build()
.buildException();
default:
PTable table =
new PTableImpl(
new PNameImpl(tableName),
tableType,
result.getMutationTime(),
0,
pkName,
saltBucketNum,
columns);
connection.addTable(schemaName, table);
if (tableType == PTableType.USER) {
connection.setAutoCommit(true);
// Delete everything in the column. You'll still be able to do queries at earlier
// timestamps
Long scn = connection.getSCN();
long ts = (scn == null ? result.getMutationTime() : scn);
PSchema schema =
new PSchemaImpl(
schemaName,
ImmutableMap.<String, PTable>of(table.getName().getString(), table));
TableRef tableRef = new TableRef(null, table, schema, ts);
byte[] emptyCF = SchemaUtil.getEmptyColumnFamily(table.getColumnFamilies());
MutationPlan plan =
new PostDDLCompiler(connection).compile(tableRef, emptyCF, null, ts);
return connection.getQueryServices().updateData(plan);
}
break;
}
return new MutationState(0, connection);
} finally {
connection.setAutoCommit(wasAutoCommit);
}
}
public static void main(String[] args) {
Statement statement = null;
Connection conn = null;
ResultSet rsMin = null;
PreparedStatement psInsert = null;
LinkedList<Statement> allStatements = new LinkedList<Statement>();
try {
// Instantiate the driver
Class.forName(JDBC_DRIVER);
} catch (ClassNotFoundException cnfe) {
System.out.println(
"Can't instantiate driver class; check you have drives and classpath configured correctly?");
cnfe.printStackTrace();
System.exit(-1); // No driver? Need to fix before anything else will work. So quit the program
}
try {
conn = DriverManager.getConnection(DB_CONNECTION_URL, USER, PASSWORD);
statement = conn.createStatement();
allStatements.add(statement);
System.out.println("Average Weather Database Program");
// Create a table in the database. Stores today's date, and the min and max temperatures
// recorded.
String createTableSQL = "CREATE TABLE temp (day date, mintemp double, maxtemp double)";
String deleteTableSQL = "DROP TABLE temp";
try {
statement.executeUpdate(createTableSQL);
System.out.println("Created temp table");
} catch (SQLException sqle) {
// Seems the table already exists. Delete it and recreate it
if (sqle.getSQLState()
.startsWith("42")) { // Error code for table already existing start with XO
System.out.println("Temp table appears to exist already, delete and recreate");
statement.executeUpdate(deleteTableSQL);
statement.executeUpdate(createTableSQL);
} else {
// Something else went wrong. If we can't create the table, no point attempting
// to run the rest of the code. Throw the exception again to be handled at the end of the
// program.
System.out.println("Got stuck in catch else " + sqle.getSQLState() + " is sql state");
throw sqle;
}
}
// Add some test data
String prepStatInsert = "INSERT INTO temp VALUES ( ?, ?, ? )";
psInsert = conn.prepareStatement(prepStatInsert);
allStatements.add(psInsert);
psInsert.setDate(1, Date.valueOf("2014-04-01"));
psInsert.setDouble(2, 44.2);
psInsert.setDouble(3, 58.7);
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-02"));
psInsert.setDouble(2, 34.6);
psInsert.setDouble(3, 55.1);
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-03"));
psInsert.setDouble(2, 43.9);
psInsert.setNull(
3, Types.DOUBLE); // Forgot to record the max temperature for this date so set it to null.
psInsert.executeUpdate();
psInsert.setDate(1, Date.valueOf("2014-04-04"));
psInsert.setDouble(2, 43.8);
psInsert.setDouble(3, 47.2);
psInsert.executeUpdate();
System.out.println("Added test data to database");
// Let's calculate the average minimum and average maximum temperature for all the days.
// Add up all the maximum temperatures and divide by number of days to get average max
// temperature.
// Add up all the minimum temperatures and divide by number of days to get average min
// temperature.
double averageMaxTemp = 0;
double averageMinTemp = 0;
String getAvgsSQL = "SELECT AVG(mintemp) AS rsMin, AVG(maxtemp) AS rsMax FROM temp";
rsMin = statement.executeQuery(getAvgsSQL);
while (rsMin.next()) { // there is only one
averageMinTemp = rsMin.getDouble("rsMin");
System.out.println("Average min is " + averageMinTemp);
averageMaxTemp = rsMin.getDouble("rsMax");
System.out.println("Average max is " + averageMaxTemp);
}
System.out.println(
"Average maximum temperature = "
+ averageMaxTemp
+ " , average minimum temperature = "
+ averageMinTemp);
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
// A finally block runs whether an exception is thrown or not. Close resources and tidy up
// whether this code worked or not.
try {
if (rsMin != null) {
rsMin.close(); // Close result set
System.out.println("ResultSet closed");
}
} catch (SQLException se) {
se.printStackTrace();
}
// Close all of the statements. Stored a reference to each statement in allStatements so we
// can loop over all of them and close them all.
for (Statement s : allStatements) {
if (s != null) {
try {
s.close();
System.out.println("Statement closed");
} catch (SQLException se) {
System.out.println("Error closing statement");
se.printStackTrace();
}
}
}
try {
if (conn != null) {
conn.close(); // Close connection to database
System.out.println("Database connection closed");
}
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("End of program");
}
public void updateISOLanguage(
CFSecurityAuthorization Authorization, CFSecurityISOLanguageBuff Buff) {
final String S_ProcName = "updateISOLanguage";
ResultSet resultSet = null;
try {
short ISOLanguageId = Buff.getRequiredISOLanguageId();
String ISO6392Code = Buff.getRequiredISO6392Code();
String ISO6391Code = Buff.getOptionalISO6391Code();
String EnglishName = Buff.getRequiredEnglishName();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "ISLN");
stmtUpdateByPKey.setShort(argIdx++, ISOLanguageId);
stmtUpdateByPKey.setString(argIdx++, ISO6392Code);
if (ISO6391Code != null) {
stmtUpdateByPKey.setString(argIdx++, ISO6391Code);
} else {
stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtUpdateByPKey.setString(argIdx++, EnglishName);
stmtUpdateByPKey.setInt(argIdx++, Revision);
try {
resultSet = stmtUpdateByPKey.executeQuery();
} catch (SQLException e) {
if (e.getErrorCode() != 1329) {
throw e;
}
resultSet = null;
}
if ((resultSet != null) && resultSet.next()) {
CFSecurityISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet);
if ((resultSet != null) && resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredISO6392Code(updatedBuff.getRequiredISO6392Code());
Buff.setOptionalISO6391Code(updatedBuff.getOptionalISO6391Code());
Buff.setRequiredEnglishName(updatedBuff.getRequiredEnglishName());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void put(StoredBlock storedBlock, StoredUndoableBlock undoableBlock)
throws BlockStoreException {
maybeConnect();
// We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
byte[] hashBytes = new byte[28];
System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28);
int height = storedBlock.getHeight();
byte[] transactions = null;
byte[] txOutChanges = null;
try {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
if (undoableBlock.getTxOutChanges() != null) {
undoableBlock.getTxOutChanges().serializeToStream(bos);
txOutChanges = bos.toByteArray();
} else {
int numTxn = undoableBlock.getTransactions().size();
bos.write((int) (0xFF & (numTxn >> 0)));
bos.write((int) (0xFF & (numTxn >> 8)));
bos.write((int) (0xFF & (numTxn >> 16)));
bos.write((int) (0xFF & (numTxn >> 24)));
for (Transaction tx : undoableBlock.getTransactions()) tx.rimbitSerialize(bos);
transactions = bos.toByteArray();
}
bos.close();
} catch (IOException e) {
throw new BlockStoreException(e);
}
try {
if (log.isDebugEnabled())
log.debug("Looking for undoable block with hash: " + Utils.bytesToHexString(hashBytes));
PreparedStatement findS =
conn.get().prepareStatement("select 1 from undoableBlocks where hash = ?");
findS.setBytes(1, hashBytes);
ResultSet rs = findS.executeQuery();
if (rs.next()) {
// We already have this output, update it.
findS.close();
// Postgres insert-or-updates are very complex (and finnicky). This level of transaction
// isolation
// seems to work for rimbitj
PreparedStatement s =
conn.get()
.prepareStatement(
"UPDATE undoableBlocks SET txOutChanges=?, transactions=?" + " WHERE hash = ?");
s.setBytes(3, hashBytes);
if (log.isDebugEnabled())
log.debug("Updating undoable block with hash: " + Utils.bytesToHexString(hashBytes));
if (transactions == null) {
s.setBytes(1, txOutChanges);
s.setNull(2, Types.BINARY);
} else {
s.setNull(1, Types.BINARY);
s.setBytes(2, transactions);
}
s.executeUpdate();
s.close();
return;
}
PreparedStatement s =
conn.get()
.prepareStatement(
"INSERT INTO undoableBlocks(hash, height, txOutChanges, transactions)"
+ " VALUES(?, ?, ?, ?)");
s.setBytes(1, hashBytes);
s.setInt(2, height);
if (log.isDebugEnabled())
log.debug(
"Inserting undoable block with hash: "
+ Utils.bytesToHexString(hashBytes)
+ " at height "
+ height);
if (transactions == null) {
s.setBytes(3, txOutChanges);
s.setNull(4, Types.BINARY);
} else {
s.setNull(3, Types.BINARY);
s.setBytes(4, transactions);
}
s.executeUpdate();
s.close();
try {
putUpdateStoredBlock(storedBlock, true);
} catch (SQLException e) {
throw new BlockStoreException(e);
}
} catch (SQLException e) {
if (!e.getSQLState().equals(POSTGRES_DUPLICATE_KEY_ERROR_CODE))
throw new BlockStoreException(e);
}
}
public void createSecDevice(CFSecurityAuthorization Authorization, CFSecuritySecDeviceBuff Buff) {
final String S_ProcName = "createSecDevice";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
UUID SecUserId = Buff.getRequiredSecUserId();
String DevName = Buff.getRequiredDevName();
String PubKey = Buff.getOptionalPubKey();
Connection cnx = schema.getCnx();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_create_secdev( ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(argIdx++, "SDEV");
stmtCreateByPKey.setString(argIdx++, SecUserId.toString());
stmtCreateByPKey.setString(argIdx++, DevName);
if (PubKey != null) {
stmtCreateByPKey.setString(argIdx++, PubKey);
} else {
stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
try {
resultSet = stmtCreateByPKey.executeQuery();
} catch (SQLException e) {
if (e.getErrorCode() != 1329) {
throw e;
}
resultSet = null;
}
if ((resultSet != null) && resultSet.next()) {
CFSecuritySecDeviceBuff createdBuff = unpackSecDeviceResultSetToBuff(resultSet);
if ((resultSet != null) && resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredSecUserId(createdBuff.getRequiredSecUserId());
Buff.setRequiredDevName(createdBuff.getRequiredDevName());
Buff.setOptionalPubKey(createdBuff.getOptionalPubKey());
Buff.setRequiredRevision(createdBuff.getRequiredRevision());
Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
Buff.setCreatedAt(createdBuff.getCreatedAt());
Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
Buff.setUpdatedAt(createdBuff.getUpdatedAt());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* Reset any events meeting the given criteria so that they can be retried.
*
* @param criteria An event containing the event-selection criteria.
* @return The number of events reset.
* @exception FrameworkException Thrown on errors.
*/
protected static int reset(Event criteria) throws FrameworkException {
Debug.log(
Debug.MSG_STATUS, "QUEUE OPERATION: Resetting events in database for database queue ...");
if (!StringUtils.hasValue(criteria.channelName)) {
throw new FrameworkException(
"ERROR: Event channel name is a required queue search criteria.");
}
Connection dbConn = null;
PreparedStatement ps = null;
long startTime = -1;
if (Debug.isLevelEnabled(Debug.BENCHMARK)) startTime = System.currentTimeMillis();
try {
dbConn = DBConnectionPool.getInstance().acquireConnection();
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Criteria used to reset events in database:\n" + criteria.describe());
// If no identifier was given that uniquely identifies a single event ...
if (criteria.id == 0) {
// Use last error time and error count, if available.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_SQL);
ps.setString(1, criteria.channelName);
if (criteria.lastErrorTime == null) ps.setNull(2, Types.DATE);
else ps.setTimestamp(2, criteria.lastErrorTime);
if (criteria.errorCount < 1) ps.setNull(3, Types.INTEGER);
else ps.setInt(3, criteria.errorCount);
} else {
// An Id was given which should uniquely identify a single event, so we should
// skip using any other qualifying criteria, if present.
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "\n" + LINE + "\nExecuting SQL:\n" + UPDATE_EVENT_RETRY_BY_ID_SQL);
ps = dbConn.prepareStatement(UPDATE_EVENT_RETRY_BY_ID_SQL);
ps.setString(1, criteria.channelName);
ps.setInt(2, criteria.id);
}
int numRows = ps.executeUpdate();
DBConnectionPool.getInstance().commit(dbConn);
if (Debug.isLevelEnabled(Debug.DB_DATA))
Debug.log(
Debug.DB_DATA, "Committed SQL execution affected [" + numRows + "] rows.\n" + LINE);
return numRows;
} catch (SQLException sqle) {
throw new DatabaseException(
"ERROR: Could not execute SQL statement:\n" + DBInterface.getSQLErrorMessage(sqle));
} catch (Exception e) {
throw new DatabaseException("ERROR: Could not execute SQL statement:\n" + e.toString());
} finally {
releaseDatabaseResources(dbConn, ps);
if (Debug.isLevelEnabled(Debug.BENCHMARK) && (startTime > 0)) {
long stopTime = System.currentTimeMillis();
Debug.log(
Debug.BENCHMARK,
"ELAPSED TIME ["
+ (stopTime - startTime)
+ "] msec: "
+ "SQL: Time to reset event(s) in PersistentEvent database table.");
}
}
}
/**
* Saves the templates to the database.
*
* @throws java.sql.SQLException Thrown on sql error.
*/
public void saveToDatabase() throws java.sql.SQLException {
setProgressIndeterminate(true);
setMessage("Saving Templates");
ArrayList templates = getTemplates();
Connection oracleConnection = getDataSource().getConnection();
try {
oracleConnection.setAutoCommit(false);
Statement query = oracleConnection.createStatement();
try {
int templateCount = templates.size();
// First remove any existing entries.
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN (");
for (int i = 0; i < templateCount; i++) {
if (i > 0) whereClause.append(", ");
whereClause.append("'");
whereClause.append(((Template) templates.get(i)).getID());
whereClause.append("'");
}
whereClause.append(")");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_REC");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TEMPLATE");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)");
PreparedStatement templateInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)");
PreparedStatement macroInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)");
PreparedStatement signalInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)");
PreparedStatement fieldInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)");
PreparedStatement requestInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)");
PreparedStatement groupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)");
PreparedStatement requestGroupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("UPDATE ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?");
PreparedStatement signalUpdateStatement =
oracleConnection.prepareStatement(sql.toString());
try {
int progress = 0;
setProgressMaximum(importedFieldCount + importedMacroCount);
setProgressValue(0);
setProgressIndeterminate(false);
for (int templateIndex = 0;
templateIndex < templateCount;
templateIndex++) {
Template currentTemplate = (Template) templates.get(templateIndex);
String currentTemplateID = currentTemplate.getID();
templateInsertStatement.setString(1, currentTemplateID);
String currentDescription = currentTemplate.getDescription();
if (currentDescription == null)
templateInsertStatement.setNull(2, Types.VARCHAR);
else templateInsertStatement.setString(2, currentDescription);
templateInsertStatement.setString(3, currentTemplate.getFileName());
templateInsertStatement.setTimestamp(
4, currentTemplate.getFileModifiedDate());
templateInsertStatement.execute();
// Need to insert macros.
int macroCount = currentTemplate.getMacroCount();
for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) {
macroInsertStatement.setString(1, currentTemplateID);
String currentMacro = currentTemplate.getMacroAt(macroIndex);
macroInsertStatement.setString(2, currentMacro);
macroInsertStatement.execute();
setProgressValue(++progress);
}
int signalCount = currentTemplate.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentTemplate.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
String currentRecordTypeID =
currentSignal.getType().getRecordType().getID();
signalInsertStatement.setString(1, currentTemplateID);
signalInsertStatement.setString(2, currentSignalID);
signalInsertStatement.setString(3, currentRecordTypeID);
signalInsertStatement.setString(4, currentSignal.getArchiveIndicator());
BigDecimal currentFrequency = currentSignal.getArchiveFrequency();
if (currentFrequency == null)
currentFrequency = new BigDecimal("60"); // 60 default in RDB
signalInsertStatement.setBigDecimal(5, currentFrequency);
String currentType = currentSignal.getArchiveType();
if (currentType == null)
currentType = "Monitor"; // 'Monitor' default in RDB
signalInsertStatement.setString(6, currentType);
signalInsertStatement.execute();
int fieldCount = currentSignal.getFieldCount();
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) {
SignalField currentField = currentSignal.getFieldAt(fieldIndex);
fieldInsertStatement.setString(1, currentTemplateID);
fieldInsertStatement.setString(2, currentSignalID);
fieldInsertStatement.setString(3, currentField.getType().getID());
fieldInsertStatement.setString(4, currentRecordTypeID);
fieldInsertStatement.setString(5, currentField.getValue());
fieldInsertStatement.execute();
if (isParseCanceled()) {
oracleConnection.rollback();
return;
}
setProgressValue(++progress);
}
}
// Insert archive requests.
int requestCount = currentTemplate.getArchiveRequestCount();
for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) {
ArchiveRequest currentRequest =
currentTemplate.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestInsertStatement.setString(1, currentTemplateID);
requestInsertStatement.setString(2, currentRequestFileName);
requestInsertStatement.execute();
signalCount = currentRequest.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentRequest.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
signalUpdateStatement.setString(
1, currentSignal.getArchiveIndicator());
signalUpdateStatement.setBigDecimal(
2, currentSignal.getArchiveFrequency());
signalUpdateStatement.setString(3, currentSignal.getArchiveType());
signalUpdateStatement.setString(4, currentRequestFileName);
signalUpdateStatement.setString(5, currentTemplateID);
signalUpdateStatement.setString(6, currentSignalID);
signalUpdateStatement.setString(
7, currentSignal.getType().getRecordType().getID());
signalUpdateStatement.execute();
int fieldCount = currentSignal.getFieldCount();
}
}
// Insert archive groups.
int groupCount = currentTemplate.getArchiveGroupCount();
for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) {
ArchiveGroup currentGroup =
currentTemplate.getArchiveGroupAt(groupIndex);
groupInsertStatement.setString(1, currentTemplateID);
String currentGroupFileName = currentGroup.getFileName();
groupInsertStatement.setString(2, currentGroupFileName);
groupInsertStatement.execute();
requestCount = currentGroup.getArchiveRequestCount();
for (int requestIndex = 0;
requestIndex < requestCount;
requestIndex++) {
ArchiveRequest currentRequest =
currentGroup.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestGroupInsertStatement.setString(1, currentTemplateID);
requestGroupInsertStatement.setString(2, currentGroupFileName);
requestGroupInsertStatement.setString(
3, currentRequest.getFileName());
}
}
}
} finally {
signalUpdateStatement.close();
}
} finally {
requestGroupInsertStatement.close();
}
} finally {
groupInsertStatement.close();
}
} finally {
requestInsertStatement.close();
}
} finally {
fieldInsertStatement.close();
}
} finally {
signalInsertStatement.close();
}
} finally {
macroInsertStatement.close();
}
} finally {
templateInsertStatement.close();
}
} catch (java.sql.SQLException ex) {
oracleConnection.rollback();
throw ex;
} finally {
query.close();
}
if (isParseCanceled()) oracleConnection.rollback();
else oracleConnection.commit();
} finally {
oracleConnection.close();
}
}
/**
* Inserta el registro dado por la entidad vData.
*
* <p><b> insert into
* GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado)
* values (?,?,?,?,?,?,?,?,?) </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - VO Dinámico que contiene a la entidad a Insertada, así como a la llave de
* esta entidad.
*/
public TVDinRep insertExiste(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
boolean lAgregarAPNC = false;
boolean lInsertaCausa = false;
TFechas dtFechaActual = new TFechas();
int iNumSolicitud, iCveTramite, iCveModalidad = 0;
int iConsecutivoPNC = 0;
int iEjercicio = 0;
try {
// LEL26092006
Vector vcDataA =
findByCustom(
"",
"SELECT "
+ "TRAREGPNCETAPA.IEJERCICIOPNC, "
+ "TRAREGPNCETAPA.INUMSOLICITUD, "
+ "TRAREGPNCETAPA.ICONSECUTIVOPNC, "
+ "TRAREGPNCETAPA.ICVETRAMITE, "
+ "TRAREGPNCETAPA.ICVEMODALIDAD "
+ "FROM TRAREGPNCETAPA "
+ "where TRAREGPNCETAPA.IEJERCICIO = "
+ vData.getInt("iEjercicio")
+ " and TRAREGPNCETAPA.INUMSOLICITUD = "
+ vData.getInt("iNumSolicitud")
+ " ORDER BY ICONSECUTIVOPNC DESC ");
TVDinRep vSoli;
if (vcDataA.size() > 0) {
vSoli = (TVDinRep) vcDataA.get(0);
iEjercicio = vSoli.getInt("IEJERCICIOPNC");
iNumSolicitud = vSoli.getInt("INUMSOLICITUD");
iConsecutivoPNC = vSoli.getInt("ICONSECUTIVOPNC");
iCveTramite = vSoli.getInt("ICVETRAMITE");
iCveModalidad = vSoli.getInt("ICVEMODALIDAD");
vcDataA = null;
vcDataA =
findByCustom(
"",
"SELECT "
+ "DTNOTIFICACION FROM TRAREGREQXTRAM "
+ "WHERE IEJERCICIO = "
+ iEjercicio
+ " AND INUMSOLICITUD = "
+ iNumSolicitud
+ " AND ICVETRAMITE = "
+ iCveTramite
+ " AND ICVEMODALIDAD = "
+ iCveModalidad
+ " AND LTIENEPNC = 1");
lAgregarAPNC = true;
for (int i = 0; i < vcDataA.size() && lAgregarAPNC == false; i++) {
vSoli = (TVDinRep) vcDataA.get(i);
if (vSoli.getDate("DTNOTIFICACION") == null) lAgregarAPNC = true;
else lAgregarAPNC = false;
}
}
if (lAgregarAPNC) {
TVDinRep vResuelto;
Vector vcDataR =
findByCustom(
"",
"SELECT lResuelto "
+ "FROM GRLREGISTROPNC WHERE "
+ "IEJERCICIO = "
+ iEjercicio
+ " AND ICONSECUTIVOPNC = "
+ iConsecutivoPNC);
if (vcDataR.size() > 0) {
vResuelto = (TVDinRep) vcDataR.get(0);
if (vResuelto.getInt("lResuelto") != 0) lAgregarAPNC = false;
}
}
// FinLEL26092006
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"insert into GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado,iCveOficina,iCveDepartamento,iCveProceso) values (?,?,?,?,?,?,?,?,?,?,?,?)";
// AGREGAR AL ULTIMO ...
if (lAgregarAPNC == false) {
Vector vcData =
findByCustom(
"",
"select MAX(iConsecutivoPNC) AS iConsecutivoPNC from GRLRegistroPNC WHERE iEjercicio = "
+ vData.getInt("iEjercicio"));
if (vcData.size() > 0) {
TVDinRep vUltimo = (TVDinRep) vcData.get(0);
vData.put("iConsecutivoPNC", vUltimo.getInt("iConsecutivoPNC") + 1);
} else vData.put("iConsecutivoPNC", 1);
} else {
vData.put("iConsecutivoPNC", iConsecutivoPNC);
}
vData.addPK(vData.getString("iConsecutivoPNC"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, dtFechaActual.getIntYear(dtFechaActual.TodaySQL()));
lPStmt.setInt(2, vData.getInt("iConsecutivoPNC"));
iConsecutivo = vData.getInt("iConsecutivoPNC");
lPStmt.setDate(3, tFecha.TodaySQL());
lPStmt.setInt(4, vData.getInt("iCveUsuario"));
lPStmt.setInt(5, vData.getInt("iCveProducto"));
lPStmt.setInt(6, vData.getInt("lResuelto"));
if (vData.getDate("dtResolucion") == null) lPStmt.setNull(7, Types.DATE);
else lPStmt.setDate(7, vData.getDate("dtResolucion"));
lPStmt.setInt(8, vData.getInt("iCveOficinaUsrAsg"));
lPStmt.setInt(9, vData.getInt("iCveDeptoUsrAsg"));
lPStmt.setInt(10, vData.getInt("iCveOficinaUsr"));
lPStmt.setInt(11, vData.getInt("iCveDeptoUsr"));
lPStmt.setInt(12, vData.getInt("iCveProceso"));
if (lAgregarAPNC == false) lPStmt.executeUpdate();
lPStmt.close();
try {
TDGRLRegCausaPNC1 dGRLRegCausaPNC = new TDGRLRegCausaPNC1();
// TDGRLRegCausaPNC dGRLRegCausaPNC = new TDGRLRegCausaPNC();
if (lAgregarAPNC == false) {
dGRLRegCausaPNC.insert(vData, conn, iConsecutivo);
// dGRLRegCausaPNC.insert(vData,conn);
} else {
dGRLRegCausaPNC.insertA(vData, conn, iConsecutivo);
// dGRLRegCausaPNC.insertA(vData,conn);
}
} catch (Exception exCausa) {
exCausa.printStackTrace();
lSuccess = false;
}
if (cnNested == null && lSuccess) conn.commit();
} catch (Exception ex) {
warn("insert", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("insert.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) lPStmt.close();
if (cnNested == null) {
if (conn != null) conn.close();
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("insert.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
public User createUser(String username, String password, String name, String email)
throws UserAlreadyExistsException {
if (isReadOnly()) {
// Reject the operation since the provider is read-only
throw new UnsupportedOperationException();
}
try {
loadUser(username);
// The user already exists since no exception, so:
throw new UserAlreadyExistsException("Username " + username + " already exists");
} catch (UserNotFoundException unfe) {
// The user doesn't already exist so we can create a new user
// Determine if the password should be stored as plain text or encrypted.
boolean usePlainPassword = JiveGlobals.getBooleanProperty("user.usePlainPassword");
String encryptedPassword = null;
if (!usePlainPassword) {
try {
encryptedPassword = AuthFactory.encryptPassword(password);
// Set password to null so that it's inserted that way.
password = null;
} catch (UnsupportedOperationException uoe) {
// Encrypting the password may have failed if in setup mode. Therefore,
// use the plain password.
}
}
Date now = new Date();
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(INSERT_USER);
pstmt.setString(1, username);
if (password == null) {
pstmt.setNull(2, Types.VARCHAR);
} else {
pstmt.setString(2, password);
}
if (encryptedPassword == null) {
pstmt.setNull(3, Types.VARCHAR);
} else {
pstmt.setString(3, encryptedPassword);
}
if (name == null) {
pstmt.setNull(4, Types.VARCHAR);
} else {
pstmt.setString(4, name);
}
if (email == null) {
pstmt.setNull(5, Types.VARCHAR);
} else {
pstmt.setString(5, email);
}
pstmt.setString(6, StringUtils.dateToMillis(now));
pstmt.setString(7, StringUtils.dateToMillis(now));
pstmt.execute();
} catch (Exception e) {
Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
} finally {
DbConnectionManager.closeConnection(pstmt, con);
}
return new User(username, name, email, now, now);
}
}
public void updateTopDomain(CFSecurityAuthorization Authorization, CFInternetTopDomainBuff Buff) {
final String S_ProcName = "updateTopDomain";
if ("TDOM".equals(Buff.getClassCode())
&& (!schema.isTenantUser(Authorization, Buff.getRequiredTenantId(), "UpdateTopDomain"))) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Permission denied -- User not part of TSecGroup UpdateTopDomain");
}
ResultSet resultSet = null;
try {
String ClassCode = Buff.getClassCode();
long TenantId = Buff.getRequiredTenantId();
long Id = Buff.getRequiredId();
String Description = Buff.getOptionalDescription();
long TldId = Buff.getRequiredTldId();
String Name = Buff.getRequiredName();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
final String sql =
"CALL sp_update_tdomdef( ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, ClassCode);
stmtUpdateByPKey.setLong(argIdx++, TenantId);
stmtUpdateByPKey.setLong(argIdx++, Id);
if (Description != null) {
stmtUpdateByPKey.setString(argIdx++, Description);
} else {
stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtUpdateByPKey.setLong(argIdx++, TldId);
stmtUpdateByPKey.setString(argIdx++, Name);
stmtUpdateByPKey.setInt(argIdx++, Revision);
resultSet = stmtUpdateByPKey.executeQuery();
if (resultSet.next()) {
CFInternetTopDomainBuff updatedBuff = unpackTopDomainResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setOptionalDescription(updatedBuff.getOptionalDescription());
Buff.setRequiredTldId(updatedBuff.getRequiredTldId());
Buff.setRequiredName(updatedBuff.getRequiredName());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* Inserta el registro dado por la entidad vData.
*
* <p><b> insert into
* GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado)
* values (?,?,?,?,?,?,?,?,?) </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return TVDinRep - VO Dinámico que contiene a la entidad a Insertada, así como a la llave de
* esta entidad.
*/
public TVDinRep insert(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
TFechas dtFechaActual = new TFechas();
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
String lSQL =
"insert into GRLRegistroPNC(iEjercicio,iConsecutivoPNC,dtRegistro,iCveUsuRegistro,iCveProducto,lResuelto,dtResolucion,iCveOficinaAsignado,iCveDeptoAsignado,iCveOficina,iCveDepartamento,iCveProceso) values (?,?,?,?,?,?,?,?,?,?,?,?)";
// AGREGAR AL ULTIMO ...
Vector vcData =
findByCustom(
"",
"select MAX(iConsecutivoPNC) AS iConsecutivoPNC from GRLRegistroPNC WHERE iEjercicio = "
+ vData.getInt("iEjercicio"));
if (vcData.size() > 0) {
TVDinRep vUltimo = (TVDinRep) vcData.get(0);
vData.put("iConsecutivoPNC", vUltimo.getInt("iConsecutivoPNC") + 1);
} else vData.put("iConsecutivoPNC", 1);
vData.addPK(vData.getString("iConsecutivoPNC"));
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, dtFechaActual.getIntYear(dtFechaActual.TodaySQL()));
lPStmt.setInt(2, vData.getInt("iConsecutivoPNC"));
iConsecutivo = vData.getInt("iConsecutivoPNC");
lPStmt.setDate(3, tFecha.TodaySQL());
lPStmt.setInt(4, vData.getInt("iCveUsuario"));
lPStmt.setInt(5, vData.getInt("iCveProducto"));
lPStmt.setInt(6, vData.getInt("lResuelto"));
if (vData.getDate("dtResolucion") == null) lPStmt.setNull(7, Types.DATE);
else lPStmt.setDate(7, vData.getDate("dtResolucion"));
lPStmt.setInt(8, vData.getInt("iCveOficinaUsrAsg"));
lPStmt.setInt(9, vData.getInt("iCveDeptoUsrAsg"));
lPStmt.setInt(10, vData.getInt("iCveOficinaUsr"));
lPStmt.setInt(11, vData.getInt("iCveDeptoUsr"));
lPStmt.setInt(12, vData.getInt("iCveProceso"));
lPStmt.executeUpdate();
lPStmt.close();
try {
TDGRLRegCausaPNC1 dGRLRegCausaPNC = new TDGRLRegCausaPNC1();
dGRLRegCausaPNC.insert(vData, conn, iConsecutivo);
} catch (Exception exCausa) {
exCausa.printStackTrace();
lSuccess = false;
throw new Exception("Error Causa");
}
if (cnNested == null && lSuccess) conn.commit();
} catch (Exception ex) {
warn("insert", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("insert.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) lPStmt.close();
if (cnNested == null) {
if (conn != null) conn.close();
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("insert.close", ex2);
}
if (lSuccess == false) throw new DAOException("");
return vData;
}
}
public void createISOCurrency(
CFSecurityAuthorization Authorization, CFSecurityISOCurrencyBuff Buff) {
final String S_ProcName = "createISOCurrency";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
short Id = Buff.getRequiredId();
String ISOCode = Buff.getRequiredISOCode();
String Name = Buff.getRequiredName();
String UnitSymbol = Buff.getOptionalUnitSymbol();
String FracSymbol = Buff.getOptionalFracSymbol();
short Precis = Buff.getRequiredPrecis();
Connection cnx = schema.getCnx();
String sql =
"exec sp_create_iso_ccy ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(argIdx++, "ISCY");
stmtCreateByPKey.setShort(argIdx++, Id);
stmtCreateByPKey.setString(argIdx++, ISOCode);
stmtCreateByPKey.setString(argIdx++, Name);
if (UnitSymbol != null) {
stmtCreateByPKey.setString(argIdx++, UnitSymbol);
} else {
stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
if (FracSymbol != null) {
stmtCreateByPKey.setString(argIdx++, FracSymbol);
} else {
stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtCreateByPKey.setShort(argIdx++, Precis);
resultSet = stmtCreateByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityISOCurrencyBuff createdBuff = unpackISOCurrencyResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredId(createdBuff.getRequiredId());
Buff.setRequiredISOCode(createdBuff.getRequiredISOCode());
Buff.setRequiredName(createdBuff.getRequiredName());
Buff.setOptionalUnitSymbol(createdBuff.getOptionalUnitSymbol());
Buff.setOptionalFracSymbol(createdBuff.getOptionalFracSymbol());
Buff.setRequiredPrecis(createdBuff.getRequiredPrecis());
Buff.setRequiredRevision(createdBuff.getRequiredRevision());
Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
Buff.setCreatedAt(createdBuff.getCreatedAt());
Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
Buff.setUpdatedAt(createdBuff.getUpdatedAt());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void createTag(CFSecurityAuthorization Authorization, CFCrmTagBuff Buff) {
final String S_ProcName = "createTag";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
long TenantId = Buff.getRequiredTenantId();
String Name = Buff.getRequiredName();
String Descr = Buff.getOptionalDescr();
Connection cnx = schema.getCnx();
String sql = "exec sp_create_tag ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtCreateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtCreateByPKey.setString(argIdx++, "CTAG");
stmtCreateByPKey.setLong(argIdx++, TenantId);
stmtCreateByPKey.setString(argIdx++, Name);
if (Descr != null) {
stmtCreateByPKey.setString(argIdx++, Descr);
} else {
stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtCreateByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtCreateByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtCreateByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtCreateByPKey.getUpdateCount()) {
break;
}
}
if (resultSet == null) {
throw CFLib.getDefaultExceptionFactory()
.newNullArgumentException(getClass(), S_ProcName, 0, "resultSet");
}
if (resultSet.next()) {
CFCrmTagBuff createdBuff = unpackTagResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredTenantId(createdBuff.getRequiredTenantId());
Buff.setRequiredId(createdBuff.getRequiredId());
Buff.setRequiredName(createdBuff.getRequiredName());
Buff.setOptionalDescr(createdBuff.getOptionalDescr());
Buff.setRequiredRevision(createdBuff.getRequiredRevision());
Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
Buff.setCreatedAt(createdBuff.getCreatedAt());
Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
Buff.setUpdatedAt(createdBuff.getUpdatedAt());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public void updateISOCurrency(
CFSecurityAuthorization Authorization, CFSecurityISOCurrencyBuff Buff) {
final String S_ProcName = "updateISOCurrency";
ResultSet resultSet = null;
try {
short ISOCurrencyId = Buff.getRequiredISOCurrencyId();
String ISOCode = Buff.getRequiredISOCode();
String Name = Buff.getRequiredName();
String UnitSymbol = Buff.getOptionalUnitSymbol();
short Precis = Buff.getRequiredPrecis();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql =
"exec sp_update_iso_ccy ?, ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "ISCY");
stmtUpdateByPKey.setShort(argIdx++, ISOCurrencyId);
stmtUpdateByPKey.setString(argIdx++, ISOCode);
stmtUpdateByPKey.setString(argIdx++, Name);
if (UnitSymbol != null) {
stmtUpdateByPKey.setString(argIdx++, UnitSymbol);
} else {
stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
}
stmtUpdateByPKey.setShort(argIdx++, Precis);
stmtUpdateByPKey.setInt(argIdx++, Revision);
stmtUpdateByPKey.execute();
boolean moreResults = true;
resultSet = null;
while (resultSet == null) {
try {
moreResults = stmtUpdateByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
resultSet = stmtUpdateByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtUpdateByPKey.getUpdateCount()) {
break;
}
}
if (resultSet == null) {
throw CFLib.getDefaultExceptionFactory()
.newNullArgumentException(getClass(), S_ProcName, 0, "resultSet");
}
if (resultSet.next()) {
CFSecurityISOCurrencyBuff updatedBuff = unpackISOCurrencyResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredISOCode(updatedBuff.getRequiredISOCode());
Buff.setRequiredName(updatedBuff.getRequiredName());
Buff.setOptionalUnitSymbol(updatedBuff.getOptionalUnitSymbol());
Buff.setRequiredPrecis(updatedBuff.getRequiredPrecis());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* Perrform process.
*
* @return Message that would be set to process infor summary (no use currently)
* @throws Exception if not successful
*/
protected String doIt() throws Exception {
// load query into cache file directly
// int userId= this.getAD_User_ID();
// User user= SecurityUtils.getUser(userId);
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
QueryEngine engine = QueryEngine.getInstance();
conn = engine.getConnection();
Configurations conf =
(Configurations)
WebUtils.getServletContextManager().getActor(nds.util.WebKeys.CONFIGURATIONS);
String folder = conf.getProperty("ahyy.payment.folder.download", "e:/act/ahyy/download");
File fd = new File(folder);
if (!fd.exists()) fd.mkdirs();
ahyyCode = conf.getProperty("ahyy.payment.code");
if (ahyyCode == null || ahyyCode.length() != 8)
throw new NDSException("Wrong code for bank payment interface");
SimpleDateFormat df = new SimpleDateFormat("yyMMdd");
String ftpFile = "MPAYREP" + ahyyCode + df.format(new java.util.Date());
String file = folder + "/" + ftpFile;
StringBuffer sb = new StringBuffer();
String lineSep = Tools.LINE_SEPARATOR;
try {
// 下载文件
CommandExecuter cmd = new CommandExecuter(folder + "/log/" + ftpFile + ".log");
String exec = conf.getProperty("ahyy.payment.download", "e:/act/bin/download.cmd");
int err = cmd.run(exec + " " + ftpFile);
if (err != 0) {
throw new NDSException("Error(code=" + err + ") when doing " + exec + " " + ftpFile);
}
if (!(new File(file)).exists()) {
throw new NDSException("File not downloaded when doing " + exec + " " + ftpFile);
}
BufferedReader in = new BufferedReader(new FileReader(file));
in.readLine(); // skip first line, which is summary
String line = in.readLine();
pstmt =
conn.prepareStatement(
"update b_pay_sum set state=?, err_code =? where billno=? and state='R'");
while (line != null) {
log.debug(line);
if (line.length() < 34) {
log.debug("line not has length: 34");
}
/*
String billType= line.substring(0,2);
String billNo= line.substring(0, 22);// billno
String amt=line.substring(22,34);
String ack=line.substring(34,36);*/
String billNo = line.substring(0, 20); // billno
String amt = line.substring(20, 32);
String ack = line.substring(32, 34);
if ("00".equals(ack)) {
java.math.BigDecimal bd = new java.math.BigDecimal(amt);
pstmt.setString(1, "Y");
pstmt.setNull(2, java.sql.Types.VARCHAR);
pstmt.setString(3, billNo);
int ret = pstmt.executeUpdate();
if (ret != 1) {
sb.append(line + "(updated " + ret + " lines)").append(lineSep);
}
} else {
pstmt.setString(1, "P");
pstmt.setString(2, ack);
pstmt.setString(3, billNo);
int ret = pstmt.executeUpdate();
if (ret != 1) {
sb.append(line + "(updated " + ret + " lines)").append(lineSep);
}
}
line = in.readLine();
}
in.close();
try {
log.debug("Move " + file + " to " + folder + "/log/" + ftpFile);
(new File(file)).renameTo(new File(folder + "/log/" + ftpFile));
} catch (Throwable t) {
log.error("Fail to move " + file + " to " + folder + "/log/" + ftpFile, t);
}
Vector vet = new Vector();
String log = sb.toString();
this.log.debug(log);
this.addLog(log);
return "完成";
} finally {
if (rs != null)
try {
rs.close();
} catch (Throwable t) {
}
if (pstmt != null)
try {
pstmt.close();
} catch (Throwable t) {
}
if (conn != null)
try {
conn.close();
} catch (Throwable t) {
}
}
}
