Re: Advice for the XML to db problem

From: GIMME (gimme_this_gimme_that_at_yahoo.com)
Date: 11/24/03 

Date: 24 Nov 2003 11:05:13 -0800

If you're using java you can combine JDOM and JDBC techology to
create a method that will use JDBC meta data to fetch column
names from the database and then build JDOM Elements.

That kind of gives you what you want. The ability to make sql
queries and get a List of JDOM Elements or a single JDOM Element.

    public static Element DoQueryAsElement( String sql , String label
)
        throws ChainedException {
        BbConnection dbc = null;
        Element xmlData = null;
        try {
            dbc = new BbConnection();
            dbc.ProcessSQL(sql);
            xmlData = new Element( label + "s");
            while ( dbc.getResultSet().next() ) { // right indent to
make easy to read
int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
Element elm = new Element(label);
for( int i = 1 ; i <= ccnt ; i ++ )
{
    String colName =
dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    elm.addContent(new
Element(colName.toLowerCase()).addContent(content));
}
xmlData.addContent(elm);
            }
        } catch ( Exception ex ) {
            throw ( new ChainedException( ex, "DoQueryAsElement failed
: " + label + ":" + sql ) );
        }
        finally
        {
            try {
                dbc.closeResultSet();
                dbc.closeStatement();
                dbc.closeConnection();
            } catch ( Exception ex ) {
                throw ( new ChainedException( ex, "DoQueryAsElement
failed : " + sql ) );
            }
        }
        return xmlData;
    }

    public static Element DoQuerySelectRow( String sql , String label
)
        throws ChainedException {
        BbConnection dbc = null;
        Element xmlData = null;
        try {
            dbc = new BbConnection();
            dbc.ProcessSQL(sql);
            xmlData = new Element( label );
            dbc.getResultSet().next();
int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
for( int i = 1 ; i <= ccnt ; i ++ )
{
    String colName =
dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    xmlData.addContent(new
Element(colName.toLowerCase()).addContent(content));
}
        } catch ( Exception ex ) {
throw ( new ChainedException( ex, "DoQuerySingleRow failed : " + label
+ ":" + sql ) );
        }
        finally
        {
            try {
                dbc.closeResultSet();
                dbc.closeStatement();
                dbc.closeConnection();
            } catch ( Exception ex ) {
                throw ( new ChainedException( ex, "DoQuerySingleRow
failed : " + sql ) );
            }
        }
        return xmlData;
    }
    public static ArrayList DoQueryAsStringList( String sql )
        throws ChainedException {
        BbConnection dbc = null;
        ArrayList v = new ArrayList();
        try {
            dbc = new BbConnection();
            dbc.ProcessSQL(sql);
            while ( dbc.getResultSet().next() ) {
                v.add(dbc.getResultSet().getString(1));
            }
        } catch ( Exception ex ) {
            throw ( new ChainedException( ex, "DoArrayList failed : "
+ sql ) );
        }
        finally
        {
            try {
                dbc.closeResultSet();
                dbc.closeStatement();
                dbc.closeConnection();
            } catch ( Exception ex ) {
                throw ( new ChainedException( ex, "DoArrayList failed
: " + sql ) );
            }
        }
        return v;
    }

    public static List DoQueryAsElementList( String sql , String label
)
        throws ChainedException {
        BbConnection dbc = null;
        ArrayList v = new ArrayList();
        try {
            dbc = new BbConnection();
            dbc.ProcessSQL(sql);
            while ( dbc.getResultSet().next() ) {
// See note 1 below
int ccnt = dbc.getResultSet().getMetaData().getColumnCount();
Element elm = new Element(label);
for( int i = 1 ; i <= ccnt ; i ++ )
{
    String colName =
dbc.getResultSet().getMetaData().getColumnName(i);
    String content = dbc.SQLHelper(dbc.getResultSet().getString(i));
    elm.addContent(new
Element(colName.toLowerCase()).addContent(content));
}
        v.add(elm);
            }
        } catch ( Exception ex ) {
throw ( new ChainedException( ex, "DoQueryAsElementList failed :(" +
label + "):" + sql ) );
        }
        finally
        {
            try {
                dbc.closeResultSet();
                dbc.closeStatement();
                dbc.closeConnection();
            } catch ( Exception ex ) {
throw ( new ChainedException( ex, "DoQueryAsElementList failed :(" +
label + "):" + sql ) );
            }
        }
        return v;
    }