While trying to create CLOBs for an Oracle database table, I was getting an Invalid Argument(s) error when trying to call CLOB.createTemporary(). I beat my head against a wall trying to figure out why I was getting null when you request the underlying connection from a Pooling Data Source but then I found some information that solved it for me.
For it to work, you need to enable access to the underlying connection to be able to get an instance of it.
There are two ways of doing this;
In your Tomcat configuration (or other Application Server), by enabling access to it in the <Context> of your web application:
<Context path="/ariweb" docBase="ariweb.war">
<Resource name="jdbc/ari" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/ari">
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<!-- NOTE: This is necessary to enable access to the Oracle connection object -->
<name>accessToUnderlyingConnectionAllowed</name>
<value>true</value>
</parameter>
<!-- Other configuration parameters -->
...
</ResourceParams>
</Context>
If you’re using Spring, you can also set it there in the parameters for your data source:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@dbhost:1521:DBSID</value></property>
<property name="username"><value>user</value></property>
<property name="password"><value>pass</value></property>
<property name="accessToUnderlyingConnectionAllowed"><value>true</value></property>
</bean>
This means that when you use the getDelegate() or getInnermostDelegate() methods, you will get the underlying connection type rather than a null.
I recently had a problem with retrieveing timestamps from an Oracle table (you might remember my previous post on Dates and Timestamps), where I would get an error such as this: Read the rest of this entry »
I spent a few days wrestling with Dates with Oracle and Java this week. I thought I’d post my findings here to save others the trouble.
When using PreparedStatement objects to interface with the Database, using PreparedStatement.setDate( int parameter, java.sql.Date field ) will truncate the time off the date.
If you require the time as well as the date, use java.sql.Timestamp.
Read the rest of this entry »
It now seems that the solution goalposts for the statistics have been moved. The Data Warehouse idea is a massive task and is ideally a project, so I’ve been instructed to just tailor it to Sakai and not the enterprise.
That is ok by me, as it’s reduced my work load considerably. Mind you, the amount I’ve read on Data Warehousing has helped me understand what I need to do a whole lot more.
I’m investigating Data Warehousing at the moment for work. It has been suggested that a Data Warehouse would be the suitable solution for the DIT Statistics for our implementation of Sakai. In the space of weeks, I have to learn the basics of what some people have an entire career based on. This should be fun.
I’ve started off reading through Ralph Kimball’s The Data Warehouse Toolkit (2nd Ed.), which is a bit like the Dummies guide but not quite as dumbed down. It’s not too bad at the moment, but there’s just a lot of information.
What’s interesting to note that when you’ve had your head buried in relational databases for some many years, Data Warehousing can be quite tricky to get your head around. This is because most of the concepts behind Data Warehousing, or at least the Data Presentation side, is that the data is not normalised. The aim of the Warehouse is to be fast and easy to understand. For it to be normalised means that there would be referential links to a billion tables so that data isn’t duplicated anywhere. To the end user, who doesn’t necessarily have any SQL knowledge is not understandable.
Thus, the need to map business processes into simple and easy to understand groups is the crux of Data Warehousing – and sometimes one of the most difficult things to understand as a database programmer!
