Hello,
the patch attached above brings the following two changes to the
SQLTransformer:
1. SQLExceptions coming from query.execute() are catched and the following
output is added to the resulting xml documenters set
<sql:rowset>
<sql:error>The error messge from the SQLException.<sql:error>
</sql:rowset>
2. The element <sql:escape-string> is added. The purpose of this is, that
you can write the following statement:
<sql:execute-query>
<sql:query isupdate="true">
update test_table set test1 = '<sql:escape-string>A string with tick ' and
backslash \ in it.</sql:escape-string>'
</sql:query>
</sql:execute-query>
The tick ' will be escaped with a double tick '' and the backslash will
be escaped with a double backlslash \\ befor adding to the query string.
With this it is although possible to create sql queries from stylesheets
using form paramters like in the following example:
<sql:execute-query>
<sql:query isupdate="true">
update test_table set
test = '<sql:escape-string><xsl:value-of
select="$input_test1"/></sql:escape-string>'
</sql:query>
</sql:execute-query>
Cheers,
Peter Seiderer
I am not subscribed to the mailing list. Please reply via
CC to [EMAIL PROTECTED]
diff -ru cocoon-2.0rc2_orig/src/org/apache/cocoon/transformation/SQLTransformer.java
cocoon-2.0rc2/src/org/apache/cocoon/transformation/SQLTransformer.java
--- cocoon-2.0rc2_orig/src/org/apache/cocoon/transformation/SQLTransformer.java Wed
Nov 7 12:36:07 2001
+++ cocoon-2.0rc2/src/org/apache/cocoon/transformation/SQLTransformer.java Mon
+Nov 26 20:53:10 2001
@@ -62,6 +62,8 @@
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_ESCAPE_STRING = "escape-string";
+ public static final String MAGIC_ERROR = "error";
public static final String MAGIC_ANCESTOR_VALUE = "ancestor-value";
public static final String MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE = "level";
@@ -81,6 +83,7 @@
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;
+ public static final int STATE_INSIDE_ESCAPE_STRING = 8;
/** Default parameters that might apply to all queries **/
protected Properties default_properties;
@@ -220,35 +223,50 @@
getLogger().debug( "SQLTransformer executing query nr " + index );
AttributesImpl attr = new AttributesImpl();
Query query = (Query) queries.elementAt( index );
+ boolean query_failure = false;
try {
- query.execute();
-
- 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 );
+ try {
+ query.execute();
+ } catch ( SQLException e ) {
+ getLogger().debug( "SQLTransformer:.executeQuery() query.execute
+failed ", e );
+ AttributesImpl my_attr = new AttributesImpl();
+ this.start( query.rowset_name, my_attr );
+ this.start( MAGIC_ERROR, my_attr);
+ this.data( e.getMessage());
+ this.end( MAGIC_ERROR );
+ this.end( query.rowset_name );
+ query_failure = true;
+ }
+ if ( !query_failure ) {
+
+ 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();
+ }
+ this.end( query.rowset_name );
+ }
+ } catch ( SQLException e ) {
+ getLogger().debug( "SQLTransformer.executeQuery()", e );
+ throw new SAXException( e );
} finally {
try {
query.close();
@@ -256,8 +274,6 @@
getLogger().warn( "SQLTransformer: Could not close JDBC connection",
e );
}
}
- this.end( query.rowset_name );
-// this.contentHandler.endPrefixMapping("");
}
protected static void throwIllegalStateException( String message ) {
@@ -439,6 +455,42 @@
current_state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
}
+ protected void startEscapeStringElement( Attributes attributes ) {
+ switch ( current_state ) {
+ case SQLTransformer.STATE_INSIDE_QUERY_ELEMENT:
+ if ( current_value.length() > 0 ) {
+ getCurrentQuery().addQueryPart( current_value.toString() );
+ getLogger().debug( "QUERY IS \"" +
+ current_value.toString() + "\"" );
+ current_value.setLength( 0 );
+ }
+
+ current_state = SQLTransformer.STATE_INSIDE_ESCAPE_STRING;
+ break;
+ default:
+ throwIllegalStateException( "Not expecting a start escape-string
+element" );
+ }
+ }
+
+ protected void endEscapeStringElement() {
+ switch ( current_state) {
+ case SQLTransformer.STATE_INSIDE_ESCAPE_STRING:
+ if ( current_value.length() > 0 ) {
+ String escape = current_value.toString();
+ escape = replaceCharWithString( escape, '\'', "''" );
+ escape = replaceCharWithString( escape, '\\', "\\\\" );
+ getCurrentQuery().addQueryPart( escape );
+ getLogger().debug( "QUERY IS \"" +
+ current_value.toString() + "\"" );
+ current_value.setLength( 0 );
+ }
+ current_state = SQLTransformer.STATE_INSIDE_QUERY_ELEMENT;
+ break;
+ default:
+ throwIllegalStateException( "Not expecting a end escape-string element" );
+ }
+ }
+
protected void startInParameterElement( Attributes attributes ) {
switch ( current_state ) {
case SQLTransformer.STATE_INSIDE_EXECUTE_QUERY_ELEMENT:
@@ -541,6 +593,8 @@
startInParameterElement( attributes );
} else if ( name.equals( SQLTransformer.MAGIC_OUT_PARAMETER ) ) {
startOutParameterElement( attributes );
+ } else if ( name.equals( SQLTransformer.MAGIC_ESCAPE_STRING ) ) {
+ startEscapeStringElement( attributes );
} else {
startValueElement( name );
}
@@ -568,6 +622,8 @@
endOutParameterElement();
} else if ( name.equals( SQLTransformer.MAGIC_VALUE ) || current_state ==
SQLTransformer.STATE_INSIDE_VALUE_ELEMENT ) {
endValueElement();
+ } else if ( name.equals( SQLTransformer.MAGIC_ESCAPE_STRING ) ) {
+ endEscapeStringElement();
} else {
super.endElement( uri, name, raw );
}
@@ -576,7 +632,8 @@
public void characters( char ary[], int start,
int length ) throws SAXException {
if ( current_state != SQLTransformer.STATE_INSIDE_VALUE_ELEMENT &&
- current_state != SQLTransformer.STATE_INSIDE_QUERY_ELEMENT ) {
+ current_state != SQLTransformer.STATE_INSIDE_QUERY_ELEMENT &&
+ current_state != SQLTransformer.STATE_INSIDE_ESCAPE_STRING ) {
super.characters( ary, start, length );
}
getLogger().debug( "RECEIVED CHARACTERS: " +
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, email: [EMAIL PROTECTED]
