Monday, March 22, 2010

Outlawed Thread

We did not realize that the O/R framework that we were using had an option to invalidate an object in cache after a timeout period. Hence we had written a thread to periodically reload the cache content, once we realized the feature in the O/R framework we deleted the thread from source control and have done numerous deployments, yet the thread seems to be present in the war file and logs peridoically that it is refreshing the cache.

Wednesday, March 17, 2010

Connecting to Oracle Database with DBA privileges

To connect to Oracle DB with DBA privileges, you will have to set a property while connecting. This code example shows how to set this property.View Source, Download and Run ( Right-click and Save Target As ).

Working with Date datatype with Time precision


This code example illustrates how to insert and access Date from Database with time precision. If you insert java.sql.Date and access it , the time part will be truncated during insert and access, hence we will have to insert and access Date as an instance of java.sql.Timestamp. This will hold the time precision during insert and access. To get milli second precision the database column should be Timestamp instead of Date. View Source, Download and Run ( Right-click and Save Target As ).

Oracle: Working with LOB of size greater than 4K

This code example illustrates how to insert, update and access LOB columns in Oracle database. We will use streams to access LOB data, using streams sheilds us from out of memory errors and its the most efficient way of accessing LOB. In this example we create a table with a CLOB column and insert a CLOB of length 5000 bytes and then update the same CLOB with a different data of length 5000 bytes.
View Source, Download and Run ( Right-click and Save Target As ).

Oracle JDBC URL

Lets learn on how to write a JDBC url. Here we'll be talking about what are the specific params needed to connect to Oracle using Oracle JDBC Drivers. To give a hint on what we are going to go thru.
1. The 'thin' and 'thick' drivers.
2. JDBC URL format
3. Where do we find the parameters for writing URL
4. JDK versions and their Drivers
5. JDBC Detect
6. JDBC Sample Code

1. The 'thin' and 'thick' drivers
  There are actually 4 different JDBC drivers, but we' ll talk about the most famous celebrity drivers in the four. The thin and thick drivers. Thin, as the names suggests its thin, meaning the file size is very less, about 1.1 MB and written 100% in Java. But the 'thick' driver is quite opposite to thin, its in native code and bulky. But people might be interested in the performance factor only, many of them have tried to benchmark both the drivers, guess what ? both we equally fast and nothing solid came out. So when do you go for thick driver ? thick drivers have many added features when compared with that of thin (like thick supports Transparent Application Failover, PlSqlTables, Middle-tier Authentication, etc.).
2. JDBC URL format
  To connect to Oracle database using JDBC, we have to do just 2 simple steps.
    a. Register the Driver tobe used for connecting.
    b. Supply the database parameters to which connection has to be established.
 

a. Registering the Driver
       One nice thing about JDBC is that, today we write code to connect to a database and tomorrow we move to another vendor, there needs to be no code change, just the driver class used has to be changed appropriately. So to write portable apps, don't hard-code the driver name, its always suggested that the class name be taken from some configuration file or System property.
     example: To register JDBC Driver
                     String jdbcDriver = System.getProperty("jdbc.driver.class");
                     Class.forName(jdbcDriver);

     To set this property, run the class with -Djdbc.driver.class=driverclassname option.
b. Supply Database parameters
      The database params can be given as a url. The format of url is
         jdbc:oracle::@::
      - can be either "thin" or "oci" (thick) [oci - Oracle Call Interface, version of oci can also be appended , oci8 - Oracle 8 DB version]
      - The machine name where the database is running (IP address can also be used).
               -  The port number where the database listener is running
        - The database SID of database
 

      example : jdbc:oracle:thin:@10.185.13.158:1521:ora9i
           here 10.185.13.158 - The IP address of machine where db is running
                   1521 - Port where db listener is running
                   ora9i - The Database SID
 

