Discussions

XML & Web services: Mapping Sybase to XML

  1. Mapping Sybase to XML (4 messages)

    Hello,
    We have a stand alone Java Application which takes the huge amount of data from Sybase and makes the XML. Thereafter the XML is changed to PDF through Apache FOP.
    Right now the XML is build by iterating the resultsets and appending to the StringBuffer to make a XML file.
    Therefore its taking some 10Hrs to complete the whole procedure.
    Is there any other way I can design this.
    Can we map the XMLs with the ResultSets directly?

    Thanks in Advance!!
    Suhas Shahapurkar

    Threaded Messages (4)

  2. Mapping Sybase to XML[ Go to top ]

    Right now the XML is build by iterating the resultsets and appending to the StringBuffer to make a XML file.Therefore its taking some 10Hrs to complete the whole procedure.
    Your description doesn't give any clue of why the process is slow. Executing a query and processing the ResultSet manually is actually the fastest way imaginable you can do DB access in Java. So the problem may be elsewhere - e.g. inefficient memory use, or suboptimal queries. Why for instance are you using a StringBuffer instead of opening a FileWriter and writing data straight to the disk?

    BTW, you may want to take a look at Cayenne DataPort example (http://objectstyle.org/cayenne/examples/data-port/index.html) that shows how to transfer the data from one DB to another using Cayenne (your case is similar, though the output will be XML). Again, using Cayenne to do such batch operations may help as far as maintainability of the queries, etc., but the reason for performance problems is elsewhere...

    Andrus
  3. Thanks Andrus[ Go to top ]

    Actually the end resultset is coming up with set of 10 Resultsets. There after I am iterating the resultsets and populating the models for all the different 10 resuoltsets. so there are 10 different Models(Java class having getter-setter methods). Thereafter I itertate the Models and use the StringBuffer to make a complete string having the XML. For Ex.:

    stringBuff.append("<parent>")
    stringBuff.append("<child1>XYZ</child1>")
    stringBuff.append("<child2>PQR</child2>")
    stringBuff.append("</parent>")

    Later opening the FileWriter and writing the whole StringBuffer to a file. This building up the XML file and populating the Models is taking lots of time. Can I do in some better ways?

    Thanks in advance.
    Suhas S.
  4. Thanks Andrus[ Go to top ]

    Like I said, try designing your process to work with an open FileWriter instead of sucking the whole DB in memory as a set of objects + a StringBuffer. Why do you even need a StringBuffer step?

    I can't give any specific advice since I don't know your schema, but it seems like your XML is structured as a dependency tree... So maybe instead of doing 10 different ResultSets, "flatten" your SQL queries, by joining at least some of the related tables. This way DB will link the related records together and you won't have to do it in memory. Let the Sybase do the work!
  5. jdbc to xml[ Go to top ]

    i know jsqlmapper can make xml out of your relational data. http://www.jnetdirect.com/products.php?op=jsqlmapper
    it does the mapping of the resultset and sub-queries for you, try it too see if it can handle the kind of load you want...