Enabling non-XA Resources in JBoss 4.2 with ATG

a dog and it's boss on Flickr
(Photo: a dog and it’s boss by Pixel Addict)

ATG documents how to enable non-XA resources in JBoss 4.2 for SOLID.  We ended up following the same instructions to work with Oracle.

JBoss Note: JBoss 4.2 by default assumes XA drivers, which some ATG applications use; however, there are no XA drivers for SOLID. To enable multiple non-XA resources in JBoss 4.2, add the property to the jbossjta-properties.xml file, under the “arjuna” property tag:

[xml]<property depends="arjuna" name="jta">
<property name="com.arjuna.ats.jta.allowMultipleLastResources" value="true"/>[/xml]

You may still see warnings in your log file, but ATG applications will run correctly. To suppress these warnings, add the following to your jboss-log4j.xml file:

[xml]<category name="com.arjuna.atg.jta.logging">
<priority value="ERROR"></priority>
</category>[/xml]

For further reading please see Starting the SOLID SQL Database document in the Running Nucleus-Based Applications section of the ATG Installation and Configuration Guide.

RQLQueryRange does not sort dates correctly

Roppongi District Desktop on Flickr

(Photo: Roppongi District Desktop by powerbooktrance)

This was bedeviling me for awhile.  I could not figure out why my RQLQueryRange droplet was not sorting by dates correctly.  It turns out this is a known ATG bug which only happens when you are using Oracle, which is 99% of the time.

Considering that this bug has been open since 2003 one can assume ATG is not interested in fixing it.  This is probably because there is a simple work around.

Instead of using the sortProperties parameter, put the the sort directive directly in the RQL statement in the queryRQL parameter.

<dsp:param name="queryRQL" value="ALL ORDER BY creationDate DESC"/>

Running JBoss with Oracle

(Photo: oracle by you are the atman)

Most commercial websites that use JBoss also use Oracle.  To run JBoss with Oracle you simply need to tell JBoss where to find the Oracle JDBC drivers. To do this modify run.bat or run.sh and set the JBOSS_CLASSPATH to include the Oracle JDBC jar file before

set JBOSS_CLASSPATH=C:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar

I did this right before run.bat checks to see if JBOSS_CLASSPATH is empty.

rem If JBOSS_CLASSPATH or JAVAC_JAR is empty, don't include it, as this will
rem result in including the local directory in the classpath, which makes
rem error tracking harder.
if not "%JAVAC_JAR%" == "" set RUNJAR=%JAVAC_JAR%;%RUNJAR%
if "%JBOSS_CLASSPATH%" == "" set RUN_CLASSPATH=%RUNJAR%
if "%RUN_CLASSPATH%" == "" set RUN_CLASSPATH=%JBOSS_CLASSPATH%;%RUNJAR%

After doing this you might need to tell your web application how to configure the data sources. I wrote a post about how to configure your data source for ATG web applications.

How to Import and Create Users in Oracle

Oracle When you do an import sometimes you will find you will need to also create a user for this new set of data. Today I find myself in that situation as I imported data from Bell Canada and set up a new user for that data.

  1. Create a new user for the data.

    This example creates a user named "foo" with the password "foo".  Foo is a typical user that can create sessions, synonyms, procedures and tables.

    CREATE USER FOO IDENTIFIED BY FOO
      DEFAULT TABLESPACE users
      TEMPORARY TABLESPACE temp
      QUOTA UNLIMITED ON users;
    
    GRANT CREATE SESSION to FOO;
    GRANT CREATE SYNONYM TO FOO;
    GRANT CREATE PROCEDURE TO FOO;
    GRANT CREATE TABLE TO FOO;
    GRANT CREATE VIEW TO FOO;

    For more information please read Oracle’s CREATE USER documentation.

    If you want this user to be a DBA you can grant that to her too

    GRANT DBA TO FOO;
  2. Import the dump using the new user.

    This example imports the dump from the file "dump.dmp". This dump was created using the user "foo" and is being imported to the user "foo". The dump will be logged in the file "dump.log".

    imp system/system@example file=dump.dmp log=dump.log fromuser=foo touser=foo

    For more information please read Oracle’s Import Export FAQ.

  3. To redo recreate user and then import again.

    If you need to redo an import the easiest thing to do is to drop the user, recreate her and then do the import again. When you drop the user specify CASCADE to drop all the objects in the user’s schema.

    DROP USER FOO CASCADE;

    For more information please read Oracle’s DROP USER documentation.

Ruby on Rails and Oracle

  1. Get the Ruby OCI8 driver.  Download the file that ends with “mswin32.rb” and install like this:
    E:\ruby>ruby ruby-oci8-1.0.3-mswin32.rb
    Copy OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
    Copy oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8
    Copy oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
    OK?
    Enter Yes/No: Yes
    Copying OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
    Copying oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8 ... done
    Copying oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
    OK

    You can test the driver by running a query using Ruby.

    E:\>ruby -r oci8 -e "OCI8.new('foo','12345','sid').exec(
    'SELECT * from users') do |r| puts r.join(' | ') ; end"
  2. Install the ActiveRecord Oracle adapter.gem
    E:\ruby>install activerecord-oracle-adapter --source http://gems.rubyonrails.org
  3. Update config/database.yml to connect to Oracle
    development:
      adapter: oracle
      database: sid
      username: foo
      password: 12345
      timeout: 5000
  4. Test by doing a rake db:migrate.
  5. Test by running the Ruby on Rails server and making sure there are no errors upon startup.

This article is based on these articles.

Best Practices for Creating Tables

Recently our DBA recommended the following for creating tables.

  1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
  2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

– Jeff Janousek, Upromise DBA

ORA-12514 and ORA-27101

This morning I was unable to access my Oracle database which runs on my laptop. It’s the first time I have had this problem.

When I tried to login I saw this.

$ sqlplus foo/foo@dev01

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I tried to login without the TNS alias I saw this:

$ sqlplus foo/foo

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

On the internet I saw recommendations to set your ORACLE_SID and ORACLE_HOME environment variables. But on my system ORACLE_SID is not set and ORACLE_HOME is blank. Finally I just tried restarting the OracleServiceDEV01 service and that worked.

SQL Update in One Table Based on Values in Another Table

Dolphin Crest on Flickr

(Photo: Dolphin Crest by jurvetson)

I was wondering how to do this and thankfully someone wrote up a nice article on the SQL update statement.

Here is their example of how to do this.

UPDATE suppliers
SET name =
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id)
WHERE EXISTS
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);