3 . Where do we find the parameters for writing URL
     Note: The below command has to be run on the machine where the Database is running and ORACLE_HOME environment variable set to the root directory of Oracle installation. (Also add ORACLE_HOME\bin directory to your system path)
         example: ORACLE_HOME=D:\Oracle9i
      i) To get the Hostname, database port of the machine where database is running
          cmdPrompt>lsnrctl status
          LSNRCTL for : Version - Production on 23-NOV-2002 17:44:33
          Copyright (c) 1991, 2001, Oracle Corporation.  All rights reserved.
          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
          STATUS of the LISTENER
          ------------------------
         Alias                     LISTENER
         Version                   TNSLSNR for : Version - Production
         Start Date                22-NOV-2002 16:37:33
         Uptime                    1 days 1 hr. 7 min. 1 sec
         Trace Level              off
         Security                  OFF
         SNMP                     OFF
         Listener Parameter File   \network\admin\listener.ora
         Listener Log File             \network\log\listener.log
         Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhostname)(PORT=1521)))
         ...............
         ...............
         ...............
        Services Summary...
        Service "MODOSE" has 1 instance(s).
        Instance "otn9i", status READY, has 1 handler(s) for this service...
        Service "PLSExtProc" has 1 instance(s).
        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
        Service "otn9i.idc.oracle.com" has 2 instance(s).
        Instance "ora9i", status UNKNOWN, has 1 handler(s) for this service...
        Instance "ora9i", status READY, has 3 handler(s) for this service...
        The command completed successfully

        Use the host and port corresponding to the address where PROTOCOL=tcp (if multiple tcp are there, then use the first one)
     ii) To get the database SID , connect to the database as SYSTEM user and run
        SQL> show parameter db_name
        NAME                                 TYPE       VALUE
        ------------------------------------      -----------     --------
        db_name                              string       otn9i    <---------Database SID

        ( OR ) run this query
       SQL> SELECT Instance_Name FROM V$Instance;
       INSTANCE_NAME
       ----------------
       ora9i   <------------ Database SID

4. JDK versions and their Drivers
            With different versions of JDK came the different versions of JDBC drivers too.
            In all versions the JDBC drivers are bundled as jar and zip and can be found under /jdbc/lib directory.  If you do not have access Oracle client or Database installation locations, then it can be downloaded for free from OTN (Oracle Technology Network) Software section.
 
 
 














JDK Version






JDBC Driver jar name






NLS Characterset jar name
1.1.x classes111.jar nls_charset11.jar
1.2.x classes12.jar nls_charset12.jar
1.3.x classes12.jar nls_charset12.jar
1.4.x ojdbc14.jar nls_charset12.jar
5. JDBC Detect     This Java program checks the environment settings and tries to connect to the database and executes a simple  query. It also provides a solution if any of the parameters to connect are invalid. Download (shift-click)
        cmdPrompt>jar -xvf JDBCCheck.jar (or use WinZip to extract the Java source)
        cmdPrompt>javac JDBCCheck.java
        cmdPrompt>java -Djdbc.url=jdbc:oracle:thin:@:: [options] JDBCCheck
        options are
               -Djdbc.driver.class=driver_class_name [default: oracle.jdbc.driver.OracleDriver]
               -Djdbc.username=user_name [default: scott]
               -Djdbc.password=password [default: tiger]
 

  example output:
      D:\>javac JDBCCheck.java
      D:\>java -Djdbc.url=jdbc:oracle:thin:@localhost:1521:ora9i JDBCCheck
      Log: Loading JDBC Driver : class=oracle.jdbc.driver.OracleDriver ...
      Log: Loaded JDBC Driver
      Log: Connecting with URL=jdbc:oracle:thin:@localhost:1521:ora9i as scott/tiger
      Log: Connected to Database
      Log: Created Statement object
      Log: Retrieved ResultSet object
      Log: Connection :PASSED
      Log: Closing Statment & ResultSet Objects
      Log: Disconnecting...
      Log: Disconnected from Database

Configure Oracle OCI Driver


How to configure Oracle OCI Driver and connect to Oracle Database using OCI ( Type II ) Driver.
About Oracle OCI driver.


