cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Interesse Michelangelo <M.Intere...@netsiel.it>
Subject R:MS-Access and ColumnFormatter issues ... & solutions
Date Thu, 03 Feb 2000 18:36:58 GMT
 <<database.xml>>  <<database.xsl>> ----------
Da:	Donald Ball[SMTP:balld@webslingerZ.com]
Risposta a: 	cocoon-dev@xml.apache.org
Inviato:	giovedì 3 febbraio 2000 8.31
A:	'cocoon-dev@xml.apache.org'
Oggetto:	Re: MS-Access and ColumnFormatter issues

On Wed, 2 Feb 2000, Interesse Michelangelo wrote:

> I've just joined this list, so I'm sorry if some of tthe following issues
> have already been taken into account.

They've been brought up but not dealt with yet :(.

> I'm running Cocoon 1.6 on Tomcat 1.6 and against MS-Access (JDBC-ODBC
> bridge) on a Windows computer.
> 
> 1) I ran the SQL example on my DB
> 	<page>
> 	 <connectiondefs>
> 	  <connection name="foo_connection">
> 	   <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>
> 	   <dburl>jdbc:odbc:Pres2000</dburl>
> 	   <username></username>
> 	   <password></password>
> 	  </connection>
> 	 </connectiondefs> 
> 	 
> 	 <query connection="foo_connection">
> 	  <!-- select Cognome,Nome from QARisorse order by Cognome -->
> 		select * from QARisorse ;
> 	 </query>
>  	</page>
> 
>    and I was astonished to get the resul message NO DATA FOUND (the table
> QARisorse was full).
> 
>    So I walked through the Cocoon code and I found the key of the mistery
in
> the "ColumnFormatter" class.
>    In the method "addColumnNode", in fact, the resutset object "rs" is
> queried a second time for the element at the same position (the first time
> it is queried in the SQLProcessor class). May be
> sun.jdbc.odbc.JdbcOdbcDriver doesn't like this. I wrote a dirthy
workaround
> (for immediate use), overloading the "addColumnNode" method with one more
> parameter: the value of the column, already queried through the
> rs.getString() in the  calling class. This work fine for my needs, but is
> there someone that would take care of the issue for the final fix?
>
>   The overloaded method I wrote is:
> 	protected void addColumnNode(Document document, Element parent,
> Column column, ResultSet rs, int i, String v) throws SQLException {
> 		String format = getFormat(column);
> 		String value = v;
> 		if (format != null) {
> 		... same handling ... or perhaps better ....
> 		}
> 		parent.appendChild(document.createTextNode(value));
> 	}

Send me a context diff patch and I'll go ahead and include it in the
processor. I've rewritten SQLProcessor as an XSP taglib and am reworking
the syntax; I have some issues left to resolve with the way cocoon
associates XSP namespaces with XSP logicsheets, but I'm working on that.

<<<---------------------------------------------------------------

The patch for the ColumnFormatter class is:
-	protected void addColumnNode(Document document, Element parent,
Column column, ResultSet rs, int i) throws SQLException {
+	protected void addColumnNode(Document document, Element parent,
Column column, ResultSet rs, int i, String v) throws SQLException {
		String format = getFormat(column);
		String value = v;

....

		}
-
parent.appendChild(document.createTextNode(rs.getString(i)));
+		parent.appendChild(document.createTextNode(value));
	}


The patch for SQLProcessor class is:
                        } else {
-
formatter.addColumnNode(document,column_element,columns[i],rs,i+1);
+
formatter.addColumnNode(document,column_element,columns[i],rs,i+1,value);
                        }
                        row_node.appendChild(column_element);
                    }


That's just  a patch, not the final solution !
--------------------------------------------------------------->>>

I'm very likely going to drop column formatting stuff from SQL's taglib.
It has been argued, and I believe I concur, that that functionality is
best left to XSLT extension functions. The transformations typically
desired (from one datetime format to another, from one string to another
with simple search and replace) are fairly general and shouldn't be
confined to a database access library.

<<<---------------------------------------------------------------

