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

[Andreas Gungl-3]

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 Gungl
Otto Group · Group Technology Partner (GTP)

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Andreas Gungl-3]

Am Mittwoch, 4. November 2009 schrieb gungl@...:
> 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

[Nick Sieger-2]

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 Gungl <gungl@...> wrote:

> Am Mittwoch, 4. November 2009 schrieb gungl@...:
>> 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
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Andreas Gungl-3]

Am Mittwoch, 4. November 2009 schrieb Nick Sieger:
> 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

> /Nick
>
> On Wed, Nov 4, 2009 at 4:46 AM, Andreas Gungl <gungl@...> wrote:
> > Am Mittwoch, 4. November 2009 schrieb gungl@...:
> >> 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
>
> ---------------------------------------------------------------------
> 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

[eranlo]

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 Gungl-3 wrote:

Am Mittwoch, 4. November 2009 schrieb Nick Sieger:
> 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

> /Nick
>
> On Wed, Nov 4, 2009 at 4:46 AM, Andreas Gungl <[e-mail]> wrote:
> > Am Mittwoch, 4. November 2009 schrieb [e-mail]:
> >> 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
>
> ---------------------------------------------------------------------
> 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