Problems with table name quoting in ActiveRecord-JDBC-Adapter > 0.9.0

Ruby JRuby
1

Hello,

I’m running a Rails 2.2.2 application on an Oracle database. To access
tables in a different schema without public synonyms we have defined our
model classes like this:

class SKey < ActiveRecord::Base
set_table_name ‘lnx.s_key’
…
end

When I try to find SKey objects in the database, then I get with
AR-JDBC-
Adapter 0.9.0 the following statement:

SELECT * FROM lnx.s_key WHERE (lnx.s_key.s_topic = ‘cprocessing’ AND
lnx.s_key.s_subtopic = ‘cprocessing’ AND lnx.s_key.s_keyword = ‘NILI’)

Newer versions of AR-JDBC-Adapter (e.g. 0.9.2) produce:

SELECT * FROM “lnx.s_key” WHERE (“lnx.s_key”.s_topic = ‘idelivery’ AND
“lnx.s_key”.s_subtopic = ‘idelivery’ AND “lnx.s_key”.s_keyword = ‘NILI’)

The difference is that the version > 0.9.0 quotes the table names and
thus
invalidates the statements.
The only working quotation were “LNX”.“S_KEY” for the example above. But
I
don’t see a way to make this work without taking out the schema from the
name. (In that case I’d write only the table name and in upper case.)

I’ve also found that the table name is only quoted if I specify it
explicitly in the class. If I comment set_table_name, the table name is
set
to s_keys (without quotation) - it’s just that we have s_key in the
database, it would work if the table name were in plural.

Can somebody give me a hint about what place needs to be looked at in
the
adapter?

Thanks in advance
Andreas

Andreas G.
Otto Group · Group Technology Partner (GTP)

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
2

Am Mittwoch, 4. November 2009 schrieb [email protected]:

The difference is that the version > 0.9.0 quotes the table names and
thus invalidates the statements.
The only working quotation were “LNX”.“S_KEY” for the example above. But
I don’t see a way to make this work without taking out the schema from
the name. (In that case I’d write only the table name and in upper
case.)

Here’s what I’ve found so far:

a) Quoting

Originally in AR 2.2.2 (that’s what I have here) there is in quoting.rb:

  # Quotes the column name. Defaults to no quoting.
  def quote_column_name(column_name)
    column_name
  end

  # Quotes the table name. Defaults to column name quoting.
  def quote_table_name(table_name)
    quote_column_name(table_name)
  end

So quote_table_name doesn’t quote here. Unfortunately jdbc_oracle.rb
redefines quote_column_name. It would be good to add there:

# Quotes the table name. Defaults to original column name quoting
# from AR which doesn't quote!
def quote_table_name(table_name)
  table_name
end

b) Schema usage

In RubyJdbcConnection.java we have:

