RE: Fixes for SQL processor - Patches Attached

["Stevenson, Chris (SSABSA)" <ch...@ssabsa.sa.gov.au>]

> > 2. The SQLProcessor reads a column as a String and then
> > calls the ColumnFormatter which re-reads the same value.
> > Well it tries to. JDK1.2.2 coredumps :-)

> I just provided a rough solution for this issue (2). 
> Refinements are welcome

There are still problems with this code - namely, in
ColumnFormatter.addColumnNode, if format!=null, then
the resultSet will be read again, causing the error.

I have attached a patches to fix them.

Discussion:

I think the only real solution is to read the value 
*as an object* in SQLProcessor and then pass this 
object to the ColumnFormatter like so:

    for (int i=0; i<columns.length; i++) {
		Object value = rs.getObject(i+1);
        if (create_row_elements && create_id_attribute 
		  && id_attribute_column_index == i) {
            row_element.setAttribute(id_attribute, value.toString());
            continue;
        }
        if (value == null && null_mode == OMIT_NULLS) continue;
        column_element =
Utils.createElement(document,namespace,columns[i].name);
        if (value == null && null_mode == ATTRIBUTE_NULLS) {
            column_element.setAttribute("NULL","YES");
            column_element.appendChild(document.createTextNode(""));
        } else {
 
formatter.addColumnNode(document,column_element,columns[i],value,i+1);
        }
        row_node.appendChild(column_element);
    }

This also lets the SQL Driver perform the appriopriate 
transforms, which is crucial for dates. If you read it 
as a string you have to hope that the driver has returned
a properly localised string - a very shaky assumption
in my experience - convert it to a Date and then back to a 
String. Better to test for a date and error if
you have something else...

Note that we could dispense with the if column.type="Date" 
tests and 

Also, if you don't pass the ResultSet at all, 
then the code will be much safer because no-one can 
accidentally read the value again and it makes the 
code simpler too. 

	protected void addColumnNode(Document document, Element parent,
Column column, Object value, int i) throws SQLException {
	    String format = getFormat(column);
	    if (format != null) {
	        if (column.type.equals("timestamp") 
	               || column.type.equals("time") 
	               || column.type.equals("date")
	               || column.type.equals("datetime")) {
	            if (value instanceof java.util.Date) {
	                SimpleDateFormat date_format = new
SimpleDateFormat(format);
	
parent.appendChild(document.createTextNode(date_format.format((java.util.Dat
e)value)));
	            }
	            else {
	                //We can't format this object as a Date 'cos it
isn't one!
	                //Fall back to simple String format
	
parent.appendChild(document.createTextNode(value.toString()));
	            }
	            return;
	        } else if (column.type.equals("varchar") ||
column.type.equals("text")) {
	            if (format.equals("br")) {
	                StringBuffer sb = new StringBuffer();
	                StringCharacterIterator iter = new
StringCharacterIterator(value.toString());
	                for (char c = iter.first(); c != iter.DONE; c =
iter.next()) {
	                    if (c == '\n') {
	                        if (sb.length() > 0) {
	
parent.appendChild(document.createTextNode(sb.toString()));
	                            sb.setLength(0);
	                        }
	
parent.appendChild(document.createElement("br"));
	                    } else {
	                        sb.append(c);
	                    }
	                }
	                if (sb.length() > 0) {
	
parent.appendChild(document.createTextNode(sb.toString()));
	                }
	                return;
	            }
	        }
	    }
	    parent.appendChild(document.createTextNode(value.toString()));
	}
	
-- Chris Stevenson ----------------------- SSABSA --
Senior Secondary Assessment Board of South Australia
60 Greenhill Road, Wayville SA 5034, Australia
email: chris@ssabsa.sa.gov.au
phone: (08) 8372 7515
  fax: (08) 8372 7590
----------------------------------------------------

RE: Fixes for SQL processor - Patches Attached

[Donald Ball <ba...@webslingerZ.com>]

On Fri, 4 Feb 2000, Stevenson, Chris (SSABSA) wrote:

> > > 2. The SQLProcessor reads a column as a String and then
> > > calls the ColumnFormatter which re-reads the same value.
> > > Well it tries to. JDK1.2.2 coredumps :-)
> 
> > I just provided a rough solution for this issue (2). 
> > Refinements are welcome
> 
> There are still problems with this code - namely, in
> ColumnFormatter.addColumnNode, if format!=null, then
> the resultSet will be read again, causing the error.

Duh....

> I have attached a patches to fix them.

I love this guy. Patched and thanks again.

> Discussion:
> 
> I think the only real solution is to read the value 
> *as an object* in SQLProcessor and then pass this 
> object to the ColumnFormatter like so:
> 
[ ... code snipped ... ]
> 
> This also lets the SQL Driver perform the appriopriate 
> transforms, which is crucial for dates. If you read it 
> as a string you have to hope that the driver has returned
> a properly localised string - a very shaky assumption
> in my experience - convert it to a Date and then back to a 
> String. Better to test for a date and error if
> you have something else...
> 
> Note that we could dispense with the if column.type="Date" 
> tests and 

and... what, test the object to see if it's a java.sql.Date, Time, or
Timestamp?

- donald