TecSpy.com Michael Erskine's Domain

package com.tecspy.jdbc3;

import java.io.StringWriter;
import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.rowset.WebRowSet;

import com.sun.rowset.WebRowSetImpl;

/**
 * Handy JDBC 3.0 SQL XML tools using {@link WebRowSet}.

 *
 * @author Michael Erskine (msemtd)
 */
public class SqlXml {

    /**
     * Get an SQL XML value of a {@link WebRowSet} from the given query on the
     * given {@link Connection}.
     *
     * @param con
     *                Established database connection.

     * @param query
     *                SQL query to be performed.
     * @return Resulting SQL XML.
     * @throws SQLException

     */
    public static String getQueryWebRowSetXml(Connection con, String query)

            throws SQLException {
        // Create statement and execute query...
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery(query);
        // Create a WebRowSet and populate with result...
        WebRowSet wrs = new WebRowSetImpl();

        wrs.populate(rs);
        // Get the XML into a String...
        StringWriter sw = new StringWriter();

        wrs.writeXml(sw);
        String result = sw.toString();

        // Clean up what we created...
        rs.close();
        rs = null;
        stmt.close();

        stmt = null;
        return result;
    }

}

package com.tecspy.jdbc3;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Handy JDBC methods.
 *
 * @author Michael Erskine (msemtd)
 */
public class Db {

    /**
     * Make a JDBC connection the old fashioned way.
     *
     * @param szDriver
     *                driver class
     * @param szUrl

     *                url
     * @param szUser
     *                user name
     * @param szPass
     *                password

     * @return established connection
     * @throws DisplayDriverException
     *                 upon failure to connect
     */
    public static Connection dbConnect(String driver, String url, String user,

            String pass) throws SQLException, ClassNotFoundException {
        // Check the driver...
        Class.forName(driver);

        Connection con;
        con = DriverManager.getConnection(url, user, pass);

        return con;
    }

}
package com.tecspy.util;

import java.io.BufferedReader;
import java.io.File;

import java.io.FileReader;
import java.io.FileWriter;

import java.io.IOException;
import java.util.List;

/**
 * Handy file read and write methods.
 * <p>
 * Inspired by Perl's File::Slurp modules.
 *
 * @author Michael Erskine (msemtd)

 */
public class FileUtils {

    /**
     * File slurp - read a complete file into a String.
     *

     * @param fname
     *                File pathname.
     * @return Complete contents of file.
     * @throws IOException

     */
    public static String slurp(String fname) throws IOException {

        StringBuilder buf = new StringBuilder();
        slurp(fname, buf, null);

        return buf.toString();
    }

    /**
     * File slurp - read a complete file into a list of String objects, one per
     * line.
     *

     * @param fname
     *                File pathname.
     * @param list
     *                List to populate.
     * @return Complete contents of file.

     * @throws IOException
     */
    public static List<String> slurp(String fname, List<String> list)

            throws IOException {
        slurp(fname, null, list);

        return list;
    }

    /**
     * Base implementation of file slurp.
     * <p>
     * If provided with a list it will add lines to the list. If provided with a

     * StringBuilder buffer it will append lines to the buffer.
     *
     * @param fname
     *                File pathname.
     * @param buf

     *                Optional buffer.
     * @param list
     *                Optional list.
     * @throws IOException
     */

    public static void slurp(String fname, StringBuilder buf, List<String> list)

            throws IOException {
        BufferedReader r = new BufferedReader(new FileReader(new File(fname)));

        String line = null;
        while ((line = r.readLine()) != null) {

            if (list != null) {
                list.add(line);

                continue;
            }
            if (buf != null) {

                buf.append(line);
                buf.append("\n");
            }
        }

    }

    /**
     * Write a single String to a file.
     *
     * @param outfile
     *                File pathname.

     * @param data
     *                Data to write.
     * @throws IOException
     */
    public static void splurge(String outfile, String data) throws IOException {

        FileWriter w = new FileWriter(outfile);
        w.write(data);

        w.close();
    }

}
package com.tecspy.jdbc3;

import java.io.StringReader;
import java.io.StringWriter;

import java.sql.Connection;

import javax.sql.rowset.WebRowSet;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;

import com.sun.rowset.WebRowSetImpl;
import com.tecspy.util.FileUtils;

import junit.framework.TestCase;

public class SqlXmlTest extends TestCase {

    /** Log4j logger. */
    private static Logger log = Logger.getLogger(SqlXml.class);

    static {
        BasicConfigurator.configure();
    }

    public SqlXmlTest(String name) {

        super(name);
    }

    protected void setUp() throws Exception {}

    protected void tearDown() throws Exception {}

    /**

     * A simple write and read test.
     */
    public final void testGetQueryWebRowSetXml() {

        String driver = "net.sourceforge.jtds.jdbc.Driver";

        String url = "jdbc:jtds:sqlserver://sqlserver/central;socketTimeout=20;appName=msemtd;progName=msemtd;prepareSQL=0";
        String user = "sa";

        String password = "";
        String query = "SELECT *\r\n" + "FROM conf_cache";

        String outfile = "c:\\whatever.xml";

        Connection con = null;

        String dataWritten = null;
        String dataRead = null;

        try {
            con = Db.dbConnect(driver, url, user, password);

            String xml = SqlXml.getQueryWebRowSetXml(con, query);
            log.debug(xml);

            FileUtils.splurge(outfile, xml);
            // take a copy...
            dataWritten = new String(xml);

        } catch (Exception e) {
            log.fatal("write failed:" + e.getMessage(), e);

            fail();
        }

        try {
            String xml = FileUtils.slurp(outfile);

            WebRowSet wrs = new WebRowSetImpl();
            wrs.readXml(new StringReader(xml));

            // to string for comparison...
            StringWriter sw = new StringWriter();
            wrs.writeXml(sw);

            String result = sw.toString();

            log.debug("RESULT = \n" + result);

            // take a copy...
            dataRead = new String(result);

        } catch (Exception e) {

            log.fatal("read failed:" + e.getMessage(), e);
            fail();

        }
        assertEquals(dataWritten, dataRead);
    }

}