001: package net.sf.saxon.sql;
002:
003: import net.sf.saxon.Controller;
004: import net.sf.saxon.event.Receiver;
005: import net.sf.saxon.event.ReceiverOptions;
006: import net.sf.saxon.expr.Expression;
007: import net.sf.saxon.expr.SimpleExpression;
008: import net.sf.saxon.expr.XPathContext;
009: import net.sf.saxon.instruct.Executable;
010: import net.sf.saxon.om.Item;
011: import net.sf.saxon.om.NamePool;
012: import net.sf.saxon.style.ExtensionInstruction;
013: import net.sf.saxon.style.StandardNames;
014: import net.sf.saxon.trans.DynamicError;
015: import net.sf.saxon.trans.XPathException;
016: import net.sf.saxon.value.ObjectValue;
017: import net.sf.saxon.value.StringValue;
018:
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023:
024: /**
025: * An sql:query element in the stylesheet.
026: * <p/>
027: * For example:
028: * <pre>
029: * <sql:query column="{$column}" table="{$table}" where="{$where}"
030: * xsl:extension-element-prefixes="sql"/ >
031: * <p/>
032: * </pre>
033: * (result with HTML-table-output) <BR>
034: * <pre>
035: * <sql:query column="{$column}" table="{$table}" where="{$where}"
036: * row-tag="TR" column-tag="TD"
037: * separatorType="tag"
038: * xsl:extension-element-prefixes="sql"/ >
039: * </pre>
040: *
041: * @author claudio.thomas@unix-ag.org (based on Michael Kay's SQLInsert.java)
042: */
043:
044: public class SQLQuery extends ExtensionInstruction {
045:
046: Expression connection;
047: /**
048: * selected column(s) to query
049: */
050: Expression column;
051: /**
052: * the table(s) to query in
053: */
054: Expression table;
055: /**
056: * conditions of query (can be omitted)
057: */
058: Expression where;
059:
060: String rowTag;
061: /**
062: * name of element to hold the rows
063: */
064: String colTag;
065: /**
066: * name of element to hold the columns
067: */
068:
069: boolean disable = false; // true means disable-output-escaping="yes"
070:
071: public void prepareAttributes() throws XPathException {
072: // Attributes for SQL-statement
073: String dbCol = attributeList.getValue("", "column");
074: if (dbCol == null) {
075: reportAbsence("column");
076: }
077: column = makeAttributeValueTemplate(dbCol);
078:
079: String dbTab = attributeList.getValue("", "table");
080: if (dbTab == null) {
081: reportAbsence("table");
082: }
083: table = makeAttributeValueTemplate(dbTab);
084:
085: String dbWhere = attributeList.getValue("", "where");
086: if (dbWhere == null) {
087: where = StringValue.EMPTY_STRING;
088: } else {
089: where = makeAttributeValueTemplate(dbWhere);
090: }
091:
092: String connectAtt = attributeList.getValue("", "connection");
093: if (connectAtt == null) {
094: reportAbsence("connection");
095: } else {
096: connection = makeExpression(connectAtt);
097: }
098:
099: // Atributes for row & column element names
100:
101: rowTag = attributeList.getValue("", "row-tag");
102: if (rowTag == null) {
103: rowTag = "row";
104: }
105: if (rowTag.indexOf(':') >= 0) {
106: compileError("rowTag must not contain a colon");
107: }
108:
109: colTag = attributeList.getValue("", "column-tag");
110: if (colTag == null) {
111: colTag = "col";
112: }
113: if (colTag.indexOf(':') >= 0) {
114: compileError("colTag must not contain a colon");
115: }
116: // Attribute output-escaping
117: String disableAtt = attributeList.getValue("",
118: "disable-output-escaping");
119: if (disableAtt != null) {
120: if (disableAtt.equals("yes")) {
121: disable = true;
122: } else if (disableAtt.equals("no")) {
123: disable = false;
124: } else {
125: compileError("disable-output-escaping attribute must be either yes or no");
126: }
127: }
128:
129: }
130:
131: public void validate() throws XPathException {
132: super .validate();
133: column = typeCheck("column", column);
134: table = typeCheck("table", table);
135: where = typeCheck("where", where);
136: connection = typeCheck("connection", connection);
137: }
138:
139: public Expression compile(Executable exec) throws XPathException {
140: QueryInstruction inst = new QueryInstruction(connection,
141: column, table, where, rowTag, colTag, disable);
142: return inst;
143: }
144:
145: private static class QueryInstruction extends SimpleExpression {
146:
147: public static final int CONNECTION = 0;
148: public static final int COLUMN = 1;
149: public static final int TABLE = 2;
150: public static final int WHERE = 3;
151: String rowTag;
152: String colTag;
153: int options;
154:
155: public QueryInstruction(Expression connection,
156: Expression column, Expression table, Expression where,
157: String rowTag, String colTag, boolean disable) {
158: Expression[] sub = { connection, column, table, where };
159: setArguments(sub);
160: this .rowTag = rowTag;
161: this .colTag = colTag;
162: this .options = (disable ? ReceiverOptions.DISABLE_ESCAPING
163: : 0);
164: }
165:
166: /**
167: * A subclass must provide one of the methods evaluateItem(), iterate(), or process().
168: * This method indicates which of the three is provided.
169: */
170:
171: public int getImplementationMethod() {
172: return Expression.PROCESS_METHOD;
173: }
174:
175: public String getExpressionType() {
176: return "sql:query";
177: }
178:
179: public void process(XPathContext context) throws XPathException {
180: // Prepare the SQL statement (only do this once)
181:
182: Controller controller = context.getController();
183: Item conn = arguments[CONNECTION].evaluateItem(context);
184: if (!(conn instanceof ObjectValue && ((ObjectValue) conn)
185: .getObject() instanceof Connection)) {
186: DynamicError de = new DynamicError(
187: "Value of connection expression is not a JDBC Connection");
188: de.setXPathContext(context);
189: throw de;
190: }
191: Connection connection = (Connection) ((ObjectValue) conn)
192: .getObject();
193:
194: String dbCol = arguments[COLUMN].evaluateAsString(context);
195: String dbTab = arguments[TABLE].evaluateAsString(context);
196: String dbWhere = arguments[WHERE].evaluateAsString(context);
197:
198: NamePool pool = controller.getNamePool();
199: int rowCode = pool.allocate("", "", rowTag);
200: int colCode = pool.allocate("", "", colTag);
201:
202: PreparedStatement ps = null;
203: ResultSet rs = null;
204: DynamicError de = null;
205:
206: try {
207: StringBuffer statement = new StringBuffer();
208: statement.append("SELECT " + dbCol + " FROM " + dbTab);
209: if (dbWhere != "") {
210: statement.append(" WHERE " + dbWhere);
211: }
212: //System.err.println("-> SQL: " + statement.toString());
213:
214: // -- Prepare the SQL statement
215: ps = connection.prepareStatement(statement.toString());
216: controller.setUserData(this , "sql:statement", ps);
217:
218: // -- Execute Statement
219: rs = ps.executeQuery();
220:
221: // -- Print out Result
222: Receiver out = context.getReceiver();
223: String result = "";
224: int icol = rs.getMetaData().getColumnCount();
225: while (rs.next()) { // next row
226: //System.out.print("<- SQL : "+ rowStart);
227: out.startElement(rowCode,
228: StandardNames.XDT_UNTYPED, locationId, 0);
229: for (int col = 1; col <= icol; col++) { // next column
230: // Read result from RS only once, because
231: // of JDBC-Specifications
232: result = rs.getString(col);
233: out.startElement(colCode,
234: StandardNames.XDT_UNTYPED, locationId,
235: 0);
236: if (result != null) {
237: out.characters(result, locationId, options);
238: }
239: out.endElement();
240: }
241: //System.out.println(rowEnd);
242: out.endElement();
243: }
244: //rs.close();
245:
246: if (!connection.getAutoCommit()) {
247: connection.commit();
248: }
249:
250: } catch (SQLException ex) {
251: de = new DynamicError("(SQL) " + ex.getMessage());
252: de.setXPathContext(context);
253: throw de;
254: } finally {
255: boolean wasDEThrown = (de != null);
256: if (rs != null) {
257: try {
258: rs.close();
259: } catch (SQLException ex) {
260: de = new DynamicError("(SQL) "
261: + ex.getMessage());
262: de.setXPathContext(context);
263: }
264: }
265: if (ps != null) {
266: try {
267: ps.close();
268: } catch (SQLException ex) {
269: de = new DynamicError("(SQL) "
270: + ex.getMessage());
271: de.setXPathContext(context);
272: }
273: }
274: if (!wasDEThrown && de != null) {
275: throw de; // test so we don't lose the real exception
276: }
277: }
278: }
279: }
280: }
281:
282: //
283: // The contents of this file are subject to the Mozilla Public License Version 1.0 (the "License");
284: // you may not use this file except in compliance with the License. You may obtain a copy of the
285: // License at http://www.mozilla.org/MPL/
286: //
287: // Software distributed under the License is distributed on an "AS IS" basis,
288: // WITHOUT WARRANTY OF ANY KIND, either express or implied.
289: // See the License for the specific language governing rights and limitations under the License.
290: //
291: //
292: // Contributor(s): claudio.thomas@unix-ag.org (based on SQLInsert.java)
293: //
|