Oracle OCI driver is also called the 'thick' driver, because of its thick native code it uses to communicate with the database. OCI expands to Oracle Call Interface, its written in C language and all the Java calls use JNI to direct the calls to the C layer. The C layer does the talking with the database and returns the results to the Java layer. Even thoug C is the fastest way to communicate with database, the overhead incurred during in the JNI layer makes  it a bit slower than the 100% Java thin driver. But in some cases the OCI driver beats the thin driver, i am yet to a performance analysis on that.
In a nutshell, the Oracle OCI (thick) driver uses Oracle Call Interface (written in C and is native code )  to communicate with the database. Hence, all Java calls have to be  mapped to the C calls in OCI. Due to this mapping, the driver classes must be in sync with the OCI  files - this results in connection problems which are listed and solved below.
Here are the steps that have to be done to Connect to Oracle database using Oracle OCI driver.
ORACLE_HOME : This refers to the folder where Oracle Client or Database has been installed.
Example D:\oracle9i , /home/oracle/orahome. This folder will have sub-folders such as bin, sqlj, network, rdbms, jdbc, jlib, lib, ...etc.
Windows
1. Set ORACLE_HOME environment variable
2. Add ORACLE_HOME\lib to System PATH
3 Set CLASSPATH environment variable to use Oracle JDBC driver from ORACLE_HOME\jdbc\lib
*nix ( Linux / Unix )
1. Set ORACLE_HOME environment variable
2. Add ORACLE_HOME/lib to LD_LIBRARY_PATH env variable
3 Set CLASSPATH environment variable to use Oracle JDBC driver from ORACLE_HOME/jdbc/lib.
While connecting to Oracle Database using OCI driver, here are some of the error messages that you might encounter. If the error message matchs, then the answer to the checklist question must be 'yes', else perform the nesseccary operation to answer 'yes'.
Example :
Is the jdbc driver from ORACLE_HOME/jdbc/lib ?
- If you are using a classes12.jar taken from an other location other than this, then your answer will be no. Hence you will have to use the jdbc driver from ORACLE_HOME/jdbc/lib.
Error Messages and Solutions For Windows Platform
Error Message : Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9.dll in java.library.path
Checklist :  1. Is ORACLE_HOME environment variable set
  2. Is ORACLE_HOME\bin added to System path
  3. Is ORACLE_HOME\lib added to System path
Error Message : Exception No Such Method : make_c_state error
Checklist :  1. Is ORACLE_HOME\lib added to System path
  2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME\jdbc\lib


Error Message :  Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
Checklist : 
 1. Is ORACLE_HOME\lib added to System path
  2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME\jdbc\lib

Error Messages and Solutions For *nix (Linux / Unix) Platform
Error Message : Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path
Checklist :
  1. Is ORACLE_HOME environment variable set
  2. Is ORACLE_HOME/lib added to LD_LIBRARY_PATH
Error Message : Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
Checklist :  1. Is ORACLE_HOME environment variable set
  2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME/jdbc/lib
Error Message :  make_c_state error
Checklist :  1. Is ORACLE_HOME/bin added to System path
  2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME/jdbc/lib
Error Message : Error connecting to database : java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff
 Checklist :  1. Is ORACLE_HOME environment variable set
I am just putting down the stack trace down here so that search engines will index them too.
 Exception in thread "main" java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver


 -- Set classpath to Oracle JDBC drivers


 ---------------------
 Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path


 Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path
         at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1403)
         at java.lang.Runtime.loadLibrary0(Runtime.java:788)
         at java.lang.System.loadLibrary(System.java:832)
         at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:300)
         at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:370)
         at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:505)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:342)
         at java.sql.DriverManager.getConnection(DriverManager.java:512)
         at java.sql.DriverManager.getConnection(DriverManager.java:171)
         at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
        at JDBCOCITest.main(JDBCOCITest.java:30)

 --  LD_LIBRARY_PATH has not been set, set LD_LIBRARY_PATH to ORACLE_HOME/lib
 ---------------------

  Error connecting to database : java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff

 Exception in thread "main" java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff
         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:188)
         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:230)
         at oracle.jdbc.oci8.OCIEnv.getEnvHandle(OCIEnv.java:79)
         at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:390)
         at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:370)
         at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:505)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:342)
         at java.sql.DriverManager.getConnection(DriverManager.java:512)
         at java.sql.DriverManager.getConnection(DriverManager.java:171)
         at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
        at JDBCOCITest.main(JDBCOCITest.java:30)
       
   -- ORACLE_HOME not set

 ---------------------

 Connecting with URL=jdbc:oracle:oci8:@ias904 as scott/tiger
 Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
         at oracle.jdbc.oci8.OCIEnv.get_env_handle(Native Method)
         at oracle.jdbc.oci8.OCIEnv.getEnvHandle(OCIEnv.java:70)
         at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:390)
         at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:361)
         at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
 va:485)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:337)
         at java.sql.DriverManager.getConnection(DriverManager.java:517)
         at java.sql.DriverManager.getConnection(DriverManager.java:177)
         at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
        at JDBCOCITest.main(JDBCOCITest.java:30)
       
    -- wrong version of classes12.jar is being used, use classes12.jar from ORACLE_HOME/jdbc/lib
   
 ---------------------


 Connecting with URL=jdbc:oracle:oci8:@ias904 as scott/tiger
  Error connecting to database : java.sql.SQLException: ORA-12154: TNS:could not resolve service name

Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve service name

         at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
         at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2321)
         at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:477)
         at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:346)
         at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)
         at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
         at java.sql.DriverManager.getConnection(DriverManager.java:517)
         at java.sql.DriverManager.getConnection(DriverManager.java:177)
         at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
        at JDBCOCITest.main(JDBCOCITest.java:30)
       
  -- The tnsname specified in jdbc url is incorrect, check if the names match in ORACLE_HOME/network/admin/tnsnames.ora

