Working With Rails

16:57 30-03-10

#01

Hi , I am facing strange problem with SQLServer. I am trying to execute the query using Activerecord::Base.connection. Here My sample Code. rs= ActiveRecord::Base.connection.execute(query) query will be the general sql query , here in this case i have used the following as query. query= 'select

            Patients.Id, Patients.Chart_ID,Patients.Last_Name PLastName,
            Patients.First_Name PFirstName,Patients.Middle_Initial PMI,
            Patients.Gender,Patients.Birth_date,
            Pat_comm.Homephone,pat_comm.Workphone,pat_Comm.mobile,pat_comm.email,
            pat_addr.address,pat_addr.city,pat_addr.state,pat_addr.zip
            from
            patients patients
            left join patient_communications pat_comm on pat_comm.patient_id=patients.id
            left join patient_addresses pat_addr on pat_addr.patient_id=patients.id
            order by patients.Last_Name,Patients.First_name'

when i am trying to iterate the rs using rs.each i am getting the error Statement already closed. DBI connection problem. But i have tried with the same using Mysql database, i can able to produce the result and prepared XML. I am not sure why for sqlserver it's throwing error, even i can't able to persist the dataobject. if i say puts rs then it's giving me the object. if i want to use that object then it's saying that " Statement already Closed" I am not aware on Why it's coming for only in sqlserver. please help me on this issue. I am using rails 2.3.4 'dbi', :version => '0.4.3' 'dbd-odbc', :version => '0.2.4', :lib => 'dbd/ODBC' 'activerecord-sqlserver-adapter', :version => '2.2.22'

ingfernandotobar

18:43 30-03-10

#02

Have you tried it with find?

this query would be something like:

patients = Patient.find(:all, :include => :comunication)

iteration eg.:

patients.each do |p|

puts 'Last Name: '+ p.last_name
puts p.comunications.map{|comunication| comunication.description }.join(", ")

end

you can use select parameter to filter. From API DOC: :select - By default, this is "*" as in "SELECT * FROM", but can be changed if you, for example, want to do a join but not include the joined columns. Takes a string with the SELECT SQL fragment (e.g. "id, name").

(assuming you have:

class Patient < ActiveRecord::Base

 has_and_belongs_to_many :comunications

end

class Comunication < ActiveRecord::Base

has_and_belongs_to_many :patients

end

or similar.)

This way you can check if your connection with the adapter is correct.

If you want to continue using ActiveRecord:: Base.execute (sql) you can see the following forum: http://railsforum.com/viewtopic.php?id=2306

Greetings

ingfernandotobar

Forum Rating: 100

14:19 31-03-10

#03

Hi, I want to use the ActiveRecord::Base.connection.execute only, if i go with the solution given by the rails conventions is not works for me, i need to use the query like that in order to produce some complex reports. That query is Works fine with the mysql database but not with the sqlserver, please help me in this

lajanus

22:19 01-04-10

#04

Hello, You can try making it a view and use view as model data, for example PatientContactInfo.

Kind Regards