I have a Rails application that is set up to use a MySQL database for
most of its data but I have a need to pull in data from a SQL Server
2008 instance as well. This holds our data for Microsoft's CRM and GP
and therefore cannot be pulled into the MySQL database. So I set up a
database configuration in config/database.yml for the connection to this
server and the connection seems to work just fine.

With that said, I have a couple of queries that I need to execute on
this database which joins together two tables and pulls columns from
each table. So I set up a model file for one of the tables like the
following...

--------------------------------------------------------
class Sop10200 < ActiveRecord::Base
self.establish_connection :gpconn
set_table_name 'SOP10200'

def self.invoice_items(invoicenmbr)
select('RGI_ENTLMT,RGI_LICLTH,RGI_PRVENTLMT,ITEMNMBR,QUANTITY,ITEMDESC').
joins('INNER JOIN RGI10200 ON (RGI10200.SOPNUMBE=SOP10200.SOPNUMBE
AND RGI10200.SOPTYPE=SOP10200.SOPTYPE AND
RGI10200.LNITMSEQ=SOP10200.LNITMSEQ)').
where('SOP10200.SOPNUMBE = ?', invoicenmbr)
end

end
--------------------------------------------------------

The first three columns in the select statement come from table RGI10200
and the last three come from SOP10200. The query executes and returns
without any errors, but when I do an inspect on the results that come
back, I realized that the tables from RGI10200 were missing. If I were
to switch them and create the model class around RGI10200, then the
fields from SOP10200 are missing. Any idea why I am missing fields from
the results?

To make sure it was sending the correct query, I copied the query that
gets put into the development log and used that in a DB client connected
to the server with the same credentials and it comes back with all the
fields present. I also verified using a packet capture that the data is
in fact coming back to the Rails system with all of the columns
populated. So it isn't the query formation or the DB server giving me
back the data I ask for.

Thanks in advance,
Michael

--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Search Discussions

  • Brynjolfur Thorvardsson at Dec 21, 2011 at 2:54 pm
    Maybe a simple solution would be to create a view in SQL Server and a model based on that view in Rails?

    -----Oprindelig meddelelse-----
    Fra: rubyonrails-talk@googlegroups.com På vegne af Michael M.
    Sendt: 21. december 2011 15:47
    Til: rubyonrails-talk@googlegroups.com
    Emne: [Rails] Active Record and SQL Server

    I have a Rails application that is set up to use a MySQL database for most of its data but I have a need to pull in data from a SQL Server
    2008 instance as well. This holds our data for Microsoft's CRM and GP and therefore cannot be pulled into the MySQL database. So I set up a database configuration in config/database.yml for the connection to this server and the connection seems to work just fine.

    With that said, I have a couple of queries that I need to execute on this database which joins together two tables and pulls columns from each table. So I set up a model file for one of the tables like the following...

    --------------------------------------------------------
    class Sop10200 < ActiveRecord::Base
    self.establish_connection :gpconn
    set_table_name 'SOP10200'

    def self.invoice_items(invoicenmbr)
    select('RGI_ENTLMT,RGI_LICLTH,RGI_PRVENTLMT,ITEMNMBR,QUANTITY,ITEMDESC').
    joins('INNER JOIN RGI10200 ON (RGI10200.SOPNUMBE=SOP10200.SOPNUMBE
    AND RGI10200.SOPTYPE=SOP10200.SOPTYPE AND RGI10200.LNITMSEQ=SOP10200.LNITMSEQ)').
    where('SOP10200.SOPNUMBE = ?', invoicenmbr)
    end

    end
    --------------------------------------------------------

    The first three columns in the select statement come from table RGI10200 and the last three come from SOP10200. The query executes and returns without any errors, but when I do an inspect on the results that come back, I realized that the tables from RGI10200 were missing. If I were to switch them and create the model class around RGI10200, then the fields from SOP10200 are missing. Any idea why I am missing fields from the results?

    To make sure it was sending the correct query, I copied the query that gets put into the development log and used that in a DB client connected to the server with the same credentials and it comes back with all the fields present. I also verified using a packet capture that the data is in fact coming back to the Rails system with all of the columns populated. So it isn't the query formation or the DB server giving me back the data I ask for.

    Thanks in advance,
    Michael

    --
    Posted via http://www.ruby-forum.com/.

    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.



    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Michael M. at Dec 21, 2011 at 3:47 pm

    Brynjolfur Thorvardsson wrote in post #1037688:
    Maybe a simple solution would be to create a view in SQL Server and a
    model based on that view in Rails?
    That would be a simple solution and one that I have thought about.
    However, in the future I am considering redeveloping the entire database
    and using SQL Server as the main platform. I wouldn't want to have to
    create a bunch of views just to get this to work. I thought the whole
    point of the Active Record/Model abstraction was so you could code it
    once and have it work for any database type.

    -Michael

    --
    Posted via http://www.ruby-forum.com/.

    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Colin Law at Dec 21, 2011 at 4:18 pm

    On 21 December 2011 14:47, Michael M. wrote:
    I have a Rails application that is set up to use a MySQL database for
    most of its data but I have a need to pull in data from a SQL Server
    2008 instance as well. This holds our data for Microsoft's CRM and GP
    and therefore cannot be pulled into the MySQL database. So I set up a
    database configuration in config/database.yml for the connection to this
    server and the connection seems to work just fine.

    With that said, I have a couple of queries that I need to execute on
    this database which joins together two tables and pulls columns from
    each table. So I set up a model file for one of the tables like the
    following...

    --------------------------------------------------------
    class Sop10200 < ActiveRecord::Base
    self.establish_connection :gpconn
    set_table_name 'SOP10200'

    def self.invoice_items(invoicenmbr)
    select('RGI_ENTLMT,RGI_LICLTH,RGI_PRVENTLMT,ITEMNMBR,QUANTITY,ITEMDESC').
    joins('INNER JOIN RGI10200 ON (RGI10200.SOPNUMBE=SOP10200.SOPNUMBE
    AND RGI10200.SOPTYPE=SOP10200.SOPTYPE AND
    RGI10200.LNITMSEQ=SOP10200.LNITMSEQ)').
    where('SOP10200.SOPNUMBE = ?', invoicenmbr)
    end

    end
    --------------------------------------------------------

    The first three columns in the select statement come from table RGI10200
    and the last three come from SOP10200. The query executes and returns
    without any errors, but when I do an inspect on the results that come
    back, I realized that the tables from RGI10200 were missing. If I were
    to switch them and create the model class around RGI10200, then the
    fields from SOP10200 are missing. Any idea why I am missing fields from
    the results?

    To make sure it was sending the correct query, I copied the query that
    gets put into the development log and used that in a DB client connected
    to the server with the same credentials and it comes back with all the
    fields present. I also verified using a packet capture that the data is
    in fact coming back to the Rails system with all of the columns
    populated. So it isn't the query formation or the DB server giving me
    back the data I ask for.
    I guess the problem is that the select statement returns a collection
    of Sop10200 objects, so there is nowhere to put the data from the
    other table. Normally in rails one would have relationships between
    the tables so that the joined data has somewhere to go.

    find_by_sql might help, have a look at http://www.railsrocket.com/find_by_sql.

    Colin

    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedDec 21, '11 at 2:47p
activeDec 21, '11 at 4:18p
posts4
users3
websiterubyonrails.org
irc#RubyOnRails

People

Translate

site design / logo © 2021 Grokbase