topic Re: Handling null values within dynamic column in Talend Studio

Handling null values within dynamic column

lennelei — Sat, 16 Nov 2024 12:18:27 GMT

Hello,
I've a job to do : using multiple .xml files as inputs, I've to extract one name and one sql query from each xml file, run the corresponding query and store the result inside a csv file using the name found inside the .xlm for the ouput file (of course, each query returns different columns).
I managed to do it (tFileList > tFileInputXML > tJavaRow > tOracleInput > tFileOutputDelimited) using a single dynamic column between the tOracleInput and the tFileOutputDelimited to handle the different queries; however, I've got trouble handling null values.
To explain this, let's use a more simple job : tOracleInput > tFileOutputDelimited.
With a specific schema (col1 as String, col2 as ...) between the components, null values are printed as empty string in the tFileOutputDelimited.
However, when using a single dynamic column between both components (col1 as Dynamic), null values are printed as "null" string in the tFileOutputDelimited.
Question: is it possible to output null values as empty string when using dynamic column?
Thanks

Re: Handling null values within dynamic column

Anonymous — Fri, 27 Apr 2012 04:14:20 GMT

Hi
I think it's hard to change the behavior of dynamic schema. But it is easy to replace all null in csv with " " by using tReplace.
Regards,
Pedro

Re: Handling null values within dynamic column

lennelei — Fri, 27 Apr 2012 11:44:33 GMT

Hi,
thanks for your answer... I agree it's easy unless you're unlucky enough to get a real "null" value as string in your data (hopefully, I'm pretty sure it's not my case, but who knows...) :s
Another question: is it hard (for me) to change the "null" string used to output null values in the components (to use something we can be sure of: such as "_null_" or even directy an empty string)?

Re: Handling null values within dynamic column

lennelei — Fri, 27 Apr 2012 11:58:09 GMT

For exemple, I juste have a quick look on the sources, it seems that writeValuesToStringArray function from DynamicUtils.java is used to display data from dynamic column :

	public static void writeValuesToStringArray(Dynamic column, String[] row, int offset) {
		for (int i = 0; i < column.getColumnCount(); i++) {
			DynamicMetadata metadata = column.getColumnMetadata(i);
			if ("id_Date".equals(metadata.getType()) && !(DBMSConstants.MSSQL.getDBmsId().equalsIgnoreCase(column.getDbmsId()) && !(metadata.getDbType().toLowerCase().indexOf("timestamp") < 0))) {
				row = FormatterUtils.format_Date((java.util.Date) column.getColumnValue(i),metadata.getFormat());
			} else {
				row = String.valueOf(column.getColumnValue(i));
			}
		}
	}

I'll probably have to think a bit about this, but what if I replace the line :

row = String.valueOf(column.getColumnValue(i));

with something like :

row = (column.getColumnValue(i) == null)?"":String.valueOf(column.getColumnValue(i));

Re: Handling null values within dynamic column

lennelei — Mon, 30 Apr 2012 11:48:47 GMT

Hello again,
Is it possible for me to do the previous modification : change DynamicUtils.java in \Talend-5.0.2\studio\Talend-Studio-r78327-V5.0.2\plugins\org.talend.desinger.routines.tisprovider_5.0.2.r78327\resources\java\routines\system\ to handle null values differently in my own Talend installation?