/** * Reads the result of an SQL query into an ArrayList * * @param sql The SQL to launch * @param limit <=0 means unlimited, otherwise this specifies the maximum number of rows read. * @return An ArrayList of rows. * @throws KettleDatabaseException if something goes wrong. */ public List<Object[]> getRows( String sql, int limit ) throws KettleDatabaseException { return getRows( sql, limit, null ); }
/** * Reads the result of an SQL query into an ArrayList * * @param sql The SQL to launch * @param limit <=0 means unlimited, otherwise this specifies the maximum number of rows read. * @param monitor The progress monitor to update while getting the rows. * @return An ArrayList of rows. * @throws KettleDatabaseException if something goes wrong. */ public List<Object[]> getRows( String sql, int limit, ProgressMonitorListener monitor ) throws KettleDatabaseException { return getRows( sql, null, null, ResultSet.FETCH_FORWARD, false, limit, monitor ); }
public List<Object[]> getRows( String sql, int limit ) throws KettleDatabaseException { return callRead( () -> database.getRows( sql, limit ) ); }
public Map<String, LongObjectId> getValueToIdMap( String tablename, String idfield, String lookupfield ) throws KettleException { String sql = new StringBuilder( "SELECT " ).append( lookupfield ).append( ", " ).append( idfield ) .append( " FROM " ).append( tablename ).toString(); Map<String, LongObjectId> result = new HashMap<String, LongObjectId>(); for ( Object[] row : callRead( () -> database.getRows( sql, new RowMeta(), new Object[] {}, ResultSet.FETCH_FORWARD, false, -1, null ) ) ) { result.put( String.valueOf( row[ 0 ] ), new LongObjectId( ( (Number) row[ 1 ] ).longValue() ) ); } return result; }
/** * Return all sequence names from connection * * @return The sequences name list. * @throws KettleDatabaseException */ public String[] getSequences() throws KettleDatabaseException { if ( databaseMeta.supportsSequences() ) { String sql = databaseMeta.getSQLListOfSequences(); if ( sql != null ) { List<Object[]> seqs = getRows( sql, 0 ); String[] str = new String[ seqs.size() ]; for ( int i = 0; i < seqs.size(); i++ ) { str[ i ] = seqs.get( i )[ 0 ].toString(); } return str; } } else { throw new KettleDatabaseException( "Sequences are only available for Oracle databases." ); } return null; }
/** * Reads the result of an SQL query into an ArrayList. * * @param sql The SQL to launch * @param params The types of any parameters to be passed to the query * @param data The values of any parameters to be passed to the query * @param fetch_mode The fetch mode for the query (ResultSet.FETCH_FORWARD, e.g.) * @param lazyConversion Whether to perform lazy conversion of the values * @param limit <=0 means unlimited, otherwise this specifies the maximum number of rows read. * @param monitor The progress monitor to update while getting the rows. * @return An ArrayList of rows. * @throws KettleDatabaseException if something goes wrong. */ public List<Object[]> getRows( String sql, RowMetaInterface params, Object[] data, int fetch_mode, boolean lazyConversion, int limit, ProgressMonitorListener monitor ) throws KettleDatabaseException { if ( monitor != null ) { monitor.setTaskName( "Opening query..." ); } ResultSet rset = openQuery( sql, params, data, fetch_mode, lazyConversion ); return getRows( rset, limit, monitor ); }
@Override public String[] call() throws Exception { ResultSet resultSet = database.openQuery( ps, parameterMeta, parameterData ); List<Object[]> rows = database.getRows( resultSet, 0, null ); if ( Utils.isEmpty( rows ) ) { return new String[ 0 ]; } // assemble the result // RowMetaInterface rowMeta = database.getReturnRowMeta(); String[] strings = new String[ rows.size() ]; for ( int i = 0; i < strings.length; i++ ) { Object[] row = rows.get( i ); strings[ i ] = rowMeta.getString( row, 0 ); } return strings; } } );
@Override public ObjectId[] call() throws Exception { ResultSet resultSet = database.openQuery( ps, parameterMeta, parameterData ); List<Object[]> rows = database.getRows( resultSet, 0, null ); if ( Utils.isEmpty( rows ) ) { return new ObjectId[ 0 ]; } RowMetaInterface rowMeta = database.getReturnRowMeta(); ObjectId[] ids = new ObjectId[ rows.size() ]; for ( int i = 0; i < ids.length; i++ ) { Object[] row = rows.get( i ); ids[ i ] = new LongObjectId( rowMeta.getInteger( row, 0 ) ); } return ids; } } );
public LongObjectId[] getIDsWithValues( String tablename, String idfield, String lookupfield, String[] values ) throws KettleException { String sql = createIdsWithValuesQuery( tablename, idfield, lookupfield, values.length ); RowMeta params = new RowMeta(); for ( int i = 0; i < values.length; i++ ) { ValueMetaInterface value = new ValueMetaString( Integer.toString( i ) ); params.addValueMeta( value ); } List<Object[]> rows = callRead( () -> database.getRows( sql, params, values, ResultSet.FETCH_FORWARD, false, -1, null ) ); LongObjectId[] result = new LongObjectId[ rows.size() ]; int i = 0; for ( Object[] row : rows ) { result[ i++ ] = new LongObjectId( ( (Number) row[ 0 ] ).longValue() ); } return result; }
private Database mockDatabase() throws KettleDatabaseException { Database databaseMock = mock( Database.class ); RowMeta databaseRowMeta = new RowMeta(); databaseRowMeta.addValueMeta( new ValueMetaString( "id" ) ); databaseRowMeta.addValueMeta( new ValueMetaString( "value" ) ); doReturn( databaseRowMeta ).when( databaseMock ).getTableFields( anyString() ); doReturn( databaseRowMeta ).when( databaseMock ).getTableFieldsMeta( anyString(), anyString() ); doReturn( Arrays.asList( new Object[][] { { "1", "value" } } ) ).when( databaseMock ).getRows( anyString(), anyInt() ); doReturn( databaseRowMeta ).when( databaseMock ).getReturnRowMeta(); return databaseMock; }
@Test public void testGetValueToIdMap() throws KettleException { String tablename = "test-tablename"; String idfield = "test-idfield"; String lookupfield = "test-lookupfield"; List<Object[]> rows = new ArrayList<Object[]>(); int id = 1234; LongObjectId longObjectId = new LongObjectId( id ); rows.add( new Object[] { lookupfield, id } ); when( database.getRows( eq( "SELECT " + lookupfield + ", " + idfield + " FROM " + tablename ), any( RowMetaInterface.class ), eq( new Object[] {} ), eq( ResultSet.FETCH_FORWARD ), eq( false ), eq( -1 ), eq( (ProgressMonitorListener) null ) ) ).thenReturn( rows ); Map<String, LongObjectId> valueToIdMap = kettleDatabaseRepositoryConnectionDelegate.getValueToIdMap( tablename, idfield, lookupfield ); assertEquals( 1, valueToIdMap.size() ); assertEquals( longObjectId, valueToIdMap.get( lookupfield ) ); } }
@Override public Collection<RowMetaAndData> call() throws Exception { ResultSet resultSet = null; try { resultSet = database.openQuery( ps, parameterMeta, parameterData ); List<Object[]> rows = database.getRows( resultSet, 0, null ); for ( Object[] row : rows ) { RowMetaAndData rowWithMeta = new RowMetaAndData( database.getReturnRowMeta(), row ); long id = rowWithMeta.getInteger( quote( KettleDatabaseRepository.FIELD_DATABASE_ATTRIBUTE_ID_DATABASE_ATTRIBUTE ), 0 ); if ( id > 0 ) { attrs.add( rowWithMeta ); } } return attrs; } catch ( KettleDatabaseException e ) { throw e; } finally { database.closeQuery( resultSet ); } } } );
@Override public List<RepositoryElementMetaInterface> call() throws Exception { List<RepositoryElementMetaInterface> repositoryObjects = new ArrayList<RepositoryElementMetaInterface>(); ResultSet rs = database.openQuery( sql, directoryIdRow.getRowMeta(), directoryIdRow.getData() ); if ( rs != null ) { List<Object[]> rows = database.getRows( rs, -1, null ); if ( rs != null ) { database.closeQuery( rs ); } RowMetaInterface rowMeta = database.getReturnRowMeta(); for ( Object[] r : rows ) { ObjectId id = new LongObjectId( rowMeta.getInteger( r, 4 ) ); repositoryObjects.add( new RepositoryObject( id, rowMeta.getString( r, 0 ), repositoryDirectory, rowMeta .getString( r, 1 ), rowMeta.getDate( r, 2 ), objectType, rowMeta.getString( r, 3 ), false ) ); } } return repositoryObjects; } } );
private DatabaseLookupData getCreatedData( boolean allEquals ) throws Exception { Database db = mock( Database.class ); when( db.getRows( anyString(), anyInt() ) ) .thenReturn( Collections.singletonList( new Object[] { 1L } ) ); RowMeta returnRowMeta = new RowMeta(); returnRowMeta.addValueMeta( new ValueMetaInteger() ); when( db.getReturnRowMeta() ).thenReturn( returnRowMeta ); DatabaseLookupMeta meta = createTestMeta(); DatabaseLookupData data = new DatabaseLookupData(); DatabaseLookup step = createSpiedStep( db, mockHelper, meta ); step.init( meta, data ); data.db = db; data.keytypes = new int[] { ValueMetaInterface.TYPE_INTEGER }; if ( allEquals ) { data.allEquals = true; data.conditions = new int[] { DatabaseLookupMeta.CONDITION_EQ }; } else { data.allEquals = false; data.conditions = new int[] { DatabaseLookupMeta.CONDITION_LT }; } step.processRow( meta, data ); return data; }
public synchronized List<Object[]> getJobAttributesWithPrefix( ObjectId jobId, String codePrefix ) throws KettleException { String sql = "SELECT *" + " FROM " + databaseMeta.getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_JOB_ATTRIBUTE ) + " WHERE " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_ID_JOB ) + " = ?" + " AND " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_CODE ) + " LIKE '" + codePrefix + "%'"; RowMetaAndData table = new RowMetaAndData(); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_ID_JOB ), new LongObjectId( jobId ) ); return callRead( () -> database.getRows( sql, table.getRowMeta(), table.getData(), ResultSet.FETCH_FORWARD, false, 0, null ) ); }
public synchronized List<Object[]> getTransAttributesWithPrefix( ObjectId id_transformation, String codePrefix ) throws KettleException { String sql = "SELECT *" + " FROM " + databaseMeta .getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_TRANS_ATTRIBUTE ) + " WHERE " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_ID_TRANSFORMATION ) + " = ?" + " AND " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_CODE ) + " LIKE '" + codePrefix + "%'"; RowMetaAndData table = new RowMetaAndData(); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_ID_TRANSFORMATION ), new LongObjectId( id_transformation ) ); return callRead( () -> database.getRows( sql, table.getRowMeta(), table.getData(), ResultSet.FETCH_FORWARD, false, 0, null ) ); }
public synchronized List<Object[]> getJobAttributes( ObjectId id_job, String code, long nr ) throws KettleException { String sql = "SELECT *" + " FROM " + databaseMeta.getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_JOB_ATTRIBUTE ) + " WHERE " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_ID_JOB ) + " = ? AND " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_CODE ) + " = ? AND " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_NR ) + " = ?" + " ORDER BY " + quote( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_VALUE_NUM ); RowMetaAndData table = new RowMetaAndData(); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_ID_JOB ), id_job ); table.addValue( new ValueMetaString( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_CODE ), code ); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_JOB_ATTRIBUTE_NR ), new Long( nr ) ); return callRead( () -> database.getRows( sql, table.getRowMeta(), table.getData(), ResultSet.FETCH_FORWARD, false, 0, null ) ); }
public synchronized List<Object[]> getTransAttributes( ObjectId id_transformation, String code, long nr ) throws KettleException { String sql = "SELECT *" + " FROM " + databaseMeta .getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_TRANS_ATTRIBUTE ) + " WHERE " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_ID_TRANSFORMATION ) + " = ? AND " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_CODE ) + " = ? AND " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_NR ) + " = ?" + " ORDER BY " + quote( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_VALUE_NUM ); RowMetaAndData table = new RowMetaAndData(); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_ID_TRANSFORMATION ), new LongObjectId( id_transformation ) ); table.addValue( new ValueMetaString( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_CODE ), code ); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_TRANS_ATTRIBUTE_NR ), new Long( nr ) ); return callRead( () -> database.getRows( sql, table.getRowMeta(), table.getData(), ResultSet.FETCH_FORWARD, false, 0, null ) ); }
public synchronized void fillStepAttributesBuffer( ObjectId id_transformation ) throws KettleException { String sql = "SELECT " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_ID_STEP ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_CODE ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_NR ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_VALUE_NUM ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_VALUE_STR ) + " " + "FROM " + databaseMeta.getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_STEP_ATTRIBUTE ) + " " + "WHERE " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_ID_TRANSFORMATION ) + " = ? " + "ORDER BY " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_ID_STEP ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_CODE ) + ", " + quote( KettleDatabaseRepository.FIELD_STEP_ATTRIBUTE_NR ); PreparedStatement ps = getPreparedStatement( sql ); RowMetaAndData parameter = getParameterMetaData( id_transformation ); stepAttributesBuffer = callRead( () -> database.getRows( database.openQuery( ps, parameter.getRowMeta(), parameter.getData() ), -1, null ) ); stepAttributesRowMeta = database.getReturnRowMeta(); // must use java-based sort to ensure compatibility with binary search // database ordering may or may not be case-insensitive // in case db sort does not match our sort // Collections.sort( stepAttributesBuffer, new StepAttributeComparator() ); // }
public synchronized List<Object[]> getJobEntryAttributesWithPrefix( ObjectId jobId, ObjectId jobEntryId, String codePrefix ) throws KettleException { String sql = "SELECT *" + " FROM " + databaseMeta.getQuotedSchemaTableCombination( null, KettleDatabaseRepository.TABLE_R_JOBENTRY_ATTRIBUTE ) + " WHERE " + quote( KettleDatabaseRepository.FIELD_JOBENTRY_ATTRIBUTE_ID_JOB ) + " = ?" + " AND " + quote( KettleDatabaseRepository.FIELD_JOBENTRY_ATTRIBUTE_ID_JOBENTRY ) + " = ?" + " AND " + quote( KettleDatabaseRepository.FIELD_JOBENTRY_ATTRIBUTE_CODE ) + " LIKE '" + codePrefix + "%'"; RowMetaAndData table = new RowMetaAndData(); table .addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_JOBENTRY_ATTRIBUTE_ID_JOB ), new LongObjectId( jobId ) ); table.addValue( new ValueMetaInteger( KettleDatabaseRepository.FIELD_JOBENTRY_ATTRIBUTE_ID_JOBENTRY ), new LongObjectId( jobEntryId ) ); return callRead( () -> database.getRows( sql, table.getRowMeta(), table.getData(), ResultSet.FETCH_FORWARD, false, 0, null ) ); }