List of usage examples for java.sql SQLXML getCharacterStream
Reader getCharacterStream() throws SQLException;
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) *//*w w w . j ava2 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; } } }