Example usage for java.sql ResultSet getSQLXML

List of usage examples for java.sql ResultSet getSQLXML

Introduction

In this page you can find the example usage for java.sql ResultSet getSQLXML.

Prototype

SQLXML getSQLXML(String columnLabel) throws SQLException;

Source Link

Document

Retrieves the value of the designated column in the current row of this ResultSet as a java.sql.SQLXML object in the Java programming language.

Usage

From source file:com.siemens.scr.avt.ad.hibernate.StringXMLType.java

public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
    SQLXML xml = rs.getSQLXML(names[0]);

    return xml.getString();
}

From source file:com.oracle.tutorial.jdbc.RSSFeedsTable.java

public void viewTable(Connection con) throws SQLException, ParserConfigurationException, SAXException,
        IOException, TransformerConfigurationException, TransformerException {
    Statement stmt = null;/*from  w w  w  .ja  va2  s  .c  o  m*/
    try {
        stmt = con.createStatement();
        if (this.dbms.equals("derby")) {
            String query = "select RSS_NAME, xmlserialize (RSS_FEED_XML as clob) from RSS_FEEDS";
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String rssName = rs.getString(1);
                String rssFeedXML = rs.getString(2);
                javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory
                        .newInstance();
                factory.setNamespaceAware(true);
                DocumentBuilder builder = factory.newDocumentBuilder();
                Document doc = builder.parse(new InputSource(new StringReader(rssFeedXML)));
                System.out.println("RSS identifier: " + rssName);
                System.out.println(JDBCTutorialUtilities.convertDocumentToString(doc));
            }
        } else if (this.dbms.equals("mysql")) {
            String query = "select RSS_NAME, RSS_FEED_XML from RSS_FEEDS";
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String rssName = rs.getString(1);
                SQLXML rssFeedXML = rs.getSQLXML(2);
                javax.xml.parsers.DocumentBuilderFactory factory = javax.xml.parsers.DocumentBuilderFactory
                        .newInstance();
                factory.setNamespaceAware(true);
                DocumentBuilder builder = factory.newDocumentBuilder();
                Document doc = builder.parse(rssFeedXML.getBinaryStream());
                System.out.println("RSS identifier: " + rssName);
                System.out.println(JDBCTutorialUtilities.convertDocumentToString(doc));
            }
        }
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

From source file:com.gdcn.modules.db.jdbc.processor.CamelBeanProcessor.java

/**
 * Convert a <code>ResultSet</code> column into an object.  Simple
 * implementations could just call <code>rs.getObject(index)</code> while
 * more complex implementations could perform type manipulation to match
 * the column's type to the bean property type.
 *
 * <p>/*from w  w  w.ja  v a  2s . co  m*/
 * This implementation calls the appropriate <code>ResultSet</code> getter
 * method for the given property type to perform the type conversion.  If
 * the property type doesn't match one of the supported
 * <code>ResultSet</code> types, <code>getObject</code> is called.
 * </p>
 *
 * @param rs The <code>ResultSet</code> currently being processed.  It is
 * positioned on a valid row before being passed into this method.
 *
 * @param index The current column index being processed.
 *
 * @param propType The bean property type that this column needs to be
 * converted into.
 *
 * @throws SQLException if a database access error occurs
 *
 * @return The object from the <code>ResultSet</code> at the given column
 * index after optional type processing or <code>null</code> if the column
 * value was SQL NULL.
 */
protected Object processColumn(ResultSet rs, int index, Class<?> propType) throws SQLException {

    if (!propType.isPrimitive() && rs.getObject(index) == null) {
        return null;
    }

    if (propType.equals(String.class)) {
        return rs.getString(index);

    } else if (propType.equals(Integer.TYPE) || propType.equals(Integer.class)) {
        return Integer.valueOf(rs.getInt(index));

    } else if (propType.equals(Boolean.TYPE) || propType.equals(Boolean.class)) {
        return Boolean.valueOf(rs.getBoolean(index));

    } else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
        return Long.valueOf(rs.getLong(index));

    } else if (propType.equals(Double.TYPE) || propType.equals(Double.class)) {
        return Double.valueOf(rs.getDouble(index));

    } else if (propType.equals(Float.TYPE) || propType.equals(Float.class)) {
        return Float.valueOf(rs.getFloat(index));

    } else if (propType.equals(Short.TYPE) || propType.equals(Short.class)) {
        return Short.valueOf(rs.getShort(index));

    } else if (propType.equals(Byte.TYPE) || propType.equals(Byte.class)) {
        return Byte.valueOf(rs.getByte(index));

    } else if (propType.equals(Timestamp.class)) {
        return rs.getTimestamp(index);

    } else if (propType.equals(SQLXML.class)) {
        return rs.getSQLXML(index);

    } else {
        return rs.getObject(index);
    }

}

