Simple Xlate Example

[Layale Zakhour]

Hi Everyone,

I’m new to Cloverleaf and I would like to request a simple example for reading data from a database table, doing a minor concatenation for 2 fields and then writing the output to a flat file.

I managed to created the database-inbound, the translator configuration and a flat file as the outbound. For some reason, when I set the route to “raw”, all the rows in the tables are written to the flat file but when I set the route to “xlate”, only the first row is returned…

Please advise.

Thank you.

Topic

[Robert Kersemakers]

Hi Layale,

Haven’t done a Database connection yet (should try it, when I have some time left over…) but maybe I can help.

In the second picture you show us the properties of the Database Inbound protocol. Right now you have the option ‘Each read as a message’ selected, as opposed to ‘Each row as a message’. So the complete read (let’s say 20 rows) will be seen as one message. If you send this message ‘raw’ then you will see all the rows (each row separated by a CarriageReturn?). However your translation only takes one (the first) row of the message and copies it to the outbound format.

My guess: changing the properties to ‘Each row as a message’ should solve your problem.

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[Layale Zakhour]

Hi Robert,

I have tried to set the option “Each row as message” before but at the end I got only one row in the flat file that shows the current row being read. For instance, the first time I open the file, I see row #1, then few second later, I see row #2 instead of row#1 while row#1 is replaced with row #2.

[Levy Lazarre]

Hi Layale,

I have done a few database-inbound connections and have never seen the issue that you are describing. I can make the following suggestions, according to the pictures you posted:

1. In Snip_4, select “Use each row as a message”

2. In Snip_4, select “Use advanced scheduling” with “Connect as needed”. In the schedule set up, select a specific time, like 5 minutes later, just for the purpose of testing.

3. Most importantly, in Snip_5, you don’t need to configure Trx ID since you will be sending every row to translation. Set this option back to the default “Fixed Record Layout (frl).

4. Snip_6  no longer applies (no Trx ID computed).

Save the NetConfig and restart the process. Wait for the schedule to trigger the connection and the database read. The destination file should fill up with all the records from the database.

I hope this helps.

[Robert Kersemakers]

The fact that you get to see more that one row with this setting is encouraging.

That the outbound file is overwritten each time with the last row is weird, as you have ‘Append to file’ set. Maybe try the ‘fileset-local’ protocol instead of ‘file’.

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[Layale Zakhour]

Dunno why when the route message is set to raw, all the records appear in the flat file and when the route message is set to xlate, I start having either one row as a result, or the whole messages one after another without any newline between them, they look like a paragraph.

[Levy Lazarre]

Then there is a problem either with your output VRL or your Xlate. You can force a newline if you have to.

[Robert Kersemakers]

In your outbound thread, check in the Outbound tab under ‘Inbound replies’ that ‘Await replies’ is not set. I have done this before (copying a tcp-thread to a fileset-thread and forgetting about the replies) and it can result in unexpected behaviour.

No errors/warnings/messages in the logfile of the process?

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[Layale Zakhour]

Turned out that my work was correct. Opened the results in Notepad++ and Excel instead of Notepad and the results were good. Awkward output in Notepad.

[Author]

Replies