List of usage examples for java.sql ResultSet getSQLXML
SQLXML getSQLXML(String columnLabel) throws SQLException;
ResultSet as a java.sql.SQLXML object in the Java programming language. 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; } } }