sql server - How to set order of SQL-response? -
i've got mule flow queries sql-server database , converts response json , writes json comma separated file.
my problem order of column in written file doesn't match order of fields in query. how tho define order of columns in query answer?
the json csv conversion this:
public class bean2csv { /** * @param args * @throws jsonexception */ public string conv2csv(object input) throws jsonexception { if (!input.equals(null)){ string inputstr = (string)input; jsonarray jsonarr = new jsonarray(inputstr); string csv = cdl.tostring(jsonarr); csv = csv.replace(',', ';'); system.out.println(inputstr); //csv system.out.println(csv); //csv return csv; } else return "";} } and here flow
<?xml version="1.0" encoding="utf-8"?> <mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:spring="http://www.springframework.org/schema/beans" xmlns:core="http://www.mulesoft.org/schema/mule/core" version="ce-3.4.0" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule- http.xsd http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/current/mule-jdbc.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring- beans-current.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd"> <context:property-placeholder location="classpath:cognos_import.properties" ignore-resource-not-found="true"/> <spring:beans> <spring:bean id="datasource" name="datasource" class="org.enhydra.jdbc.standard.standarddatasource" destroy-method="shutdown"> <spring:property name="drivername" value="net.sourceforge.jtds.jdbc.driver"/> <spring:property name="user" value="${dbusername}"/> <spring:property name="password" value="${dbpassword}"/> </spring:bean> <spring:bean id="changedb" class="changedatabase"> <spring:property name="serverip" value="${dbserverip}"/> <spring:property name="serverport" value="${dbserverport}"/> <spring:property name="dbprefix" value="${dbprefix}"/> </spring:bean> </spring:beans> <jdbc:connector name="db_conn" datasource-ref="datasource" validateconnections="false" pollingfrequency="5000" doc:name="database"> <jdbc:query key="readbal" value="select #[header:inbound:company] company, acno konto , r2 avd, #[header:inbound:period] period, sum(acam) saldo actr (acyrpr <= #[header:inbound:period]) , (acno < 3000) group acno,r2 order acno,r2;"/> <jdbc:query key="readres" value="select #[header:inbound:company] company, acno konto , r2 avd, #[header:inbound:period] period, sum(acam) saldo actr (acyrpr <= #[header:inbound:period]) , (acyrpr >= #[header:inbound:starttid]) , (acno >= 3000) group acno,r2 order acno,r2"/> <jdbc:query key="readiclr" value="select #[header:inbound:company] company, actr.acno konto , actr.r2 avd, #[header:inbound:period] period, sum(actr.acam) saldo,sum(actr.curam) valutabel, actr.cur valuta, actr.r1 ftgid actr left join actor on actr.r1=actor.r1 (actr.acyrpr <= #[header:inbound:period]) , (actr.acyrpr >= #[header:inbound:starttid]) , actor.sacset=2 , (actr.acno=3019 or actr.acno=3099 or actr.acno=3199 or actr.acno=3299 or actr.acno=3499 or actr.acno=3519 or actr.acno=3599 or actr.acno=3699 or actr.acno=3799 or actr.acno=3919 or actr.acno=3999 or actr.acno=4299 or actr.acno=4399 or actr.acno=4599 or actr.acno=4699 or actr.acno=4799 or actr.acno=5099 or actr.acno=5299 or actr.acno=5499 or actr.acno=5699 or actr.acno=5799 or actr.acno=5999 or actr.acno=6099 or actr.acno=6399 or actr.acno=6499 or actr.acno=6999 or actr.acno=7999 or actr.acno=8399 or actr.acno=8499) , actr.sup<>0 group actr.acno,actr.r2,actr.r1,actr.cur"/> <jdbc:query key="readickr" value="select #[header:inbound:company] company, actr.acno konto , actr.r2 avd, #[header:inbound:period] period, sum(actr.acam) saldo,sum(actr.curam) valutabel, actr.cur valuta, actr.r1 ftgid actr left join actor on actr.r1=actor.r1 (actr.acyrpr <= #[header:inbound:period]) , (actr.acyrpr >= #[header:inbound:starttid]) , actor.cacset=2 , (actr.acno=3019 or actr.acno=3099 or actr.acno=3199 or actr.acno=3299 or actr.acno=3499 or actr.acno=3519 or actr.acno=3599 or actr.acno=3699 or actr.acno=3799 or actr.acno=3919 or actr.acno=3999 or actr.acno=4299 or actr.acno=4399 or actr.acno=4599 or actr.acno=4699 or actr.acno=4799 or actr.acno=5099 or actr.acno=5299 or actr.acno=5499 or actr.acno=5699 or actr.acno=5799 or actr.acno=5999 or actr.acno=6099 or actr.acno=6399 or actr.acno=6499 or actr.acno=6999 or actr.acno=7999 or actr.acno=8399 or actr.acno=8499) , actr.cust<>0 group actr.acno,actr.r2,actr.r1,actr.cur"/> <jdbc:query key="readiclb" value="select #[header:inbound:company] company, actr.acno konto, actr.r2 avd, #[header:inbound:period] period, sum(actr.acam) saldo, sum(actr.curam) valutabel, actr.cur valuta, actr.r1 ftgid actr left join actor on actr.r1=actor.r1 (actr.acyrpr <= #[header:inbound:period]) , actor.sacset=2 , actr.sup<>0 , (actr.acno=1511 or actr.acno=2441) group actr.acno,actr.r2,actr.r1,actr.cur"/> <jdbc:query key="readickb" value="select #[header:inbound:company] company, actr.acno konto, actr.r2 avd, #[header:inbound:period] period, sum(actr.acam) saldo, sum(actr.curam) valutabel, actr.cur valuta, actr.r1 ftgid actr left join actor on actr.r1=actor.r1 (actr.acyrpr <= #[header:inbound:period]) , actor.cacset=2 , actr.cust<>0 , (actr.acno=1511 or actr.acno=2441) group actr.acno,actr.r2,actr.r1,actr.cur"/> </jdbc:connector> <file:connector name="output" outputpattern=" #[function:datestamp:dd-mm-yy-hh-mm-ss]#[header:inbound:company].txt" autodelete="false" outputappend="true" streaming="false" validateconnections="false" doc:name="file"/> <message-properties-transformer name="delete-content-type-header" doc:name="message properties"> <delete-message-property key="content-type"/> </message-properties-transformer> <message-properties-transformer name="add-csv-content-type-header" doc:name="message properties"> <add-message-property key="content-type" value="text/csv"/> </message-properties-transformer> <message-properties-transformer name="add-filename" doc:name="message properties"> <add-message-property key="content-disposition" value="attachment; filename=testfil.txt"/> </message-properties-transformer> <flow name="cd-test1flow1" doc:name="cd-test1flow1"> <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8082" doc:name="http"/> <not-filter doc:name="not"> <wildcard-filter pattern="*favicon*" casesensitive="true"/> </not-filter> <logger message="log1 #[message.getpayload()]! " level="info" doc:name="logger1"/> <http:body-to-parameter-map-transformer doc:name="body parameter map"/> <component class="validationservice3x" doc:name="java"/> <echo-component doc:name="echo"/> <set-variable variablename="filename" value=" #[function:datestamp:dd-mm-yy-hh-mm-ss]-#[header:inbound:company]-#[header:inbound:period]" doc:name="filenamevar"/> <component doc:name="java"> <spring-object bean="changedb"/> </component> <all doc:name="all"> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readbal" connector-ref="db_conn" doc:name="db"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csv" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-bal.csv" connector-ref="output" doc:name="file"/> </processor-chain> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readres" connector-ref="db_conn" doc:name="db100-ickr"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csv" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-res.csv" connector-ref="output" doc:name="file"/> </processor-chain> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readiclb" connector-ref="db_conn" doc:name="db100-ickr"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csv" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-icb.csv" connector-ref="output" doc:name="file"/> </processor-chain> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readickb" connector-ref="db_conn" doc:name="db100-ickr"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csvnotfirstline" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-icb.csv" connector-ref="output" doc:name="file"/> </processor-chain> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readiclr" connector-ref="db_conn" doc:name="db100-ickr"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csv" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-icr.csv" connector-ref="output" doc:name="file"/> </processor-chain> <processor-chain> <jdbc:outbound-endpoint exchange-pattern="request-response" querykey="readickr" connector-ref="db_conn" doc:name="db100-ickr"/> <expression-filter expression="payload.size() > 0" doc:name="not empty"/> <json:object-to-json-transformer doc:name="object json"/> <component class="bean2csvnotfirstline" doc:name="java"/> <echo-component doc:name="echo"/> <file:outbound-endpoint path="${outputfolder}" outputpattern="#[flowvars['filename']]-icr.csv" connector-ref="output" doc:name="file"/> </processor-chain> </all> <set-payload value="nu är 100 tankat till #[flowvars['filename']] " doc:name="set payload"/> <http:response-builder doc:name="http response builder"/> </flow>
Comments
Post a Comment