Echo2 or Echo 3 and the infamous prolog error

This issue has been driving our team, management and me crazy. It all started with users starting to see their screen freezing, how could a screen freeze in a web app, that is bcoz Echo puts a div element when it makes AJAX calls in the backend. This div element can be customized and usually occupies the entire screen so that an impatient user would not go about clicking when the request is being processed. 


We could see exceptions like the below one being logged in the server
2010-02-14 11:19:08,768 INFO [STDOUT] [Fatal Error] :1:1: Content is not allowed in prolog.
2010-02-14 11:19:08,784 WARN [org.apache.catalina.core.ContainerBase.[jboss.web].[localhost].[/jbApps/xxx].[xxServlet]] Servlet.service() for servlet xxServlet threw exception
java.io.IOException: Provided InputStream cannot be parsed: org.xml.sax.SAXParseException: Content is not allowed in prolog.
at nextapp.echo2.webrender.service.SynchronizeService.parseRequestDocument(SynchronizeService.java:184)
at nextapp.echo2.webrender.service.SynchronizeService.service(SynchronizeService.java:259)
at nextapp.echo2.webrender.WebRenderServlet.process(WebRenderServlet.java:283)
at com.xx.xx.common.xxx.adapter.echo2.AjaxServlet.process(AjaxServlet.java:159)
at nextapp.echo2.webrender.WebRenderServlet.doPost(WebRenderServlet.java:199)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
...


We were using IE7/8 which was hitting an Apache which in turn fwd the request to JBoss.


We initially thought the issue was due to some invalid content in the client message, and we also ended up in a Unit test that produced this stack trace. The billion dollar question was, how can a blank message be sent from the server. At one point after loosing all hope and getting into several traps, we witnessed it. The app hangs for about 5 mins (read socket timeout in Apache) and the socket timeout kicks in and sends a blank message which causes the prolog error. 

On browsing certain posts, we could only find users facing socket timeout erors happening in AJAX calls rather than the error that we saw in Apache (broken pipe ) 
[Tue Aug 04 05:04:55 2009] [debug] mod_dumpio.c(102): mod_dumpio: dumpio_in [getline-blocking] 0 readbytes
[Tue Aug 04 05:04:55 2009] [debug] mod_dumpio.c(50): mod_dumpio: dumpio_in (data-HEAP): 2 bytes
[Tue Aug 04 05:04:55 2009] [debug] mod_dumpio.c(66): mod_dumpio: dumpio_in (data-HEAP): \r\n
[Tue Aug 04 05:04:56 2009] [debug] mod_dumpio.c(116): mod_dumpio: dumpio_in - 104
[Tue Aug 04 05:04:56 2009] [info] [client 10.224.1.8] (32)Broken pipe: core_output_filter: writing data to the network
[Tue Aug 04 05:04:56 2009] [debug] mod_dumpio.c(102): mod_dumpio: dumpio_in [readbytes-blocking] 228 readbytes
[Tue Aug 04 05:04:56 2009] [debug] mod_dumpio.c(50): mod_dumpio: dumpio_in (metadata-EOS): 0 bytes
[Tue Aug 04 05:04:56 2009] [debug] mod_dumpio.c(102): mod_dumpio: dumpio_in [getline-blocking] 0 readbytes
[Tue Aug 04 05:05:00 2009] [debug] mod_dumpio.c(102): mod_dumpio: dumpio_in [getline-blocking] 0 readbytes
[Tue Aug 04 05:05:00 2009] [debug] mod_dumpio.c(50): mod_dumpio: dumpio_in (data-HEAP): 17 bytes
[Tue Aug 04 05:05:00 2009] [debug] mod_dumpio.c(66): mod_dumpio: dumpio_in (data-HEAP): HEAD / HTTP/1.1\r\n
[Tue Aug 04 05:05:00 2009] [debug] mod_dumpio.c(102): mod_dumpio: dumpio_in [getline-blocking] 0 readbytes
[Tue Aug 04 05:05:00 2009] [debug] mod_dumpio.c(50): mod_dumpio: dumpio_in (data-HEAP): 21 bytes


Finally this was the post that help us resolve the issue
http://www.opensubscriber.com/message/users@tomcat.apache.org/10657361.html


Turn Off Keep-Alive in apache or don't use IE