java.sql.SQLException: ORA-01843: not a valid month

You think it is easy to insert a date into a database? Then you never tried it on an Oracle database! Today I spent some time on the „java.sql.SQLException: ORA-01843: not a valid month“ – exception. It was caused by the query

Query: UPDATE myTable SET dataSent=?, dataSentDate=? WHERE email=? Parameters: [Y, 2010-05-17 11:49:50, name@mydomain.com]>

… but the date looks good, or? Some documentation showing this notation. But Oracle (and the web) says it was caused by a date specified an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON. Action: Enter a valid month value in the correct format. I tried to change my SimpleDateFormatter…

new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
new SimpleDateFormat("yyyy-MMMMM-dd hh:mm:ss").format(new Date());
new SimpleDateFormat("yyyy/MM/dd hh:mm:ss").format(new Date());

.. but nothing woked. Finally I found the solution: you have to tell Oracle the formatting of the date! So changing the insert statement like this…

update("UPDATE member SET dataSent=?, dataSentDate=to_date(?,'DD/MM/YYYY HH24:MI:SS') WHERE email=?", updateParams);

.. and of course change the SimpleDateFormatter to the same brings me to the solution!

Weblogic, sql-taglib and JNDI: DataSource invalid: No suitable driver found

On a Weblogic 10 environment, you may get an error like
javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: „java.sql.SQLException: No suitable driver found for jndi_ds
if you are using the sql-taglib to access your JNDI name from the Weblogic application server, configured via the Weblogic console.

The solution is quiet simple, but not that good documented. It is not possible to use the paramter „driver“ inside the sql:setDataSource – tag. You rather have to promote the JNDI name in your web.xml like this:

<resource-ref>
<res-ref-name>jndi_ds</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Than you can set the dataSource with the sql:setDataSource – tag like this:

<sql:setDataSource dataSource="jndi_ds" />

and finally use the sql-taglib as you did in any other environment like Tomcat server and so on.

How to remove jsessionid from url / Weblogic server

On a Weblogic server, you don’t need a rewite rule or filter to get rid of this …;jsessionid… stuff on any URL build using the „c:url“ tag. You simply need to put one more line to your weblogic.xml:

<session-descriptor>
    <url-rewriting-enabled>false</url-rewriting-enabled>
</session-descriptor>