Wednesday, August 18, 2010

Type coercion in JSTL - for sql:param

Keywords:
jstl sql integer string type coercion postgres serial operator does not exist: bigint = character varying

Problem:
After upgrading from PostgreSQL 8.0 to 8.4 the following JSTL that queries a table by a passed in "ID":
<sql:query var="examples" dataSource="${exampleDataSource}">
    select ExampleName as "name"
    from ExampleTable 
    where ExampleId = ?
    order by ExampleName ASC
    <sql:param value="${param['ID']}"/>
</sql:query>

Fails with the exception:
javax.servlet.jsp.JspException:
    select ExampleName as "name"
    from ExampleTable
    where ExampleId = ?
    order by ExampleName ASC

: ERROR: operator does not exist: bigint = character varying
        at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:220)
        ....
Caused by: java.sql.SQLException: ERROR: operator does not exist: bigint = character varying
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
        at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:215)


Why are there data type errors all of a sudden? ... and how do you fix it?

Solution:
There is normally Type Coercion for EL expressions but it's a bit vague for sql:param. Ideally it should coerce the param into the type required but this would require knowing the schema & the SQL being executed. So looking at the source for QueryTagSupport it will just call setObject using the default type that was supplied in the param.

Contrary to the sql:param documentation the value does not have to be a String.

To get the right type into sql:param use type coercion in EL before the param gets the value. To coerce a String to Integer, you could multiply by 1. For example:
<sql:query var="examples" dataSource="${exampleDataSource}">
    select ExampleName as "name"
    from ExampleTable 
    where ExampleId = ?
    order by ExampleName ASC
    <sql:param value="${param['ID']*1}"/>
</sql:query>

Or in two steps:
<c:set var="exampleId" value="${param['ID']*1}"/>
<sql:query var="examples" dataSource="${exampleDataSource}">
    select ExampleName as "name"
    from ExampleTable 
    where ExampleId = ?
    order by ExampleName ASC
    <sql:param value="${exampleId}"/>
</sql:query>


Why did this come up after a PostgreSQL upgrade? It seems something to do with different handling of the SERIAL data type which is now compiled to it's actual representation of integer with sequence rather than leaving it as it's "notational convenience" name. Perhaps the PostgreSQL JDBC will coerce a String to a serial but not an integer?

It's hard to say the above work around is best practice but it will be harmless for databases that handle coercion at the JDBC level and necessary for those that don't.

Thursday, June 03, 2010

Stop AXIS output of anonymous complex types in xsi:type

Keywords:
apache axis xsi:type anonymous complex type .NET "No type definition found for the type referenced by the attribute 'xsi:type'"

Problem:
The schema definition for a response element may define the complex type "inline" rather than by reference. This is known as an "anonymous type". Eg:
<xs:element name="MyResponse">
    <xs:annotation>
        <xs:documentation>This is the root element of the Response.</xs:documentation>
    </xs:annotation>
    <xs:complexType>
        <xs:sequence>
            <xs:element name="PropertyOne" type="xs:string" minOccurs="0"/>
            <xs:element name="PropertyTwo" type="xs:string" minOccurs="0"/>
        </xs:sequence>
    </xs:complexType>
</xs:element>

When axis generates the SOAP response (with the default settings) this includes xsi:type attributes. Eg:
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
     <MyResponse xsi:type="ns1:MyResponse" xmlns="http://example.com/service" xmlns:ns1="http://example.com/service">
        ...

This is accepted by AXIS-generated client code but in .NET (and other schema-validating tools such as XMLSpy) you'll get an error along the lines of
No type definition found for the type referenced by the attribute 'xsi:type'='ns1:MyResponse' of element <MyResponse>

The error inidicates validation is probably doing the right thing with the xsi:type attribute and checking it - there is actually no type with this name, it's anonymous. Can the behavour in AXIS be changed?

Solution:
Thankfully there is a configuration setting in AXIS to stop this output - sendXsiTypes, which is true by default:
<parameter name="sendXsiTypes" value="false"/>

There are a number of ways to get this setting in there. I'd recommend creating a wsdd deploy file for just the globalConfiguration and deploy this to the AXIS AdminServlet as you would do with the deploy.wsdd definitions for the other service(s).
So an example globalConfig-deploy.wsdd:
<deployment xmlns="http://xml.apache.org/axis/wsdd/"
    xmlns:java="http://xml.apache.org/axis/wsdd/providers/java">
    <globalConfiguration>
        ...
        <parameter name="sendXsiTypes" value="false" />
        ...
    </globalConfiguration>
</deployment>

You'll find the MyApp/WEB-INF/server-config.wsdd will then be updated and the SOAP excludes this attribute as advertised:
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
     <MyResponse xmlns="http://example.com/service">
        ...


The alternative to this (if you do want the xsi:type attributes) is to define explicit types for your root response elements. Eg:
<xs:element name="MyResponse" type="MyResponseType">
    <xs:annotation>
        <xs:documentation>This is the root element of the Response.</xs:documentation>
    </xs:annotation>
</xs:element>
<xs:complexType name="MyResponseType">
    <xs:annotation>
        <xs:documentation>This is the type for the root element of the Response.</xs:documentation>
    </xs:annotation>
    <xs:sequence>
        <xs:element name="PropertyOne" type="xs:string" minOccurs="0"/>
        <xs:element name="PropertyTwo" type="xs:string" minOccurs="0"/>
    </xs:sequence>
</xs:complexType>

The XML from AXIS will then include xsi:type but the value will be correct - referencing a type that does exist. Eg:
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Body>
     <MyResponse xsi:type="ns1:MyResponseType" xmlns="http://example.com/service" xmlns:ns1="http://example.com/service">
        ...

