0001: /*
0002: * Licensed to the Apache Software Foundation (ASF) under one or more
0003: * contributor license agreements. See the NOTICE file distributed with
0004: * this work for additional information regarding copyright ownership.
0005: * The ASF licenses this file to You under the Apache License, Version 2.0
0006: * (the "License"); you may not use this file except in compliance with
0007: * the License. You may obtain a copy of the License at
0008: *
0009: * http://www.apache.org/licenses/LICENSE-2.0
0010: *
0011: * Unless required by applicable law or agreed to in writing, software
0012: * distributed under the License is distributed on an "AS IS" BASIS,
0013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014: * See the License for the specific language governing permissions and
0015: * limitations under the License.
0016: */
0017: package org.apache.cocoon.transformation;
0018:
0019: import java.io.IOException;
0020: import java.io.InputStream;
0021: import java.io.Reader;
0022: import java.io.StringReader;
0023: import java.lang.reflect.Field;
0024: import java.sql.CallableStatement;
0025: import java.sql.Clob;
0026: import java.sql.Connection;
0027: import java.sql.DriverManager;
0028: import java.sql.PreparedStatement;
0029: import java.sql.ResultSet;
0030: import java.sql.ResultSetMetaData;
0031: import java.sql.SQLException;
0032: import java.util.ArrayList;
0033: import java.util.HashMap;
0034: import java.util.Iterator;
0035: import java.util.List;
0036: import java.util.Map;
0037: import java.util.Properties;
0038: import java.util.TreeMap;
0039:
0040: import org.apache.avalon.excalibur.datasource.DataSourceComponent;
0041: import org.apache.avalon.framework.configuration.Configuration;
0042: import org.apache.avalon.framework.configuration.ConfigurationException;
0043: import org.apache.avalon.framework.logger.AbstractLogEnabled;
0044: import org.apache.avalon.framework.parameters.Parameters;
0045: import org.apache.avalon.framework.service.ServiceException;
0046: import org.apache.avalon.framework.service.ServiceManager;
0047: import org.apache.avalon.framework.service.ServiceSelector;
0048: import org.apache.excalibur.xml.sax.SAXParser;
0049:
0050: import org.apache.cocoon.ProcessingException;
0051: import org.apache.cocoon.components.sax.XMLDeserializer;
0052: import org.apache.cocoon.components.sax.XMLSerializer;
0053: import org.apache.cocoon.environment.SourceResolver;
0054: import org.apache.cocoon.transformation.helpers.TextRecorder;
0055: import org.apache.cocoon.xml.IncludeXMLConsumer;
0056:
0057: import org.apache.commons.lang.StringEscapeUtils;
0058: import org.apache.commons.lang.StringUtils;
0059: import org.xml.sax.Attributes;
0060: import org.xml.sax.InputSource;
0061: import org.xml.sax.SAXException;
0062: import org.xml.sax.helpers.AttributesImpl;
0063:
0064: /**
0065: * The <code>SQLTransformer</code> can be plugged into a pipeline to transform
0066: * SAX events into updated or queries and responses to/from a SQL interface.
0067: *
0068: * <p>
0069: * It is declared and configured as follows:
0070: * <pre>
0071: * <map:transformers default="...">
0072: * <map:transformer name="sql" src="org.apache.cocoon.transformation.SQLTransformer">
0073: * <old-driver>false</old-driver>
0074: * <connection-attempts>5</connection-attempts>
0075: * <connection-waittime>5000</connection-waittime>
0076: * </map:transformer>
0077: * </map:transformers>
0078: * </pre>
0079: * </p>
0080: *
0081: * <p>
0082: * It can be used in the sitemap pipeline as follows:
0083: * <code>
0084: * <map:transform type="sql">
0085: * <!-- True to force each query to create its own connection: -->
0086: * <map:parameter name="own-connection" value="..."/>
0087: * <!-- Specify either name of datasource: -->
0088: * <map:parameter name="use-connection" value="..."/>
0089: * <!-- Or connection parameters: -->
0090: * <map:parameter name="dburl" value="..."/>
0091: * <map:parameter name="username" value="..."/>
0092: * <map:parameter name="password" value="..."/>
0093: *
0094: * <!-- Default query parameters: -->
0095: * <map:parameter name="show-nr-or-rows" value="false"/>
0096: * <map:parameter name="doc-element" value="rowset"/>
0097: * <map:parameter name="row-element" value="row"/>
0098: * <map:parameter name="namespace-uri" value="http://apache.org/cocoon/SQL/2.0"/>
0099: * <map:parameter name="namespace-prefix" value="sql"/>
0100: * <map:parameter name="clob-encoding" value=""/>
0101: * </map:transform>
0102: * </pre>
0103: * </p>
0104: *
0105: * <p>
0106: * The following DTD is valid:
0107: * <code>
0108: * <!ENTITY % param "(own-connection?,(use-connection|(dburl,username,password))?,show-nr-or-rows?,doc-element?,row-element?,namespace-uri?,namespace-prefix?,clob-encoding?)"><br>
0109: * <!ELEMENT execute-query (query,(in-parameter|in-xml-parameter|out-parameter)*,execute-query?, %param;)><br>
0110: * <!ELEMENT own-connection (#PCDATA)><br>
0111: * <!ELEMENT use-connection (#PCDATA)><br>
0112: * <!ELEMENT query (#PCDATA | substitute-value | ancestor-value | escape-string| xml)*><br>
0113: * <!ATTLIST query name CDATA #IMPLIED isstoredprocedure (true|false) "false" isupdate (true|false) "false"><br>
0114: * <!ELEMENT substitute-value EMPTY><br>
0115: * <!ATTLIST substitute-value name CDATA #REQUIRED><br>
0116: * <!ELEMENT ancestor-value EMPTY><br>
0117: * <!ATTLIST ancestor-value name CDATA #REQUIRED level CDATA #REQUIRED><br>
0118: * <!ELEMENT in-parameter EMPTY><br>
0119: * <!ATTLIST in-parameter nr CDATA #REQUIRED type CDATA #REQUIRED><br>
0120: * <!ELEMENT in-xml-parameter EMPTY><br>
0121: * <!ATTLIST in-xml-parameter nr CDATA #REQUIRED type CDATA #REQUIRED><br>
0122: * <!ELEMENT out-parameter EMPTY><br>
0123: * <!ATTLIST out-parameter nr CDATA #REQUIRED name CDATA #REQUIRED type CDATA #REQUIRED><br>
0124: * <!ELEMENT escape-string (#PCDATA)><br>
0125: * <!ELEMENT xml (#PCDATA)><br>
0126: * </code>
0127: * </p>
0128: *
0129: * <p>
0130: * Each query can override default transformer parameters. Nested queries do not inherit parent
0131: * query parameters, but only transformer parameters. Each query can have connection to different
0132: * database, directly or using the connection pool. If database connection parameters are the same
0133: * as for any of the ancestor queries, nested query will re-use ancestor query connection.
0134: * </p>
0135: *
0136: * <p>
0137: * Connection sharing between queries can be disabled, globally or on per-query basis, using
0138: * <code>own-connection</code> parameter.
0139: * </p>
0140: *
0141: * <p>
0142: * By default, CLOBs are read from the database using getSubString, so that character
0143: * decoding is performed by the database. Using <code>clob-encoding</code> parameter,
0144: * this behavior can be overrided, so that data is read as byte stream and decoded using
0145: * specified character encoding.
0146: * </p>
0147: *
0148: * <p>
0149: * Inserting of XML data can be done by using the new sql:xml or SQL:in-xml-parameter tags.
0150: * - sql:xml must be used like sql:escape-string
0151: * - sql:in-xml-parameter must be used like sql:in-parameter.
0152: * </p>
0153: *
0154: * @author <a href="mailto:cziegeler@apache.org">Carsten Ziegeler</a>
0155: * @author <a href="mailto:balld@webslingerZ.com">Donald Ball</a>
0156: * @author <a href="mailto:giacomo.pati@pwr.ch">Giacomo Pati</a>
0157: * (PWR Organisation & Entwicklung)
0158: * @author <a href="mailto:sven.beauprez@the-ecorp.com">Sven Beauprez</a>
0159: * @author <a href="mailto:a.saglimbeni@pro-netics.com">Alfio Saglimbeni</a>
0160: * @author <a href="mailto:pmhahn@titan.lahn.de">Philipp Hahn</a>
0161: * @author <a href="mailto:vgritsenko@apache.org">Vadim Gritsenko</a>
0162: * @version $Id: SQLTransformer.java 467820 2006-10-26 00:33:41Z vgritsenko $
0163: */
0164: public class SQLTransformer extends AbstractSAXTransformer {
0165:
0166: private static final int BUFFER_SIZE = 1024;
0167:
0168: /** The SQL transformer namespace */
0169: public static final String NAMESPACE = "http://apache.org/cocoon/SQL/2.0";
0170:
0171: // The SQL trasformer namespace element names
0172: public static final String MAGIC_EXECUTE_QUERY = "execute-query";
0173: private static final String MAGIC_OWN_CONNECTION = "own-connection";
0174: public static final String MAGIC_CONNECTION = "use-connection";
0175: public static final String MAGIC_DBURL = "dburl";
0176: public static final String MAGIC_USERNAME = "username";
0177: public static final String MAGIC_PASSWORD = "password";
0178: public static final String MAGIC_PROP = "prop";
0179: public static final String MAGIC_NR_OF_ROWS = "show-nr-of-rows";
0180: public static final String MAGIC_QUERY = "query";
0181: public static final String MAGIC_VALUE = "value";
0182: public static final String MAGIC_COLUMN_CASE = "column-case";
0183: public static final String MAGIC_DOC_ELEMENT = "doc-element";
0184: public static final String MAGIC_ROW_ELEMENT = "row-element";
0185: public static final String MAGIC_IN_PARAMETER = "in-parameter";
0186: public static final String MAGIC_IN_PARAMETER_NR_ATTRIBUTE = "nr";
0187: public static final String MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE = "value";
0188: public static final String MAGIC_OUT_PARAMETER = "out-parameter";
0189: public static final String MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE = "name";
0190: public static final String MAGIC_OUT_PARAMETER_NR_ATTRIBUTE = "nr";
0191: public static final String MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE = "type";
0192: public static final String MAGIC_ESCAPE_STRING = "escape-string";
0193: public static final String MAGIC_XML = "xml";
0194: public static final String MAGIC_IN_XML_PARAMETER = "in-xml-parameter";
0195: public static final String MAGIC_ERROR = "error";
0196:
0197: public static final String MAGIC_NS_URI_ELEMENT = "namespace-uri";
0198: public static final String MAGIC_NS_PREFIX_ELEMENT = "namespace-prefix";
0199:
0200: public static final String MAGIC_ANCESTOR_VALUE = "ancestor-value";
0201: public static final String MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE = "level";
0202: public static final String MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE = "name";
0203: public static final String MAGIC_SUBSTITUTE_VALUE = "substitute-value";
0204: public static final String MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE = "name";
0205: public static final String MAGIC_NAME_ATTRIBUTE = "name";
0206: public static final String MAGIC_STORED_PROCEDURE_ATTRIBUTE = "isstoredprocedure";
0207: public static final String MAGIC_UPDATE_ATTRIBUTE = "isupdate";
0208: public static final String CLOB_ENCODING = "clob-encoding";
0209:
0210: // The states we are allowed to be in
0211: protected static final int STATE_OUTSIDE = 0;
0212: protected static final int STATE_INSIDE_EXECUTE_QUERY_ELEMENT = 1;
0213: protected static final int STATE_INSIDE_VALUE_ELEMENT = 2;
0214: protected static final int STATE_INSIDE_QUERY_ELEMENT = 3;
0215: protected static final int STATE_INSIDE_ANCESTOR_VALUE_ELEMENT = 4;
0216: protected static final int STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT = 5;
0217: protected static final int STATE_INSIDE_IN_PARAMETER_ELEMENT = 6;
0218: protected static final int STATE_INSIDE_OUT_PARAMETER_ELEMENT = 7;
0219: protected static final int STATE_INSIDE_ESCAPE_STRING = 8;
0220: protected static final int STATE_INSIDE_XML = 9;
0221: protected static final int STATE_INSIDE_IN_XML_PARAMETER_ELEMENT = 10;
0222:
0223: //
0224: // Configuration
0225: //
0226:
0227: /** Is the old-driver turned on? (default is off) */
0228: protected boolean oldDriver;
0229:
0230: /** How many connection attempts to do? (default is 5 times) */
0231: protected int connectAttempts;
0232:
0233: /** How long wait between connection attempts? (default is 5000 ms) */
0234: protected int connectWaittime;
0235:
0236: //
0237: // State
0238: //
0239:
0240: /** The current query we are working on */
0241: protected Query query;
0242:
0243: /** The current state of the event receiving FSM */
0244: protected int state;
0245:
0246: /** The datasource component selector */
0247: protected ServiceSelector datasources;
0248:
0249: /** The "name" of the connection shared by top level queries (if configuration allows) */
0250: protected String connName;
0251:
0252: /** The connection shared by top level queries (if configuration allows) */
0253: protected Connection conn;
0254:
0255: // Used to parse XML from database.
0256: protected XMLSerializer compiler;
0257: protected XMLDeserializer interpreter;
0258: protected SAXParser parser;
0259:
0260: /**
0261: * Constructor
0262: */
0263: public SQLTransformer() {
0264: super .defaultNamespaceURI = NAMESPACE;
0265: }
0266:
0267: //
0268: // Lifecycle Methods
0269: //
0270:
0271: /**
0272: * Serviceable
0273: */
0274: public void service(ServiceManager manager) throws ServiceException {
0275: super .service(manager);
0276: try {
0277: this .datasources = (ServiceSelector) manager
0278: .lookup(DataSourceComponent.ROLE + "Selector");
0279: } catch (ServiceException e) {
0280: getLogger().warn(
0281: "DataSource component selector is not available.",
0282: e);
0283: }
0284: }
0285:
0286: /**
0287: * Configure transformer. Supported configuration elements:
0288: * <ul>
0289: * <li>old-driver</li>
0290: * <li>connect-attempts</li>
0291: * <li>connect-waittime</li>
0292: * </ul>
0293: */
0294: public void configure(Configuration conf)
0295: throws ConfigurationException {
0296: super .configure(conf);
0297:
0298: this .oldDriver = conf.getChild("old-driver").getValueAsBoolean(
0299: false);
0300: if (getLogger().isDebugEnabled()) {
0301: getLogger().debug(
0302: "Value for old-driver is " + this .oldDriver);
0303: }
0304:
0305: this .connectAttempts = conf.getChild("connect-attempts")
0306: .getValueAsInteger(5);
0307: this .connectWaittime = conf.getChild("connect-waittime")
0308: .getValueAsInteger(5000);
0309: }
0310:
0311: /**
0312: * Setup for the current request.
0313: */
0314: public void setup(SourceResolver resolver, Map objectModel,
0315: String source, Parameters parameters)
0316: throws ProcessingException, SAXException, IOException {
0317: super .setup(resolver, objectModel, source, parameters);
0318:
0319: // Setup instance variables
0320: this .state = SQLTransformer.STATE_OUTSIDE;
0321: this .connName = name(super .parameters);
0322: }
0323:
0324: /**
0325: * Recycle this component
0326: */
0327: public void recycle() {
0328: this .query = null;
0329: try {
0330: // Close the connection used by all top level queries
0331: if (this .conn != null) {
0332: this .conn.close();
0333: this .conn = null;
0334: }
0335: } catch (SQLException e) {
0336: getLogger().info("Could not close connection", e);
0337: }
0338: this .connName = null;
0339:
0340: this .manager.release(this .parser);
0341: this .parser = null;
0342: this .manager.release(this .compiler);
0343: this .compiler = null;
0344: this .manager.release(this .interpreter);
0345: this .interpreter = null;
0346:
0347: super .recycle();
0348: }
0349:
0350: /**
0351: * Dispose
0352: */
0353: public void dispose() {
0354: if (this .datasources != null) {
0355: this .manager.release(this .datasources);
0356: this .datasources = null;
0357: }
0358: super .dispose();
0359: }
0360:
0361: /**
0362: * Return attribute value.
0363: * First try non-namespaced attribute, then try this transformer namespace.
0364: * @param name local attribute name
0365: */
0366: private String getAttributeValue(Attributes attr, String name) {
0367: String value = attr.getValue("", name);
0368: if (value == null) {
0369: value = attr.getValue(this .namespaceURI, name);
0370: }
0371:
0372: return value;
0373: }
0374:
0375: //
0376: // SAX Events Handlers
0377: //
0378:
0379: protected static void throwIllegalStateException(String message) {
0380: throw new IllegalStateException("Illegal state: " + message);
0381: }
0382:
0383: /** <execute-query> */
0384: protected void startExecuteQueryElement() {
0385: switch (state) {
0386: case SQLTransformer.STATE_OUTSIDE:
0387: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0388: // Create root query (if query == null), or child query
0389: this .query = new Query(this .query);
0390: this .query.enableLogging(getLogger()
0391: .getChildLogger("query"));
0392: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0393: break;
0394:
0395: default:
0396: throwIllegalStateException("Not expecting a start execute query element");
0397: }
0398: }
0399:
0400: /** <*> */
0401: protected void startValueElement(String name) throws SAXException {
0402: switch (state) {
0403: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0404: this .stack.push(name);
0405: startTextRecording();
0406: state = SQLTransformer.STATE_INSIDE_VALUE_ELEMENT;
0407: break;
0408:
0409: default:
0410: throwIllegalStateException("Not expecting a start value element: "
0411: + name);
0412: }
0413: }
0414:
0415: /** <query> */
0416: protected void startQueryElement(Attributes attributes)
0417: throws SAXException {
0418: switch (state) {
0419: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0420: startTextRecording();
0421: state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
0422:
0423: String isUpdate = attributes.getValue("",
0424: SQLTransformer.MAGIC_UPDATE_ATTRIBUTE);
0425: if (isUpdate != null && !isUpdate.equalsIgnoreCase("false")) {
0426: query.setUpdate(true);
0427: }
0428:
0429: String isProcedure = attributes.getValue("",
0430: SQLTransformer.MAGIC_STORED_PROCEDURE_ATTRIBUTE);
0431: if (isProcedure != null
0432: && !isProcedure.equalsIgnoreCase("false")) {
0433: query.setStoredProcedure(true);
0434: }
0435:
0436: String name = attributes.getValue("",
0437: SQLTransformer.MAGIC_NAME_ATTRIBUTE);
0438: if (name != null) {
0439: query.setName(name);
0440: }
0441: break;
0442:
0443: default:
0444: throwIllegalStateException("Not expecting a start query element");
0445: }
0446: }
0447:
0448: /** </query> */
0449: protected void endQueryElement() throws ProcessingException,
0450: SAXException {
0451: switch (state) {
0452: case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
0453: final String value = endTextRecording();
0454: if (value.length() > 0) {
0455: query.addQueryPart(value);
0456: }
0457: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0458: break;
0459:
0460: default:
0461: throwIllegalStateException("Not expecting a stop query element");
0462: }
0463: }
0464:
0465: /** </*> */
0466: protected void endValueElement() throws SAXException {
0467: switch (state) {
0468: case SQLTransformer.STATE_INSIDE_VALUE_ELEMENT:
0469: final String name = (String) this .stack.pop();
0470: final String value = endTextRecording();
0471: query.setParameter(name, value);
0472: this .state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0473: break;
0474:
0475: default:
0476: throwIllegalStateException("Not expecting an end value element");
0477: }
0478: }
0479:
0480: /** </execute-query> */
0481: protected void endExecuteQueryElement() throws SAXException {
0482: switch (state) {
0483: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0484: if (query.parent == null) {
0485: query.executeQuery();
0486: query = null;
0487: state = SQLTransformer.STATE_OUTSIDE;
0488: } else {
0489: query.parent.addNestedQuery(query);
0490: query = query.parent;
0491: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0492: }
0493: break;
0494:
0495: default:
0496: throwIllegalStateException("Not expecting an end execute query element");
0497: }
0498: }
0499:
0500: /** <ancestor-value> */
0501: protected void startAncestorValueElement(Attributes attributes)
0502: throws ProcessingException, SAXException {
0503: switch (state) {
0504: case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
0505: int level = 0;
0506: try {
0507: level = Integer
0508: .parseInt(getAttributeValue(
0509: attributes,
0510: SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE));
0511: } catch (Exception e) {
0512: getLogger()
0513: .debug(
0514: "Invalid or missing value for "
0515: + SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE
0516: + " attribute", e);
0517: throwIllegalStateException("Ancestor value elements must have a "
0518: + SQLTransformer.MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE
0519: + " attribute");
0520: }
0521:
0522: String name = getAttributeValue(attributes,
0523: SQLTransformer.MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE);
0524: if (name == null) {
0525: throwIllegalStateException("Ancestor value elements must have a "
0526: + SQLTransformer.MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE
0527: + " attribute");
0528: }
0529:
0530: final String value = endTextRecording();
0531: if (value.length() > 0) {
0532: query.addQueryPart(value);
0533: }
0534: query.addQueryPart(new AncestorValue(level, name));
0535: startTextRecording();
0536:
0537: state = SQLTransformer.STATE_INSIDE_ANCESTOR_VALUE_ELEMENT;
0538: break;
0539: default:
0540: throwIllegalStateException("Not expecting a start ancestor value element");
0541: }
0542: }
0543:
0544: /** </ancestor-value> */
0545: protected void endAncestorValueElement() {
0546: state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
0547: }
0548:
0549: /** <substitute-value> */
0550: protected void startSubstituteValueElement(Attributes attributes)
0551: throws ProcessingException, SAXException {
0552: switch (state) {
0553: case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
0554: String name = getAttributeValue(
0555: attributes,
0556: SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE);
0557: if (name == null) {
0558: throwIllegalStateException("Substitute value elements must have a "
0559: + SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE
0560: + " attribute");
0561: }
0562: String substitute = parameters.getParameter(name, null);
0563: // Escape single quote
0564: substitute = StringEscapeUtils.escapeSql(substitute);
0565:
0566: final String value = endTextRecording();
0567: if (value.length() > 0) {
0568: query.addQueryPart(value);
0569: }
0570: query.addQueryPart(substitute);
0571: startTextRecording();
0572:
0573: state = SQLTransformer.STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT;
0574: break;
0575:
0576: default:
0577: throwIllegalStateException("Not expecting a start substitute value element");
0578: }
0579: }
0580:
0581: /** </substitute-value> */
0582: protected void endSubstituteValueElement() {
0583: state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
0584: }
0585:
0586: /** <escape-string> */
0587: protected void startEscapeStringElement(Attributes attributes)
0588: throws ProcessingException, SAXException {
0589: switch (state) {
0590: case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
0591: final String value = endTextRecording();
0592: if (value.length() > 0) {
0593: query.addQueryPart(value);
0594: }
0595: startTextRecording();
0596:
0597: state = SQLTransformer.STATE_INSIDE_ESCAPE_STRING;
0598: break;
0599:
0600: default:
0601: throwIllegalStateException("Not expecting a start escape-string element");
0602: }
0603: }
0604:
0605: /** </escape-string> */
0606: protected void endEscapeStringElement() throws SAXException {
0607: switch (state) {
0608: case SQLTransformer.STATE_INSIDE_ESCAPE_STRING:
0609: String value = endTextRecording();
0610: if (value.length() > 0) {
0611: value = StringEscapeUtils.escapeSql(value);
0612: value = StringUtils.replace(value, "\\", "\\\\");
0613: query.addQueryPart(value);
0614: }
0615: startTextRecording();
0616: state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
0617: break;
0618:
0619: default:
0620: throwIllegalStateException("Not expecting a end escape-string element");
0621: }
0622: }
0623:
0624: /** <xml> */
0625: protected void startXmlElement(Attributes attributes)
0626: throws ProcessingException, SAXException {
0627: switch (state) {
0628: case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
0629: final String value = endTextRecording();
0630: if (value.length() > 0) {
0631: query.addQueryPart(value);
0632: }
0633: startSerializedXMLRecording(null);
0634:
0635: state = SQLTransformer.STATE_INSIDE_XML;
0636: //this.getLogger().debug("startXmlElement: ");
0637: break;
0638:
0639: default:
0640: throwIllegalStateException("Not expecting a start escape-string element");
0641: }
0642: }
0643:
0644: /** </xml> */
0645: protected void endXmlElement() throws ProcessingException,
0646: SAXException {
0647: switch (state) {
0648: case SQLTransformer.STATE_INSIDE_XML:
0649: String value = endSerializedXMLRecording();
0650: //this.getLogger().debug("endXmlElement: " + value);
0651: if (value.length() > 0) {
0652: value = StringEscapeUtils.escapeSql(value);
0653: query.addQueryPart(value);
0654: }
0655: startTextRecording();
0656: state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
0657: break;
0658:
0659: default:
0660: throwIllegalStateException("Not expecting a end escape-string element");
0661: }
0662: }
0663:
0664: /** <xml> */
0665: protected void startInXmlParameterElement(Attributes attributes)
0666: throws ProcessingException, SAXException {
0667: switch (state) {
0668: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0669: String nr = getAttributeValue(attributes,
0670: SQLTransformer.MAGIC_IN_PARAMETER_NR_ATTRIBUTE);
0671: this .stack.push(nr);
0672: startSerializedXMLRecording(null);
0673:
0674: state = SQLTransformer.STATE_INSIDE_IN_XML_PARAMETER_ELEMENT;
0675: //this.getLogger().debug("startXmlElement: ");
0676: break;
0677:
0678: default:
0679: throwIllegalStateException("Not expecting a start escape-string element");
0680: }
0681: }
0682:
0683: /** </xml> */
0684: protected void endInXmlParameterElement()
0685: throws ProcessingException, SAXException {
0686: switch (state) {
0687: case SQLTransformer.STATE_INSIDE_IN_XML_PARAMETER_ELEMENT:
0688: String value = endSerializedXMLRecording();
0689: //this.getLogger().debug("endXmlElement: "+value);
0690: if (value.length() > 0) {
0691: int position = Integer.parseInt((String) this .stack
0692: .pop());
0693: query.setInXmlParameter(position, value);
0694: }
0695: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0696: break;
0697:
0698: default:
0699: throwIllegalStateException("Not expecting a end escape-string element");
0700: }
0701: }
0702:
0703: /** <in-parameter> */
0704: protected void startInParameterElement(Attributes attributes) {
0705: switch (state) {
0706: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0707: String nr = getAttributeValue(attributes,
0708: SQLTransformer.MAGIC_IN_PARAMETER_NR_ATTRIBUTE);
0709: String value = getAttributeValue(attributes,
0710: SQLTransformer.MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE);
0711: if (getLogger().isDebugEnabled()) {
0712: getLogger().debug(
0713: "IN PARAMETER NR " + nr + "; VALUE " + value);
0714: }
0715:
0716: int position = Integer.parseInt(nr);
0717: query.setInParameter(position, value);
0718: state = SQLTransformer.STATE_INSIDE_IN_PARAMETER_ELEMENT;
0719: break;
0720:
0721: default:
0722: throwIllegalStateException("Not expecting an in-parameter element");
0723: }
0724: }
0725:
0726: /** </in-parameter> */
0727: protected void endInParameterElement() {
0728: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0729: }
0730:
0731: /** <out-parameter> */
0732: protected void startOutParameterElement(Attributes attributes) {
0733: switch (state) {
0734: case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
0735: String name = getAttributeValue(attributes,
0736: SQLTransformer.MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE);
0737: String nr = getAttributeValue(attributes,
0738: SQLTransformer.MAGIC_OUT_PARAMETER_NR_ATTRIBUTE);
0739: String type = getAttributeValue(attributes,
0740: SQLTransformer.MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE);
0741: if (getLogger().isDebugEnabled()) {
0742: getLogger().debug(
0743: "OUT PARAMETER NAME" + name + ";NR " + nr
0744: + "; TYPE " + type);
0745: }
0746:
0747: int position = Integer.parseInt(nr);
0748: query.setOutParameter(position, type, name);
0749: state = SQLTransformer.STATE_INSIDE_OUT_PARAMETER_ELEMENT;
0750: break;
0751:
0752: default:
0753: throwIllegalStateException("Not expecting an out-parameter element");
0754: }
0755: }
0756:
0757: /** </out-parameter> */
0758: protected void endOutParameterElement() {
0759: state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
0760: }
0761:
0762: /**
0763: * ContentHandler method
0764: */
0765: public void startTransformingElement(String uri, String name,
0766: String raw, Attributes attributes)
0767: throws ProcessingException, SAXException {
0768: if (name.equals(SQLTransformer.MAGIC_EXECUTE_QUERY)) {
0769: startExecuteQueryElement();
0770: } else if (name.equals(SQLTransformer.MAGIC_QUERY)) {
0771: startQueryElement(attributes);
0772: } else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
0773: startAncestorValueElement(attributes);
0774: } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
0775: startSubstituteValueElement(attributes);
0776: } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
0777: startInParameterElement(attributes);
0778: } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
0779: startOutParameterElement(attributes);
0780: } else if (name.equals(SQLTransformer.MAGIC_ESCAPE_STRING)) {
0781: startEscapeStringElement(attributes);
0782: } else if (name.equals(SQLTransformer.MAGIC_XML)) {
0783: startXmlElement(attributes);
0784: } else if (name.equals(SQLTransformer.MAGIC_IN_XML_PARAMETER)) {
0785: startInXmlParameterElement(attributes);
0786: } else {
0787: startValueElement(name);
0788: }
0789: }
0790:
0791: /**
0792: * ContentHandler method
0793: */
0794: public void endTransformingElement(String uri, String name,
0795: String raw) throws ProcessingException, IOException,
0796: SAXException {
0797: if (name.equals(SQLTransformer.MAGIC_EXECUTE_QUERY)) {
0798: endExecuteQueryElement();
0799: } else if (name.equals(SQLTransformer.MAGIC_QUERY)) {
0800: endQueryElement();
0801: } else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
0802: endAncestorValueElement();
0803: } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
0804: endSubstituteValueElement();
0805: } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
0806: endInParameterElement();
0807: } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
0808: endOutParameterElement();
0809: } else if (name.equals(SQLTransformer.MAGIC_ESCAPE_STRING)) {
0810: endEscapeStringElement();
0811: } else if (name.equals(SQLTransformer.MAGIC_XML)) {
0812: endXmlElement();
0813: } else if (name.equals(SQLTransformer.MAGIC_IN_XML_PARAMETER)) {
0814: endInXmlParameterElement();
0815: } else {
0816: endValueElement();
0817: }
0818: }
0819:
0820: //
0821: // Helper methods for the Query
0822: //
0823:
0824: /**
0825: * Qualifies an element name by giving it a prefix.
0826: * @param name the element name
0827: * @param prefix the prefix to qualify with
0828: * @return a namespace qualified name that is correct
0829: */
0830: protected String nsQualify(String name, String prefix) {
0831: if (StringUtils.isEmpty(name)) {
0832: return name;
0833: }
0834:
0835: if (StringUtils.isNotEmpty(prefix)) {
0836: return prefix + ":" + name;
0837: }
0838:
0839: return name;
0840: }
0841:
0842: /**
0843: * Helper method for generating SAX events
0844: */
0845: protected void start(String uri, String prefix, String name,
0846: Attributes attr) throws SAXException {
0847: try {
0848: super .startTransformingElement(uri, name, nsQualify(name,
0849: prefix), attr);
0850: } catch (IOException e) {
0851: throw new SAXException(e);
0852: } catch (ProcessingException e) {
0853: throw new SAXException(e);
0854: }
0855: }
0856:
0857: /**
0858: * Helper method for generating SAX events
0859: */
0860: protected void end(String uri, String prefix, String name)
0861: throws SAXException {
0862: try {
0863: super .endTransformingElement(uri, name, nsQualify(name,
0864: prefix));
0865: } catch (IOException e) {
0866: throw new SAXException(e);
0867: } catch (ProcessingException e) {
0868: throw new SAXException(e);
0869: }
0870: }
0871:
0872: /**
0873: * Helper method for generating SAX events
0874: */
0875: protected void data(String data) throws SAXException {
0876: if (data != null) {
0877: super .characters(data.toCharArray(), 0, data.length());
0878: }
0879: }
0880:
0881: /**
0882: * Get 'name' for the connection which can be obtained using provided
0883: * connection parameters.
0884: */
0885: private String name(Parameters params) {
0886: final boolean ownConnection = params.getParameterAsBoolean(
0887: SQLTransformer.MAGIC_OWN_CONNECTION, false);
0888: if (ownConnection) {
0889: return null;
0890: }
0891:
0892: final String datasourceName = params.getParameter(
0893: SQLTransformer.MAGIC_CONNECTION, null);
0894: if (datasourceName != null) {
0895: return "ds:" + datasourceName;
0896: }
0897:
0898: final String dburl = params.getParameter(
0899: SQLTransformer.MAGIC_DBURL, null);
0900: if (dburl != null) {
0901: final String username = params.getParameter(
0902: SQLTransformer.MAGIC_USERNAME, null);
0903: final String password = params.getParameter(
0904: SQLTransformer.MAGIC_PASSWORD, null);
0905:
0906: if (username == null || password == null) {
0907: return "db:@" + dburl;
0908: } else {
0909: return "db:" + username + ":" + password + "@" + dburl;
0910: }
0911: }
0912:
0913: // Nothing configured
0914: return "";
0915: }
0916:
0917: /**
0918: * Open database connection using provided parameters.
0919: * Return null if neither datasource nor jndi URL configured.
0920: */
0921: private Connection open(Parameters params) throws SQLException {
0922: Connection result = null;
0923:
0924: // First check datasource name parameter
0925: final String datasourceName = params.getParameter(
0926: SQLTransformer.MAGIC_CONNECTION, null);
0927: if (datasourceName != null) {
0928: // Use datasource components
0929: if (this .datasources == null) {
0930: throw new SQLException(
0931: "Unable to get connection from datasource '"
0932: + datasourceName
0933: + "': "
0934: + "No datasources configured in cocoon.xconf.");
0935: }
0936:
0937: DataSourceComponent datasource = null;
0938: try {
0939: datasource = (DataSourceComponent) this .datasources
0940: .select(datasourceName);
0941: for (int i = 0; i < this .connectAttempts
0942: && result == null; i++) {
0943: try {
0944: result = datasource.getConnection();
0945: } catch (SQLException e) {
0946: if (i + 1 < this .connectAttempts) {
0947: final long waittime = this .connectWaittime;
0948: // Log exception if debug enabled.
0949: if (getLogger().isDebugEnabled()) {
0950: getLogger()
0951: .info(
0952: "Unable to get connection; waiting "
0953: + waittime
0954: + "ms to try again.",
0955: e);
0956: } else {
0957: getLogger().info(
0958: "Unable to get connection; waiting "
0959: + waittime
0960: + "ms to try again.");
0961: }
0962: try {
0963: Thread.sleep(waittime);
0964: } catch (InterruptedException ex) {
0965: /* ignored */
0966: }
0967: }
0968: }
0969: }
0970: } catch (ServiceException e) {
0971: throw new SQLException(
0972: "Unable to get connection from datasource '"
0973: + datasourceName + "': "
0974: + "No such datasource.");
0975: } finally {
0976: if (datasource != null) {
0977: this .datasources.release(datasource);
0978: }
0979: }
0980:
0981: if (result == null) {
0982: throw new SQLException(
0983: "Failed to obtain connection from datasource '"
0984: + datasourceName + "'. " + "Made "
0985: + this .connectAttempts
0986: + " attempts with "
0987: + this .connectWaittime + "ms interval");
0988: }
0989: } else {
0990: // Then, check connection URL parameter
0991: final String dburl = params.getParameter(
0992: SQLTransformer.MAGIC_DBURL, null);
0993: if (dburl != null) {
0994: final String username = params.getParameter(
0995: SQLTransformer.MAGIC_USERNAME, null);
0996: final String password = params.getParameter(
0997: SQLTransformer.MAGIC_PASSWORD, null);
0998: final String prop = params.getParameter(
0999: SQLTransformer.MAGIC_PROP, null);
1000:
1001: if (username == null || password == null) {
1002: result = DriverManager.getConnection(dburl);
1003: } else if (prop == null) {
1004: result = DriverManager.getConnection(dburl,
1005: username, password);
1006: } else {
1007: Properties props = new Properties();
1008: props.put("user", username);
1009: props.put("password", password);
1010: int proppos = prop.indexOf('=');
1011: if (proppos > 0) {
1012: String propname = prop.substring(0, proppos);
1013: String propvalue = prop.substring(proppos + 1);
1014: props.put(propname, propvalue);
1015: }
1016: result = DriverManager.getConnection(dburl, props);
1017: }
1018: } else {
1019: // Nothing configured
1020: }
1021: }
1022: return result;
1023: }
1024:
1025: /**
1026: * Attempt to parse string value
1027: */
1028: private void stream(String value) throws ServiceException,
1029: SAXException, IOException {
1030: try {
1031: // Strip off the XML Declaration if there is one!
1032: if (value.startsWith("<?xml ")) {
1033: value = value.substring(value.indexOf("?>") + 2);
1034: }
1035:
1036: // Lookup components
1037: if (this .parser == null) {
1038: this .parser = (SAXParser) manager
1039: .lookup(SAXParser.ROLE);
1040: }
1041: if (this .compiler == null) {
1042: this .compiler = (XMLSerializer) manager
1043: .lookup(XMLSerializer.ROLE);
1044: }
1045: if (this .interpreter == null) {
1046: this .interpreter = (XMLDeserializer) manager
1047: .lookup(XMLDeserializer.ROLE);
1048: }
1049:
1050: this .parser.parse(new InputSource(new StringReader("<root>"
1051: + value + "</root>")), this .compiler);
1052:
1053: IncludeXMLConsumer filter = new IncludeXMLConsumer(this ,
1054: this );
1055: filter.setIgnoreRootElement(true);
1056:
1057: this .interpreter.setConsumer(filter);
1058: this .interpreter
1059: .deserialize(this .compiler.getSAXFragment());
1060: } finally {
1061: // otherwise serializer won't be reset
1062: if (this .compiler != null) {
1063: manager.release(this .compiler);
1064: this .compiler = null;
1065: }
1066: }
1067: }
1068:
1069: /**
1070: * One of the queries in the query tree formed from nested queries.
1071: */
1072: private class Query extends AbstractLogEnabled {
1073:
1074: /** Parent query, or null for top level query */
1075: protected Query parent;
1076:
1077: /** Nested sub-queries we have. */
1078: protected final List nested = new ArrayList();
1079:
1080: /** The parts of the query */
1081: protected final List parts = new ArrayList();
1082:
1083: //
1084: // Query Configuration
1085: //
1086:
1087: /** Name of the query */
1088: protected String name;
1089:
1090: /** If this query is actually an update (insert, update, delete) */
1091: protected boolean isUpdate;
1092:
1093: /** If this query is actually a stored procedure */
1094: protected boolean isStoredProcedure;
1095:
1096: /** Query configuration parameters */
1097: protected Parameters params;
1098:
1099: /** The namespace uri of the XML output. Defaults to {@link SQLTransformer#namespaceURI}. */
1100: protected String outUri;
1101:
1102: /** The namespace prefix of the XML output. Defaults to 'sql'. */
1103: protected String outPrefix;
1104:
1105: /** rowset element name */
1106: protected String rowsetElement;
1107:
1108: /** row element name */
1109: protected String rowElement;
1110:
1111: /** number of rows attribute name */
1112: protected String nrOfRowsAttr = "nrofrows";
1113:
1114: /** Query name attribute name */
1115: protected String nameAttr = "name";
1116:
1117: /** Handling of case of column names in results */
1118: protected int columnCase;
1119:
1120: /** Registered IN parameters */
1121: protected Map inParameters;
1122:
1123: /** Registered IN XML parameters */
1124: protected Map inXmlParameters;
1125:
1126: /** Registered OUT parameters */
1127: protected Map outParameters;
1128:
1129: /** Mapping out parameters - objectModel */
1130: protected Map outParametersNames;
1131:
1132: /** Check if nr of rows need to be written out. */
1133: protected boolean showNrOfRows;
1134:
1135: /** Encoding we use for CLOB field */
1136: protected String clobEncoding;
1137:
1138: //
1139: // Query State
1140: //
1141:
1142: /** The connection */
1143: protected Connection conn;
1144:
1145: /** The 'name' of the connection */
1146: protected String connName;
1147:
1148: /** Is it our own connection? */
1149: protected boolean ownConn;
1150:
1151: /** Prepared statement */
1152: protected PreparedStatement pst;
1153:
1154: /** Callable statement */
1155: protected CallableStatement cst;
1156:
1157: /** The results, of course */
1158: protected ResultSet rs;
1159:
1160: /** And the results' metadata */
1161: protected ResultSetMetaData md;
1162:
1163: /** If it is an update/etc, the return value (num rows modified) */
1164: protected int rv = -1;
1165:
1166: protected Query(Query parent) {
1167: this .parent = parent;
1168: this .params = new Parameters();
1169: this .params.merge(SQLTransformer.this .parameters);
1170: }
1171:
1172: /** Add nested sub-query. */
1173: protected void addNestedQuery(Query query) {
1174: nested.add(query);
1175: }
1176:
1177: protected void addQueryPart(Object value) {
1178: if (getLogger().isDebugEnabled()) {
1179: getLogger()
1180: .debug("Adding query part \"" + value + "\"");
1181: }
1182: parts.add(value);
1183: }
1184:
1185: protected String getName() {
1186: return name;
1187: }
1188:
1189: protected void setName(String name) {
1190: this .name = name;
1191: }
1192:
1193: protected void setParameter(String name, String value) {
1194: if (getLogger().isDebugEnabled()) {
1195: getLogger().debug(
1196: "Adding parameter name {" + name + "} value {"
1197: + value + "}");
1198: }
1199: params.setParameter(name, value);
1200: }
1201:
1202: protected void setUpdate(boolean flag) {
1203: isUpdate = flag;
1204: }
1205:
1206: protected void setStoredProcedure(boolean flag) {
1207: isStoredProcedure = flag;
1208: }
1209:
1210: protected void setInParameter(int pos, String val) {
1211: if (inParameters == null) {
1212: inParameters = new HashMap();
1213: }
1214: inParameters.put(new Integer(pos), val);
1215: }
1216:
1217: protected void setInXmlParameter(int pos, String val) {
1218: if (inXmlParameters == null) {
1219: inXmlParameters = new HashMap();
1220: }
1221: inXmlParameters.put(new Integer(pos), val);
1222: }
1223:
1224: protected void setOutParameter(int pos, String type, String name) {
1225: if (outParameters == null) {
1226: // make sure output parameters are ordered
1227: outParameters = new TreeMap();
1228: outParametersNames = new HashMap();
1229: }
1230: outParameters.put(new Integer(pos), type);
1231: outParametersNames.put(new Integer(pos), name);
1232: }
1233:
1234: private void setColumnCase(String columnCase) {
1235: if (columnCase.equals("lowercase")) {
1236: this .columnCase = -1;
1237: } else if (columnCase.equals("uppercase")) {
1238: this .columnCase = +1;
1239: } else if (columnCase.equals("preserve")) {
1240: // Do nothing
1241: this .columnCase = 0;
1242: } else {
1243: getLogger()
1244: .warn(
1245: "["
1246: + columnCase
1247: + "] is not a valid value for <column-case>. "
1248: + "Column name retrieved from database will be used.");
1249: }
1250: }
1251:
1252: private void registerInParameters() throws SQLException {
1253: if (inParameters != null) {
1254: Iterator i = inParameters.keySet().iterator();
1255: while (i.hasNext()) {
1256: Integer counter = (Integer) i.next();
1257: String value = (String) inParameters.get(counter);
1258: try {
1259: pst.setObject(counter.intValue(), value);
1260: } catch (SQLException e) {
1261: getLogger().error("Caught a SQLException", e);
1262: throw e;
1263: }
1264: }
1265: }
1266: }
1267:
1268: private void registerInXmlParameters() throws SQLException {
1269: if (inXmlParameters != null) {
1270: Iterator i = inXmlParameters.keySet().iterator();
1271: while (i.hasNext()) {
1272: Integer counter = (Integer) i.next();
1273: String value = (String) inXmlParameters
1274: .get(counter);
1275: try {
1276: pst.setString(counter.intValue(), value);
1277: } catch (SQLException e) {
1278: getLogger().error("Caught a SQLException", e);
1279: throw e;
1280: }
1281: }
1282: }
1283: }
1284:
1285: private void registerOutParameters(CallableStatement cst)
1286: throws SQLException {
1287: if (outParameters != null) {
1288: Iterator i = outParameters.keySet().iterator();
1289: while (i.hasNext()) {
1290: Integer counter = (Integer) i.next();
1291: String type = (String) outParameters.get(counter);
1292:
1293: int index = type.lastIndexOf(".");
1294: String className, fieldName;
1295: if (index == -1) {
1296: getLogger().error("Invalid SQLType: " + type,
1297: null);
1298: throw new SQLException("Invalid SQLType: "
1299: + type);
1300: }
1301: className = type.substring(0, index);
1302: fieldName = type
1303: .substring(index + 1, type.length());
1304:
1305: try {
1306: Class clss = Class.forName(className);
1307: Field fld = clss.getField(fieldName);
1308: cst.registerOutParameter(counter.intValue(),
1309: fld.getInt(fieldName));
1310: } catch (Exception e) {
1311: // Lots of different exceptions to catch
1312: getLogger().error(
1313: "Invalid SQLType: " + className + "."
1314: + fieldName, e);
1315: }
1316: }
1317: }
1318: }
1319:
1320: /**
1321: * Open database connection
1322: */
1323: private void open() throws SQLException {
1324: this .connName = SQLTransformer.this .name(this .params);
1325:
1326: // Check first if connection sharing disabled
1327: if (this .connName == null) {
1328: this .conn = SQLTransformer.this .open(this .params);
1329: this .ownConn = true;
1330: return;
1331: }
1332:
1333: // Iterate through parent queries and get appropriate connection
1334: Query query = this .parent;
1335: while (query != null) {
1336: if (this .connName.equals(query.connName)) {
1337: this .conn = query.conn;
1338: this .ownConn = false;
1339: return;
1340: }
1341: query = query.parent;
1342: }
1343:
1344: // Check 'global' connection
1345: if (this .connName.equals(SQLTransformer.this .connName)) {
1346: // Use SQLTransformer configuration: it has same connection parameters
1347: if (SQLTransformer.this .conn == null) {
1348: SQLTransformer.this .conn = SQLTransformer.this
1349: .open(SQLTransformer.this .parameters);
1350: }
1351:
1352: this .conn = SQLTransformer.this .conn;
1353: this .ownConn = false;
1354: return;
1355: }
1356:
1357: // Create own connection
1358: this .conn = SQLTransformer.this .open(this .params);
1359: this .ownConn = true;
1360: }
1361:
1362: /**
1363: * This will be the meat of SQLTransformer, where the query is run.
1364: */
1365: protected void executeQuery() throws SAXException {
1366: if (getLogger().isDebugEnabled()) {
1367: getLogger().debug("Executing query " + this );
1368: }
1369:
1370: this .outUri = this .params.getParameter(
1371: SQLTransformer.MAGIC_NS_URI_ELEMENT,
1372: SQLTransformer.this .namespaceURI);
1373: this .outPrefix = this .params.getParameter(
1374: SQLTransformer.MAGIC_NS_PREFIX_ELEMENT, "sql");
1375: this .rowsetElement = this .params.getParameter(
1376: SQLTransformer.MAGIC_DOC_ELEMENT, "rowset");
1377: this .rowElement = this .params.getParameter(
1378: SQLTransformer.MAGIC_ROW_ELEMENT, "row");
1379:
1380: this .showNrOfRows = parameters.getParameterAsBoolean(
1381: SQLTransformer.MAGIC_NR_OF_ROWS, false);
1382: this .clobEncoding = parameters.getParameter(
1383: SQLTransformer.CLOB_ENCODING, "");
1384: if (this .clobEncoding.length() == 0) {
1385: this .clobEncoding = null;
1386: }
1387:
1388: // Start prefix mapping for output namespace, only if it's not mapped yet
1389: final String prefix = SQLTransformer.this
1390: .findPrefixMapping(this .outUri);
1391: if (prefix == null) {
1392: SQLTransformer.this .startPrefixMapping(this .outPrefix,
1393: this .outUri);
1394: } else {
1395: this .outPrefix = prefix;
1396: }
1397:
1398: boolean success = false;
1399: try {
1400: try {
1401: open();
1402: execute();
1403: success = true;
1404: } catch (SQLException e) {
1405: getLogger().info("Failed to execute query " + this ,
1406: e);
1407: start(this .rowsetElement, EMPTY_ATTRIBUTES);
1408: start(MAGIC_ERROR, EMPTY_ATTRIBUTES);
1409: data(e.getMessage());
1410: end(MAGIC_ERROR);
1411: end(this .rowsetElement);
1412: }
1413:
1414: if (success) {
1415: AttributesImpl attr = new AttributesImpl();
1416: if (showNrOfRows) {
1417: attr.addAttribute("", this .nrOfRowsAttr,
1418: this .nrOfRowsAttr, "CDATA", String
1419: .valueOf(getNrOfRows()));
1420: }
1421: String name = getName();
1422: if (name != null) {
1423: attr.addAttribute("", this .nameAttr,
1424: this .nameAttr, "CDATA", name);
1425: }
1426: start(this .rowsetElement, attr);
1427:
1428: // Serialize stored procedure output parameters
1429: if (isStoredProcedure) {
1430: serializeStoredProcedure();
1431: }
1432:
1433: // Serialize result set
1434: while (next()) {
1435: start(this .rowElement, EMPTY_ATTRIBUTES);
1436: serializeRow();
1437: for (Iterator i = this .nested.iterator(); i
1438: .hasNext();) {
1439: ((Query) i.next()).executeQuery();
1440: }
1441: end(this .rowElement);
1442: }
1443:
1444: end(this .rowsetElement);
1445: }
1446: } catch (SQLException e) {
1447: getLogger().debug("Exception in executeQuery()", e);
1448: throw new SAXException(e);
1449: } finally {
1450: close();
1451: }
1452:
1453: if (prefix == null) {
1454: SQLTransformer.this .endPrefixMapping(this .outPrefix);
1455: }
1456: }
1457:
1458: /**
1459: * Execute the query. Connection must be set already.
1460: */
1461: private void execute() throws SQLException {
1462: setColumnCase(params.getParameter(
1463: SQLTransformer.MAGIC_COLUMN_CASE, "lowercase"));
1464:
1465: // Construct query string
1466: StringBuffer sb = new StringBuffer();
1467: for (Iterator i = parts.iterator(); i.hasNext();) {
1468: Object object = i.next();
1469: if (object instanceof String) {
1470: sb.append((String) object);
1471: } else if (object instanceof AncestorValue) {
1472: // Do a lookup into the ancestors' result's values
1473: AncestorValue av = (AncestorValue) object;
1474: Query query = this ;
1475: for (int k = av.level; k > 0; k--) {
1476: query = query.parent;
1477: }
1478: sb.append(query.getColumnValue(av.name));
1479: }
1480: }
1481:
1482: String query = StringUtils.replace(sb.toString().trim(),
1483: "\r", " ", -1);
1484: // Test, if this is an update (by comparing with select)
1485: if (!isStoredProcedure && !isUpdate) {
1486: if (query.length() > 6
1487: && !query.substring(0, 6).equalsIgnoreCase(
1488: "SELECT")) {
1489: isUpdate = true;
1490: }
1491: }
1492:
1493: if (getLogger().isDebugEnabled()) {
1494: getLogger().debug("Executing " + query);
1495: }
1496: if (!isStoredProcedure) {
1497: if (oldDriver) {
1498: pst = conn.prepareStatement(query);
1499: } else {
1500: pst = conn.prepareStatement(query,
1501: ResultSet.TYPE_SCROLL_INSENSITIVE,
1502: ResultSet.CONCUR_READ_ONLY);
1503: }
1504: } else {
1505: if (oldDriver) {
1506: cst = conn.prepareCall(query);
1507: } else {
1508: cst = conn.prepareCall(query,
1509: ResultSet.TYPE_SCROLL_INSENSITIVE,
1510: ResultSet.CONCUR_READ_ONLY);
1511: }
1512: registerOutParameters(cst);
1513: pst = cst;
1514: }
1515:
1516: registerInParameters();
1517: registerInXmlParameters();
1518: boolean result = pst.execute();
1519: if (result) {
1520: rs = pst.getResultSet();
1521: md = rs.getMetaData();
1522: } else {
1523: rv = pst.getUpdateCount();
1524: }
1525: }
1526:
1527: protected int getNrOfRows() throws SQLException {
1528: int nr = 0;
1529:
1530: if (rs != null) {
1531: if (oldDriver) {
1532: nr = -1;
1533: } else {
1534: try {
1535: rs.last();
1536: nr = rs.getRow();
1537: rs.beforeFirst();
1538: } catch (NullPointerException e) {
1539: // A NullPointerException here crashes a whole lot of C2 --
1540: // catching it so it won't do any harm for now, but seems like it should be solved seriously
1541: getLogger().error(
1542: "NPE while getting the nr of rows", e);
1543: }
1544: }
1545: } else {
1546: if (outParameters != null) {
1547: nr = outParameters.size();
1548: }
1549: }
1550: return nr;
1551: }
1552:
1553: protected String getColumnValue(ResultSet rs, int i)
1554: throws SQLException {
1555: final int type = rs.getMetaData().getColumnType(i);
1556: if (type == java.sql.Types.DOUBLE) {
1557: return getStringValue(rs.getBigDecimal(i));
1558: } else if (type == java.sql.Types.CLOB) {
1559: return getStringValue(rs.getClob(i));
1560: } else {
1561: return getStringValue(rs.getObject(i));
1562: }
1563: }
1564:
1565: // fix not applied here because there is no metadata from Name -> number and coltype
1566: // for a given "name" versus number. That being said this shouldn't be an issue
1567: // as this function is only called for ancestor lookups.
1568: protected String getColumnValue(String name)
1569: throws SQLException {
1570: //noinspection UnnecessaryLocalVariable
1571: String retval = getStringValue(rs.getObject(name));
1572: // if (rs.getMetaData().getColumnType( name ) == java.sql.Types.DOUBLE)
1573: // retval = transformer.getStringValue( rs.getBigDecimal( name ) );
1574: return retval;
1575: }
1576:
1577: protected boolean next() throws SQLException {
1578: // If rv is not -1, then an SQL insert, update, etc, has
1579: // happened (see JDBC docs - return codes for executeUpdate)
1580: if (rv != -1) {
1581: // Output row with return code. Once.
1582: return true;
1583: }
1584:
1585: if (rs != null && rs.next()) {
1586: // Have next row
1587: return true;
1588: }
1589:
1590: while (pst.getMoreResults()) {
1591: rs = pst.getResultSet();
1592: md = rs.getMetaData();
1593: if (rs.next()) {
1594: // Have next row in next result set
1595: return true;
1596: }
1597: }
1598:
1599: // Nothing left
1600: return false;
1601: }
1602:
1603: /**
1604: * Closes all the resources, ignores (but logs) exceptions.
1605: */
1606: protected void close() {
1607: if (rs != null) {
1608: try {
1609: rs.close();
1610: } catch (SQLException e) {
1611: getLogger().info("Unable to close the result set.",
1612: e);
1613: }
1614: // This prevents us from using the resultset again.
1615: rs = null;
1616: }
1617:
1618: if (pst != null && pst != cst) {
1619: try {
1620: pst.close();
1621: } catch (SQLException e) {
1622: getLogger().info("Unable to close the statement.",
1623: e);
1624: }
1625: }
1626: // Prevent using pst again.
1627: pst = null;
1628:
1629: if (cst != null) {
1630: try {
1631: cst.close();
1632: } catch (SQLException e) {
1633: getLogger().info("Unable to close the statement.",
1634: e);
1635: }
1636: // Prevent using cst again.
1637: cst = null;
1638: }
1639:
1640: try {
1641: if (ownConn && conn != null) {
1642: conn.close();
1643: }
1644: } catch (SQLException e) {
1645: getLogger().info("Unable to close the connection", e);
1646: }
1647: // Prevent using conn again.
1648: conn = null;
1649: }
1650:
1651: protected void serializeData(String value) throws SQLException,
1652: SAXException {
1653: if (value != null) {
1654: value = value.trim();
1655: // Could this be XML ?
1656: if (value.length() > 0 && value.charAt(0) == '<') {
1657: try {
1658: stream(value);
1659: } catch (Exception ignored) {
1660: // FIXME: bad coding "catch(Exception)"
1661: // If an exception occured the data was not (valid) xml
1662: data(value);
1663: }
1664: } else {
1665: data(value);
1666: }
1667: }
1668: }
1669:
1670: protected void serializeRow() throws SQLException, SAXException {
1671: if (rv != -1) {
1672: start("returncode", EMPTY_ATTRIBUTES);
1673: serializeData(String.valueOf(rv));
1674: end("returncode");
1675: // We only want the return code shown once.
1676: // Reset rv so next() returns false next time.
1677: rv = -1;
1678: } else {
1679: for (int i = 1; i <= md.getColumnCount(); i++) {
1680: String columnName = getColumnName(md
1681: .getColumnName(i));
1682: start(columnName, EMPTY_ATTRIBUTES);
1683: serializeData(getColumnValue(rs, i));
1684: end(columnName);
1685: }
1686: }
1687: }
1688:
1689: private void serializeResultSet(ResultSet rs)
1690: throws SQLException, SAXException {
1691: final ResultSetMetaData md = rs.getMetaData();
1692: final int n = md.getColumnCount();
1693:
1694: // Get column names
1695: final String[] columns = new String[n + 1];
1696: for (int i = 1; i <= n; i++) {
1697: columns[i] = getColumnName(md.getColumnName(i));
1698: }
1699:
1700: // Process rows
1701: while (rs.next()) {
1702: start(rowElement, EMPTY_ATTRIBUTES);
1703: for (int i = 1; i <= n; i++) {
1704: start(columns[i], EMPTY_ATTRIBUTES);
1705: serializeData(getColumnValue(rs, i));
1706: end(columns[i]);
1707: }
1708: end(this .rowElement);
1709: }
1710: }
1711:
1712: protected void serializeStoredProcedure() throws SQLException,
1713: SAXException {
1714: if (outParametersNames == null || cst == null) {
1715: return;
1716: }
1717:
1718: Iterator itOutKeys = outParameters.keySet().iterator();
1719: while (itOutKeys.hasNext()) {
1720: final Integer counter = (Integer) itOutKeys.next();
1721: try {
1722: final Object obj = cst
1723: .getObject(counter.intValue());
1724: final String name = (String) outParametersNames
1725: .get(counter);
1726: start(name, EMPTY_ATTRIBUTES);
1727:
1728: if (!(obj instanceof ResultSet)) {
1729: serializeData(getStringValue(obj));
1730: } else {
1731: final ResultSet rs = (ResultSet) obj;
1732: try {
1733: serializeResultSet(rs);
1734: } finally {
1735: try {
1736: rs.close();
1737: } catch (SQLException e) { /* ignored */
1738: }
1739: }
1740: }
1741:
1742: end(name);
1743: } catch (SQLException e) {
1744: getLogger().error("Caught a SQLException", e);
1745: throw e;
1746: }
1747: }
1748: }
1749:
1750: private String getColumnName(String columnName) {
1751: switch (this .columnCase) {
1752: case -1:
1753: columnName = columnName.toLowerCase();
1754: break;
1755: case +1:
1756: columnName = columnName.toUpperCase();
1757: break;
1758: default:
1759: // Do nothing
1760: }
1761: return columnName;
1762: }
1763:
1764: /**
1765: * Convert object to string represenation
1766: */
1767: private String getStringValue(Object object)
1768: throws SQLException {
1769: if (object instanceof byte[]) {
1770: // FIXME Encoding?
1771: return new String((byte[]) object);
1772: }
1773:
1774: if (object instanceof char[]) {
1775: return new String((char[]) object);
1776: }
1777:
1778: // Old behavior: Read bytes & decode
1779: if (object instanceof Clob && this .clobEncoding != null) {
1780: Clob clob = (Clob) object;
1781: StringBuffer buffer = new StringBuffer();
1782: InputStream is = clob.getAsciiStream();
1783: try {
1784: byte[] bytes = new byte[BUFFER_SIZE];
1785: int n;
1786: while ((n = is.read(bytes)) > -1) {
1787: buffer.append(new String(bytes, 0, n,
1788: this .clobEncoding));
1789: }
1790: } catch (IOException e) {
1791: throw new SQLException(
1792: "Error reading stream from CLOB");
1793: }
1794: return buffer.toString();
1795: }
1796:
1797: // Correct behavior: Read character data
1798: if (object instanceof Clob) {
1799: Clob clob = (Clob) object;
1800: StringBuffer buffer = new StringBuffer();
1801: Reader cs = clob.getCharacterStream();
1802: try {
1803: char[] chars = new char[BUFFER_SIZE];
1804: int n;
1805: while ((n = cs.read(chars)) > -1) {
1806: buffer.append(chars, 0, n);
1807: }
1808: } catch (IOException e) {
1809: throw new SQLException(
1810: "Error reading stream from CLOB");
1811: }
1812: return buffer.toString();
1813: }
1814:
1815: if (object != null) {
1816: return object.toString();
1817: }
1818:
1819: return "";
1820: }
1821:
1822: private void start(String name, Attributes attr)
1823: throws SAXException {
1824: SQLTransformer.this .start(this .outUri, this .outPrefix,
1825: name, attr);
1826: }
1827:
1828: private void end(String name) throws SAXException {
1829: SQLTransformer.this .end(this .outUri, this .outPrefix, name);
1830: }
1831:
1832: private void data(String data) throws SAXException {
1833: SQLTransformer.this .data(data);
1834: }
1835: }
1836:
1837: private static class AncestorValue {
1838: protected int level;
1839: protected String name;
1840:
1841: protected AncestorValue(int level, String name) {
1842: this .level = level;
1843: this .name = name;
1844: }
1845:
1846: public String toString() {
1847: return "<ancestor level " + level + ", name " + name + ">";
1848: }
1849: }
1850:
1851: /**
1852: * Stop recording of text and return the recorded information.
1853: * @return The String, trimmed.
1854: *
1855: * NB. SQLTransformer needs to have a special version of this method
1856: * It needs the TextRecorder to not trim whitespace from the queries it is building
1857: *
1858: */
1859: public String endTextRecording() throws SAXException {
1860: sendEndPrefixMapping();
1861:
1862: TextRecorder recorder = (TextRecorder) removeRecorder();
1863: String text = recorder.getAllText();
1864: if (getLogger().isDebugEnabled()) {
1865: getLogger().debug("End text recording. Text=" + text);
1866: }
1867: return text;
1868: }
1869: }
|