Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Five Stars

Convert tOracle_input to tOracle_row for prepared statement with dynamic row

I have a tOracleInput with the following query

"SELECT * FROM VW_LATEST WHERE "+
DTC_EVENT_ID = "+context.event_id

 

This is inefficient and needs to be converted to a prepared statement using tOracleRow. However the output from the tOracleInput is a dynamic row. How can I convert from the object in tOracleRow to a dynamic row output?


Accepted Solutions
Five Stars

Re: Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Not really what I was looking for as it still does the tOracleInput. I resolved it with a tJavaRow and processed the record set rather than the dynamic row.

Five Stars

Re: Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Capture.PNG

 

TJavaRow has the following code

java.sql.ResultSet re_tParseRecordSet_3 = (java.sql.ResultSet) row7.dynamicRow;


boolean hasNextRecord_tParseRecordSet_3 = false;
saRowMap = ((java.util.Map<String, Object>)globalMap.get("saRowMap"));
globalMap.put("saRowMap", saRowMap);
int numColumns1 = re_tParseRecordSet_3.getMetaData().getColumnCount();
//System.out.println(numColumns1);
//System.out.println(saRowMap);
hasNextRecord_tParseRecordSet_3 = re_tParseRecordSet_3.next();
for (int i = 1; i < numColumns1 + 1; i++) {
//System.out.println(i);

String colName = re_tParseRecordSet_3.getMetaData().getColumnName(i);

if (!saRowMap.containsKey(colName)) {
Object colVal = re_tParseRecordSet_3.getObject(i);
// System.out.println(colName);
// System.out.println(re_tParseRecordSet_3.getMetaData().getColumnTypeName(i));
if ("DATE".equals(re_tParseRecordSet_3.getMetaData().getColumnTypeName(i)) && colVal != null){
colVal = TalendDate.formatDate("dd/MM/yyyy", (Date) colVal);
// System.out.println(colName);
}
// System.out.println(colVal);
saRowMap.put(colName, colVal);
}
}

RulesEngine.evaluateExpressions(saRowMap,
(List<java.util.Map<String, String>>)context.j902_exprList,
(List<java.util.Map<String, String[]>>)context.j902_paramList);

output_row.EVENT_ID = context.event_id;
output_row.STATUS_ID = 30;
output_row.INPUT_MAP = saRowMap;
output_row.CHANGE_USER = "TALEND";
//System.out.println(output_row);

All Replies
Employee

Re: Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Hi,

 

    You can pass the output of tDBRow to a tFlowtoIterate component where the column name is called "query". In the tDBInput, use COntrol+Space to get this value under tFlowtoIterate component. When you select it, it will add to query section as shown below.

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Five Stars

Re: Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Not really what I was looking for as it still does the tOracleInput. I resolved it with a tJavaRow and processed the record set rather than the dynamic row.

Five Stars

Re: Convert tOracle_input to tOracle_row for prepared statement with dynamic row

Capture.PNG

 

TJavaRow has the following code

java.sql.ResultSet re_tParseRecordSet_3 = (java.sql.ResultSet) row7.dynamicRow;


boolean hasNextRecord_tParseRecordSet_3 = false;
saRowMap = ((java.util.Map<String, Object>)globalMap.get("saRowMap"));
globalMap.put("saRowMap", saRowMap);
int numColumns1 = re_tParseRecordSet_3.getMetaData().getColumnCount();
//System.out.println(numColumns1);
//System.out.println(saRowMap);
hasNextRecord_tParseRecordSet_3 = re_tParseRecordSet_3.next();
for (int i = 1; i < numColumns1 + 1; i++) {
//System.out.println(i);

String colName = re_tParseRecordSet_3.getMetaData().getColumnName(i);

if (!saRowMap.containsKey(colName)) {
Object colVal = re_tParseRecordSet_3.getObject(i);
// System.out.println(colName);
// System.out.println(re_tParseRecordSet_3.getMetaData().getColumnTypeName(i));
if ("DATE".equals(re_tParseRecordSet_3.getMetaData().getColumnTypeName(i)) && colVal != null){
colVal = TalendDate.formatDate("dd/MM/yyyy", (Date) colVal);
// System.out.println(colName);
}
// System.out.println(colVal);
saRowMap.put(colName, colVal);
}
}

RulesEngine.evaluateExpressions(saRowMap,
(List<java.util.Map<String, String>>)context.j902_exprList,
(List<java.util.Map<String, String[]>>)context.j902_paramList);

output_row.EVENT_ID = context.event_id;
output_row.STATUS_ID = 30;
output_row.INPUT_MAP = saRowMap;
output_row.CHANGE_USER = "TALEND";
//System.out.println(output_row);

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download