/** * Tests the implementation of getCharacterStream(long pos, long length). * * @throws Exception */ public void testGetCharacterStreamLong() throws Exception { String str1 = "This is a test String. This is a test String"; Reader r1 = new java.io.StringReader(str1); PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, r1); ps.execute(); ps.close(); Statement st = createStatement(); ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id); rs.next(); Clob clob = rs.getClob(1); Reader r_1 = clob.getCharacterStream(2L, 5L); String str2 = str1.substring(1, 6); Reader r_2 = new java.io.StringReader(str2); assertEquals(r_2, r_1); rs.close(); st.close(); }
public void deleteUser(String username) { if (isReadOnly()) { // Reject the operation since the provider is read-only throw new UnsupportedOperationException(); } Connection con = null; PreparedStatement pstmt = null; boolean abortTransaction = false; try { // Delete all of the users's extended properties con = DbConnectionManager.getTransactionConnection(); pstmt = con.prepareStatement(DELETE_USER_PROPS); pstmt.setString(1, username); pstmt.execute(); pstmt.close(); // Delete the actual user entry pstmt = con.prepareStatement(DELETE_USER); pstmt.setString(1, username); pstmt.execute(); } catch (Exception e) { Log.error(e); abortTransaction = true; } finally { DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction); } }
/** * Method called by the Form panel to delete existing data. * * @param persistentObject value object to delete * @return an ErrorResponse value object in case of errors, VOResponse if the operation is * successfully completed */ public Response deleteRecord(ValueObject persistentObject) throws Exception { PreparedStatement stmt = null; try { EmpVO vo = (EmpVO) persistentObject; // delete from WORKING_DAYS... stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?"); stmt.setString(1, vo.getEmpCode()); stmt.execute(); stmt.close(); // delete from EMP... stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?"); stmt.setString(1, vo.getEmpCode()); stmt.execute(); gridFrame.reloadData(); frame.getGrid().clearData(); return new VOResponse(vo); } catch (SQLException ex) { ex.printStackTrace(); return new ErrorResponse(ex.getMessage()); } finally { try { stmt.close(); conn.commit(); } catch (SQLException ex1) { } } }
public void deleteTSecGroupByTenantVisIdx( CFSecurityAuthorization Authorization, long TenantId, boolean IsVisible) { final String S_ProcName = "deleteTSecGroupByTenantVisIdx"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "exec sp_delete_tsecgrp_by_tenantvisidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?"; if (stmtDeleteByTenantVisIdx == null) { stmtDeleteByTenantVisIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByTenantVisIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByTenantVisIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByTenantVisIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByTenantVisIdx.setLong(argIdx++, TenantId); if (IsVisible) { stmtDeleteByTenantVisIdx.setString(argIdx++, "Y"); } else { stmtDeleteByTenantVisIdx.setString(argIdx++, "N"); } Object stuff = null; boolean moreResults = stmtDeleteByTenantVisIdx.execute(); while (stuff == null) { try { moreResults = stmtDeleteByTenantVisIdx.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { stuff = stmtDeleteByTenantVisIdx.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtDeleteByTenantVisIdx.getUpdateCount()) { break; } } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
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 void inserir(Cidadao cidadao) { String sql = "insert into cidadao (cpf, nome, sexo, estado_civil, data_nascimento, cep, cidade, bairro, estado) " + "values(?,?,?,?,?,?,?,?,?)"; try { PreparedStatement p = con.prepareStatement(sql); p.setString(1, cidadao.getCpf()); p.setString(2, cidadao.getNome()); p.setString(3, cidadao.getSexo()); p.setString(4, cidadao.getEstado_civil()); p.setString(5, cidadao.getData_nascimento()); p.setString(6, cidadao.getCep()); p.setString(7, cidadao.getCidade()); p.setString(8, cidadao.getBairro()); p.setString(9, cidadao.getEstado()); p.execute(); con.close(); } catch (SQLException e) { throw new RuntimeException(e); } System.out.println("Cidadao Gravado!"); }
public void alterar(Cidadao cidadao) { String sql = "update cidadao set cpf=?, nome=?, sexo=?, estado_civil=?, data_nascimento=?, cep=?, cidade=?, bairro=?, estado=?, delegacia=? where id_cidadao=?"; try { PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, cidadao.getCpf()); ps.setString(2, cidadao.getNome()); ps.setString(3, cidadao.getSexo()); ps.setString(4, cidadao.getEstado_civil()); ps.setString(5, cidadao.getData_nascimento()); ps.setString(6, cidadao.getCep()); ps.setString(7, cidadao.getCidade()); ps.setString(8, cidadao.getBairro()); ps.setString(9, cidadao.getEstado()); ps.setInt(10, cidadao.getId_cidadao()); ps.execute(); con.close(); } catch (SQLException e) { throw new RuntimeException(e); } System.out.println("Cidadao Alterado!"); }
public void deleteContactTag(CFSecurityAuthorization Authorization, CFCrmContactTagBuff Buff) { final String S_ProcName = "deleteContactTag"; try { Connection cnx = schema.getCnx(); long TenantId = Buff.getRequiredTenantId(); long ContactId = Buff.getRequiredContactId(); long TagId = Buff.getRequiredTagId(); String sql = "exec sp_delete_ctc_tag ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"; if (stmtDeleteByPKey == null) { stmtDeleteByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByPKey.setLong(argIdx++, TenantId); stmtDeleteByPKey.setLong(argIdx++, ContactId); stmtDeleteByPKey.setLong(argIdx++, TagId); stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision()); ; Object stuff = null; boolean moreResults = stmtDeleteByPKey.execute(); while (stuff == null) { try { moreResults = stmtDeleteByPKey.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { stuff = stmtDeleteByPKey.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtDeleteByPKey.getUpdateCount()) { break; } } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } }
public void deleteISOTimezoneByOffsetIdx( CFSecurityAuthorization Authorization, short argTZHourOffset, short argTZMinOffset) { final String S_ProcName = "deleteISOTimezoneByOffsetIdx"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "exec sp_delete_isotz_by_offsetidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?"; if (stmtDeleteByOffsetIdx == null) { stmtDeleteByOffsetIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByOffsetIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByOffsetIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByOffsetIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByOffsetIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByOffsetIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByOffsetIdx.setShort(argIdx++, argTZHourOffset); stmtDeleteByOffsetIdx.setShort(argIdx++, argTZMinOffset); Object stuff = null; boolean moreResults = stmtDeleteByOffsetIdx.execute(); while (stuff == null) { try { moreResults = stmtDeleteByOffsetIdx.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { stuff = stmtDeleteByOffsetIdx.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtDeleteByOffsetIdx.getUpdateCount()) { break; } } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
/** * Obtains streams from the Clob reading portions of the content, always including the last * character in the Clob. * * <p>This case fills the Clob with Chinese/Japanese/Korean characters. */ public void testGetCharacterStreamLongLastCharCJK() throws IOException, SQLException { CharAlphabet alphabet = CharAlphabet.cjkSubset(); // Insert a Clob int length = 9001; PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(length, alphabet), length); ps.execute(); ps.close(); // Perform the actual test. getCharacterStreamLongLastChar(id, length, alphabet); }
private static TableRef initTableValues( long ts, int targetQueryConcurrency, int maxQueryConcurrency) throws Exception { byte[][] splits = new byte[][] {Ka1A, Ka1B, Ka1E, Ka1G, Ka1I, Ka2A}; Configuration config = driver.getQueryServices().getConfig(); config.setInt(QueryServices.MAX_QUERY_CONCURRENCY_ATTRIB, maxQueryConcurrency); config.setInt(QueryServices.TARGET_QUERY_CONCURRENCY_ATTRIB, targetQueryConcurrency); createTestTable(getUrl(), DDL, splits, ts - 2); String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts; Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(url, props); PreparedStatement stmt = conn.prepareStatement("upsert into " + TABLE_NAME + " VALUES (?, ?)"); stmt.setString(1, new String("a1A")); stmt.setInt(2, 1); stmt.execute(); stmt.setString(1, new String("a1E")); stmt.setInt(2, 2); stmt.execute(); conn.commit(); conn.close(); PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class); PSchema schema = pconn.getPMetaData().getSchemas().get(""); return new TableRef(null, schema.getTable(TABLE_NAME), schema, ts); }
public void excluir(Cidadao cidadao) { String sql = "delete from cidadao where id_cidadao=?"; try { PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, cidadao.getId_cidadao()); ps.execute(); con.close(); } catch (SQLException e) { throw new RuntimeException(e); } System.out.println("Excluido!"); }
/** * Inserts a Clob with the specified length, using a stream source, then fetches it from the * database and checks the length. * * @param length number of characters in the Clob * @throws IOException if reading from the source fails * @throws SQLException if something goes wrong */ private void insertAndFetchTest(long length) throws IOException, SQLException { PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(length), length); long tsStart = System.currentTimeMillis(); ps.execute(); println( "Inserted " + length + " chars (length specified) in " + (System.currentTimeMillis() - tsStart) + " ms"); Statement stmt = createStatement(); tsStart = System.currentTimeMillis(); ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id); assertTrue("Clob not inserted", rs.next()); Clob aClob = rs.getClob(1); assertEquals("Invalid length", length, aClob.length()); println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms"); rs.close(); // Insert same Clob again, using the lengthless override. id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(length)); tsStart = System.currentTimeMillis(); ps.executeUpdate(); println( "Inserted " + length + " chars (length unspecified) in " + (System.currentTimeMillis() - tsStart) + " ms"); rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id); assertTrue("Clob not inserted", rs.next()); aClob = rs.getClob(1); assertEquals("Invalid length", length, aClob.length()); println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms"); rs.close(); rollback(); }
public void deleteFeeDetailByIdIdx( CFSecurityAuthorization Authorization, long TenantId, long FeeDetailId) { final String S_ProcName = "deleteFeeDetailByIdIdx"; try { Connection cnx = schema.getCnx(); String sql = "exec sp_delete_feedtl ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?"; if (stmtDeleteByPKey == null) { stmtDeleteByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtDeleteByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtDeleteByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtDeleteByPKey.setLong(argIdx++, TenantId); stmtDeleteByPKey.setLong(argIdx++, FeeDetailId); Object stuff = null; boolean moreResults = stmtDeleteByPKey.execute(); while (stuff == null) { try { moreResults = stmtDeleteByPKey.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { stuff = stmtDeleteByPKey.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtDeleteByPKey.getUpdateCount()) { break; } } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } }
/** * Method called by the Form panel to delete existing data. * * @param persistentObject value object to delete * @return an ErrorResponse value object in case of errors, VOResponse if the operation is * successfully completed */ public Response deleteRecord(ValueObject persistentObject) throws Exception { PreparedStatement stmt = null; try { stmt = conn.prepareStatement("delete from DEMO4 where TEXT=?"); DetailTestVO vo = (DetailTestVO) persistentObject; stmt.setString(1, vo.getStringValue()); stmt.execute(); // this instruction is no more needed: the grid has been linked to the Form (see Form.linkGrid // method...) // gridFrame.reloadData(); return new VOResponse(vo); } catch (SQLException ex) { ex.printStackTrace(); return new ErrorResponse(ex.getMessage()); } finally { try { stmt.close(); conn.commit(); } catch (SQLException ex1) { } } }
/** * Insert a row with a large clob into the test table. Read the row from the database and assign * the clob value to <code>clob</code>. * * @return The id of the row that was inserted * @throws java.sql.SQLException */ private int initializeLongClob() throws SQLException { // Clob needs to be larger than one page for locking to occur final int lobLength = 40000; // Insert a long Clob PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)"); int id = BlobClobTestSetup.getID(); ps.setInt(1, id); ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength); ps.execute(); ps.close(); commit(); // Fetch the Clob object from the database Statement st = createStatement(); ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id); rs.next(); clob = rs.getClob(1); rs.close(); st.close(); return id; }
private void addOperations( PreparedStatement statement, long testId, String version, MeasuredOperationList operations) throws SQLException { for (MeasuredOperation operation : operations) { statement.setLong(1, testId); statement.setString(2, version); statement.setBigDecimal( 3, operation.getTotalTime().toUnits(Duration.MILLI_SECONDS).getValue()); statement.setBigDecimal( 4, operation.getConfigurationTime().toUnits(Duration.MILLI_SECONDS).getValue()); statement.setBigDecimal( 5, operation.getExecutionTime().toUnits(Duration.MILLI_SECONDS).getValue()); statement.setBigDecimal( 6, operation.getTotalMemoryUsed().toUnits(DataAmount.BYTES).getValue()); statement.setBigDecimal( 7, operation.getTotalHeapUsage().toUnits(DataAmount.BYTES).getValue()); statement.setBigDecimal(8, operation.getMaxHeapUsage().toUnits(DataAmount.BYTES).getValue()); statement.setBigDecimal( 9, operation.getMaxUncollectedHeap().toUnits(DataAmount.BYTES).getValue()); statement.setBigDecimal( 10, operation.getMaxCommittedHeap().toUnits(DataAmount.BYTES).getValue()); statement.execute(); } }
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); } }
/** * Method called by the Form panel to insert new data. * * @param newValueObject value object to save * @return an ErrorResponse value object in case of errors, VOResponse if the operation is * successfully completed */ public Response insertRecord(ValueObject newPersistentObject) throws Exception { // mapping between attributes and database fields... Map attribute2dbField = new HashMap(); attribute2dbField.put("empCode", "EMP_CODE"); attribute2dbField.put("firstName", "FIRST_NAME"); attribute2dbField.put("lastName", "LAST_NAME"); attribute2dbField.put("deptCode", "DEPT_CODE"); attribute2dbField.put("taskCode", "TASK_CODE"); attribute2dbField.put("sex", "SEX"); attribute2dbField.put("hireDate", "HIRE_DATE"); attribute2dbField.put("salary", "SALARY"); attribute2dbField.put("note", "NOTE"); Response res = QueryUtil.insertTable(conn, newPersistentObject, "EMP", attribute2dbField, "Y", "N", true); if (res.isError()) { conn.rollback(); return res; } else { // insert 7 records in WORKING_DAYS, one for each day of week... PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement("insert into WORKING_DAYS(EMP_CODE,DAY) values(?,?)"); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.SUNDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.MONDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.TUESDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.WEDNESDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.THURSDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.FRIDAY); pstmt.execute(); pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode()); pstmt.setInt(2, Calendar.SATURDAY); pstmt.execute(); conn.commit(); frame .getGrid() .getOtherGridParams() .put("empCode", ((EmpVO) newPersistentObject).getEmpCode()); frame.getGrid().reloadData(); return res; } catch (Exception ex) { conn.rollback(); return new ErrorResponse(ex.getMessage()); } finally { try { pstmt.close(); } catch (Exception ex1) { } } } /* // an alternative way: you can define your own business logic to store data at hand... PreparedStatement stmt = null; try { stmt = conn.prepareStatement("insert into EMP(EMP_CODE,FIRST_NAME,LAST_NAME,DEPT_CODE,TASK_CODE,SEX,HIRE_DATE,SALARY,NOTE) values(?,?,?,?,?,?,?,?,?)"); EmpVO vo = (EmpVO)newPersistentObject; stmt.setString(1,vo.getEmpCode()); stmt.setString(2,vo.getFirstName()); stmt.setString(3,vo.getLastName()); stmt.setString(4,vo.getDeptCode()); stmt.setString(5,vo.getTaskCode()); stmt.setString(6,vo.getSex()); stmt.setDate(7,vo.getHireDate()); stmt.setBigDecimal(8,vo.getSalary()); stmt.setString(9,vo.getNote()); stmt.execute(); pk = vo.getEmpCode(); gridFrame.reloadData(); return new VOResponse(vo); } catch (SQLException ex) { ex.printStackTrace(); return new ErrorResponse(ex.getMessage()); } finally { try { stmt.close(); conn.commit(); } catch (SQLException ex1) { } } */ }
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 { String ISOCode = Buff.getRequiredISOCode(); String Name = Buff.getRequiredName(); String UnitSymbol = Buff.getOptionalUnitSymbol(); 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.setString(argIdx++, ISOCode); stmtCreateByPKey.setString(argIdx++, Name); if (UnitSymbol != null) { stmtCreateByPKey.setString(argIdx++, UnitSymbol); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR); } stmtCreateByPKey.setShort(argIdx++, Precis); 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()) { 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.setRequiredISOCurrencyId(createdBuff.getRequiredISOCurrencyId()); Buff.setRequiredISOCode(createdBuff.getRequiredISOCode()); Buff.setRequiredName(createdBuff.getRequiredName()); Buff.setOptionalUnitSymbol(createdBuff.getOptionalUnitSymbol()); 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 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); } }
/** Business logic to execute. */ public final Response executeCommand( Object inputPar, UserSessionParameters userSessionPars, HttpServletRequest request, HttpServletResponse response, HttpSession userSession, ServletContext context) { Connection conn = null; PreparedStatement pstmt = null; try { conn = ConnectionManager.getConnection(context); // fires the GenericEvent.CONNECTION_CREATED event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.CONNECTION_CREATED, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, null)); Object[] pars = (Object[]) inputPar; DetailPurchaseDocRowVO voTemplate = (DetailPurchaseDocRowVO) pars[0]; VariantsMatrixVO matrixVO = (VariantsMatrixVO) pars[1]; Object[][] cells = (Object[][]) pars[2]; BigDecimal currencyDecimals = (BigDecimal) pars[3]; Map attribute2dbField = new HashMap(); attribute2dbField.put("companyCodeSys01DOC07", "COMPANY_CODE_SYS01"); attribute2dbField.put("docTypeDOC07", "DOC_TYPE"); attribute2dbField.put("docYearDOC07", "DOC_YEAR"); attribute2dbField.put("docNumberDOC07", "DOC_NUMBER"); attribute2dbField.put("rowNumberDOC07", "ROW_NUMBER"); attribute2dbField.put("itemCodeItm01DOC07", "ITEM_CODE_ITM01"); attribute2dbField.put("supplierItemCodePur02DOC07", "SUPPLIER_ITEM_CODE_PUR02"); attribute2dbField.put("vatCodeItm01DOC07", "VAT_CODE_ITM01"); attribute2dbField.put("valuePur04DOC07", "VALUE_PUR04"); attribute2dbField.put("valueDOC07", "VALUE"); attribute2dbField.put("qtyDOC07", "QTY"); attribute2dbField.put("discountValueDOC07", "DISCOUNT_VALUE"); attribute2dbField.put("discountPercDOC07", "DISCOUNT_PERC"); attribute2dbField.put("vatValueDOC07", "VAT_VALUE"); attribute2dbField.put("vatDescriptionDOC07", "VAT_DESCRIPTION"); attribute2dbField.put("startDatePur04DOC07", "START_DATE_PUR04"); attribute2dbField.put("endDatePur04DOC07", "END_DATE_PUR04"); attribute2dbField.put("umCodePur02DOC07", "UM_CODE_PUR02"); attribute2dbField.put("decimalsReg02DOC07", "DECIMALS_REG02"); attribute2dbField.put("minPurchaseQtyPur02DOC07", "MIN_PURCHASE_QTY_PUR02"); attribute2dbField.put("multipleQtyPur02DOC07", "MULTIPLE_QTY_PUR02"); attribute2dbField.put("valueReg01DOC07", "VALUE_REG01"); attribute2dbField.put("deductibleReg01DOC07", "DEDUCTIBLE_REG01"); attribute2dbField.put("taxableIncomeDOC07", "TAXABLE_INCOME"); attribute2dbField.put("progressiveHie02DOC07", "PROGRESSIVE_HIE02"); attribute2dbField.put("deliveryDateDOC07", "DELIVERY_DATE"); attribute2dbField.put("inQtyDOC07", "IN_QTY"); attribute2dbField.put("orderQtyDOC07", "ORDER_QTY"); attribute2dbField.put("invoiceQtyDOC07", "INVOICE_QTY"); attribute2dbField.put("variantTypeItm06DOC07", "VARIANT_TYPE_ITM06"); attribute2dbField.put("variantCodeItm11DOC07", "VARIANT_CODE_ITM11"); attribute2dbField.put("variantTypeItm07DOC07", "VARIANT_TYPE_ITM07"); attribute2dbField.put("variantCodeItm12DOC07", "VARIANT_CODE_ITM12"); attribute2dbField.put("variantTypeItm08DOC07", "VARIANT_TYPE_ITM08"); attribute2dbField.put("variantCodeItm13DOC07", "VARIANT_CODE_ITM13"); attribute2dbField.put("variantTypeItm09DOC07", "VARIANT_TYPE_ITM09"); attribute2dbField.put("variantCodeItm14DOC07", "VARIANT_CODE_ITM14"); attribute2dbField.put("variantTypeItm10DOC07", "VARIANT_TYPE_ITM10"); attribute2dbField.put("variantCodeItm15DOC07", "VARIANT_CODE_ITM15"); DetailPurchaseDocRowVO vo = null; VariantsMatrixColumnVO colVO = null; VariantsMatrixRowVO rowVO = null; Response res = null; for (int i = 0; i < cells.length; i++) { rowVO = (VariantsMatrixRowVO) matrixVO.getRowDescriptors().get(i); if (matrixVO.getColumnDescriptors().size() == 0) { if (cells[i][0] != null) { vo = (DetailPurchaseDocRowVO) voTemplate.clone(); if (!containsVariant(matrixVO, "ITM11_VARIANTS_1")) { // e.g. color but not no size... vo.setVariantCodeItm11DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm06DOC07(ApplicationConsts.JOLLY); } else { vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06()); } if (!containsVariant(matrixVO, "ITM12_VARIANTS_2")) { vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY); } else { vo.setVariantCodeItm12DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm07DOC07(rowVO.getVariantTypeITM06()); } if (!containsVariant(matrixVO, "ITM13_VARIANTS_3")) { vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY); } else { vo.setVariantCodeItm13DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm08DOC07(rowVO.getVariantTypeITM06()); } if (!containsVariant(matrixVO, "ITM14_VARIANTS_4")) { vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY); } else { vo.setVariantCodeItm14DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm09DOC07(rowVO.getVariantTypeITM06()); } if (!containsVariant(matrixVO, "ITM15_VARIANTS_5")) { vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY); } else { vo.setVariantCodeItm15DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm10DOC07(rowVO.getVariantTypeITM06()); } vo.setQtyDOC07((BigDecimal) cells[i][0]); PurchaseUtils.updateTotals(vo, currencyDecimals.intValue()); /* vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY); vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY); vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY); vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY); vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY); */ vo.setInQtyDOC07(new BigDecimal(0)); vo.setOrderQtyDOC07(vo.getQtyDOC07()); if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0)); vo.setRowNumberDOC07( progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn)); // insert into DOC07... res = QueryUtil.insertTable( conn, userSessionPars, vo, "DOC07_PURCHASE_ITEMS", attribute2dbField, "Y", "N", context, true); if (res.isError()) { conn.rollback(); return res; } } // end if on not null } else for (int k = 0; k < matrixVO.getColumnDescriptors().size(); k++) { colVO = (VariantsMatrixColumnVO) matrixVO.getColumnDescriptors().get(k); if (cells[i][k] != null) { vo = (DetailPurchaseDocRowVO) voTemplate.clone(); vo.setQtyDOC07((BigDecimal) cells[i][k]); PurchaseUtils.updateTotals(vo, currencyDecimals.intValue()); vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11()); vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06()); vo.setVariantCodeItm12DOC07( colVO.getVariantCodeITM12() == null ? ApplicationConsts.JOLLY : colVO.getVariantCodeITM12()); vo.setVariantCodeItm13DOC07( colVO.getVariantCodeITM13() == null ? ApplicationConsts.JOLLY : colVO.getVariantCodeITM13()); vo.setVariantCodeItm14DOC07( colVO.getVariantCodeITM14() == null ? ApplicationConsts.JOLLY : colVO.getVariantCodeITM14()); vo.setVariantCodeItm15DOC07( colVO.getVariantCodeITM15() == null ? ApplicationConsts.JOLLY : colVO.getVariantCodeITM15()); vo.setVariantTypeItm07DOC07( colVO.getVariantTypeITM07() == null ? ApplicationConsts.JOLLY : colVO.getVariantTypeITM07()); vo.setVariantTypeItm08DOC07( colVO.getVariantTypeITM08() == null ? ApplicationConsts.JOLLY : colVO.getVariantTypeITM08()); vo.setVariantTypeItm09DOC07( colVO.getVariantTypeITM09() == null ? ApplicationConsts.JOLLY : colVO.getVariantTypeITM09()); vo.setVariantTypeItm10DOC07( colVO.getVariantTypeITM10() == null ? ApplicationConsts.JOLLY : colVO.getVariantTypeITM10()); vo.setInQtyDOC07(new BigDecimal(0)); vo.setOrderQtyDOC07(vo.getQtyDOC07()); if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0)); vo.setRowNumberDOC07( progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn)); // insert into DOC07... res = QueryUtil.insertTable( conn, userSessionPars, vo, "DOC07_PURCHASE_ITEMS", attribute2dbField, "Y", "N", context, true); if (res.isError()) { conn.rollback(); return res; } } // end if on not null } // end inner for } // end outer for // recalculate totals... PurchaseDocPK pk = new PurchaseDocPK( vo.getCompanyCodeSys01DOC07(), vo.getDocTypeDOC07(), vo.getDocYearDOC07(), vo.getDocNumberDOC07()); Response docResponse = docBean.loadPurchaseDoc( conn, pk, userSessionPars, request, response, userSession, context); if (docResponse.isError()) { conn.rollback(); return docResponse; } DetailPurchaseDocVO docVO = (DetailPurchaseDocVO) ((VOResponse) docResponse).getVo(); Response totalResponse = totalBean.calcDocTotals( conn, docVO, userSessionPars, request, response, userSession, context); if (totalResponse.isError()) { conn.rollback(); return totalResponse; } pstmt = conn.prepareStatement( "update DOC06_PURCHASE set TAXABLE_INCOME=?,TOTAL_VAT=?,TOTAL=?,DOC_STATE=? where COMPANY_CODE_SYS01=? and DOC_TYPE=? and DOC_YEAR=? and DOC_NUMBER=?"); pstmt.setBigDecimal(1, docVO.getTaxableIncomeDOC06()); pstmt.setBigDecimal(2, docVO.getTotalVatDOC06()); pstmt.setBigDecimal(3, docVO.getTotalDOC06()); pstmt.setString(4, ApplicationConsts.HEADER_BLOCKED); pstmt.setString(5, vo.getCompanyCodeSys01DOC07()); pstmt.setString(6, vo.getDocTypeDOC07()); pstmt.setBigDecimal(7, vo.getDocYearDOC07()); pstmt.setBigDecimal(8, vo.getDocNumberDOC07()); pstmt.execute(); Response answer = new VOResponse(vo); // fires the GenericEvent.BEFORE_COMMIT event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.BEFORE_COMMIT, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, answer)); conn.commit(); // fires the GenericEvent.AFTER_COMMIT event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.AFTER_COMMIT, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, answer)); return answer; } catch (Throwable ex) { Logger.error( userSessionPars.getUsername(), this.getClass().getName(), "executeCommand", "Error while inserting new purchase order rows", ex); try { conn.rollback(); } catch (Exception ex3) { } return new ErrorResponse(ex.getMessage()); } finally { try { pstmt.close(); } catch (Exception ex2) { } try { ConnectionManager.releaseConnection(conn, context); } catch (Exception ex1) { } } }
public void updateISOCountry( CFSecurityAuthorization Authorization, CFSecurityISOCountryBuff Buff) { final String S_ProcName = "updateISOCountry"; ResultSet resultSet = null; try { short Id = Buff.getRequiredId(); String ISOCode = Buff.getRequiredISOCode(); String Name = Buff.getRequiredName(); int Revision = Buff.getRequiredRevision(); Connection cnx = schema.getCnx(); String sql = "exec sp_update_iso_cntry ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"; 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++, "ISOC"); stmtUpdateByPKey.setShort(argIdx++, Id); stmtUpdateByPKey.setString(argIdx++, ISOCode); stmtUpdateByPKey.setString(argIdx++, Name); 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()) { CFSecurityISOCountryBuff updatedBuff = unpackISOCountryResultSetToBuff(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.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; } } }
/** Business logic to execute. */ public final Response executeCommand( Object inputPar, UserSessionParameters userSessionPars, HttpServletRequest request, HttpServletResponse response, HttpSession userSession, ServletContext context) { String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(); Connection conn = null; PreparedStatement pstmt = null; try { conn = ConnectionManager.getConnection(context); // fires the GenericEvent.CONNECTION_CREATED event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.CONNECTION_CREATED, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, null)); ArrayList oldVOs = ((ArrayList[]) inputPar)[0]; ArrayList newVOs = ((ArrayList[]) inputPar)[1]; RoleFunctionCompanyVO oldVO = null; RoleFunctionCompanyVO newVO = null; Response res = null; for (int i = 0; i < oldVOs.size(); i++) { oldVO = (RoleFunctionCompanyVO) oldVOs.get(i); newVO = (RoleFunctionCompanyVO) newVOs.get(i); if (!oldVO.getCanView().booleanValue()) { // no record in SYS02 yet... if (newVO.getCanView().booleanValue()) { pstmt = conn.prepareStatement( "insert into SYS02_COMPANIES_ACCESS(PROGRESSIVE_SYS04,FUNCTION_CODE_SYS06,CAN_INS,CAN_UPD,CAN_DEL,COMPANY_CODE_SYS01) " + "values(?,?,?,?,?,?)"); pstmt.setBigDecimal(1, newVO.getProgressiveSys04SYS02()); pstmt.setString(2, newVO.getFunctionCodeSys06SYS02()); pstmt.setString(3, newVO.getCanInsSYS02().booleanValue() ? "Y" : "N"); pstmt.setString(4, newVO.getCanUpdSYS02().booleanValue() ? "Y" : "N"); pstmt.setString(5, newVO.getCanDelSYS02().booleanValue() ? "Y" : "N"); pstmt.setString(6, newVO.getCompanyCodeSys01SYS02()); pstmt.execute(); } } else { // record already exists in SYS02... if (newVO.getCanView().booleanValue()) { // record in SYS02 will be updated... pstmt = conn.prepareStatement( "update SYS02_COMPANIES_ACCESS set CAN_INS=?,CAN_UPD=?,CAN_DEL=? where " + "PROGRESSIVE_SYS04=? and FUNCTION_CODE_SYS06=? and COMPANY_CODE_SYS01=? "); pstmt.setString(1, newVO.getCanInsSYS02().booleanValue() ? "Y" : "N"); pstmt.setString(2, newVO.getCanUpdSYS02().booleanValue() ? "Y" : "N"); pstmt.setString(3, newVO.getCanDelSYS02().booleanValue() ? "Y" : "N"); pstmt.setBigDecimal(4, newVO.getProgressiveSys04SYS02()); pstmt.setString(5, newVO.getFunctionCodeSys06SYS02()); pstmt.setString(6, newVO.getCompanyCodeSys01SYS02()); pstmt.execute(); } else { // delete record from SYS02... pstmt = conn.prepareStatement( "delete from SYS02_COMPANIES_ACCESS where PROGRESSIVE_SYS04=? and FUNCTION_CODE_SYS06=? and COMPANY_CODE_SYS01=?"); pstmt.setBigDecimal(1, newVO.getProgressiveSys04SYS02()); pstmt.setString(2, newVO.getFunctionCodeSys06SYS02()); pstmt.setString(3, newVO.getCompanyCodeSys01SYS02()); pstmt.execute(); } } } Response answer = new VOListResponse(newVOs, false, newVOs.size()); // fires the GenericEvent.BEFORE_COMMIT event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.BEFORE_COMMIT, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, answer)); conn.commit(); // fires the GenericEvent.AFTER_COMMIT event... EventsManager.getInstance() .processEvent( new GenericEvent( this, getRequestName(), GenericEvent.AFTER_COMMIT, (JAIOUserSessionParameters) userSessionPars, request, response, userSession, context, conn, inputPar, answer)); return answer; } catch (Throwable ex) { Logger.error( userSessionPars.getUsername(), this.getClass().getName(), "executeCommand", "Error while updating company-role-function settings", ex); try { pstmt.close(); } catch (Exception ex2) { } try { conn.rollback(); } catch (Exception ex3) { } return new ErrorResponse(ex.getMessage()); } finally { try { ConnectionManager.releaseConnection(conn, context); } catch (Exception ex1) { } } }
public void createHostNode(CFSecurityAuthorization Authorization, CFSecurityHostNodeBuff Buff) { final String S_ProcName = "createHostNode"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { long ClusterId = Buff.getRequiredClusterId(); String Description = Buff.getRequiredDescription(); String HostName = Buff.getRequiredHostName(); Connection cnx = schema.getCnx(); String sql = "exec sp_create_hostnode ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?"; 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++, "HSND"); stmtCreateByPKey.setLong(argIdx++, ClusterId); stmtCreateByPKey.setString(argIdx++, Description); stmtCreateByPKey.setString(argIdx++, HostName); 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()) { CFSecurityHostNodeBuff createdBuff = unpackHostNodeResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredClusterId(createdBuff.getRequiredClusterId()); Buff.setRequiredHostNodeId(createdBuff.getRequiredHostNodeId()); Buff.setRequiredDescription(createdBuff.getRequiredDescription()); Buff.setRequiredHostName(createdBuff.getRequiredHostName()); 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 runReplicate() throws InterruptedException { if (verbose) log.append("Started at " + Calendar.getInstance().getTime() + "\r\n"); Connection connectionFrom = null; Connection connectionTo = null; try { connectionFrom = providerI.getConnection(dbFrom); connectionTo = providerI.getConnection(dbTo); PreparedStatement preparedStatementFrom = null; PreparedStatement preparedStatementTo = null; ResultSet resultSet = null; for (Iterator<HashMap<String, String>> HashMapIterator = tables.iterator(); HashMapIterator.hasNext(); ) { try { HashMap<String, String> h = HashMapIterator.next(); String name = h.get("name"); String name2 = h.get("name2"); String where1 = h.get("where1"); String where2 = h.get("where2"); if (verbose) log.append("Replicating " + dbFrom + "/" + name + " to " + dbTo + "/" + name2 + "\r\n"); preparedStatementFrom = connectionFrom.prepareStatement("select * from " + name + " WHERE 1=1 " + where1); boolean b = preparedStatementFrom.execute(); HashMap meta = new HashMap(); // 1. find all ids from DB1 ArrayList db1 = new ArrayList(); String nullMark = "____NULL"; if (b) { resultSet = preparedStatementFrom.getResultSet(); ResultSetMetaData setMetaData = resultSet.getMetaData(); for (int i = 1; i <= setMetaData.getColumnCount(); i++) { meta.put( setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i)); } while (resultSet.next()) { HashMap results = new HashMap(); for (int i = 1; i <= setMetaData.getColumnCount(); i++) { String columnName = setMetaData.getColumnLabel(i).toLowerCase(); Object o = resultSet.getObject(i); if (o != null) { results.put(columnName, o); } else { results.put(columnName + nullMark, nullMark); } } db1.add(results); } resultSet.close(); } else { log.append("Couldn't execute select from " + dbFrom + "/" + name + " /r/n"); return; } StringBuffer allIds = new StringBuffer(); HashMap records1 = new HashMap(); HashMap toInsert = new HashMap(); HashMap toUpdate = new HashMap(); for (Iterator iterator = db1.iterator(); iterator.hasNext(); ) { HashMap record = (HashMap) iterator.next(); if (allIds.length() != 0) { allIds.append(","); } allIds.append(record.get("id")); records1.put(record.get("id"), record); } toInsert.putAll(records1); // 2. find all ids to delete in DB2; if (allIds.length() > 0) { preparedStatementTo = connectionTo.prepareStatement( "delete from " + name2 + " where id not in (" + allIds.toString() + ")" + where2); if (verbose) log.append( "deleted from " + dbTo + "/" + name2 + " " + preparedStatementTo.executeUpdate() + " records;\r\n"); } else { if (verbose) log.append( "No records in " + dbFrom + "/" + name + ", nothing to delete in " + dbTo + "/" + name2 + " ;\r\n"); } // 3. find all ids from DB2; preparedStatementTo = connectionTo.prepareStatement("select * from " + name2 + " WHERE 1=1 " + where2); b = preparedStatementTo.execute(); HashMap meta2 = new HashMap(); ArrayList db2 = new ArrayList(); if (b) { resultSet = preparedStatementTo.getResultSet(); ResultSetMetaData setMetaData = resultSet.getMetaData(); for (int i = 1; i <= setMetaData.getColumnCount(); i++) { meta2.put( setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i)); } while (resultSet.next()) { HashMap results = new HashMap(); for (int i = 1; i <= setMetaData.getColumnCount(); i++) { String columnName = setMetaData.getColumnLabel(i).toLowerCase(); Object o = resultSet.getObject(i); if (o != null) { results.put(columnName, o); } else { results.put(columnName + nullMark, nullMark); } } db2.add(results); } } else { log.append("Couldn't execute select from " + dbTo + "/" + name2 + " /r/n"); return; } // compare meta-data; { HashMap temp = new HashMap(); { temp.putAll(meta); Set set = meta2.keySet(); for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) { Object o = iteratorSet.next(); if (meta.containsKey(o)) { if (meta.get(o).equals(meta2.get(o))) { temp.remove(o); } } else { log.append("ERROR: Meta data not equals! \r\n"); log.append(o + "\t" + temp.get(o) + " not present in table " + name + "\r\n"); } } } if (!temp.isEmpty()) { log.append("ERROR: Meta data not equals! \r\n"); Set set = temp.keySet(); for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) { Object o = iteratorSet.next(); log.append(o + "\t" + temp.get(o) + " != " + meta2.get(o) + "\r\n"); } return; } } for (Iterator iterator = db2.iterator(); iterator.hasNext(); ) { HashMap db2Record = (HashMap) iterator.next(); if (toInsert.containsKey(db2Record.get("id"))) { HashMap db1Record = (HashMap) toInsert.get(db2Record.get("id")); boolean equal = true; Set set = meta2.keySet(); for (Iterator iteratorSet = set.iterator(); equal && iteratorSet.hasNext(); ) { String columnName2 = (String) iteratorSet.next(); if (db2Record.containsKey(columnName2 + nullMark) || db1Record.containsKey(columnName2 + nullMark)) { equal = db2Record.containsKey(columnName2 + nullMark) && db1Record.containsKey(columnName2 + nullMark); } else { // checking not-null; equal = equalRecords(db1Record.get(columnName2), db2Record.get(columnName2)); } } if (!equal) { toUpdate.put(db2Record.get("id"), toInsert.get(db2Record.get("id"))); } toInsert.remove(db2Record.get("id")); } else { // this case shouldn't happen at all, since we've deleted all such records } } log.append( "Found " + toUpdate.size() + " to update, and " + toInsert.size() + " to insert.\r\n"); int totalUpdated = 0; // 4. calculate all to update in DB2 if (!toUpdate.isEmpty()) { Set set = toUpdate.keySet(); for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) { StringBuffer sql = new StringBuffer(); Object id = iteratorSet.next(); HashMap r = (HashMap) toUpdate.get(id); sql.append("UPDATE " + name2 + " SET "); StringBuffer values = new StringBuffer(); Set en = meta2.keySet(); for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) { Object o = iteratorSetEn.next(); if (!o.equals("id")) { if (values.length() != 0) { values.append(","); } Object quote = dbQuotes.get(dbToType); if (quote == null) { quote = ""; } values.append(quote).append(o).append(quote); values.append(" = ? "); } } values.append(" WHERE id = '" + r.get("id") + "';"); PreparedStatement statement = connectionTo.prepareStatement(sql.toString() + values.toString()); en = meta2.keySet(); int i = 0; for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) { Object o = iteratorSetEn.next(); if (!o.equals("id")) { i++; statement.setObject(i, r.get(o)); } } try { totalUpdated += statement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); log.append("Error occured: " + e + "\r\n"); } } } if (verbose) log.append("Updated " + totalUpdated + " records.\r\n"); // 4. calculate all to insert to DB2 if (!toInsert.isEmpty()) { StringBuffer header = new StringBuffer(); if (header.length() == 0) { header.append(" INSERT INTO " + name2 + " ("); StringBuffer columns = new StringBuffer(); Set en = meta2.keySet(); for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) { Object o = iteratorSetEn.next(); if (columns.length() != 0) { columns.append(","); } Object quote = dbQuotes.get(dbToType); if (quote == null) { quote = ""; } columns.append(quote).append(o).append(quote); } header.append(columns.toString()); header.append(") VALUES "); } Set enumeration = toInsert.keySet(); for (Iterator iteratorSetX = enumeration.iterator(); iteratorSetX.hasNext(); ) { Object id = iteratorSetX.next(); HashMap r = (HashMap) toInsert.get(id); StringBuffer values = new StringBuffer(); if (values.length() != 0) { values.append(","); } values.append("("); StringBuffer columns = new StringBuffer(); Set en = meta2.keySet(); for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) { Object o = iteratorSetEn.next(); if (columns.length() != 0) { columns.append(","); } columns.append(" ? "); } values.append(columns.toString()); values.append(");"); PreparedStatement statement = connectionTo.prepareStatement(header.toString() + values.toString()); en = meta2.keySet(); int i = 0; for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) { Object o = iteratorSetEn.next(); i++; statement.setObject(i, r.get(o)); } statement.execute(); } } if (verbose) log.append("Replication finished OK.\r\n"); } catch (Exception e) { log.append("Some error occured: " + e + "\r\n"); log.append(e.getMessage() + "\r\n"); e.printStackTrace(); } } } catch (Exception e) { log.append("Error with query = " + e); log.append(e.getMessage()); return; } finally { try { if (connectionFrom != null && !connectionFrom.isClosed()) { connectionFrom.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (connectionTo != null && !connectionTo.isClosed()) { connectionTo.close(); } } catch (SQLException e) { e.printStackTrace(); } } if (verbose) log.append("Ended at " + Calendar.getInstance().getTime()); }
public CFSecurityISOCountryBuff lockBuff( CFSecurityAuthorization Authorization, CFSecurityISOCountryPKey PKey) { final String S_ProcName = "lockBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); short Id = PKey.getRequiredId(); String sql = "{ call sp_lock_iso_cntry( ?, ?, ?, ?, ?" + ", " + "?" + " ) }"; if (stmtLockBuffByPKey == null) { stmtLockBuffByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtLockBuffByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtLockBuffByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtLockBuffByPKey.setShort(argIdx++, Id); stmtLockBuffByPKey.execute(); boolean moreResults = true; resultSet = null; while (resultSet == null) { try { moreResults = stmtLockBuffByPKey.getMoreResults(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } if (moreResults) { try { resultSet = stmtLockBuffByPKey.getResultSet(); } catch (SQLException e) { } } else if (-1 == stmtLockBuffByPKey.getUpdateCount()) { break; } } if ((resultSet != null) && resultSet.next()) { CFSecurityISOCountryBuff buff = unpackISOCountryResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public MutationState addColumn(AddColumnStatement statement) throws SQLException { connection.rollback(); boolean wasAutoCommit = connection.getAutoCommit(); try { connection.setAutoCommit(false); TableName tableNameNode = statement.getTableName(); String schemaName = tableNameNode.getSchemaName(); String tableName = tableNameNode.getTableName(); PTable table = getLatestTable(schemaName, tableName); PSchema schema = connection.getPMetaData().getSchema(schemaName); boolean retried = false; while (true) { int ordinalPosition = table.getColumns().size(); // TODO: disallow adding columns if last column is fixed width and nullable List<PColumn> columns = Lists.newArrayListWithExpectedSize(1); ColumnDef colDef = statement.getColumnDef(); if (!colDef.isNull() && colDef.isPK()) { throw new SQLExceptionInfo.Builder(SQLExceptionCode.NOT_NULLABLE_COLUMN_IN_ROW_KEY) .setColumnName(colDef.getColumnDefName().getColumnName().getName()) .build() .buildException(); } PreparedStatement colUpsert = connection.prepareStatement(INSERT_COLUMN); Pair<byte[], Map<String, Object>> family = null; PColumn column = newColumn(ordinalPosition++, colDef, null); addColumnMutation(schemaName, tableName, column, colUpsert); columns.add(column); if (column.getFamilyName() != null) { family = new Pair<byte[], Map<String, Object>>( column.getFamilyName().getBytes(), statement.getProps()); } final long seqNum = table.getSequenceNumber() + 1; PreparedStatement tableUpsert = connection.prepareStatement(MUTATE_TABLE); tableUpsert.setString(1, schemaName); tableUpsert.setString(2, tableName); tableUpsert.setString(3, table.getType().getSerializedValue()); tableUpsert.setLong(4, seqNum); tableUpsert.setInt(5, ordinalPosition); tableUpsert.execute(); final List<Mutation> tableMetaData = connection.getMutationState().toMutations(); connection.rollback(); byte[] emptyCF = null; if (table.getType() != PTableType.VIEW && family != null && table.getColumnFamilies().isEmpty()) { emptyCF = family.getFirst(); } MetaDataMutationResult result = connection .getQueryServices() .addColumn(tableMetaData, table.getType() == PTableType.VIEW, family); try { MutationCode code = processMutationResult(schemaName, tableName, result); if (code == MutationCode.COLUMN_ALREADY_EXISTS) { connection.addTable(schemaName, result.getTable()); if (!statement.ifNotExists()) { throw new ColumnAlreadyExistsException( schemaName, tableName, SchemaUtil.findExistingColumn(result.getTable(), columns)); } return new MutationState(0, connection); } connection.addColumn(schemaName, tableName, columns, seqNum, result.getMutationTime()); if (emptyCF != null) { Long scn = connection.getSCN(); connection.setAutoCommit(true); // Delete everything in the column. You'll still be able to do queries at earlier // timestamps long ts = (scn == null ? result.getMutationTime() : scn); TableRef tableRef = new TableRef(null, table, schema, ts); MutationPlan plan = new PostDDLCompiler(connection).compile(tableRef, emptyCF, null, ts); return connection.getQueryServices().updateData(plan); } return new MutationState(0, connection); } catch (ConcurrentTableMutationException e) { if (retried) { throw e; } table = connection.getPMetaData().getSchema(schemaName).getTable(tableName); retried = true; } } } finally { connection.setAutoCommit(wasAutoCommit); } }
/* * Builds sensors table from list of sensors currently loaded in the system * */ public static boolean buildGeoIndex() { boolean success = true; sensors = new Vector<String>(); coordinates = new Vector<Point>(); getListOfSensors(); Properties properties = loadProperties(); if (properties != null) { try { dburl = properties.getProperty("dburl"); dbuser = properties.getProperty("dbuser"); dbpass = properties.getProperty("dbpass"); Connection conn = connect(dburl, dbuser, dbpass); // ((org.postgresql.PGConnection) conn).addDataType("geometry", "org.postgis.PGgeometry"); // ((org.postgresql.PGConnection) conn).addDataType("box3d", "org.postgis.PGbox3d"); String st_create_table = "DROP INDEX IF EXISTS gist_sensors;" + " DROP TABLE IF EXISTS sensors;" + " CREATE TABLE sensors ( \"name\" character(255) NOT NULL, \"location\" geometry NOT NULL );" + " CREATE INDEX gist_sensors ON sensors USING GIST ( location ); "; logger.warn("Running query: " + st_create_table); PreparedStatement prepareStatement = conn.prepareStatement(st_create_table); prepareStatement.execute(); prepareStatement.close(); for (int i = 0; i < coordinates.size(); i++) { String insert = "insert into sensors values ( '" + sensors.get(i) + "', ST_MakePoint(" + coordinates.get(i).getX() + " , " + coordinates.get(i).getY() + " , " + coordinates.get(i).getZ() + ") );"; PreparedStatement ps = conn.prepareStatement(insert); ps.execute(); ps.close(); logger.warn(insert); } Statement s = conn.createStatement(); ResultSet r = s.executeQuery("select location, name from sensors"); while (r.next()) { PGgeometry geom = (PGgeometry) r.getObject(1); String name = r.getString(2); logger.warn("Geometry " + geom.toString() + " : " + name); } s.close(); conn.close(); } catch (SQLException e) { logger.warn(e.getMessage(), e); success = false; } catch (ClassNotFoundException e) { logger.warn(e.getMessage(), e); success = false; } } else { logger.warn("Couldn't load properties files for PostGIS"); success = false; } return success; }
public MutationState dropColumn(DropColumnStatement statement) throws SQLException { connection.rollback(); boolean wasAutoCommit = connection.getAutoCommit(); try { connection.setAutoCommit(false); TableName tableNameNode = statement.getTableName(); String schemaName = tableNameNode.getSchemaName(); String tableName = tableNameNode.getTableName(); PTable table = getLatestTable(schemaName, tableName); // TODO: Do in resolver? boolean retried = false; while (true) { final ColumnResolver resolver = FromCompiler.getResolver(statement, connection); ColumnRef columnRef = null; try { columnRef = resolver.resolveColumn((ColumnParseNode) statement.getColumnRef()); } catch (ColumnNotFoundException e) { if (statement.ifExists()) { return new MutationState(0, connection); } throw e; } TableRef tableRef = columnRef.getTableRef(); PColumn columnToDrop = columnRef.getColumn(); if (SchemaUtil.isPKColumn(columnToDrop)) { throw new SQLExceptionInfo.Builder(SQLExceptionCode.CANNOT_DROP_PK) .setColumnName(columnToDrop.getName().getString()) .build() .buildException(); } int columnCount = table.getColumns().size() - 1; String familyName = null; List<String> binds = Lists.newArrayListWithExpectedSize(4); StringBuilder buf = new StringBuilder( "DELETE FROM " + TYPE_SCHEMA + ".\"" + TYPE_TABLE + "\" WHERE " + TABLE_SCHEM_NAME); if (schemaName == null || schemaName.length() == 0) { buf.append(" IS NULL AND "); } else { buf.append(" = ? AND "); binds.add(schemaName); } buf.append(TABLE_NAME_NAME + " = ? AND " + COLUMN_NAME + " = ? AND " + TABLE_CAT_NAME); binds.add(tableName); binds.add(columnToDrop.getName().getString()); if (columnToDrop.getFamilyName() == null) { buf.append(" IS NULL"); } else { buf.append(" = ?"); binds.add(familyName = columnToDrop.getFamilyName().getString()); } PreparedStatement colDelete = connection.prepareStatement(buf.toString()); for (int i = 0; i < binds.size(); i++) { colDelete.setString(i + 1, binds.get(i)); } colDelete.execute(); PreparedStatement colUpdate = connection.prepareStatement(UPDATE_COLUMN_POSITION); colUpdate.setString(1, schemaName); colUpdate.setString(2, tableName); for (int i = columnToDrop.getPosition() + 1; i < table.getColumns().size(); i++) { PColumn column = table.getColumns().get(i); colUpdate.setString(3, column.getName().getString()); colUpdate.setString( 4, column.getFamilyName() == null ? null : column.getFamilyName().getString()); colUpdate.setInt(5, i); colUpdate.execute(); } final long seqNum = table.getSequenceNumber() + 1; PreparedStatement tableUpsert = connection.prepareStatement(MUTATE_TABLE); tableUpsert.setString(1, schemaName); tableUpsert.setString(2, tableName); tableUpsert.setString(3, table.getType().getSerializedValue()); tableUpsert.setLong(4, seqNum); tableUpsert.setInt(5, columnCount); tableUpsert.execute(); final List<Mutation> tableMetaData = connection.getMutationState().toMutations(); connection.rollback(); // If we're dropping the last KV colum, we have to pass an indication along to the // dropColumn call // to populate a new empty KV column byte[] emptyCF = null; if (table.getType() != PTableType.VIEW && !SchemaUtil.isPKColumn(columnToDrop) && table.getColumnFamilies().get(0).getName().equals(columnToDrop.getFamilyName()) && table.getColumnFamilies().get(0).getColumns().size() == 1) { emptyCF = SchemaUtil.getEmptyColumnFamily( table.getColumnFamilies().subList(1, table.getColumnFamilies().size())); } MetaDataMutationResult result = connection .getQueryServices() .dropColumn( tableMetaData, emptyCF != null && Bytes.compareTo(emptyCF, QueryConstants.EMPTY_COLUMN_BYTES) == 0 ? emptyCF : null); try { MutationCode code = processMutationResult(schemaName, tableName, result); if (code == MutationCode.COLUMN_NOT_FOUND) { connection.addTable(schemaName, result.getTable()); if (!statement.ifExists()) { throw new ColumnNotFoundException( schemaName, tableName, familyName, columnToDrop.getName().getString()); } return new MutationState(0, connection); } connection.removeColumn( schemaName, tableName, familyName, columnToDrop.getName().getString(), seqNum, result.getMutationTime()); // If we have a VIEW, then only delete the metadata, and leave the table data alone if (table.getType() != PTableType.VIEW) { connection.setAutoCommit(true); Long scn = connection.getSCN(); // Delete everything in the column. You'll still be able to do queries at earlier // timestamps long ts = (scn == null ? result.getMutationTime() : scn); MutationPlan plan = new PostDDLCompiler(connection) .compile(tableRef, emptyCF, Collections.singletonList(columnToDrop), ts); return connection.getQueryServices().updateData(plan); } return new MutationState(0, connection); } catch (ConcurrentTableMutationException e) { if (retried) { throw e; } table = connection.getPMetaData().getSchema(schemaName).getTable(tableName); retried = true; } } } finally { connection.setAutoCommit(wasAutoCommit); } }