API Connect

 View Only
Expand all | Collapse all

Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

  • 1.  Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Sat February 04, 2023 05:22 AM

    Hi All,

    I've a requirement like iam getting a bulk file in form-data from a client to my API Connect and I need to Transfer that file to a ftp server and a Database.

    Do we able to do with gatewayscript coding? If yes please let me know how can we do that.




    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------


  • 2.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Wed February 08, 2023 12:53 AM
    Hi sorry can you give us more data? What type of database & ftp server?
    How big is the data?

    ------------------------------
    Matthias Siebler
    MA
    ------------------------------

    Original Message


  • 3.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Wed February 08, 2023 01:24 AM

    Hi @Matthias Siebler

    let my file is of 4mb and and it is CSV extension and the data inside is PIPE Separated. I want to test this first in my local if achieved then I'll implement in real-time.

    Consider I am using a FileZilla ftp server for testing and an Oracle Database.

    Now my question is,
    Am I able to send the incoming multipart/form-data file to FTP server and at the same time I want to handle the data inside the file and I want to insert the data in specified fields of Oracle Database?​

    If we can do this by using IBM API Connect v10 Please let me know how can this be achieved. If possible send me the yaml or code snippets to do this.


    Thanks in Advance @Matthias Siebler !


    Regards

    ​

    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Original Message


  • 4.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Thu February 09, 2023 11:37 AM

    Hi Vyasavardhan,

    Since multipart/form-data can have multiple parts/files, how are you extracting each file(s)? GatewayScript would be the best approach to parse out each part based upon the boundary specified in the content type header, but sending that file to a ftp server or database would require an xslt that would be called from your GatewayScript using a transform.xslt asynchronous function call, passing your file data, perhaps wrapped in an xml tag, as input to your stylesheet.  Note GatewayScript itself only supports via the urlopen module the http(s), (dp)mq, and graphql protocols, but the xslt dp:url-open extension function would also support the ftp or sql protocols.  See https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=open-url-sql.  For your question about handling "data inside the file",  your csv file is ascii but 4MB is a lot of data to process, what would that really be? A CSV of thousands of rows that would require thousands of SQL Inserts?  That would seem to be a very latent process that would consume a lot of resources in the gateway while processing.  Perhaps you can explain further what you're trying to accomplish with your Database.

    Best Regards,
    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------

    Original Message


  • 5.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Thu February 09, 2023 01:00 PM

    Thanks Steve, if i can add to that; while it is allowed to do these types of async actions; where the APIGW is sending data to 2 places simultaneously, that can also be risky, as if when the sidecall is slow to respond but new connections are continuously arriving at the apigw; leading to all available memory being consumed. Rate limits can only partially mitigate that problem; timeouts also need to be carefully set to help prevent a slow db from causing the whole system to go down.



    ------------------------------
    Matthias Siebler
    MA
    ------------------------------

    Original Message


  • 6.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Thu February 09, 2023 02:10 PM

    Hi @matthias siebler

    I didn't get your point of 'that can also be risky' what do you want to conclude me on that statement.

    Is it not possible with APIC or it is more complex thing.

    Please give me a detailed clarity about this.

    And thanks for responding.!!

    If possible say me an alternative.



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Original Message


  • 7.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Thu February 09, 2023 02:04 PM

    Thanks @Steve Linn 

    Iam waiting for your rly on this issue from the past 5days.

    Let my file is of bulk paymentes transactions details of the customers. So i need to insert that data in corresponding fields in the Database from a csv file which is pipe seperated and at the same time i need to place the same file in a ftp location.

    Can i able to do that?

    If yes. Please help me with the codes how i'll do that.

    4mb file is just an assumption. Think that my file having 100customers details of their transactions so i need to put the data in data base and in ftp for the further external processing of external consumers.

    Help me in this.

    Thanks in advance!!!

    Sorry to bother you but If possible send me a yaml of the API in v10.



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Original Message


  • 8.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Fri February 10, 2023 09:35 AM

    Hi Vyasavardhan,
    The old phrase that this is simply a SMOP ("simple matter of programming") applies here.  I believe it is possible, although as Matthias points out, the risk you take with long running transactions is that memory is consumed and then held for that transaction until the entire transaction completes.  Depending upon what other transactions are executing on DataPower for your API Connect environment, this can lead to memory and resource exhaustion when the appliance is under load which would cause the appliance to reload. Assuming you get a working solution in place, I'd recommend you determine how latent the transaction is for your typical .csv file and then add that into any capacity planning you have for your API Connect Gateways.

    A solution for this will take some time for you to develop.  It is not anything that I have an example of to assist you.  All I can do is to point you to the DataPower and API Connect documentation.

    1.  As I noted above, at present there is not a multi-part form parser in API Connect.   The only solution I've played around with in a GatewayScript policy is to read request.body as a Buffer, and based upon the content-type request header, you can extract the boundary of the multipart form and then step through the parts of the body using the boundaries, getting the filename and payload from the Buffer using the Content-Disposition header of each part.  It is usually important that you work with a Buffer object and not simply generate a string of the multi-part form payload using the toString() function as that will encode the Buffer to utf-8 encoding which will change non ascii data in your payloads which you generally don't want to do, but in your case if you only will have a .csv file it may be acceptable to do that which would make the parsing of the payload a little easier.
    2. Once you have the filename and payload from the multi-part part, you'll need to send that file to a ftp server? That is doable, but only in an xslt stylesheet.  GatewayScript can call an xslt (see the transform.xslt module https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=apis-transform-module) and pass data through one of two mechanisms, either an input to the stylesheet, in which case you'll need to place a XML wrapper element around your data so the stylesheet can consume it, or you can save the filename and payload into context variables and then have the stylesheet read these to process them.  The dp:url-open extension function can be used to send data to a ftp server. (https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=open-url-ftp)
    3. As for the sql inserts you're looking to do, again this is an xslt only solution using the dp:url-open (https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=open-url-sql) or perhaps the dp:sql-execute (https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=functions-dpsql-execute) extension functions.   I've not done much in DataPower with SQL and that was many years ago so I can't offer much assistance here.  I did an internet search and found a few links which may be helpful https://blogs.perficient.com/2016/09/26/configure-sql-data-source-in-ibm-datapower-gateway/ and https://docplayer.net/38340942-Websphere-datapower-appliances-sql-odbc-use-cases-presentation-part-2.html for examples.  These links may be somewhat dated but hopefully they provide some more concrete examples to the base product documentation.  Your task here would be to tokenize each row, which I assume is linefeed delimited, and you might also need to do something with each column within the row, again tokenizing each row based upon the pipe character.  There will also be dependent DataPower objects you'll need to deploy about your sql server separate from the stylesheet.  In this case, those objects in v10 would be deployed using a Gateway Extension that would be associated with your Gateway in CMC (see https://www.ibm.com/docs/en/api-connect/10.0.1.x?topic=gateway-extensions-manifest for information about building a gateway-extension), and your dp:url-open or dp:sql-execute would just reference that sql server object.  The dependent stylesheet may also need to be deployed using a gateway-extension.  I'm assuming you're wanting to do a sql insert for each row into some table, so you'll need to iterate over each row with an xsl:for-each.

    It sounds like an interesting development effort.

    Best Regards,
    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------

    Original Message


  • 9.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Tue February 14, 2023 06:20 AM
    Edited by Stefen Salvatore Tue February 14, 2023 06:20 AM

    Hi @Steve Linn 

    XSLT code is reading an xml file from ftp server what if i want to read the json or csv file from ftp using api connect v10 via dp:url-open



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Original Message


  • 10.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Tue February 14, 2023 08:10 AM

    Hi yes it is possible to read a ftp file using xslt url-open call.

    Note that url-open cannot stream the data; it will be buffered in memory; so how large can the file be?

    If it is very large; e.g. several gigs; then that can be a concern for memory management & also the parsing limits set on the xml manager.



    ------------------------------
    Matthias Siebler
    DataPower L3 Team Lead

    ------------------------------

    Original Message


  • 11.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Tue February 14, 2023 08:58 AM
    Edited by Stefen Salvatore Tue February 14, 2023 09:03 AM

    hi @Matthias Siebler 

    thanks for the rly and I have another doubt.

    what if it is just a small JSON file with some kb's so I need to read it since gateway script won't support for ftp and SQL need to call with XSLT. so please help me in this with a snippet or something else.

    Iam able to read xml file using XSLT but Json cannot be handled with XSLT, so I need to read it using gateway script in that XSLT .

    Thanks in advance!



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Original Message


  • 12.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Tue February 14, 2023 10:02 AM

    Hi Vyasavardhan,

    See 

    https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=elements-dpurl-open ,

    specifically you'll need to provide the response attribute of the dp:url-open as binary node. Then you can use the dp:binaryNodeAsString extension to get the JSON in ascii. 

    Best Regards,

    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------

    Original Message


  • 13.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Wed February 15, 2023 01:09 AM
      |   view attached

    Hi @Steve Linn 

    I tried it but Iam unable to achieve what Iam looking for. Here is the snippet of codes which I used to read a Json file from ftp. Please go through it and let me know where Iam going wrong. And treat this as a priority. I need to complete this by today EOD.

    Thanks in Advance Steve!!



    ------------------------------
    Vyasavardhan Ramagiri
    ------------------------------

    Attachment(s)

    txt
    comm_readfilefromftp.txt   1 KB 1 version
    Original Message


  • 14.  RE: Can we able to send a file from multipart/form-data to a ftp server and a database at a time using IBM APIconnect V10

    Posted Thu February 16, 2023 01:49 PM

    Hi Vyasavardhan,
    In your xslt code:

    <xsl:variable name="ftp-put-url"
   select="'ftp://xxsx:xxrx@192.168.0.1x8/test/test.json'"/>
<dp:url-open target="{$ftp-put-url}">
  <xsl:copy-of select="/"/>
</dp:url-open>

    You're url-open extension function only has the target attribute.   If you're doing a get of your json file from ftp, the child of the url-open won't be there since you are not sending a payload, but instead a get request.   You'll need to add the response="responsecode-binary" attribute to indicate that you are not expecting an XML response.   See https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=elements-dpurl-open to see what the XML results of using the attribute are.   For a http(s) request this is documented as returning an XML document like the following.  Other than the binary element in the result, I'm not sure what metadata is also returned for a ftp url-open, but you could dump the result to a temporary file using the dp:dump-nodes extension function if you're curious.

    <result>
  <binary>***BINARY NODE***</binary>
  <responsecode>200</responsecode>
  <reasonphrase>OK</reasonphrase>
  <httpversion>...</httpversion>
  <headers> ... </headers>
</result>

    This will place the non-xml response in what is called a binaryNode, a DataPower only XML node type.   You can then use the dp:binaryNodeToString extension function (https://www.ibm.com/docs/en/datapower-gateway/10.0.1?topic=functions-dpbinarynodetostring) on the result to translate that binary node to a string with your JSON ascii text.  So something like

    <xsl:variable name="ftp-put-url"  select="'ftp://xxsx:xxrx@192.168.0.1x8/test/test.json'"/>
<xsl:variable name="urlopenResult">
  <dp:url-open target="{$ftp-put-url}" response="responsecode-binary" />
</xsl:variable>
<!-- a file in temporary:///ftpget-urlopenresult.xml will be created using the urlopenResult variable -->
<dp:dump-nodes file="'ftpget-urlopenresult.xml'" nodes="$urlopenResult"/>
<xsl:variable name="jsonPayload" select="dp:binaryNodeToString($urlopenResult/result/binary)" />


    Best Regards,
    Steve Linn



    ------------------------------
    Steve Linn
    Senior Consulting I/T Specialist
    IBM
    ------------------------------

    Original Message