Wednesday, March 03, 2010

Enable debug/trace level logging for Tomcat 6 Realms

Keywords:
tomcat 6 realm logging juli logging.properties debug="9" debug="99" debug="true" JNDIRealm trace verbose jndi realm JNDIRealm

Problem:
Apparently Tomcat 6 Logging is greatly improved ... more granularity, flexibility in choosing java.util.logging or log4j, etc. This is great, but I'm happy with the default logging - ie if something goes wrong let me get the detail from a log file.

This attitude hits a snag where things go wrong and there's nothing in the logs - in my case, setup of a org.apache.catalina.realm.JNDIRealm is not letting me in but there's no details why. It used to be a matter of simply setting debug="9" on the Realm definition and you have verbose logging - the examples in the Tomcat 6 Realm documentation still use this:
<Realm className="org.apache.catalina.realm.JNDIRealm" debug="99"
    connectionURL="ldap://localhost:389"
    userPattern="uid={0},ou=people,dc=mycompany,dc=com"
    roleBase="ou=groups,dc=mycompany,dc=com"
    roleName="cn"
    roleSearch="(uniqueMember={0})"
/>
But this has no effect on logging. You'll get a warning telling you as much:
03/03/2010 10:56:08 AM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Realm} Setting property 'debug'
 to '99' did not find a matching property.
What's the minimum I have to do to enable debug?

Solution:
You have to edit the $CATALINA_HOME/conf/logging.properties file.

1. Configure debug logging for Realms and Authentication

Insert the following lines (in blue):
############################################################
# Facility specific properties.
# Provides extra control for each logger.
############################################################
# This would turn on trace-level for everything
# the possible levels are: SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST or ALL
#org.apache.catalina.level = ALL
#org.apache.catalina.handlers = 2localhost.org.apache.juli.FileHandler
org.apache.catalina.realm.level = ALL
org.apache.catalina.realm.useParentHandlers = true
org.apache.catalina.authenticator.level = ALL
org.apache.catalina.authenticator.useParentHandlers = true

org.apache.catalina.core.ContainerBase.[Catalina].[localhost].level = INFO
org.apache.catalina.core.ContainerBase.[Catalina].[localhost].handlers = 2localhost.org.apache.juli.FileHandler
This will give you debug/trace level logging to console and the file assuming you've kept the default config. But you only see debug in the console, not the catalina.[date yyyy-MM-dd].log file - in fact, the log file empty? The buffering means the file-logging is only written when the buffer is full.

2. Disable buffering for FileHandler logging

(until the issue is resolved of course) Insert the line (in blue):
1catalina.org.apache.juli.FileHandler.level = FINE
1catalina.org.apache.juli.FileHandler.directory = ${catalina.base}/logs
1catalina.org.apache.juli.FileHandler.prefix = catalina.
1catalina.org.apache.juli.FileHandler.bufferSize = -1
These two inserts give you pretty much the equivalent of the old debug="9" and you'll (hopefully) get the verbose information required - happy debugging ...

Thursday, February 18, 2010

Configure endorsed libraries in Tomcat 6

Keywords:
Tomcat 6 endorsed java.endorsed.dirs Endorsed Standards Override Mechanism XML libraries xerces

Problem:
If your webapp needs its own XML libraries (xerces in particular) how do you get Tomcat 6 to use this and not the JAXP APIs packaged into the JSE? This used to be as simple as dropping them into ${CATALINA_BASE}/common/endorsed but there's only a ${CATALINA_BASE}/lib folder ...

Solution:
Thankfully found the solution in this blog (and comments).

Simply, create a ${CATALINA_BASE}/endorsed folder and drop the jar files in there. Tomcat will be setup to use this if it exists.


Notes:
No explicit mention of this in Tomcat 6 Class Loader notes

It does note the -Djava.endorsed.dirs system property is set but you need to check setclasspath.[bat|sh] for when it's set and what it's set to by default - ie ${CATALINA_BASE}/endorsed.

Thursday, February 04, 2010

HSQL random function - calling scalar functions via JDBC

Keywords:
HSQL JDBC select random scalar built-in function rand fn

Problem:
I want to demstrate database interaction but the data doesn't really matter in this case. HSQL is ideal because you can set a connection to in-memory database .. but there's no data. Random would do, is there a random function in HSQL? Where is the function reference? Is it called rand(), random() or other?

Solution:
Short answer is it's called "rand". So the SQL would be:
CALL rand()
With JDBC this is:
PreparedStatement stmt = connection.prepareStatement("CALL rand()");
stmt.execute();
... and I can't find a complete function reference for HSQL.


Digging a bit further there is JDBC convention for calling scalar/built-in function in a vendor independent way - See JDBC 2.0: Escape Syntax and Scalar Functions. This involves the use of the "fn" keyword and braces - so the syntax is:
{fn <function()>}
Whether the JDBC driver recognises the syntax and maps it correctly to the underlying function ("rand" is called "random" in postgres for example) is up to the vendor. I can confirm that HSQL seems to handle most numeric, string and date functions (as mentioned above, can't find doco for this. The derby JDBC function reference is a reasonably good overview). So the vendor agnostic random call becomes:
PreparedStatement stmt = connection.prepareStatement("CALL {fn rand()}");



Notes:
What I could find was documentation of HSQL "Java Stored Procedures". This effectively lets you call any public static java method (from a class in the classpath) right there in the SQL statement. So, yet another (non-JDBC standard) way of making the random call would be:
CALL "java.lang.Math.random"()
... or doing more work in the SQL:
CALL "java.lang.Math.floor"("java.lang.Math.random"() * 100)
Pretty cool, I must think of a use for this :)