I have done some googling to try and figure out how I can use COPY FROM STDIN with rails and have ran into some posts on stackoverflow or the mailing list but none of them seem to be working with rails 3.2 Does anybody have a working example of using COPY FROM STDIN? Some things I have tried https://bitbucket.org/ged/ruby-pg/src/tip/sample/copyfrom.rb http://blog.edseek.com/archives/2009/04/26/putline-undefined-for-pgconn/ and similar variations. Nothing seems to be working though. Thanks. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Mar 20, 2012, at 8:49 PM, Tim Uckun wrote:> I have done some googling to try and figure out how I can use COPY > FROM STDIN with rails and have ran into some posts on stackoverflow or > the mailing list but none of them seem to be working with rails 3.2 > Does anybody have a working example of using COPY FROM STDIN? > > Some things I have tried > > https://bitbucket.org/ged/ruby-pg/src/tip/sample/copyfrom.rb > http://blog.edseek.com/archives/2009/04/26/putline-undefined-for-pgconn/ > > and similar variations. Nothing seems to be working though.I didn''t do this on 3.2, but 3.0.x... not sure if this is one of the stackoverflow options you tried or not... conn = ActiveRecord::Base.connection_pool.checkout raw = conn.raw_connection raw.exec("COPY tablename (col1, col2, col3) FROM STDIN") # open up your CSV file looping through line by line and getting the line into a format suitable for pg''s COPY... rc.put_copy_data line # once all done... rc.put_copy_end while res = rc.get_result do; end # very important to do this after a copy ActiveRecord::Base.connection_pool.checkin(conn) Source: http://stackoverflow.com/a/6780870/91830 -philip -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> conn = ActiveRecord::Base.connection_pool.checkout > raw = conn.raw_connection > raw.exec("COPY tablename (col1, col2, col3) FROM STDIN") > # open up your CSV file looping through line by line and getting the line into a format suitable for pg''s COPY... > rc.put_copy_data line > # once all done... > rc.put_copy_end > while res = rc.get_result do; end # very important to do this after a copy > ActiveRecord::Base.connection_pool.checkin(conn) >I did try that and it doesn''t work. Not only doesn''t it work but it fails silently. I wrote a more elaborate routine which I can get to run without errors but it still doesn''t add the records in the table. conn.transaction do rc = conn.raw_connection rc.exec "TRUNCATE TABLE #{table_name};" if options[:truncate] sql = "COPY #{table_name} (#{field_list.join('','')}) FROM STDIN #{sql_parameters} " p sql rc.exec(sql) begin if method == 1 rc.put_copy_data text + "\\.\n" else text.each_line { |line| rc.put_copy_data(line) } end rescue Errno => err errmsg = "%s while reading copy data: %s" % [err.class.name, err.message] puts "an error occured" end if errmsg rc.put_copy_end(errmsg) puts "ERROR #{errmsg}" else rc.put_copy_end end while res = rc.get_result puts "Result of COPY is: %s" % [res.res_status(res.result_status)] end puts "end" end #transaction Maybe it''s a bug in the PG gem? -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org 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.