dims 01/06/21 04:08:02
Modified: src/org/apache/cocoon/transformation SQLTransformer.java
xdocs docs-book.xml site-book.xml
Added: src/org/apache/cocoon/transformation FilterTransformer.java
ReadDOMSessionTransformer.java
WriteDOMSessionTransformer.java
xdocs sql-transformer.xml
Log:
- Added New Transformers contributed by Sven Beauprez <[EMAIL PROTECTED]>
- Updated SQLTransformer contributed by Sven Beauprez <[EMAIL PROTECTED]>
- Added doc for SQLTransformer.
Revision Changes Path
1.7 +434 -95
xml-cocoon2/src/org/apache/cocoon/transformation/SQLTransformer.java
Index: SQLTransformer.java
===================================================================
RCS file:
/home/cvs/xml-cocoon2/src/org/apache/cocoon/transformation/SQLTransformer.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- SQLTransformer.java 2001/06/07 06:15:39 1.6
+++ SQLTransformer.java 2001/06/21 11:07:57 1.7
@@ -13,13 +13,16 @@
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.sql.CallableStatement;
import java.util.Enumeration;
import java.util.Map;
+import java.util.HashMap;
+import java.util.TreeMap;
+import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;
-import org.apache.avalon.excalibur.datasource.DataSourceComponent;
-import org.apache.avalon.excalibur.pool.Recyclable;
+import java.lang.reflect.Field;
import org.apache.avalon.framework.activity.Disposable;
import org.apache.avalon.framework.component.Component;
import org.apache.avalon.framework.component.ComponentException;
@@ -27,12 +30,15 @@
import org.apache.avalon.framework.component.ComponentSelector;
import org.apache.avalon.framework.component.Composable;
import org.apache.avalon.framework.parameters.Parameters;
+import org.apache.avalon.excalibur.datasource.DataSourceComponent;
+import org.apache.avalon.excalibur.pool.Recyclable;
+import org.apache.cocoon.environment.SourceResolver;
import org.apache.cocoon.ProcessingException;
import org.apache.cocoon.Roles;
-import org.apache.cocoon.environment.SourceResolver;
import org.apache.cocoon.util.ClassUtils;
import org.apache.cocoon.xml.XMLConsumer;
import org.apache.cocoon.xml.XMLProducer;
+import org.apache.cocoon.util.Tokenizer;
import org.apache.log.Logger;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
@@ -41,19 +47,21 @@
import org.xml.sax.ext.LexicalHandler;
import org.xml.sax.helpers.AttributesImpl;
+import org.apache.cocoon.transformation.AbstractTransformer;
+
/**
*
* @author <a href="mailto:[EMAIL PROTECTED]">Donald Ball</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Giacomo Pati</a>
* (PWR Organisation & Entwicklung)
- * @version CVS $Revision: 1.6 $ $Date: 2001/06/07 06:15:39 $ $Author:
cziegeler $
+ * @author <a href="mailto:[EMAIL PROTECTED]">Sven Beauprez</a>
+ * @version CVS $Revision: 1.7 $ $Date: 2001/06/21 11:07:57 $ $Author: dims $
*/
-public class SQLTransformer extends AbstractTransformer
-implements Composable, Recyclable, Disposable {
+public class SQLTransformer extends AbstractTransformer implements
Composable, Recyclable, Disposable {
/** The SQL namespace **/
- public static final String my_uri = "http://apache.org/cocoon/SQL/2.0";;
+ public static final String my_uri = "http://apache.org/cocoon/SQL";;
public static final String my_name = "SQLTransformer";
/** The SQL namespace element names **/
@@ -62,13 +70,26 @@
public static final String MAGIC_DBURL = "dburl";
public static final String MAGIC_USERNAME = "username";
public static final String MAGIC_PASSWORD = "password";
+ public static final String MAGIC_NR_OF_ROWS = "show-nr-of-rows";
public static final String MAGIC_QUERY = "query";
public static final String MAGIC_VALUE = "value";
public static final String MAGIC_DOC_ELEMENT = "doc-element";
public static final String MAGIC_ROW_ELEMENT = "row-element";
+ public static final String MAGIC_IN_PARAMETER = "in-parameter";
+ public static final String MAGIC_IN_PARAMETER_NR_ATTRIBUTE = "nr";
+ public static final String MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE = "value";
+ public static final String MAGIC_OUT_PARAMETER = "out-parameter";
+ public static final String MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE = "name";
+ public static final String MAGIC_OUT_PARAMETER_NR_ATTRIBUTE = "nr";
+ public static final String MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE = "type";
+
public static final String MAGIC_ANCESTOR_VALUE = "ancestor-value";
public static final String MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE =
"level";
public static final String MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE = "name";
+ public static final String MAGIC_SUBSTITUTE_VALUE = "substitute-value";
+ public static final String MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE =
"name";
+ public static final String MAGIC_NAME_ATTRIBUTE = "name";
+ public static final String MAGIC_STORED_PROCEDURE_ATTRIBUTE =
"isstoredprocedure";
public static final String MAGIC_UPDATE_ATTRIBUTE = "isupdate";
/** The states we are allowed to be in **/
@@ -77,6 +98,9 @@
public static final int STATE_INSIDE_VALUE_ELEMENT = 2;
public static final int STATE_INSIDE_QUERY_ELEMENT = 3;
public static final int STATE_INSIDE_ANCESTOR_VALUE_ELEMENT = 4;
+ public static final int STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT = 5;
+ public static final int STATE_INSIDE_IN_PARAMETER_ELEMENT = 6;
+ public static final int STATE_INSIDE_OUT_PARAMETER_ELEMENT = 7;
/** Default parameters that might apply to all queries **/
protected Properties default_properties;
@@ -86,7 +110,7 @@
/** The offset of the current query in the queries list **/
protected int current_query_index;
-
+
/** The name of the value element we're currently receiving **/
protected String current_name;
@@ -96,9 +120,15 @@
/** The value of the value element we're currently receiving **/
protected StringBuffer current_value;
+ /** Check if nr of rows need to be written out. **/
+ protected String showNrOfRows;
+
protected ComponentSelector dbSelector = null;
protected ComponentManager manager;
+ protected Parameters parameters;
+ protected Map objectModel;
+
public void compose(ComponentManager manager) {
try {
this.manager = manager;
@@ -111,7 +141,7 @@
/**
* Recycle this component
*/
- public void recycle() {
+ public void recylce() {
super.recycle();
this.queries = null;
this.current_value = null;
@@ -119,6 +149,13 @@
this.default_properties = null;
}
+ /**
+ * dispose
+ */
+ public void dispose() {
+ if(this.dbSelector != null) this.manager.release((Component)
this.dbSelector);
+ }
+
/** BEGIN SitemapComponent methods **/
public void setup(SourceResolver resolver, Map objectModel,
@@ -131,28 +168,28 @@
this.current_query_index = -1;
this.default_properties = new Properties();
this.current_state = SQLTransformer.STATE_OUTSIDE;
+
+ this.parameters = parameters;
+ this.objectModel = objectModel;
+
+ current_state = SQLTransformer.STATE_OUTSIDE;
// Check for connection
String parameter =
parameters.getParameter(SQLTransformer.MAGIC_CONNECTION, null);
if (parameter != null) {
getLogger().debug("CONNECTION: "+parameter);
-
default_properties.setProperty(SQLTransformer.MAGIC_CONNECTION,
parameter);
} else {
-
// Check the dburl
parameter =
parameters.getParameter(SQLTransformer.MAGIC_DBURL,null);
if (parameter != null) {
getLogger().debug("DBURL: "+parameter);
-
default_properties.setProperty(SQLTransformer.MAGIC_DBURL,parameter);
}
-
// Check the username
parameter =
parameters.getParameter(SQLTransformer.MAGIC_USERNAME,null);
if (parameter != null) {
getLogger().debug("USERNAME: "+parameter);
-
default_properties.setProperty(SQLTransformer.MAGIC_USERNAME,parameter);
}
@@ -162,16 +199,9 @@
default_properties.setProperty(SQLTransformer.MAGIC_PASSWORD,parameter);
}
}
-
- }
-
- /**
- * dispose
- */
- public void dispose() {
- if(this.dbSelector != null) this.manager.release((Component)
this.dbSelector);
+ showNrOfRows =
parameters.getParameter(SQLTransformer.MAGIC_NR_OF_ROWS,null);
}
-
+
/** END SitemapComponent methods **/
/** BEGIN my very own methods **/
@@ -180,7 +210,8 @@
* This will be the meat of SQLTransformer, where the query is run.
*/
protected void executeQuery(int index) throws SAXException {
- this.contentHandler.startPrefixMapping("",my_uri);
+// this.contentHandler.startPrefixMapping("",my_uri);
+ getLogger().debug("SQLTransformer executing query nr " + index);
AttributesImpl attr = new AttributesImpl();
Query query = (Query) queries.elementAt(index);
try {
@@ -189,22 +220,36 @@
getLogger().debug("SQLTransformer.executeQuery()", e);
throw new SAXException(e);
}
- this.start(query.rowset_name, attr);
try {
- while (query.next()) {
- this.start(query.row_name, attr);
- query.serializeRow();
- if (index + 1 < queries.size()) {
- executeQuery(index + 1);
- }
- this.end(query.row_name);
+ if (showNrOfRows!=null && showNrOfRows.equalsIgnoreCase("true"))
{
+
attr.addAttribute(my_uri,query.nr_of_rows,query.nr_of_rows,"CDATA",
+ String.valueOf(query.getNrOfRows()));
+ }
+ String name = query.getName();
+ if (name !=null) {
+
attr.addAttribute(my_uri,query.name_attribute,query.name_attribute,"CDATA",
+ name);
+ }
+ this.start(query.rowset_name, attr);
+ attr=new AttributesImpl();
+ if (!query.isStoredProcedure()) {
+ while (query.next()) {
+ this.start(query.row_name, attr);
+ query.serializeRow();
+ if (index + 1 < queries.size()) {
+ executeQuery(index + 1);
+ }
+ this.end(query.row_name);
+ }
+ } else {
+ query.serializeStoredProcedure();
}
} catch (SQLException e) {
getLogger().debug("SQLTransformer.executeQuery()", e);
throw new SAXException(e);
}
this.end(query.rowset_name);
- this.contentHandler.endPrefixMapping("");
+// this.contentHandler.endPrefixMapping("");
}
protected static void throwIllegalStateException(String message) {
@@ -242,11 +287,21 @@
switch (current_state) {
case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
current_value.setLength(0);
+ Query q = getCurrentQuery();
current_state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
String isupdate =
- attributes.getValue("",
SQLTransformer.MAGIC_UPDATE_ATTRIBUTE);
- if (isupdate != null && !isupdate.equalsIgnoreCase("false"))
- getCurrentQuery().setUpdate(true);
+ attributes.getValue("",
SQLTransformer.MAGIC_UPDATE_ATTRIBUTE);
+ if (isupdate != null && !isupdate.equalsIgnoreCase("false"))
+ q.setUpdate(true);
+ String isstoredprocedure =
+ attributes.getValue("",
SQLTransformer.MAGIC_STORED_PROCEDURE_ATTRIBUTE);
+ if (isstoredprocedure != null &&
!isstoredprocedure.equalsIgnoreCase("false"))
+ q.setStoredProcedure(true);
+ String name =
+ attributes.getValue("",
SQLTransformer.MAGIC_NAME_ATTRIBUTE);
+ if (name != null) {
+ q.setName(name);
+ }
break;
default:
throwIllegalStateException("Not expecting a start query
element");
@@ -261,7 +316,6 @@
current_value.toString());
getLogger().debug("QUERY IS \""+
current_value.toString() + "\"");
-
current_value.setLength(0);
}
current_state =
SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
@@ -345,6 +399,82 @@
current_state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
}
+ protected void startSubstituteValueElement(Attributes attributes) {
+ switch (current_state) {
+ case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
+ String name = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE);
+ if (name == null) {
+ throwIllegalStateException("Substitute value elements
must have a "+
+
SQLTransformer.MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE + " attribute");
+ }
+ String substitute = parameters.getParameter(name,null);
+ //escape single quote
+ substitute = replaceCharWithString(substitute,'\'',"''");
+ getLogger().debug("SUBSTITUTE VALUE "+substitute);
+ if (current_value.length() > 0) {
+ getCurrentQuery().addQueryPart(current_value.toString());
+ getLogger().debug("QUERY IS \""+
+ current_value.toString() + "\"");
+
+ current_value.setLength(0);
+ }
+ getCurrentQuery().addQueryPart(substitute);
+ current_state =
SQLTransformer.STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT;
+ break;
+ default:
+ throwIllegalStateException("Not expecting a start substitute
value element");
+ }
+ }
+
+ protected void endSubstituteValueElement() {
+ current_state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
+ }
+
+ protected void startInParameterElement(Attributes attributes) {
+ switch (current_state) {
+ case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
+ String nr = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_IN_PARAMETER_NR_ATTRIBUTE);
+ String value = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE);
+ getLogger().debug("IN PARAMETER NR " + nr + "; VALUE "+ value);
+ int position = Integer.parseInt(nr);
+ getCurrentQuery().setInParameter(position,value);
+ current_state = SQLTransformer.STATE_INSIDE_IN_PARAMETER_ELEMENT;
+ break;
+ default:
+ throwIllegalStateException("Not expecting an in-parameter
element");
+ }
+ }
+
+ protected void endInParameterElement() {
+ current_state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
+ }
+
+ protected void startOutParameterElement(Attributes attributes) {
+ switch (current_state) {
+ case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
+ String name = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE);
+ String nr = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_OUT_PARAMETER_NR_ATTRIBUTE);
+ String type = attributes.getValue(my_uri,
+
SQLTransformer.MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE);
+ getLogger().debug("OUT PARAMETER NAME" + name +";NR " + nr + ";
TYPE "+ type);
+ int position = Integer.parseInt(nr);
+ getCurrentQuery().setOutParameter(position,type,name);
+ current_state =
SQLTransformer.STATE_INSIDE_OUT_PARAMETER_ELEMENT;
+ break;
+ default:
+ throwIllegalStateException("Not expecting an out-parameter
element");
+ }
+ }
+
+ protected void endOutParameterElement() {
+ current_state = SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT;
+ }
+
protected Query getCurrentQuery() {
return (Query) queries.elementAt(current_query_index);
}
@@ -352,6 +482,25 @@
protected Query getQuery(int i) {
return (Query) queries.elementAt(i);
}
+
+ private String replaceCharWithString(String in, char c, String with) {
+ Tokenizer tok;
+ StringBuffer replaced=null;
+ if (in.indexOf(c)>-1) {
+ tok = new Tokenizer(in,c);
+ replaced=new StringBuffer();
+ while (tok.hasMoreTokens()) {
+ replaced.append(tok.nextToken());
+ if (tok.hasMoreTokens())
+ replaced.append(with);
+ }
+ }
+ if (replaced!=null) {
+ return replaced.toString();
+ } else {
+ return in;
+ }
+ }
/** END my very own methods **/
@@ -378,7 +527,13 @@
startQueryElement(attributes);
} else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
startAncestorValueElement(attributes);
- } else {
+ } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
+ startSubstituteValueElement(attributes);
+ } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
+ startInParameterElement(attributes);
+ } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
+ startOutParameterElement(attributes);
+ } else {
startValueElement(name);
}
}
@@ -389,8 +544,7 @@
super.endElement(uri, name, raw);
return;
}
- getLogger().debug("RECEIVED END ELEMENT "+name + "("+uri +
- ")");
+ getLogger().debug("RECEIVED END ELEMENT "+name + "("+uri + ")");
if (name.equals(SQLTransformer.MAGIC_EXECUTE_QUERY)) {
endExecuteQueryElement();
@@ -398,6 +552,12 @@
endQueryElement();
} else if (name.equals(SQLTransformer.MAGIC_ANCESTOR_VALUE)) {
endAncestorValueElement();
+ } else if (name.equals(SQLTransformer.MAGIC_SUBSTITUTE_VALUE)) {
+ endSubstituteValueElement();
+ } else if (name.equals(SQLTransformer.MAGIC_IN_PARAMETER)) {
+ endInParameterElement();
+ } else if (name.equals(SQLTransformer.MAGIC_OUT_PARAMETER)) {
+ endOutParameterElement();
} else if (name.equals(SQLTransformer.MAGIC_VALUE) || current_state
== SQLTransformer.STATE_INSIDE_VALUE_ELEMENT) {
endValueElement();
} else {
@@ -413,7 +573,6 @@
}
getLogger().debug("RECEIVED CHARACTERS: "+
new String(ary, start, length));
-
current_value.append(ary, start, length);
}
@@ -432,9 +591,10 @@
}
private void data(String data) throws SAXException {
- if (data != null)
- super.contentHandler.characters(data.toCharArray(), 0,
+ if (data != null) {
+ super.contentHandler.characters(data.toCharArray(), 0,
data.length());
+ }
}
protected static String getStringValue(Object object) {
@@ -467,30 +627,47 @@
protected Properties properties;
/** Dummy static variables for the moment **/
- protected String rowset_name = "ROWSET";
- protected String row_name = "ROW";
+ protected String rowset_name = "rowset";
+ protected String nr_of_rows = "nrofrows";
+ protected String row_name = "row";
+ protected String name_attribute = "name";
/** The connection, once opened **/
protected Connection conn;
- /** And the statement **/
- protected Statement st;
+ /** And the statements **/
+ protected PreparedStatement pst;
+ protected CallableStatement cst;
/** The results, of course **/
protected ResultSet rs = null;
/** And the results' metadata **/
protected ResultSetMetaData md = null;
+
+ /** If this query is actually an update (insert, update, delete) **/
+ protected boolean isupdate = false;
- /** If this query is actually an update (insert, update, delete) **/
- protected boolean isupdate = false;
+ /** If this query is actually a stored procedure **/
+ protected boolean isstoredprocedure = false;
- /** If it is an update/etc, the return value (num rows modified) **/
- protected int rv = -1;
+ protected String name=null;
+
+ /** If it is an update/etc, the return value (num rows modified) **/
+ protected int rv = -1;
/** The parts of the query **/
protected Vector query_parts = new Vector();
+ /** In parameters **/
+ protected HashMap inParameters=null;
+
+ /** Out parameters **/
+ protected HashMap outParameters=null;
+
+ /** Mapping out parameters - objectModel **/
+ protected HashMap outParametersNames=null;
+
protected Query(SQLTransformer transformer, int query_index) {
this.transformer = transformer;
this.query_index = query_index;
@@ -505,6 +682,87 @@
isupdate = flag;
}
+ protected void setStoredProcedure(boolean flag) {
+ isstoredprocedure = flag;
+ }
+
+ protected boolean isStoredProcedure() {
+ return isstoredprocedure;
+ }
+
+ protected void setName(String name) {
+ this.name = name;
+ }
+
+ protected String getName() {
+ return name;
+ }
+
+ protected void setInParameter(int pos, String val) {
+ if (inParameters == null) {
+ inParameters = new HashMap();
+ }
+ inParameters.put(new Integer(pos),val);
+ }
+
+ protected void setOutParameter(int pos, String type, String name) {
+ if (outParameters == null) {
+ outParameters = new HashMap();
+ outParametersNames = new HashMap();
+ }
+ outParameters.put(new Integer(pos),type);
+ outParametersNames.put(new Integer(pos),name);
+ }
+
+ private void registerInParameters(PreparedStatement pst) throws
SQLException{
+ if (inParameters==null)
+ return;
+ Iterator itInKeys = inParameters.keySet().iterator();
+ Integer counter;
+ String value;
+ while (itInKeys.hasNext()) {
+ counter = (Integer)itInKeys.next();
+ value = (String)inParameters.get(counter);
+ try {
+ pst.setObject(counter.intValue(),value);
+ }catch (SQLException e) {
+ transformer.getTheLogger().error("Caught a SQLException", e);
+ throw e;
+ }
+ }
+ }
+
+ private void registerOutParameters(CallableStatement cst) throws
SQLException {
+ if (outParameters==null)
+ return;
+ Iterator itOutKeys = outParameters.keySet().iterator();
+ Integer counter;
+ int index;
+ String type, className, fieldName;
+ Class clss;
+ Field fld;
+ while (itOutKeys.hasNext()) {
+ counter = (Integer)itOutKeys.next();
+ type = (String)outParameters.get(counter);
+ index = type.lastIndexOf(".");
+ if (index>-1) {
+ className = type.substring(0,index);
+ fieldName = type.substring(index+1,type.length());
+ }else {
+ transformer.getTheLogger().error("Invalid SQLType: " +type,
null);
+ throw new SQLException("Wrong SQLType");
+ }
+ try {
+ clss = Class.forName(className);
+ fld = clss.getField(fieldName);
+
cst.registerOutParameter(counter.intValue(),fld.getInt(fieldName));
+ }catch (Exception e) { //lots of different exceptions to catch
+ transformer.getTheLogger().error("Invalid SQLType: " +
+ className +"."+fieldName, e);
+ }
+ }
+ }
+
protected void execute() throws SQLException {
if (null !=
properties.getProperty(SQLTransformer.MAGIC_DOC_ELEMENT)) {
this.rowset_name =
properties.getProperty(SQLTransformer.MAGIC_DOC_ELEMENT);
@@ -536,6 +794,7 @@
}
}
String query = sb.toString();
+ transformer.getTheLogger().debug("EXECUTING " + query);
DataSourceComponent datasource = null;
try {
if (connection != null) {
@@ -549,12 +808,24 @@
password);
}
}
- st = conn.createStatement();
- if (isupdate)
- rv = st.executeUpdate(query);
- else {
- rs = st.executeQuery(query);
- md = rs.getMetaData();
+ if (!isstoredprocedure) {
+ pst = conn.prepareStatement(query,
+
ResultSet.TYPE_SCROLL_INSENSITIVE,
+ ResultSet.CONCUR_READ_ONLY );
+ } else {
+ cst = conn.prepareCall(query,
+
ResultSet.TYPE_SCROLL_INSENSITIVE,
+ ResultSet.CONCUR_READ_ONLY );
+ registerOutParameters(cst);
+ pst = cst;
+ }
+ registerInParameters(pst);
+ boolean result = pst.execute();
+ if (result) {
+ rs = pst.getResultSet();
+ md = rs.getMetaData();
+ } else {
+ rv = pst.getUpdateCount();
}
} catch (SQLException e) {
transformer.getTheLogger().error("Caught a SQLException", e);
@@ -562,11 +833,33 @@
} catch (ComponentException cme) {
transformer.getTheLogger().error("Could not use connection:
" + connection, cme);
} finally {
- close();
+ if (conn!=null && !conn.isClosed()) {
+ conn.close();
+ }
if (datasource != null) dbSelector.release(datasource);
}
}
+ protected int getNrOfRows() throws SQLException {
+ int nr = 0;
+ if (rs!=null) {
+ try {
+ rs.last();
+ nr = rs.getRow();
+ rs.beforeFirst();
+ } catch (SQLException e) {
+ transformer.getTheLogger().debug("SQLTransformer", e);
+ close();
+ throw e;
+ }
+ } else {
+ if (outParameters!=null) {
+ nr=outParameters.size();
+ }
+ }
+ return nr;
+ }
+
protected String getColumnValue(int i) throws SQLException {
try {
return transformer.getStringValue(rs.getObject(i));
@@ -589,15 +882,15 @@
protected boolean next() throws SQLException {
try {
- // if rv is not -1, then an SQL insert, update, etc, has
- // happened (see JDBC docs - return codes for executeUpdate)
- if (rv != -1)
- return true;
- if (rs == null || !rs.next()) {
- close();
- return false;
- }
+ // if rv is not -1, then an SQL insert, update, etc, has
+ // happened (see JDBC docs - return codes for executeUpdate)
+ if (rv != -1)
return true;
+ if (rs == null || !rs.next()) {
+ close();
+ return false;
+ }
+ return true;
} catch (SQLException e) {
transformer.getTheLogger().debug("SQLTransformer", e);
close();
@@ -609,14 +902,15 @@
try {
if (rs != null)
rs.close();
- rs = null;
- if (st != null)
- st.close();
- st = null;
+ if (pst != null)
+ pst.close();
+ if (cst != null)
+ cst.close();
} finally {
- if (conn != null)
- conn.close();
- conn = null;
+ // already closed in execute()
+// if (conn!=null && !conn.isClosed()) {
+// conn.close();
+// }
}
}
@@ -625,32 +919,77 @@
}
protected void serializeRow() throws SQLException, SAXException {
- AttributesImpl attr = new AttributesImpl();
-
- if (!isupdate) {
- for (int i = 1; i <= md.getColumnCount(); i++) {
- transformer.start(md.getColumnName(i).toLowerCase(),
attr);
- try {
- transformer.data(getColumnValue(i));
- } catch (SQLException e) {
- transformer.getTheLogger().debug("SQLTransformer",
e);
- close();
- throw e;
- }
- transformer.end(md.getColumnName(i).toLowerCase());
+ AttributesImpl attr = new AttributesImpl();
+ if (!isupdate && !isstoredprocedure) {
+ for (int i = 1; i <= md.getColumnCount(); i++) {
+ transformer.start(md.getColumnName(i).toLowerCase(), attr);
+ try {
+ transformer.data(getColumnValue(i));
+ } catch (SQLException e) {
+ transformer.getTheLogger().debug("SQLTransformer", e);
+ close();
+ throw e;
}
- } else {
- transformer.start("returncode", attr);
- transformer.data(String.valueOf(rv));
- transformer.end("returncode");
- rv = -1; // we only want the return code shown once.
- }
+ transformer.end(md.getColumnName(i).toLowerCase());
+ }
+ } else if (isupdate && !isstoredprocedure){
+ transformer.start("returncode", attr);
+ transformer.data(String.valueOf(rv));
+ transformer.end("returncode");
+ rv = -1; // we only want the return code shown once.
+ }
+ }
+
+ protected void serializeStoredProcedure() throws SQLException,
SAXException {
+ if (outParametersNames==null || cst==null)
+ return;
+ //make sure output follows order as parameter order in stored
procedure
+ Iterator itOutKeys = (new
TreeMap(outParameters)).keySet().iterator();
+ Integer counter;
+ AttributesImpl attr = new AttributesImpl();
+ while (itOutKeys.hasNext()) {
+ counter = (Integer)itOutKeys.next();
+ try {
+ Object obj = cst.getObject(counter.intValue());
+ if (!(obj instanceof ResultSet)) {
+ transformer.start((String)outParametersNames.get(counter),
attr);
+ transformer.data(transformer.getStringValue(obj));
+ transformer.end((String)outParametersNames.get(counter));
+ } else {
+ ResultSet rs = (ResultSet)obj;
+
transformer.start((String)outParametersNames.get(counter), attr);
+ ResultSetMetaData md = rs.getMetaData();
+ while (rs.next()) {
+ transformer.start(this.row_name, attr);
+ for (int i = 1; i <= md.getColumnCount(); i++) {
+
transformer.start(md.getColumnName(i).toLowerCase(), attr);
+ try {
+
transformer.data(transformer.getStringValue(rs.getObject(i)));
+ } catch (SQLException e) {
+
transformer.getTheLogger().debug("SQLTransformer", e);
+ rs.close();
+ close();
+ throw e;
+ }
+ transformer.end(md.getColumnName(i).toLowerCase());
+ }
+ transformer.end(this.row_name);
+ }
+ rs.close();
+ transformer.end((String)outParametersNames.get(counter));
+ }
+ }catch (SQLException e) {
+ transformer.getTheLogger().error("Caught a SQLException", e);
+ throw e;
+ } finally {
+ close();
+ }
+ }
+
}
-
}
private class AncestorValue {
-
protected int level;
protected String name;
1.1
xml-cocoon2/src/org/apache/cocoon/transformation/FilterTransformer.java
Index: FilterTransformer.java
===================================================================
package org.apache.cocoon.transformation;
import java.util.Map;
import java.io.IOException;
import org.apache.cocoon.ProcessingException;
import org.apache.cocoon.transformation.AbstractTransformer;
import org.apache.cocoon.environment.SourceResolver;
import org.apache.avalon.framework.parameters.Parameters;
import org.apache.avalon.excalibur.pool.Poolable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.AttributesImpl;
/**
* The filter transformer can be used to let only an amount of elements
through in
* a given block.
*
* Usage in sitemap
* <map:transform type="filter">
* <map:parameter name="element-name" value="row"/>
* <map:parameter name="count" value="5"/>
* <map:parameter name="blocknr" value="3"/>
* </map:transform>
*
* Only the 3th block will be shown, containing only 5 row elements.
*
* @author <a href="mailto:[EMAIL PROTECTED]">Sven Beauprez</a>
* @version CVS $Revision: 1.1 $ $Date: 2001/06/21 11:07:56 $ $Author: dims $
*/
public class FilterTransformer extends AbstractTransformer implements
Poolable {
private static final String ELEMENT = "element-name";
private static final String COUNT = "count";
private static final String BLOCKNR = "blocknr";
private static final String BLOCK = "block";
private static final String BLOCKID = "id";
private static final int DEFAULT_COUNT = 10;
private static final int DEFAULT_BLOCK = 1;
private int counter;
private int count;
private int blocknr;
private int currentBlocknr;
private String elementName;
private String parentName;
boolean skip;
boolean foundIt;
/** BEGIN SitemapComponent methods **/
public void setup(SourceResolver resolver, Map objectModel, String source,
Parameters parameters)
throws ProcessingException, SAXException, IOException {
counter=0;
currentBlocknr=0;
skip=false;
foundIt=false;
parentName=null;
elementName = parameters.getParameter(FilterTransformer.ELEMENT,null);
String tmpCount =
parameters.getParameter(FilterTransformer.COUNT,""+FilterTransformer.DEFAULT_COUNT);
String tmpBlockNr =
parameters.getParameter(FilterTransformer.BLOCKNR,""+FilterTransformer.DEFAULT_BLOCK);
if (elementName==null || tmpCount==null) {
getLogger().error("FilterTransformer: both "+ FilterTransformer.ELEMENT
+ " and " +
FilterTransformer.COUNT + " parameters need to be
specified");
}else {
try {
count = Integer.parseInt(tmpCount);
getLogger().debug("FilterTransformer: " + FilterTransformer.COUNT +
"=" + count);
} catch (NumberFormatException e) {
//set default values so we can move on
count=FilterTransformer.DEFAULT_COUNT;
getLogger().error("FilterTransformer: " + FilterTransformer.COUNT +
" not valid, using default value of
"+FilterTransformer.DEFAULT_COUNT);
}
try {
blocknr = Integer.parseInt(tmpBlockNr);
getLogger().debug("FilterTransformer: " + FilterTransformer.BLOCKNR +
"=" + blocknr);
} catch (NumberFormatException e) {
//set default values so we can move on
blocknr=FilterTransformer.DEFAULT_BLOCK;
getLogger().error("FilterTransformer: " + FilterTransformer.BLOCKNR +
" not valid, using default value of "+
FilterTransformer.DEFAULT_BLOCK);
}
}
}
/** END SitemapComponent methods **/
/** BEGIN SAX ContentHandler handlers **/
public void startElement(String uri, String name, String raw, Attributes
attributes)
throws SAXException {
if (name.equalsIgnoreCase(elementName)) {
foundIt=true;
counter++;
if (counter <= (count*(blocknr)) && counter > (count*(blocknr-1))) {
skip=false;
} else {
skip=true;
}
if (currentBlocknr != (int)Math.ceil((float)counter/count)) {
currentBlocknr = (int)Math.ceil((float)counter/count);
AttributesImpl attr = new AttributesImpl();
attr.addAttribute(uri,FilterTransformer.BLOCKID,FilterTransformer.BLOCKID,"CDATA",String.valueOf(currentBlocknr));
if (counter < count) {
super.contentHandler.startElement(uri,FilterTransformer.BLOCK,FilterTransformer.BLOCK,attr);
} else {
super.contentHandler.endElement(uri,FilterTransformer.BLOCK,FilterTransformer.BLOCK);
super.contentHandler.startElement(uri,FilterTransformer.BLOCK,FilterTransformer.BLOCK,attr);
}
}
} else if (!foundIt) {
parentName = name;
}
if (!skip) {
super.contentHandler.startElement(uri,name,raw,attributes);
}
}
public void endElement(String uri,String name,String raw)
throws SAXException {
if (name.equalsIgnoreCase(parentName) && foundIt) {
super.contentHandler.endElement(uri,FilterTransformer.BLOCK,FilterTransformer.BLOCK);
super.contentHandler.endElement(uri,name,raw);
foundIt=false;
skip=false;
} else if (!skip) {
super.contentHandler.endElement(uri,name,raw);
}
}
public void characters(char c[], int start, int len)
throws SAXException {
if (!skip) {
super.contentHandler.characters(c,start,len);
}
}
public void processingInstruction(String target, String data)
throws SAXException {
if (!skip) {
super.contentHandler.processingInstruction(target, data);
}
}
public void startEntity(String name)
throws SAXException {
if (!skip) {
super.lexicalHandler.startEntity(name);
}
}
public void endEntity(String name)
throws SAXException {
if (!skip) {
super.lexicalHandler.endEntity( name);
}
}
public void startCDATA()
throws SAXException {
if (!skip) {
super.lexicalHandler.startCDATA();
}
}
public void endCDATA()
throws SAXException {
if (!skip) {
super.lexicalHandler.endCDATA();
}
}
public void comment(char ch[], int start, int len)
throws SAXException {
if (!skip) {
super.lexicalHandler.comment(ch, start, len);
}
}
/** END SAX ContentHandler handlers **/
}
1.1
xml-cocoon2/src/org/apache/cocoon/transformation/ReadDOMSessionTransformer.java
Index: ReadDOMSessionTransformer.java
===================================================================
package org.apache.cocoon.transformation;
import java.util.Map;
import java.io.IOException;
import org.apache.cocoon.Constants;
import org.apache.cocoon.environment.Session;
import org.apache.cocoon.environment.Request;
import org.apache.cocoon.environment.SourceResolver;
import org.apache.cocoon.ProcessingException;
import org.apache.cocoon.transformation.AbstractTransformer;
import org.apache.cocoon.xml.dom.DOMStreamer;
import org.apache.avalon.framework.parameters.Parameters;
import org.apache.avalon.excalibur.pool.Poolable;
import org.w3c.dom.Node;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
/**
* With this transformer, a DOM-object that is stored in the session, can be
inserted
* in the SAX stream at a given position.
*
* Usage in sitemap:
* <map:transform type="readDOMsession">
* <map:parameter name="dom-name" value="companyInfo"/>
* <map:parameter name="trigger-element" value="company"/>
* <map:parameter name="position" value="after"/>
* </map:transform>
*
* where:
* dom-name is the name of the DOM object in the session
* trigger-element is the element that we need to insert the SAX events
* postion is the actual place where the stream will be inserted, ie before,
after or in
* the trigger-element
*
* @author <a href="mailto:[EMAIL PROTECTED]">Sven Beauprez</a>
* @version CVS $Revision: 1.1 $ $Date: 2001/06/21 11:07:56 $ $Author: dims $
*/
public class ReadDOMSessionTransformer extends AbstractTransformer implements
Poolable {
public static final String DOM_NAME = "dom-name";
public static final String TRIGGER_ELEMENT = "trigger-element";
/*
position where the sax events from the dom should be insterted
this can be: 'before', 'after' or 'in'
*/
public static final String POSITION = "position";
Node node;
DOMStreamer streamer;
Session session;
String DOMName;
String trigger;
String position;
/** BEGIN SitemapComponent methods **/
public void setup(SourceResolver resolver, Map objectModel,
String source, Parameters parameters)
throws ProcessingException, SAXException, IOException {
Request request = (Request)(objectModel.get(Constants.REQUEST_OBJECT));
session = request.getSession(false);
if (session != null) {
getLogger().debug("ReadSessionTransformer:
SessioID="+session.getId());
getLogger().debug("ReadSessionTransformer: Session available, try to
read from it");
DOMName =
parameters.getParameter(ReadDOMSessionTransformer.DOM_NAME,null);
trigger =
parameters.getParameter(ReadDOMSessionTransformer.TRIGGER_ELEMENT,null);
position =
parameters.getParameter(ReadDOMSessionTransformer.POSITION,"in");
getLogger().debug("ReadSessionTransformer: " +
ReadDOMSessionTransformer.DOM_NAME + "="+
DOMName + " " +
ReadDOMSessionTransformer.TRIGGER_ELEMENT + "=" +
trigger + " " + ReadDOMSessionTransformer.POSITION
+ "=" +
position);
} else {
getLogger().error("ReadSessionTransformer: no session object");
}
}
/** END SitemapComponent methods **/
/** BEGIN SAX ContentHandler handlers **/
public void startElement(String uri, String name, String raw, Attributes
attributes)
throws SAXException {
//start streaming after certain startelement is encountered
if (name.equalsIgnoreCase(trigger)) {
getLogger().debug("ReadSessionTransformer: trigger encountered");
if (position.equalsIgnoreCase("before")) {
streamDOM();
super.contentHandler.startElement(uri,name,raw,attributes);
} else if (position.equalsIgnoreCase("in")) {
super.contentHandler.startElement(uri,name,raw,attributes);
streamDOM();
} else if (position.equalsIgnoreCase("after")) {
super.contentHandler.startElement(uri,name,raw,attributes);
}
} else {
super.contentHandler.startElement(uri,name,raw,attributes);
}
}
public void endElement(String uri,String name,String raw)
throws SAXException {
super.contentHandler.endElement(uri,name,raw);
if (name.equalsIgnoreCase(trigger)) {
if (position.equalsIgnoreCase("after")) {
streamDOM();
}
}
}
/** END SAX ContentHandler handlers **/
/** own methods **/
private void streamDOM()
throws SAXException {
if (DOMName!=null) {
node = (Node)session.getAttribute(DOMName);
} else {
getLogger().error("ReadSessionTransformer: no "+
ReadDOMSessionTransformer.DOM_NAME
+" parameter specified");
}
if (node!=null) {
getLogger().debug("ReadSessionTransformer: start streaming");
streamer = new DOMStreamer(super.contentHandler,super.lexicalHandler);
streamer.stream(node);
}else {
getLogger().error("ReadSessionTransformer: no Document in session");
}
}
}
1.1
xml-cocoon2/src/org/apache/cocoon/transformation/WriteDOMSessionTransformer.java
Index: WriteDOMSessionTransformer.java
===================================================================
package org.apache.cocoon.transformation;
import java.util.Map;
import java.io.IOException;
import javax.xml.parsers.ParserConfigurationException;
import org.apache.cocoon.Constants;
import org.apache.cocoon.Roles;
import org.apache.cocoon.environment.Session;
import org.apache.cocoon.environment.Request;
import org.apache.cocoon.environment.SourceResolver;
import org.apache.cocoon.ProcessingException;
import org.apache.cocoon.transformation.AbstractTransformer;
import org.apache.cocoon.xml.dom.DOMBuilder;
import org.apache.cocoon.components.parser.Parser;
import org.apache.avalon.framework.activity.Disposable;
import org.apache.avalon.framework.component.Component;
import org.apache.avalon.framework.component.Composable;
import org.apache.avalon.framework.component.ComponentManager;
import org.apache.avalon.framework.parameters.Parameters;
import org.apache.avalon.excalibur.pool.Poolable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
/**
* Make a DOM object from SAX events and write it to the session.
*
* Usage in sitemap:
* <map:transform type="writeDOMsession">
* <map:parameter name="dom-name" value="content"/>
* <map:parameter name="dom-root-element" value="companies"/>
* </map:transform>
*
* Where:
* dom-name is the name for the DOM object in the session
* dom-root-element is the trigger that will be the root element of the DOM
*
* @author <a href="mailto:[EMAIL PROTECTED]">Sven Beauprez</a>
* @version CVS $Revision: 1.1 $ $Date: 2001/06/21 11:07:57 $ $Author: dims $
*/
public class WriteDOMSessionTransformer extends AbstractTransformer
implements
Poolable, Composable, Disposable {
public static final String DOM_NAME = "dom-name";
public static final String DOM_ROOT_ELEMENT = "dom-root-element";
private boolean buildDom = false;
private boolean sessionAvailable = false;
private Session session;
private Parameters parameters;
private DOMBuilder builder;
private ComponentManager manager;
private Parser parser;
private String DOMName;
private String rootElement;
/** BEGIN SitemapComponent methods **/
public void setup(SourceResolver resolver, Map objectModel,
String source, Parameters parameters)
throws ProcessingException, SAXException, IOException {
getLogger().debug("WriteSessionTransformer: setup");
Request request = (Request)(objectModel.get(Constants.REQUEST_OBJECT));
session = request.getSession(false);
if (session != null) {
DOMName =
parameters.getParameter(WriteDOMSessionTransformer.DOM_NAME,null);
rootElement =
parameters.getParameter(WriteDOMSessionTransformer.DOM_ROOT_ELEMENT,null);
if (DOMName!=null && rootElement!=null) {
//only now we know it is usefull to store something in the session
getLogger().debug("WriteSessionTransformer:
"+WriteDOMSessionTransformer.DOM_NAME + "=" +
DOMName + "; " +
WriteDOMSessionTransformer.DOM_ROOT_ELEMENT + "=" +
rootElement);
sessionAvailable = true;
} else {
getLogger().error("WriteSessionTransformer: need " +
WriteDOMSessionTransformer.DOM_NAME +
" and " +
WriteDOMSessionTransformer.DOM_ROOT_ELEMENT + " parameters");
}
} else {
getLogger().error("WriteSessionTransformer: no session object");
}
}
/** END SitemapComponent methods **/
public void compose(ComponentManager manager) {
this.manager = manager;
try {
getLogger().debug("Looking up " + Roles.PARSER);
parser = (Parser)manager.lookup(Roles.PARSER);
} catch (Exception e) {
getLogger().error("Could not find component", e);
}
}
public void dispose() {
if (parser!=null) {
manager.release((Component)parser);
}
}
/** BEGIN SAX ContentHandler handlers **/
public void startPrefixMapping(String prefix, String uri)
throws SAXException {
super.startPrefixMapping(prefix,uri);
if (buildDom) {
builder.startPrefixMapping(prefix,uri);
}
}
public void startElement(String uri, String name, String raw, Attributes
attributes)
throws SAXException {
//only build the DOM tree if session is available
if (name.equalsIgnoreCase(rootElement) && sessionAvailable) {
getLogger().debug("WriteSessionTransformer: start building DOM
tree");
buildDom = true;
builder = new DOMBuilder(parser);
builder.startDocument();
builder.startElement(uri,name,raw,attributes);
} else if (buildDom) {
builder.startElement(uri,name,raw,attributes);
}
super.contentHandler.startElement(uri,name,raw,attributes);
}
public void endElement(String uri, String name, String raw)
throws SAXException {
if (name.equalsIgnoreCase(rootElement) && sessionAvailable) {
buildDom = false;
builder.endElement(uri,name,raw);
builder.endDocument();
getLogger().debug("WriteSessionTransformer: putting DOM tree in
session object");
session.setAttribute(DOMName,builder.getDocument().getFirstChild());
getLogger().debug("WriteSessionTransformer: DOM tree is in session
object");
} else if (buildDom) {
builder.endElement(uri,name,raw);
}
super.contentHandler.endElement(uri,name,raw);
}
public void characters(char c[], int start, int len)
throws SAXException {
if (buildDom) {
builder.characters(c,start,len);
}
super.contentHandler.characters(c,start,len);
}
public void startCDATA()
throws SAXException {
if (buildDom) {
builder.startCDATA();
}
super.lexicalHandler.startCDATA();
}
public void endCDATA()
throws SAXException {
if (buildDom) {
builder.endCDATA();
}
super.lexicalHandler.endCDATA();
}
/** END SAX ContentHandler handlers **/
}
1.10 +2 -0 xml-cocoon2/xdocs/docs-book.xml
Index: docs-book.xml
===================================================================
RCS file: /home/cvs/xml-cocoon2/xdocs/docs-book.xml,v
retrieving revision 1.9
retrieving revision 1.10
diff -u -r1.9 -r1.10
--- docs-book.xml 2001/06/19 17:28:52 1.9
+++ docs-book.xml 2001/06/21 11:07:59 1.10
@@ -29,6 +29,8 @@
<page id="logicsheet-esql" label="ESQL Logicsheet" source="esql.xml"/>
<page id="logicsheet-forms" label="Forms" source="logicsheet-forms.xml"/>
<separator/>
+ <page id="sql-transformer" label="SQL Transformer"
source="sql-transformer.xml"/>
+<separator/>
<external label="XML Links"
href="http://dmoz.org/Computers/Data_Formats/Markup_Languages/XML/"/>
<separator/>
<page id="who" label="Who we are" source="who.xml"/>
1.13 +2 -0 xml-cocoon2/xdocs/site-book.xml
Index: site-book.xml
===================================================================
RCS file: /home/cvs/xml-cocoon2/xdocs/site-book.xml,v
retrieving revision 1.12
retrieving revision 1.13
diff -u -r1.12 -r1.13
--- site-book.xml 2001/06/19 17:28:53 1.12
+++ site-book.xml 2001/06/21 11:08:00 1.13
@@ -33,6 +33,8 @@
<page id="logicsheet-esql" label="ESQL Logicsheet" source="esql.xml"/>
<page id="logicsheet-forms" label="Forms" source="logicsheet-forms.xml"/>
<separator/>
+ <page id="sql-transformer" label="SQL Transformer"
source="sql-transformer.xml"/>
+<separator/>
<external href="apidocs/index.html" label="API (Javadoc)"/>
<separator/>
<external label="XML Links"
href="http://dmoz.org/Computers/Data_Formats/Markup_Languages/XML/"/>
1.1 xml-cocoon2/xdocs/sql-transformer.xml
Index: sql-transformer.xml
===================================================================
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE document SYSTEM "./dtd/document-v10.dtd">
<document><header><title>SQL Transformer</title>
<authors>
<person name="Sven Beauprez" email="[EMAIL PROTECTED]"/>
<person name="Davanum Srinivas" email="[EMAIL PROTECTED]"/>
</authors></header><body>
<s1 title="SQL Transformer">
<p>
With the SQLTransformer, it is possible to process a query that was
delivered by a generator.
The input XML has the following structure:
</p>
<source><![CDATA[
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<title>Hello</title>
<content>
<para>This is my first Cocoon2 page filled with sql data!</para>
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0";>
<query name="department" show-nr-of-rows="true">
select id,name from department_table
where name='<substitute-value sql:name="name"/>'
</query>
<execute-query>
<query name="employee">
select id,name from employee_table
where department_id = <ancestor-value sql:name="id" sql:level="1"/>
</query>
</execute-query>
</execute-query>
</content>
</page>
]]></source>
<p>
The substitute-value is a parameter in the sitemap for the
SQLTransformer, eg
</p>
<source><![CDATA[
<map:transform type="sql">
<map:parameter name="use-connection" value="personnel"/>
<map:parameter name="name" value="Programmers"/>
</map:transform>
]]></source>
<p>
The result of the first query is used to process the second one. The
values for id are sequentially substituted
in the second query which is repeatedly executed for each value. This is
done by using the ancestor-value element.
The result will look as follows:
</p>
<source><![CDATA[
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<title>Hello</title>
<content>
<para>This is my first Cocoon2 page filled with sql data!</para>
<rowset name="department" xmlns="http://apache.org/cocoon/SQL/2.0";>
<row>
<id>1</id>
<name>Programmers</name>
<rowset name="employee">
<row>
<id>1</id>
<name>Donald Ball</name>
</row>
<row>
<id>2</id>
<name>Stefano Mazzocchi</name>
</row>
</rowset>
</row>
</rowset>
</content>
</page>
]]></source>
<p>
It is also possible to call stored procedures, the following example
shows how to use an oracle stored procedure,
that returns a code (Integer) and a resultset (ResultSet).
</p>
<source><![CDATA[
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0";>
<execute-query>
<query isstoredprocedure="true" name="searchresult">
begin QUICK_SEARCH.SEARCH('<substitute-value
sql:name="department"/>',?,?); end;
</query>
<out-parameter sql:nr="1" sql:name="code"
sql:type="java.sql.Types.INTEGER"/>
<out-parameter sql:nr="2" sql:name="resultset"
sql:type="oracle.jdbc.driver.OracleTypes.CURSOR"/>
</execute-query>
</page>
]]></source>
<p>
The generated SAX events will be as follow (assuming that the resultset
contains a firstname and a department name):
</p>
<source><![CDATA[
<rowset name="searchresult>
<code>0</code>
<resultset>
<row>
<firstname>
Donald
</firstname>
<department>
Programmers
</department>
</row>
<row>
<firstname>
Stefano
</firstname>
<department>
Programmers
</department>
</row>
</resultset>
</rowset>
]]></source>
<p>
It is also possible to use in-parameters, eg. <in-parameter sql:nr="1"
sql:value="1"/>.
Used in combination with an out-parameter, the first ?-parameter can be
an in-parameter and an out-parameter at the same time.
(in-parameters can also be used with sql statements)
</p>
</s1>
</body></document>
----------------------------------------------------------------------
In case of troubles, e-mail: [EMAIL PROTECTED]
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
