[JIRA] Created: (ACTIVERECORD_JDBC-100) Table column types not inferred correctly if table is accessed via Oracle synonym

["prafulk (JIRA)" <jira-no-reply@kenai.com>]

Table column types not inferred correctly if table is accessed via Oracle 
synonym
---------------------------------------------------------------------------------

                 Key: ACTIVERECORD_JDBC-100
                 URL: http://kenai.com/jira/browse/ACTIVERECORD_JDBC-100
             Project: activerecord-jdbc
          Issue Type: Bug
          Components: Oracle
    Affects Versions: 0.9.2
         Environment: Windows 7 32-bit
Oracle JDBC driver: ojdbc6.jar
Java: 1.6.0_16; Java HotSpot(TM) Client VM 14.2-b01
JRuby: 1.4.0
Oracle database: 10.2.0.1 64-bit
            Reporter: prafulk
            Priority: Minor


When accessing an Oracle table via a synonym, the columns of the query result 
set are all of string type. If accessed directly, the columns are the correct 
type.

If I have defined a table synonym in Oracle eg

{code}
create synonym CURVE for schemab.CURVE
{code}

When I query the table, in Ruby
{code}
  Curve.find(:all, :conditions => {:code => code, :value_date => 
valuation_date},
    :select => "tenor, rate")
{code}

The columns are of type string for {{tenor}} and {{rate}} when, in fact, they 
are of type NUMBER in the database.

If I run the following:
{code}
ActiveRecord::Base.establish_connection(
  :adapter => 'jdbc',
  :driver => 'oracle.jdbc.driver.OracleDriver',
  :url => "jdbc:oracle:thin:@SERVERA:1431:asid",
  :username => "myusername",
  :password => "mypassword"
)
class Curve < ActiveRecord::Base
  self.table_name = 'CURVE'
end

cols = Curve.columns
cols.each {|c| p c.type}
{code}

The columns have no type info for synonym tables. The types are correct if a 
normal table is used i.e. one not accessed via a synonym.

I assume that because the column types are not being inferred, the types of 
the columns in the query result set are defaulting to string.

Note the oracle_enhanced gem ({{:adapter => "oracle_enhanced"}}) when used 
with JRuby provides the correct column types. So I'm re-testing my app with 
that adapter instead of the jdbc adapter.

I tried using 0.9.3 of the jdbc adapter and although the database connection 
is made, I get 
{code}
C:/apps/jruby/1.4.0/lib/ruby/gems/1.8/gems/activesupport-2.3.5/lib/active_support/callbacks.rb:82:in
 `alias_chained_method': undefined method `jdbc_oracle_insert' for class 
`ActiveRecord::ConnectionAdapters::JdbcAdapter' (NameError)
        from 
C:/apps/jruby/1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.3-java/lib/jdbc_adapter/jdbc_oracle.rb:30:in
 `extended'
        from 
C:/apps/jruby/1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.3-java/lib/jdbc_adapter/jdbc_oracle.rb:29:in
 `class_eval'
{code}

when running the line {{cols = Curve.columns}}.

I confirmed that JDBC itself works with Oracle synonyms using the following 
Java:

{code:title=Main.java|borderStyle=solid}
package javaapplication1;

import java.sql.*;

/**
 *
 * @author kapadia
 */
public class Main {

  Connection conn = null;

  public void connect() {
    try {
      // Load the JDBC driver
      String driverName = "oracle.jdbc.driver.OracleDriver";
      Class.forName(driverName);
      String url = "jdbc:oracle:thin:@SERVERA:1431:asid";
      String username = "myusername";
      String password = "mypassword";
      conn = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
      // Could not find the database driver
      System.out.println("Error: " + e.getMessage());
    } catch (SQLException e) {
      // Could not connect to the database }
      System.out.println("Error2: " + e.getMessage());
    }
  }

  public void print_metadata() {
    try {
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM curve");
      ResultSetMetaData md = rs.getMetaData();

      for (int i = 1; i <= md.getColumnCount(); i++) {
        System.out.println(md.getColumnLabel(i) + " " + 
md.getColumnTypeName(i));
      }
    } catch (Exception e) {
      System.out.println("metadata error" + e.getMessage());
    }
  }

  public static void main(String[] args) {
    Main m = new Main();
    m.connect();
    m.print_metadata();
  }
}

{code}

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://kenai.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira