Clarity

Hello,

I need some help for the automatic generation of files in Clarity.

I wrote the following GEL script :

<gel:script


xmlns:core="jelly:core"


xmlns:f="jelly:com.niku.union.gel.FileTagLibrary"


xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"


xmlns:sql="jelly:sql">

<gel:parameter default="C:\PERSO" var="filePath1"/>
<gel:parameter default="Test Import GEL.csv" var="fileName1"/>

<gel:formatDate format="dd-MM-yyyy-HH-mm" stringVar="run_date"/>

<core:set value="0" var="totalrecords"/>

<sql:setDataSource url="jdbc:sqlserver://esvsql62\proj1:1443;DatabaseName=clarity"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="niku" password="password" />

<sql:query var="result">

SELECT


NAME

PROJET,


UNIQUE_NAME
ID_PROJET,


CREATED_DATE
DATE_CREATION

FROM


niku.SRM_PROJECTS

WHERE


NAME LIKE 'DRP%'


ORDER BY NAME
</sql:query>

<core:forEach indexVar="j" items="${result.rows}" var="row">

<core:set value="${row.get('projet')}" var="projet"/>

<core:set value="${row.get('id_projet')}" var="id_projet"/>

<core:set value="${row.get('date_creation')}" var="date_creation"/>


<core:catch var="filerrcreate">



<f:writeFile delimiter=";" embedded="false" fileName="${filePath1}\${run_date}${fileName1}">




<f:line>





<f:column value="${projet}"/>





<f:column value="${id_projet}"/>





<f:column value="${date_creation}"/>




</f:line>



</f:writeFile>


</core:catch>

<core:set value="${totalrecords + 1}" var="totalrecords"/>
</core:forEach>
</gel:script>

You can find the generated file in attachments.

There is only one line in the generated file instead of several,

The query should returns the following lines :

DRP 2011 Achat
DrpAchat2011
2011-01-21 09:09:15.437
DRP 2011 Généralités
DrpGen2011
2011-01-21 09:46:02.677
DRP 2011 Méthodes
DrpMeth2011
2011-01-19 15:00:30.387
DRP 2011 Qualité
DrpQualite2011
2011-01-21 09:44:24.007
DRP 2011 Sécurité
DrpSecu2011
2011-01-21 09:31:05.163

So I need help with cases :

1. Why only one line is generated ?

2. Why some characters like "é" are differently returned ? I also have problems with the format of returned dates.

3. How can I create the job based on this GEL script in Clarity ?

Thank you very much for your answer and have a nice day.

With regards,

Ebubekir AYDIN in Systalians
0033 247347908

1.

I think you are looping OK around your results, but overwriting your output file with each iteration.

Your "writeFile" needs to be outside your "foreach" loop - i.e. once, at the start of the code.

--

2.

Not sure about the accented letters ; but date formats will depend upon the NLS settings of the environment - if you need specific date formats, then you should cast them in the SQL step.

--

3.

Create a new process.

Make the start step a Custom Action - this is where you put the GEL script.
Make the process go from the start-step straight to the stop-step.

Run the "Execute a Process" job and specify your new process as the process to run.

NOTE that when the BG runs the process, this is running on the BG server, so your file directories are relative to that server (i.e. check "C:\PERSO" is available on the BG server)

Hello,

Thank you very much for your answer.

1. I put the "writefile" outside my "foreach" loop and it works well.

2. I changed my SQL query for having the correct date formats, but my accented letters are not returned in csv files, il will create an incident for this case.

3. I have not tested yet the creation of a new process, I will test and close this dicussion later.

Regards,

Ebubekir