Please let me know, as soon as available, what is the equivalent framework.
--------------------------------------------------------------->>>

One question I've got for you - I was also under the impression that
calling rs.getString(i) on a numeric column could cause problems when
using Access via the JDBC-ODBC bridge. Is that in fact the case? If so, do
you see a simple patch to clean up that behavior?

<<<---------------------------------------------------------------

Sorry, You are not right ! In fact, the first call rs.getString(i) is
performed inside the SQLProcessor class and it really works. The problem is
to call the same method on the same element twice that goes wrong ! (I guess
some JDBC-ODBC leak, at least in JDK 1.1.8)

--------------------------------------------------------------->>>

>   2) about the SQLProcessor class, did somebody  polanned to extend its
> output DOM tree, in order to get an XML document with information like
> metadata out of the result query? Don't you thing this could be helpful
when
> processing the XML output document through an XSL document in order to
make
> some other processing or style transformation?

If you can come up with some good syntax for configuring this behavior,
I'll very likely add it to the XSP taglib. If you're interested in coding
this behavior, so much the better. Sure, most all of the metadata
information could come in handy at some point or another.

<<<---------------------------------------------------------------

Actually I extended the SQLProcessor class with .... a more general
SQLMetaProcessor class (sorry OO purists !). In the new class (but you can
insert the code in the base class or wherever you planned to move it) the
change was:

    protected void processQuery(Document document, Dictionary parameters,
Element query_element, Properties query_props, Connection conn) throws
Exception {
        HttpServletRequest req =
(HttpServletRequest)parameters.get("request");
        String doc_element_name = query_props.getProperty("doc-element");
        String row_element_name = query_props.getProperty("row-element");
///+++
        String column_element_name =
query_props.getProperty("column-element");
        String meta_element_name = query_props.getProperty("meta-element");
        boolean create_meta_elements = true;
        if (meta_element_name.equals("")) create_meta_elements = false;
//+++///

....

                Element column_element;
                Node row_node = results_node;
                Element row_element = null;
///+++
                Node meta_node = results_node;
                Element meta_element = null;
                if (create_meta_elements) {
                    meta_element =
Utils.createElement(document,namespace,meta_element_name);
                    meta_node = meta_element;
                    for (int i=0;i<columns.length;i++)
                    {
                    	column_element =
Utils.createElement(document,namespace,column_element_name.equals("")?column
s[i].name:column_element_name);
                 	    if (create_id_attribute &&
!column_element_name.equals("")) {
 
column_element.setAttribute(id_attribute,columns[i].name);
                  	    }

						int j = i+1;
 
column_element.setAttribute("label",md.getColumnLabel(j));
 
column_element.setAttribute("name",md.getColumnName(j));
 
column_element.setAttribute("type",Integer.toString(md.getColumnType(j)));
 
column_element.setAttribute("type-name",md.getColumnTypeName(j));
 
column_element.setAttribute("read-only",md.isReadOnly(j) ? TRUE : FALSE);
 
column_element.setAttribute("writable",md.isWritable(j) ? TRUE : FALSE);
 
column_element.setAttribute("definitely-writable",md.isDefinitelyWritable(j)
? TRUE : FALSE);
 
column_element.setAttribute("auto-inc",md.isAutoIncrement(j) ? TRUE :
FALSE);
 
column_element.setAttribute("catalog-name",md.getCatalogName(j));
 
column_element.setAttribute("schema-name",md.getSchemaName(j));
 
column_element.setAttribute("table-name",md.getTableName(j));
 
column_element.setAttribute("searchable",md.isSearchable(j) ? TRUE : FALSE);
 
column_element.setAttribute("case-sensitive",md.isCaseSensitive(j) ? TRUE :
FALSE);
 
column_element.setAttribute("display-size",Integer.toString(md.getColumnDisp
laySize(j)));
               	        column_element.setAttribute("signed",md.isSigned(j)
? TRUE : FALSE);
 
column_element.setAttribute("precision",Integer.toString(md.getPrecision(j))
);
 
column_element.setAttribute("scale",Integer.toString(md.getScale(j)));
 
column_element.setAttribute("currency",md.isCurrency(j) ? TRUE : FALSE);
               	        if (md.isNullable(j) == md.columnNoNulls)
 
column_element.setAttribute("nullable",FALSE);
               	        else if (md.isNullable(j) == md.columnNullable)
 
column_element.setAttribute("nullable",TRUE);
		/* ... whatever you need add as an attribute ... */

                 	    meta_node.appendChild(column_element);
                  	}
                  	results_node.appendChild(meta_node);
                }
//+++///

....

                        if (value == null && null_mode == OMIT_NULLS)
continue;
-                        column_element =
Utils.createElement(document,namespace,columns[i].name);
///+++
                        column_element =
Utils.createElement(document,namespace,column_element_name.equals("")?column
s[i].name:column_element_name);
                   	    if (create_id_attribute &&
!column_element_name.equals("")) {
 
column_element.setAttribute(id_attribute,columns[i].name);
                   	    }
//+++///

But, do not forget the following changes in ConnectionDefs:

    protected static Properties master_default_query_props = new
Properties();
    static {
        master_default_query_props.put("doc-element","ROWSET");
        master_default_query_props.put("row-element","ROW");
///+++
        master_default_query_props.put("column-element","");
        master_default_query_props.put("meta-element","");
//+++///

....

    public Properties getQueryProperties(String name) {
        if (name == null || name.equals("")) return getQueryProperties();
		Properties props = (Properties)query_props_table.get(name);
-		if (props != null) return getQueryProperties();
+		if (props == null) return getQueryProperties();
		return (Properties)props.clone();
    }

The last one is not relevant to my new functionalities, but may be a bug!

So I added two new attributes for the XML/DOM input (in the querydefs):
column-element and meta-element.

The output XML/DOM will have the METADATA at the same level (brother) of
ROW, i.e.:
	<page>
		<RESULTSET>
			<METADATA>
				<COL ID="column-1-name" .... />
				<COL ID="column-2-name" .... />
				....
			</METADATA>

			<ROW ID="0">
				<COL ID="column-1-name">
					column-1-value
				</COL>
				<COL ID="column-2-name">
					column-2-value
				</COL>

				...
			</ROW>
		</RESULTSET>
	</page>

Such a structure, will also prevent failures of the previous implementation,
when the nama of the columns contain blanks (possible in MS-Access!!!), so
they cannot be tag names but they can be attribute values !!
At the same time, such a design allows to link column values with metadata
informations (type, length, ...), usefull for subsequent transformations of
the DOM output.

The next step is to include other information in the DOM output, like the
original query issued, the elapsed time, and so on. But I want to be sure of
the final shape of the SQL processor before to do that. When will it be
ready?

You will find enclosed an example of such a chained transformation, that
could be packed in the next release.

Can you provide me with some hints for chaining JSP processing (Tomcat) with
Cocoon transformations (except XSP) ? I'd like to reuse the work I did with
JSP and extend it through the Cocoon framework. It looks to me that XSP is
still not as solid and clean as the code written in JSP can be. I use
extensively JSP scriptlets, with the most of the logic incapsulated in the
java classes. I use java code inside JSP pages just to obtain come control
flow and dynamic values during the presentation.

--------------------------------------------------------------->>>



>   3) what's the framework, in Cocoon, to chain more processing starting
from
> an XML document and getting, at the other end, an HTML document ?

Simply, just put these PIs in your document:

<?cocoon-process type="xslt"?>
<?xml-stylesheet test="text/xsl" href="foo.xsl"?>

for more complex scenarios, consult the web pages, and/or search through
the mailing list archives for 'sitemap'. better yet, i should repost the
last firm syntax/thoughts proposed for said endeavor.

- donald

<<<---------------------------------------------------------------

Michelangelo Interesse
----------------------------
      Process Support Systems
               Netsiel S.p.A.
      È ++39-080-5092.220
----------------------------



Mime
View raw message