public IRubyObject columns_internal(final ThreadContext context,

final IRubyObject[] args)
throws SQLException, IOException {
return (IRubyObject) withConnectionAndRetry(context, new
SQLBlock() {
public Object call(Connection c, Block proc) throws
SQLException {
ResultSet results = null;
try {
String table_name =
rubyApi.convertToRubyString(args[0]).getUnicodeValue();
String schemaName = null;

                int index = table_name.indexOf(".");
                if(index != -1) {
                    schemaName = table_name.substring(0, index);
                    table_name = table_name.substring(index + 1);
                }

                DatabaseMetaData metadata = c.getMetaData();

                if(args.length > 2 && schemaName == null) schemaName

= toStringOrNull(args[2]);

                if (schemaName != null) schemaName =

caseConvertIdentifierForJdbc(metadata, schemaName);
table_name = caseConvertIdentifierForJdbc(metadata,
table_name);

The problem is that the schema name is corectly parsed from the table
name
but then it’s unconditionally replaced by the schema name of the current
user.
The commands should get rearranged like this:

                String table_name =

rubyApi.convertToRubyString(args[0]).getUnicodeValue();
String schemaName = null;

                DatabaseMetaData metadata = c.getMetaData();

                int index = table_name.indexOf(".");
                if(index != -1) {
                    schemaName = table_name.substring(0, index);
                    table_name = table_name.substring(index + 1);
                } else {
                    if(args.length > 2) schemaName =

args[2].toString();
}

                if (schemaName != null) schemaName =

caseConvertIdentifierForJdbc(metadata, schemaName);
table_name = caseConvertIdentifierForJdbc(metadata,
table_name);

That means to read the meta data before the check for a schema part in
the
table name, and then use the schema from the meta data only if no schema
name was found.

Best regards,
Andreas

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
3

Hi Andreas,

Thanks for the report. Looks like we have some work to do on the
Oracle adapter. If you monkey-patch the JdbcSpec::Oracle module to add
a #quote_table_name method that does what you need, does it help? Or
do you still need the Java patch below?

In any case, if you could file this in
http://kenai.com/jira/browse/ACTIVERECORD_JDBC, that would be great.

/Nick

On Wed, Nov 4, 2009 at 4:46 AM, Andreas G. [email protected] wrote:

a) Quoting
quote_column_name(table_name)

               String table_name = rubyApi.convertToRubyString(args[0]).getUnicodeValue();
               if(args.length > 2 && schemaName == null) schemaName = toStringOrNull(args[2]);

               if (schemaName != null) schemaName = caseConvertIdentifierForJdbc(metadata, schemaName);

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
4

I’m having a similar problem with mysql db where the schema name is
stripped
from the query. In my model I have
set_table_name “myschema.companies”
but when I try to access it I get the following:

Company.find(1)
ActiveRecord::ActiveRecordError: Table companies does not exist
from
c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in
columns_with_query_cache' from c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1271:in columns’
from
c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1279:in
columns_hash' from c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1578:in find_one’
from
c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1569:in
find_from_ids' from c:/dev/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:616:in find’
from (irb):3

I can see that the full table name is being passed down to the
JdbcConnection in the JdbcAdapter. I recently upgraded from rails 2.0.2
where this code worked without a problem. Any ideas? Will the change to
RubyJdbcConnection.java fix it? And if so, is it planned as part of a
new
version?

Thanks,
Eran

Andreas G.-3 wrote:

  end
    Date.new(value.year, value.month, value.day) : value

/Nick

Here’s what I’ve found so far:

def quote_table_name(table_name)
SQLBlock() { public Object call(Connection c, Block proc) throws
}
The problem is that the schema name is corectly parsed from the table
if(index != -1) {
That means to read the meta data before the check for a schema part in

http://xircles.codehaus.org/manage_email

–
View this message in context:
http://old.nabble.com/Problems-with-table-name-quoting-in-ActiveRecord-JDBC-Adapter->-0.9.0-tp26193773p26504281.html
Sent from the JRuby - User mailing list archive at Nabble.com.

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email
5

Am Mittwoch, 4. November 2009 schrieb Nick S.:

Hi Andreas,

Thanks for the report. Looks like we have some work to do on the
Oracle adapter. If you monkey-patch the JdbcSpec::Oracle module to add
a #quote_table_name method that does what you need, does it help? Or
do you still need the Java patch below?

Hi Nick,

I needed both changes. The Ruby part corrects the quoting of the table
name,
the Java part fixes the extraction of the schema name from the model’s
table_name (if set via: set_table_name “schema.table”).

Further I’ve found in jdbc_oracle.rb:

  def self.guess_date_or_time(value)
    (value.hour == 0 && value.min == 0 && value.sec == 0) ?
    new_date(value.year, value.month, value.day) : value
  end

I haven’t found a definition for new_date(). If my code hits that line,
I
get an exception…
I think, the code as in the oracle_enhanced adapter is safer (as I also
had
problems with the hour() method in some circumstances):

  def self.guess_date_or_time(value)
    value.respond_to?(:hour) &&
        (value.hour == 0 && value.min == 0 && value.sec == 0) ?
    Date.new(value.year, value.month, value.day) : value
  end

In any case, if you could file this in
http://kenai.com/jira/browse/ACTIVERECORD_JDBC, that would be great.

Sure, I will do including a patch against the files from 0.9.2.

Andreas

 end

from AR which doesn’t quote!

   return (IRubyObject) withConnectionAndRetry(context, new
                   table_name = table_name.substring(index + 1);

               int index = table_name.indexOf(".");

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email