Christophe.Raymond

Querying data from a SQL Server DB for writing that data into a csv file

Discussion created by Christophe.Raymond on Mar 14, 2012
Latest reply on Mar 16, 2012 by Christophe.Raymond
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

Outcomes