Rails - Apr 2005 - trying to write odbc adapter, but stumbled into a blocker

Hello,

I''ve started to write an odbc adapter for rails, but I''m
having trouble,
so I''m seeking some advice here :-)

First: I assume that the database that''s being connected to through
odbc
supports stuff like auto-incrementing primary keys, table creation and
such, so I leave it up to the user to make sure his db and corresponding
db-to-odbc-driver is up to par (or to use only the things that work, for
example for a read-only web app).

Second: I use the ruby ODBC driver (http://www.ch-werner.de/rubyodbc/)

Third: I''m testing on Windows with ODBC-Jet, ODBC-Postgres and
ODBC-Borland drivers

The trouble I have is with the quoting of variables in SQL WHERE clauses.
ActiveRecord::AbstractAdapter provides the method quote(value, column nil) that
needs to be overriden in the odbc_adapter.

The problem now is that the underlying code (the finders in
ActiveRecord::Base) always cast parameters to string (in
sanitize/sanitize_sql), after which the quote method is called, but
almost always without the second parameter column!
This raises the following problem:
odbc doesn''t want quotes around numerics, but wants quotes around
strings. But in the quote method, most of the time I don''t know the
column that it''s destined for, so I can''t decide whether to
put quotes
around the value or not. Which inevitably gives my either a "type
mismatch" odbc exception (when using a quoted numeric) or a "Too few
parameters" odbc exception (when using an unquoted string).

To fix this, either the quote function should always be called with the
corresponding column as second parameter, or it should cast it to the
correct db-columntype before calling the quote method. Or best, combine
quoting and casting in an appropriate method in the db adapter.

Appearantly this problem has not occured before because the supported
database drivers accept quoted numerics (at least i tried with postgres,
and indeed, no problem), but odbc (or at least MS-windows ODBC) doesn''t
like it.

Hopefully someone can point me to something I overlooked or
misunderstood... Or can convince me to file a bug :-)

If someone wants to play with the code I have till now, just shoot me an
e-mail directly and I''ll mail it back to you (it''s too fresh
to put on a
webpage right now :-) ).

Best regards,
Bas van Klinkenberg

I''ve got some of the same issues with my attempts to get Firebird up
and
running with Rails. I''m specifically dealing with adding the fixtures
to
the system so that it can run the unittests properly (Firebird doesn''t
like date with time columns). The following code is what I''m using in
fixtures.rb to get the value list of the insert statements working. The
concept certainly could apply to your case as well.

  def value_list
    if
!ActiveRecord::Base.connection.is_a?(ActiveRecord::ConnectionAdapters::FirebirdAdapter)
      values = @fixture.values.map { |v|
ActiveRecord::Base.connection.quote(v).gsub(''\\n'',
"\n").gsub(''\\r'',
"\r") }.join(", ")
    else
      columns = ActiveRecord::Base.connection.columns(@table_name)
      column_ordinals = columns.map { |col| col.name }
      values = ''''
      @fixture.values.each_with_index { |v,i|
        values << ", " unless values.length == 0
        values << ActiveRecord::Base.connection.quote(v,
columns[column_ordinals.index(@fixture.keys[i])]).gsub(''\\n'',
"\n").gsub(''\\r'', "\r")
      }
    end
    values
  end

I''m sure there is a better "ruby style" solution to handling
the columns
array (specifically finding the correct column using the name field of
the array member) - but the above does work. And for me that''s a good
starting point.....


John W Higgins
develop-U23jnKMpDSxBDgjK7y7TUQ@public.gmane.org


Bas van Klinkenberg wrote:
> Hello,
>
> I''ve started to write an odbc adapter for rails, but I''m
having trouble,
> so I''m seeking some advice here :-)
>
> First: I assume that the database that''s being connected to
through odbc
> supports stuff like auto-incrementing primary keys, table creation and
> such, so I leave it up to the user to make sure his db and corresponding
> db-to-odbc-driver is up to par (or to use only the things that work, for
> example for a read-only web app).
>
> Second: I use the ruby ODBC driver (http://www.ch-werner.de/rubyodbc/)
>
> Third: I''m testing on Windows with ODBC-Jet, ODBC-Postgres and
> ODBC-Borland drivers
>
> The trouble I have is with the quoting of variables in SQL WHERE clauses.
> ActiveRecord::AbstractAdapter provides the method quote(value, column >
nil) that needs to be overriden in the odbc_adapter.
>
> The problem now is that the underlying code (the finders in
> ActiveRecord::Base) always cast parameters to string (in
> sanitize/sanitize_sql), after which the quote method is called, but
> almost always without the second parameter column!
> This raises the following problem:
> odbc doesn''t want quotes around numerics, but wants quotes around
> strings. But in the quote method, most of the time I don''t know
the
> column that it''s destined for, so I can''t decide whether
to put quotes
> around the value or not. Which inevitably gives my either a "type
> mismatch" odbc exception (when using a quoted numeric) or a "Too
few
> parameters" odbc exception (when using an unquoted string).
>
> To fix this, either the quote function should always be called with the
> corresponding column as second parameter, or it should cast it to the
> correct db-columntype before calling the quote method. Or best, combine
> quoting and casting in an appropriate method in the db adapter.
>
> Appearantly this problem has not occured before because the supported
> database drivers accept quoted numerics (at least i tried with postgres,
> and indeed, no problem), but odbc (or at least MS-windows ODBC)
doesn''t
> like it.
>
> Hopefully someone can point me to something I overlooked or
> misunderstood... Or can convince me to file a bug :-)
>
> If someone wants to play with the code I have till now, just shoot me an
> e-mail directly and I''ll mail it back to you (it''s too
fresh to put on a
> webpage right now :-) ).
>
> Best regards,
> Bas van Klinkenberg
>
>
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Did you make any progress on this?

Bas van Klinkenberg <lists@...> writes:
> 
> Hello,
> 
> I''ve started to write an odbc adapter for rails, but I''m
having trouble,
> so I''m seeking some advice here 
> 
>