Esempio n. 1
0
 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();
 }
Esempio n. 2
0
 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);
   }
 }
Esempio n. 3
0
 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));
   }
 }
Esempio n. 4
0
 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()));
   }
 }
Esempio n. 5
0
 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;
     }
   }
 }
Esempio n. 7
0
  @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;
     }
   }
 }
Esempio n. 10
0
  /**
   * 根据数据库连接和表和相关列,做数据库的插入操作
   *
   * @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();
    }
  }
Esempio n. 11
0
  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();
    }
  }
Esempio n. 12
0
  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);
    }
  }
Esempio n. 13
0
  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;
     }
   }
 }
Esempio n. 17
0
  /**
   * 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.");
      }
    }
  }
Esempio n. 18
0
 /**
  * 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;
    }
  }
Esempio n. 20
0
  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;
     }
   }
 }
Esempio n. 26
0
  /**
   * 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) {
        }
    }
  }