From the category archives:

Database

A simple task queue

November 25, 2009

I’m working on a little sample framework - really only to keep my sanity and practice my chosen craft - that allows you to string together tasks in a pipeline for processing. To exercise that framework - to flush out the pros and cons of the implementation - I’m writing a sample application.
The basic idea [...]

Read the full article →

Sharing data between applications

July 24, 2009

Sometimes applications need access to data from another application. Here is one approach you could take:

Define a view in the database that owns the data - this view should expose the data that the external application needs
Grant select permissions so that the external application can select from the view
Create a synonym in the external applications [...]

Read the full article →

Finding constraints in Oracle

May 13, 2009

It can be quite frustrating when you get exceptions like those below - constraint violations where the constraint has a system generated name which means nothing to you:

2009-03-23 23:30:43 ERROR [AbstractFlushingEventListener.performExecutions] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
…
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SAMPLE.SYS_C00123456) violated
…

The easiest way to find [...]

Read the full article →

Playlists with MythTV

March 22, 2009

I’ve been wanting a way to use playlists on my media center either from within MythTV or with any other player. The reason is I have a bunch of short videos (music and documentaries) that I’d like to be able to play consecutively.
It turns out to be a case of just RTFM. You can create [...]

Read the full article →

ST_Intersects performance

October 18, 2008

I was just using a query which made use of the ST_INTERSECTS function:

select * from table1 where st_intersects(st_point( ?, ?, 1),shape)=1

With the data I had, this query took 30 seconds! Before launching into an investigation to find out why, I just decided to swap the parameters - this made all the difference:

select * from table1 [...]

Read the full article →

Finding table differences in Oracle

October 10, 2008

Often there is a need to compare two databases and see the differences. I come across this a lot when releasing a new build into an existing environment - the new code runs on the development database, but the test environment needs a schema upgrade before the code will run.
There are tools that will compare [...]

Read the full article →

Displaying dates and times with Oracle

September 4, 2008

I use SQLDeveloper when interacting with Oracle. When viewing tables with date columns it can be frustrating that the default display does not show time (just day, month and year).
To change this behaviour, you can set the date format for your current session:

alter session set NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'

Now, when you view tables or resultsets, you’ll have [...]

Read the full article →

Oracle types

August 1, 2008

It frequently surprises me when seemingly simple things are missing from mature products. For example, Oracle doesn’t have a boolean type. Strange but apparently true.
Never mind, this article describes how to work around this.

Read the full article →

Oracle version information

July 31, 2008

When making support requests it is always helpful to include version information about the product in question. Rather than just stating ‘version X’ I like to get the software to display the version information and just copy and paste it - this way there can be no confusion, and there may even be extra useful [...]

Read the full article →

grails-p6spy-0.2

September 19, 2007

This information is out of date - please see http://grails.org/plugin/p6spy for the current documentation.
Grails 0.6 was recently released, and this new version included changes to the way the DataSource is defined. So, to make my grails-p6spy-plugin compatible with 0.6, here is a new release:
grails-p6spy-0.2.zip
P6Spy lets you monitor the JDBC queries by proxying your database driver. [...]

Read the full article →

← Previous Entries