1

I need to have a schedule job to read list of file names from DB and them upload them to a ftp site. How do I do that in WSO2 ESB?

Thanks

Updates (7/19/16): Based on the suggestions given, I wrote a stored procedure to return all file names in a xml format. Now that I need to figure out how to parse this xml formatted result and they can be ftp using fileconnector. Please let me know if my approach is workable. Thank you so much for your help.

<proxy name="FileUpload2CDGPS" startOnLoad="true" trace="disable" transports="https http" xmlns="http://ws.apache.org/ns/synapse">
    <target>
        <inSequence>
            <sequence key="FileLookupSeq"/>
            <sequence key="SendFile2VendorSeq"/>
            <send/>
        </inSequence>
        <outSequence/>
        <faultSequence>
            <drop/>
        </faultSequence>
    </target>
</proxy>

<sequence name="FileLookupSeq" trace="disable" xmlns="http://ws.apache.org/ns/synapse">
  <log description="LogMessage" level="custom">
    <property name="message" value="Find CDR files to upload"/>
  </log>
  <dblookup description="Get CDR files to be upload">
    <connection>
      <pool>
        <password>***</password>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://localhost:5432/esbcdrdb</url>
        <user>***</user>
      </pool>
    </connection>
    <statement>
      <sql><![CDATA[SELECT * FROM find_cdr_file_to_upload(1)]]></sql>
      <result name="xml_file" column="find_cdr_file_to_upload"/>
    </statement>
  </dblookup>
</sequence>

<sequence name="SendFile2VendorSeq" trace="disable" xmlns="http://ws.apache.org/ns/synapse">
  <iterate attachPath="//files/files-set" description=""
    expression="//files/files-set/file" preservePayload="true">
    <target>
      <sequence>
        <property description="fileName"
          expression="//files/files-set/file/name" name="fileName"
          scope="default" type="STRING"/>
        <property description="fileId"
          expression="//files/files-set/file/id" name="fileId"
          scope="default" type="STRING"/>
        <property description="fileSource"
          expression="//files/files-set/file/path" name="fileSource"
          scope="default" type="STRING"/>
        <property description="vendorId"
          expression="//files/files-set/file/vendor-id" name="vendorId"
          scope="default" type="STRING"/>
        <property description="vendorDest"
          expression="//files/files-set/file/vendor-dest"
          name="vendorDest" scope="default" type="STRING"/>
        <fileconnector.copy>
          <source>{$ctx:fileSource}</source>
          <destination>{$ctx:vendorDest}</destination>
          <filePattern>{$ctx:fileName}</filePattern>
        </fileconnector.copy>
      </sequence>
    </target>
  </iterate>
</sequence>
Community
  • 1
  • 1
M. Tai
  • 49
  • 11

1 Answers1

2

There are many ways you can get this done.

  • You can directly write your requirement inside the schedule task, which handles the db connection and uploading the file to ftp site. How to write a schedule task
  • Or You can invoke a proxy through a scheduled task sample can be found here. And inside the proxy you can connect to the db and get the required data and upload it to ftp using vfs, or you can write a custom mediator which get executed inside the proxy.
Isuru Gunawardana
  • 2,847
  • 6
  • 28
  • 60
  • I like the approach of invoking a proxy thru a scheduled task. The proxy will connect to the DB and get the required data. However, there will be multiple rows returned from the DB. I thought that dblookup mediator can only set a property from one row in a result set, am I right? If so, is there other mediator that I can utilize? – M. Tai Jul 12 '16 at 14:38
  • @M.Tai , I found that its not possible to get multiple rows, and a solution can be writing a custom mediator. Similar question and answers can be found here http://stackoverflow.com/a/16264739/993892 – Isuru Gunawardana Jul 12 '16 at 15:13
  • @M.Tai If you think this is the answer, mark this as the answer, keep commenting happy to help you to solve your problem. – Isuru Gunawardana Jul 15 '16 at 05:36
  • Isuru, instead of writing a custom DBLookup, I wrote the stored procedure to return all rows in a XML format. Now, I am trying to figure out how to use Iterate Mediator to parse this result. Do you know if my approach is correct? I will attach codes below. – M. Tai Jul 19 '16 at 21:21
  • I will accept the answer since I believe it guided me toward the right direction even though there are still technical detail to be sorted out. – M. Tai Jul 20 '16 at 17:54