topic tOracleRow reject empty result set in Talend Studio

tOracleRow reject empty result set

Anonymous — Thu, 14 Feb 2019 19:49:06 GMT

I have a tOracleRow that executes a PreparedStatement based on values from an input. Say the SQL statement is something like "SELECT foo FROM bar WHERE baz = ?", and I substitute the lookup value of baz based on a flow input to the tOracleComponent i.e., in advanced settings I have parameter index 1 assigned to inputRow.someValue. I get the values of foo in the output like I expect, but I want the values of baz that don't exist in table bar. In other words, I want the values of baz for which the query returned no results to be sent to the rejects output of the tOracleRow. Is this possible?

Re: tOracleRow reject empty result set

Anonymous — Thu, 14 Feb 2019 20:41:00 GMT

What do you want to achieve, can you say more clearly on it with a screenshot

Re: tOracleRow reject empty result set

Anonymous — Thu, 14 Feb 2019 22:16:05 GMT

I don't think a screenshot would be helpful. I'm currently achieving what I want with a tJavaFlex. Here's the Java

Start code:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, context.Login, context.Password);

PreparedStatement l_pstmt = conn.prepareStatement("SELECT * FROM foo WHERE bar = ?");

Main code:

l_pstmt.setLong(1, inputRow.bar);
ResultSet resultSet = l_pstmt.executeQuery();

if(resultSet.next()) {
outputRow.lookupSuccessful = true;
outputRow.PRIMARY_KEY = resultSet.getBigDecimal("PRIMARY_KEY");
outputRow.bar = new BigDecimal(inputRow.bar);
} else {
outputRow.lookupSuccessful = false;
outputRow.bar = inputRow.bar;
}

End code:

l_pstmt.close();
conn.close();

And then I use a downstream tMap to switch on lookupSuccessful. Good records get inserted into a different table. Bad ones get logged. I would like to achieve this with a tOracleRow (customer would prefer less bespoke Java). I was hoping there was a way of sending the lookupSuccessful == false records to the rejects output of this component.