Defining a long string variable

[PhilHibbs]

I need to store a long, multi-line string (i.e. containing newlines) and use it later in my job. Putting it in the globalMap makes sense, but what's the best way to get it there? Is there a component that allows me to enter a multi-line string? At the moment I have a tJava that builds it up line with string concatenations and \\n in the string but I'd rather just enter it literally with line breaks in an editor like you can for instance in the Query property of a SQL execution component.

The string is a SQL statement, which I will be using as a template to modify and run several variants later on in tHiveRow components.

I there a component that lets me enter a multi-line string with line breaks, and writes that string to the global map?

[Anonymous]

Hi,

   Since you are trying to use an SQL, why don't you remove the line break and store each SQL in single line. It will be easy to manage in long run that way.

   Now, a quick work around is to read the data file with a unknown delimiter (in the case of DB, the data will already have new line embedded in input string if you have given that way) and then assign it to a String variable as shown below.

The sample file is as shown below.

The data is assigned directly to the variable and printed as shown in tJava component.

The printing of variable displays that any newline inside that variable is retained for further processing.

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

[PhilHibbs]

I'm moving away from storing the SQL in a file. It is in a file right now, and I'm trying to get rid of the file and have the template SQL defined in the job. And we're using a tFileInputRaw which I guess is like a tFileInputDelimited with no delimiter.

The line ends in the SQL aren't really important to me. The important thing is that the SQL be readable and maintainable, and that means line breaks and indentation.

Imagine you had to put this SQL into a job:

select brand_code, count(*) products
  from ~~product_table~~
 group by brand_code
 order by brand_code
having count(*) > ~~threshold~~

You need to do a search and replace on ~~product_table~~ and ~~threshold~~ to insert configurable values later on. So you want to store that SQL in a string in the global map. Here's one way to do it:

StringBuilder template_sql = new StringBuilder{};

template_sql.apend("select brand_code, count(*) products\n");
template_sql.apend("  from ~~product_table~~\n");
template_sql.apend(" group by brand_code\n");
template_sql.apend(" order by brand_code\n");
template_sql.apend("having count(*) > ~~threshold~~\n");

globalMap.put("template_sql",template_sql.toString());

It's a bit ugly though. The SQL is not as readable as it was without all the Java syntax around it. What I want is an edit box that I can paste the text into just like the bare SQL, and have that stored in a string that I can use.

Here's another way.

globalMap.put("template_slq"
, "select brand_code, count(*) products\n"
+ "  from ~~product_table~~\n"
+ " group by brand_code\n"
+ " order by brand_code\n"
+ "having count(*) > ~~threshold~~\n"
);

That's a little bit tidier, although the reason I used a StringBuilder is I might have a bunch of optional joins that are based on flags so some of the .append calls are inside if statements.