From source file:it.greenvulcano.gvesb.datahandling.dbo.utils.StandardRowSetBuilder.java

public int build(Document doc, String id, ResultSet rs, Set<Integer> keyField,
        Map<String, FieldFormatter> fieldNameToFormatter, Map<String, FieldFormatter> fieldIdToFormatter)
        throws Exception {
    if (rs == null) {
        return 0;
    }//w w  w . java  2 s.  c  o  m
    int rowCounter = 0;
    Element docRoot = doc.getDocumentElement();
    ResultSetMetaData metadata = rs.getMetaData();
    FieldFormatter[] fFormatters = buildFormatterArray(metadata, fieldNameToFormatter, fieldIdToFormatter);

    boolean noKey = ((keyField == null) || keyField.isEmpty());

    //boolean isNull = false;
    Element data = null;
    Element row = null;
    Element col = null;
    Text text = null;
    String textVal = null;
    String precKey = null;
    String colKey = null;
    Map<String, String> keyAttr = new HashMap<String, String>();
    while (rs.next()) {
        if (rowCounter % 10 == 0) {
            ThreadUtils.checkInterrupted(getClass().getSimpleName(), name, logger);
        }
        row = parser.createElement(doc, AbstractDBO.ROW_NAME);

        parser.setAttribute(row, AbstractDBO.ID_NAME, id);
        for (int j = 1; j <= metadata.getColumnCount(); j++) {
            FieldFormatter fF = fFormatters[j];

            //isNull = false;
            col = parser.createElement(doc, AbstractDBO.COL_NAME);
            switch (metadata.getColumnType(j)) {
            case Types.DATE: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DATE_TYPE);
                java.sql.Date dateVal = rs.getDate(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_DATE_FORMAT);
            }
                break;
            case Types.TIME: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIME_TYPE);
                java.sql.Time dateVal = rs.getTime(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_TIME_FORMAT);
            }
                break;
            case Types.TIMESTAMP: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.TIMESTAMP_TYPE);
                Timestamp dateVal = rs.getTimestamp(j);
                textVal = processDateTime(col, fF, dateVal, AbstractDBO.DEFAULT_DATE_FORMAT);
            }
                break;
            case Types.DOUBLE: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                double numVal = rs.getDouble(j);
                textVal = processDouble(col, fF, numVal);
            }
                break;
            case Types.FLOAT:
            case Types.REAL: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.FLOAT_TYPE);
                float numVal = rs.getFloat(j);
                textVal = processDouble(col, fF, numVal);
            }
                break;
            case Types.BIGINT: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BIGINT_TYPE);
                long numVal = rs.getLong(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.INTEGER: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.INTEGER_TYPE);
                int numVal = rs.getInt(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.SMALLINT:
            case Types.TINYINT: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.SMALLINT_TYPE);
                short numVal = rs.getShort(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(numVal);
            }
                break;
            case Types.NUMERIC:
            case Types.DECIMAL: {
                BigDecimal bigdecimal = rs.getBigDecimal(j);
                boolean isNull = bigdecimal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                    }
                    textVal = "";
                } else {
                    if (fF != null) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, fF.getNumberFormat());
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, fF.getGroupSeparator());
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, fF.getDecSeparator());
                        textVal = fF.formatNumber(bigdecimal);
                    } else if (metadata.getScale(j) > 0) {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DECIMAL_TYPE);
                        parser.setAttribute(col, AbstractDBO.FORMAT_NAME, numberFormat);
                        parser.setAttribute(col, AbstractDBO.GRP_SEPARATOR_NAME, groupSeparator);
                        parser.setAttribute(col, AbstractDBO.DEC_SEPARATOR_NAME, decSeparator);
                        textVal = numberFormatter.format(bigdecimal);
                    } else {
                        parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NUMERIC_TYPE);
                        textVal = bigdecimal.toString();
                    }
                }
            }
                break;
            case Types.BOOLEAN: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BOOLEAN_TYPE);
                boolean bVal = rs.getBoolean(j);
                parser.setAttribute(col, AbstractDBO.NULL_NAME, "false");
                textVal = String.valueOf(bVal);
            }
                break;
            case Types.SQLXML: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.XML_TYPE);
                SQLXML xml = rs.getSQLXML(j);
                boolean isNull = xml == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    textVal = xml.getString();
                }
            }
                break;
            case Types.NCHAR:
            case Types.NVARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.NSTRING_TYPE);
                textVal = rs.getNString(j);
                if (textVal == null) {
                    textVal = "";
                }
            }
                break;
            case Types.CHAR:
            case Types.VARCHAR: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.STRING_TYPE);
                textVal = rs.getString(j);
                boolean isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
                break;
            case Types.NCLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_NSTRING_TYPE);
                NClob clob = rs.getNClob(j);
                if (clob != null) {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                } else {
                    textVal = "";
                }
            }
                break;
            case Types.CLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.LONG_STRING_TYPE);
                Clob clob = rs.getClob(j);
                if (clob != null) {
                    Reader is = clob.getCharacterStream();
                    StringWriter str = new StringWriter();

                    IOUtils.copy(is, str);
                    is.close();
                    textVal = str.toString();
                } else {
                    textVal = "";
                }
            }
                break;
            case Types.BLOB: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.BASE64_TYPE);
                Blob blob = rs.getBlob(j);
                boolean isNull = blob == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                } else {
                    InputStream is = blob.getBinaryStream();
                    ByteArrayOutputStream baos = new ByteArrayOutputStream();
                    IOUtils.copy(is, baos);
                    is.close();
                    try {
                        byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length());
                        textVal = Base64.getEncoder().encodeToString(buffer);
                    } catch (SQLFeatureNotSupportedException exc) {
                        textVal = Base64.getEncoder().encodeToString(baos.toByteArray());
                    }
                }
            }
                break;
            default: {
                parser.setAttribute(col, AbstractDBO.TYPE_NAME, AbstractDBO.DEFAULT_TYPE);
                textVal = rs.getString(j);
                boolean isNull = textVal == null;
                parser.setAttribute(col, AbstractDBO.NULL_NAME, String.valueOf(isNull));
                if (isNull) {
                    textVal = "";
                }
            }
            }
            if (textVal != null) {
                text = doc.createTextNode(textVal);
                col.appendChild(text);
            }
            if (!noKey && keyField.contains(new Integer(j))) {
                if (textVal != null) {
                    if (colKey == null) {
                        colKey = textVal;
                    } else {
                        colKey += "##" + textVal;
                    }
                    keyAttr.put("key_" + j, textVal);
                }
            } else {
                row.appendChild(col);
            }
        }
        if (noKey) {
            if (data == null) {
                data = parser.createElement(doc, AbstractDBO.DATA_NAME);
                parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            }
        } else if ((colKey != null) && !colKey.equals(precKey)) {
            if (data != null) {
                docRoot.appendChild(data);
            }
            data = parser.createElement(doc, AbstractDBO.DATA_NAME);
            parser.setAttribute(data, AbstractDBO.ID_NAME, id);
            for (Entry<String, String> keyAttrEntry : keyAttr.entrySet()) {
                parser.setAttribute(data, keyAttrEntry.getKey(), keyAttrEntry.getValue());
            }
            keyAttr.clear();
            precKey = colKey;
        }
        colKey = null;
        data.appendChild(row);
        rowCounter++;
    }
    if (data != null) {
        docRoot.appendChild(data);
    }

    return rowCounter;
}

