Websphere Wierdness

I discovered a joyful thing with the websphere JDBC connection:

You want to make a prepared statement that uses the “like” operator.

You write :

select * from SOMETABLE where SOMECOLUMN like ?

and it throws an IndexOutOfBoundsException !!

YOU HAVE TO GET THE CASE CORRECT! so :

select * from SOMETABLE where SOMECOLUMN LIKE ?

or

select * from SOMETABLE where SOMECOLUMN Like ?

Weird eh ?

also if you want to select by a date you have to do :

select * from SOMETABLE where SOMEDATECOLUMN = to_date(?, ‘dd-mm-yyyy’)

putting in whatever format you have.

Share

Filtering a view which is based on an xml CLOB

Ok, so you create a table which has an xml blob.

THen you create a view onto that table which maps elements inside the XML blob to columns in the view, thus “expanding” out the xml to look like a normal table.

To do this you use the extract() method in oracle (see other posts) .

Now you want to filter the view based on nodes which are inside the xml data but oracle wont let you do it directly because the columns arent real columns.

SO

You need to include a unique key in the view – then you can do the select on the original table and JOIN the view to it! bit convoluted but it works! as long as there is a unique key of course – so you might have to design your xml table with this in mind but even if this is arbitrary it will still work.

here is an example :

SELECT tbview.* FROM TABLE_ONE tb, TABLE_ONE_VIEW tbview
WHERE
tbview.UNIQUE_ID=tb.UNIQUE_ID
AND
tb.XML_COLUMN.existsNode(‘/some/expath[with_test_node=”TEST_2″]’)=1;

Sweet!

Share