You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by neil <nb...@aisoftware.com.au> on 2002/02/08 06:58:15 UTC
patch to SQLTransformer for result sets returned from stored procedures
The version of org.apache.cocoon.transformation.SQLTransformer.java in
cocoon 2.0 handles ResultSets returned from a stored procedure as OUT
parameters.
Oracle and some others do this, however MS SQL Server and some others can
return multiple results each of which is either a ResultSet or a count of
updated rows.
The following patch attempts to jack up SQLTransformer v1.24 to handle these
multiple results. It works well enough for what I need, but has only been
tested with a MS SQL Server stored procedure that returns one ResultSet.
v1.24 is the most recent version before SQLTransformer was moved somewhere
else in the source tree. It's more recent than what comes with cocoon 2.0.
If anyone wants to try it or just review the changes and offer suggestions
for improvements please feel free. I just compile this one file and jar the
resulting classes into cocoon\WEB-INF\lib\AAnewstuff.jar and cocoon picks it
up from there.
Cheers,
Neil.
Result of: LC_ALL=C TZ=UTC0 diff -Naurb SQLTransformer-1.24.java
SQLTransformer.java
--- SQLTransformer-1.24.java Thu Feb 7 01:23:52 2002
+++ SQLTransformer.java Fri Feb 8 05:25:10 2002
@@ -35,7 +35,7 @@
* @author <a href="mailto:giacomo.pati@pwr.ch">Giacomo Pati</a>
* (PWR Organisation & Entwicklung)
* @author <a href="mailto:sven.beauprez@the-ecorp.com">Sven Beauprez</a>
- * @version CVS $Revision: 1.24 $ $Date: 2001/12/15 00:21:19 $ $Author:
vgritsenko $
+ * @version CVS $Revision: 1.2 $ $Date: 2002/02/08 05:25:10 $ $Author: neil
$
*/
public class SQLTransformer extends AbstractTransformer implements
Composable, Recyclable, Disposable, Configurable {
@@ -263,7 +263,6 @@
}
getLogger().debug( "SQLTransformer executing query nr " + index );
- AttributesImpl attr = new AttributesImpl();
Query query = (Query) queries.elementAt( index );
boolean query_failure = false;
try {
@@ -280,19 +279,19 @@
query_failure = true;
}
if ( !query_failure ) {
-
+ if ( !query.isStoredProcedure() ) {
+ AttributesImpl rowsetAttr = new AttributesImpl();
if ( showNrOfRows != null &&
showNrOfRows.equalsIgnoreCase( "true" ) ) {
- attr.addAttribute( my_uri, query.nr_of_rows,
query.nr_of_rows, "CDATA",
+ rowsetAttr.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",
+ rowsetAttr.addAttribute( my_uri, query.name_attribute,
query.name_attribute, "CDATA",
name );
}
- this.start( query.rowset_name, attr );
- attr = new AttributesImpl();
- if ( !query.isStoredProcedure() ) {
+ this.start( query.rowset_name, rowsetAttr );
+ AttributesImpl attr = new AttributesImpl();
while ( query.next() ) {
this.start( query.row_name, attr );
query.serializeRow();
@@ -301,10 +300,10 @@
}
this.end( query.row_name );
}
+ this.end( query.rowset_name );
} else {
query.serializeStoredProcedure();
}
- this.end( query.rowset_name );
}
} catch ( SQLException e ) {
getLogger().debug( "SQLTransformer.executeQuery()", e );
@@ -566,7 +565,7 @@
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 );
+ 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;
@@ -759,13 +758,13 @@
class Query {
/** Who's your daddy? **/
- protected SQLTransformer transformer;
+ protected final SQLTransformer transformer;
/** What index are you in daddy's queries list **/
- protected int query_index;
+ protected final int query_index;
/** SQL configuration information **/
- protected Properties properties;
+ protected final Properties properties;
/** Dummy static variables for the moment **/
protected String rowset_name = "rowset";
@@ -774,29 +773,10 @@
protected String name_attribute = "name";
/** The connection, once opened **/
- protected Connection conn;
-
- /** 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 a stored procedure **/
- protected boolean isstoredprocedure = false;
+ protected Connection conn = null;
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();
@@ -809,6 +789,30 @@
/** Mapping out parameters - objectModel **/
protected HashMap outParametersNames = null;
+ /** If this query is actually a stored procedure **/
+ protected boolean isstoredprocedure = false;
+
+ /** Following used for queries (not stored procedures). */
+
+ protected PreparedStatement pst = null;
+ /** If this query is actually an update (insert, update, delete)
**/
+ protected boolean isupdate = false;
+ /** If it is an update/etc, the return value (num rows modified)
**/
+ protected int rv = -1;
+ /** The results, of course **/
+ protected ResultSet rs = null;
+ /** And the results' metadata **/
+ protected ResultSetMetaData md = null;
+
+ /** Following used for stored procedures (not queries). */
+
+ protected CallableStatement cst = null;
+ /** true if there is another return value and the next return value
+ * is a ResultSet.
+ * @see CallableStatement#execute
+ */
+ protected boolean nextResultIsResultSet = false;
+
protected Query( SQLTransformer transformer, int query_index ) {
this.transformer = transformer;
this.query_index = query_index;
@@ -922,7 +926,13 @@
try {
result = datasource.getConnection();
} catch ( Exception e ) {
- long waittime = 5000 * (long) Math.random();
+ // long waittime = 5000 * (long) Math.random();
+ /* Isn't this always 0? Math.random() < 1.0 and the
+ * Java lang spec, section 5.1.3 Narrowing Primitive Conversions,
+ * says that (long) of a float or double uses IEEE 754
+ * round-toward-zero mode.
+ */
+ long waittime = (long) (5000.0 * Math.random());
getTheLogger().debug( "SQLTransformer$Query:
could not acquire a Connection -- waiting " + waittime + " ms to try
again." );
try {
Thread.sleep( waittime );
@@ -958,7 +968,6 @@
this.row_name = properties.getProperty(
SQLTransformer.MAGIC_ROW_ELEMENT );
}
-
Enumeration enum = query_parts.elements();
StringBuffer sb = new StringBuffer();
while ( enum.hasMoreElements() ) {
@@ -985,6 +994,14 @@
pst = conn.prepareStatement( query,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
+ registerInParameters( pst );
+ boolean result = pst.execute();
+ if ( result ) {
+ rs = pst.getResultSet();
+ md = rs.getMetaData();
+ } else {
+ rv = pst.getUpdateCount();
+ }
}
} else {
if ( oldDriver ) {
@@ -995,17 +1012,10 @@
ResultSet.CONCUR_READ_ONLY );
}
registerOutParameters( cst );
- pst = cst;
+ registerInParameters( cst );
+ nextResultIsResultSet = cst.execute();
}
- 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 );
throw e;
@@ -1047,6 +1057,8 @@
}
protected boolean next() throws SQLException {
+ if ( isstoredprocedure )
+ 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 )
@@ -1076,10 +1088,10 @@
}
if ( pst != null )
pst.close();
- pst = null; // Prevent using pst again.
- if ( cst != null )
+ if ( cst != null && cst != pst )
cst.close();
- cst = null; // Prevent using cst again.
+ pst = null; // Prevent using pst again.
+ cst = null;
} finally {
if ( conn != null )
conn.close();
@@ -1092,14 +1104,16 @@
}
protected void serializeRow() throws SQLException, SAXException {
+ if ( isstoredprocedure )
+ return;
AttributesImpl attr = new AttributesImpl();
- if ( !isupdate && !isstoredprocedure ) {
+ if ( !isupdate ) {
for ( int i = 1; i <= md.getColumnCount(); i++ ) {
transformer.start( md.getColumnName( i ).toLowerCase(),
attr );
transformer.data( getColumnValue( i ) );
transformer.end( md.getColumnName( i ).toLowerCase() );
}
- } else if ( isupdate && !isstoredprocedure ) {
+ } else {
transformer.start( "returncode", attr );
transformer.data( String.valueOf( rv ) );
transformer.end( "returncode" );
@@ -1110,25 +1124,75 @@
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;
+ if ( !itOutKeys.hasNext() )
+ return;
AttributesImpl attr = new AttributesImpl();
- try {
+
+ // Handle 0..n returned ResultSetS.
+ // Whereas Oracle and others only return ResultSetS as out parameters,
+ // MS SQL Server and others may return multiple ResultSetS this way.
+ // MS's beta SQL Server JDBC driver can't handle anything as radical
as
+ // a new line within "{ ... }"!
+ // In the XML passed to the SQLTransformer use:
+ // <query isstoredprocedure="true">{ ? = <storedProcedureName>(
<arg1>, ... ) }</query>
+ // <out-parameter sql:nr="1" sql:name="resultset"
sql:type="java.sql.Types.INTEGER"/>
+ // The <out-parameter> results in entries in outParameters &
outParametersNames
+ // for the returned results.
+ int resultCount = 0;
+ for ( ; true; nextResultIsResultSet = cst.getMoreResults() ) {
+ if ( nextResultIsResultSet ) {
+ ++resultCount;
+ ResultSet rs = cst.getResultSet();
+ getTheLogger().debug( "serializeStoredProcedure: returned result " +
resultCount + " is a ResultSet." );
+ serializeResultSet( rs, rowset_name );
+ } else {
+ // either the next result is an update count or there are no more
results
+ int updateCount = cst.getUpdateCount();
+ if ( updateCount == -1 ) {
+ break; // no more results
+ }
+ ++resultCount;
+ getTheLogger().debug( "serializeStoredProcedure: returned result " +
resultCount + " is an update row count = " + updateCount );
+ String name = "returncode";
+ // don't like the name, but its consistent with that used for an
update
+ transformer.start( name, attr );
+ transformer.data( String.valueOf( updateCount ) );
+ transformer.end( name );
+ }
+ }
+ if ( resultCount > 0 )
+ itOutKeys.next(); // consume first outParameter
+
while ( itOutKeys.hasNext() ) {
- counter = (Integer) itOutKeys.next();
try {
- if ( cst == null ) getTheLogger().debug(
"SQLTransformer: cst is null" );
- if ( counter == null ) getTheLogger().debug( "
SQLTransformer: counter is null" );
+ // if ( cst == null ) getTheLogger().debug( "SQLTransformer: cst is
null" );
+ Integer counter = (Integer) itOutKeys.next();
+ if ( counter == null ) getTheLogger().debug( "SQLTransformer: counter
is null" );
+ String outParameterName = (String) outParametersNames.get( counter );
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 ) );
+ if ( obj instanceof ResultSet ) {
+ serializeResultSet( (ResultSet) obj, outParameterName );
} else {
- ResultSet rs = (ResultSet) obj;
+ transformer.start( outParameterName, attr );
+ transformer.data( transformer.getStringValue( obj ) );
+ transformer.end( outParameterName );
+ }
+ } catch ( SQLException e ) {
+ getTheLogger().error( "Caught a SQLException", e );
+ throw e;
+ }
+ }
+
+ }
+
+ protected void serializeResultSet(ResultSet rs, String outParameterName)
throws SQLException, SAXException {
+ getTheLogger().debug( " SQLTransformer: serializeResultSet named '" +
outParameterName + "'.");
+ AttributesImpl attr = new AttributesImpl();
try {
- transformer.start( (String)
outParametersNames.get( counter ), attr );
+ transformer.start( outParameterName, attr );
ResultSetMetaData md = rs.getMetaData();
while ( rs.next() ) {
transformer.start( this.row_name,
attr );
@@ -1141,18 +1205,8 @@
}
} finally {
rs.close();
- rs = null;
- }
- transformer.end( (String)
outParametersNames.get( counter ) );
- }
- } catch ( SQLException e ) {
- transformer.getTheLogger().error( "Caught a
SQLException", e );
- throw e;
- }
- }
- } finally {
- //close();
}
+ transformer.end( outParameterName );
}
}
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>