From source file:com.github.woonsan.jdbc.jcr.impl.JcrJdbcResultSetTest.java

@Test
public void testUnsupportedOperations() throws Exception {
    Statement statement = getConnection().createStatement();
    ResultSet rs = statement.executeQuery(SQL_EMPS);

    try {/*from ww w . jav  a  2s .  c  o m*/
        rs.getWarnings();
        fail();
    } catch (UnsupportedOperationException ignore) {
    }

    try {
        rs.clearWarnings();
        fail();
    } catch (UnsupportedOperationException ignore) {
    }

    try {
        rs.getCursorName();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject("ename");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.isLast();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.beforeFirst();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.afterLast();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.first();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.last();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.absolute(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.relative(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.previous();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.moveToCurrentRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNull(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNull("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBoolean(1, true);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBoolean("col1", true);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateByte(1, (byte) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateByte("col1", (byte) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateShort(1, (short) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateShort("col1", (short) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateInt(1, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateInt("col1", 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateLong(1, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateLong("col1", (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateFloat(1, (float) 0.1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateFloat("col1", (float) 0.1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateDouble(1, 0.1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateDouble("col1", 0.1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBigDecimal(1, new BigDecimal("100000000"));
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBigDecimal("col1", new BigDecimal("100000000"));
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateString(1, "Unknown");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateString("col1", "Unknown");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBytes(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBytes("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateDate(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateDate("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateTime(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateTime("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateTimestamp(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateTimestamp("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateObject(1, null, 1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateObject("col1", null, 1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateObject(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateObject("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.insertRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.deleteRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.refreshRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.cancelRowUpdates();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.moveToInsertRow();
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject(1, (Map<String, Class<?>>) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject("col1", (Map<String, Class<?>>) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getRef(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getRef("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getBlob(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getBlob("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getClob(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getClob("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getURL(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getURL("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateRef(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateRef("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob(1, (Blob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob("col1", (Blob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob(1, (Clob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob("col1", (Clob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateArray(1, (Array) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateArray("col1", (Array) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getRowId(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getRowId("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateRowId(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateRowId("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNString(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNString("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob(1, (NClob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob("col1", (NClob) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNClob(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNClob("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getSQLXML(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getSQLXML("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateSQLXML(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateSQLXML("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNString(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNString("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNCharacterStream(1);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getNCharacterStream("col1");
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null, (long) 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob(1, null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob("col1", null, 0);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNCharacterStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateAsciiStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBinaryStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream(1, null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateCharacterStream("col1", null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob(1, (InputStream) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateBlob("col1", (InputStream) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob(1, (Reader) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateClob("col1", (Reader) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob(1, (Reader) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.updateNClob("col1", (Reader) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject(1, (Class<?>) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    try {
        rs.getObject("col1", (Class<?>) null);
        fail();
    } catch (SQLFeatureNotSupportedException ignore) {
    }

    rs.close();
    assertTrue(rs.isClosed());

    statement.close();
    assertTrue(statement.isClosed());
}

From source file:org.exist.xquery.modules.sql.ExecuteFunction.java

/**
 * evaluate the call to the XQuery execute() function, it is really the main entry point of this class.
 *
 * @param   args             arguments from the execute() function call
 * @param   contextSequence  the Context Sequence to operate on (not used here internally!)
 *
 * @return  A node representing the SQL result set
 *
 * @throws  XPathException  DOCUMENT ME!
 *
 * @see     org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], org.exist.xquery.value.Sequence)
 *//* ww  w  . j a  v a 2  s.co m*/
@Override
public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException {
    // was a connection and SQL statement specified?
    if (args[0].isEmpty() || args[1].isEmpty()) {
        return (Sequence.EMPTY_SEQUENCE);
    }

    // get the Connection
    long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong();
    Connection con = SQLModule.retrieveConnection(context, connectionUID);

    if (con == null) {
        return (Sequence.EMPTY_SEQUENCE);
    }

    boolean preparedStmt = false;

    //setup the SQL statement
    String sql = null;
    Statement stmt = null;
    boolean executeResult = false;
    ResultSet rs = null;

    try {
        boolean makeNodeFromColumnName = false;
        MemTreeBuilder builder = context.getDocumentBuilder();
        int iRow = 0;

        //SQL or PreparedStatement?
        if (args.length == 3) {

            // get the SQL statement
            sql = args[1].getStringValue();
            stmt = con.createStatement();
            makeNodeFromColumnName = ((BooleanValue) args[2].itemAt(0)).effectiveBooleanValue();

            //execute the statement
            executeResult = stmt.execute(sql);

        } else if (args.length == 4) {

            preparedStmt = true;

            //get the prepared statement
            long statementUID = ((IntegerValue) args[1].itemAt(0)).getLong();
            PreparedStatementWithSQL stmtWithSQL = SQLModule.retrievePreparedStatement(context, statementUID);
            sql = stmtWithSQL.getSql();
            stmt = stmtWithSQL.getStmt();
            makeNodeFromColumnName = ((BooleanValue) args[3].itemAt(0)).effectiveBooleanValue();

            if (!args[2].isEmpty()) {
                setParametersOnPreparedStatement(stmt, (Element) args[2].itemAt(0));
            }

            //execute the prepared statement
            executeResult = ((PreparedStatement) stmt).execute();
        } else {
            //TODO throw exception
        }

        // DW: stmt can be null ?

        // execute the query statement
        if (executeResult) {
            /* SQL Query returned results */

            // iterate through the result set building an XML document
            rs = stmt.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            int iColumns = rsmd.getColumnCount();

            builder.startDocument();

            builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
            builder.addAttribute(new QName("count", null, null), String.valueOf(-1));

            while (rs.next()) {
                builder.startElement(new QName("row", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
                builder.addAttribute(new QName("index", null, null), String.valueOf(rs.getRow()));

                // get each tuple in the row
                for (int i = 0; i < iColumns; i++) {
                    String columnName = rsmd.getColumnLabel(i + 1);

                    if (columnName != null) {

                        String colElement = "field";

                        if (makeNodeFromColumnName && columnName.length() > 0) {
                            // use column names as the XML node

                            /**
                             * Spaces in column names are replaced with
                             * underscore's
                             */
                            colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_'));
                        }

                        builder.startElement(new QName(colElement, SQLModule.NAMESPACE_URI, SQLModule.PREFIX),
                                null);

                        if (!makeNodeFromColumnName || columnName.length() <= 0) {
                            String name;

                            if (columnName.length() > 0) {
                                name = SQLUtils.escapeXmlAttr(columnName);
                            } else {
                                name = "Column: " + String.valueOf(i + 1);
                            }

                            builder.addAttribute(new QName("name", null, null), name);
                        }

                        builder.addAttribute(
                                new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX),
                                rsmd.getColumnTypeName(i + 1));
                        builder.addAttribute(new QName(TYPE_ATTRIBUTE_NAME, Namespaces.SCHEMA_NS, "xs"),
                                Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1))));

                        //get the content
                        if (rsmd.getColumnType(i + 1) == Types.SQLXML) {
                            //parse sqlxml value
                            try {
                                final SQLXML sqlXml = rs.getSQLXML(i + 1);

                                if (rs.wasNull()) {
                                    // Add a null indicator attribute if the value was SQL Null
                                    builder.addAttribute(
                                            new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX),
                                            "true");
                                } else {

                                    SAXParserFactory factory = SAXParserFactory.newInstance();
                                    factory.setNamespaceAware(true);
                                    InputSource src = new InputSource(sqlXml.getCharacterStream());
                                    SAXParser parser = factory.newSAXParser();
                                    XMLReader xr = parser.getXMLReader();

                                    SAXAdapter adapter = new AppendingSAXAdapter(builder);
                                    xr.setContentHandler(adapter);
                                    xr.setProperty(Namespaces.SAX_LEXICAL_HANDLER, adapter);
                                    xr.parse(src);
                                }
                            } catch (Exception e) {
                                throw new XPathException(
                                        "Could not parse column of type SQLXML: " + e.getMessage(), e);
                            }
                        } else {
                            //otherwise assume string value
                            final String colValue = rs.getString(i + 1);

                            if (rs.wasNull()) {
                                // Add a null indicator attribute if the value was SQL Null
                                builder.addAttribute(
                                        new QName("null", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "true");
                            } else {
                                if (colValue != null) {
                                    builder.characters(SQLUtils.escapeXmlText(colValue));
                                }
                            }
                        }

                        builder.endElement();
                    }
                }

                builder.endElement();
                iRow++;
            }

            builder.endElement();
        } else {
            /* SQL Query performed updates */

            builder.startDocument();

            builder.startElement(new QName("result", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
            builder.addAttribute(new QName("updateCount", null, null), String.valueOf(stmt.getUpdateCount()));
            builder.endElement();
        }

        // Change the root element count attribute to have the correct value
        NodeValue node = (NodeValue) builder.getDocument().getDocumentElement();
        Node count = node.getNode().getAttributes().getNamedItem("count");

        if (count != null) {
            count.setNodeValue(String.valueOf(iRow));
        }

        builder.endDocument();

        // return the XML result set
        return (node);

    } catch (SQLException sqle) {
        LOG.error("sql:execute() Caught SQLException \"" + sqle.getMessage() + "\" for SQL: \"" + sql + "\"",
                sqle);

        //return details about the SQLException
        MemTreeBuilder builder = context.getDocumentBuilder();

        builder.startDocument();
        builder.startElement(new QName("exception", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);

        boolean recoverable = false;

        if (sqle instanceof SQLRecoverableException) {
            recoverable = true;
        }
        builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable));

        builder.startElement(new QName("state", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
        builder.characters(sqle.getSQLState());
        builder.endElement();

        builder.startElement(new QName("message", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);

        String state = sqle.getMessage();

        if (state != null) {
            builder.characters(state);
        }

        builder.endElement();

        builder.startElement(new QName("stack-trace", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
        ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream();
        sqle.printStackTrace(new PrintStream(bufStackTrace));
        builder.characters(new String(bufStackTrace.toByteArray()));
        builder.endElement();

        builder.startElement(new QName("sql", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
        builder.characters(SQLUtils.escapeXmlText(sql));
        builder.endElement();

        if (stmt instanceof PreparedStatement) {
            Element parametersElement = (Element) args[2].itemAt(0);

            if (parametersElement.getNamespaceURI().equals(SQLModule.NAMESPACE_URI)
                    && parametersElement.getLocalName().equals(PARAMETERS_ELEMENT_NAME)) {
                NodeList paramElements = parametersElement.getElementsByTagNameNS(SQLModule.NAMESPACE_URI,
                        PARAM_ELEMENT_NAME);

                builder.startElement(
                        new QName(PARAMETERS_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);

                for (int i = 0; i < paramElements.getLength(); i++) {
                    Element param = ((Element) paramElements.item(i));
                    String value = param.getFirstChild().getNodeValue();
                    String type = param.getAttributeNS(SQLModule.NAMESPACE_URI, TYPE_ATTRIBUTE_NAME);

                    builder.startElement(
                            new QName(PARAM_ELEMENT_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);

                    builder.addAttribute(
                            new QName(TYPE_ATTRIBUTE_NAME, SQLModule.NAMESPACE_URI, SQLModule.PREFIX), type);
                    builder.characters(SQLUtils.escapeXmlText(value));

                    builder.endElement();
                }

                builder.endElement();
            }
        }

        builder.startElement(new QName("xquery", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), null);
        builder.addAttribute(new QName("line", null, null), String.valueOf(getLine()));
        builder.addAttribute(new QName("column", null, null), String.valueOf(getColumn()));
        builder.endElement();

        builder.endElement();
        builder.endDocument();

        return ((NodeValue) builder.getDocument().getDocumentElement());
    } finally {

        // close any record set or statement
        if (rs != null) {

            try {
                rs.close();
            } catch (SQLException se) {
                LOG.warn("Unable to cleanup JDBC results", se);
            }
            rs = null;
        }

        if (!preparedStmt && stmt != null) {

            try {
                stmt.close();
            } catch (SQLException se) {
                LOG.warn("Unable to cleanup JDBC results", se);
            }
            stmt = null;
        }

    }
}