1. Michael Granger
  2. ruby-pg

Issues

Issue #119 invalid

Trying to use COPY FROM STDIN succeeds but no records in the table

Digital Dialogue
created an issue

The following code runs without errors but it does not put the data in the table. {{{

!ruby

ActiveRecord::Base.connection_pool.with_connection do |conn| 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
end #connection

}}}

Comments (2)

  1. Michael Granger repo owner

    There's an example in `samples/copyfrom.rb` that does nearly the same thing, and it works, so there's something else going on with your implementation.

    Without knowing what your data looks like, what the code surrounding this does, and what the variables like `method` and `field_list` are, I can only guess at what might be going wrong.

    One thing I see immediately: are you declaring `errmsg` somewhere outside of the code you've pasted? If not, it's only scoped to the rescue clause, and so will always be `nil` when `if errmsg` runs. Otherwise, I'd suggest calling `conn.trace( $stderr )` and see if you can spot something happening that way.

    If you're still stuck, post a standalone version of your code to the mailing list and we'll try to get you sorted out.

  2. Michael Granger repo owner

    I took your example, and got it to run on its own and filled in some missing pieces:

    #!/usr/bin/env ruby
    
    require 'pg'
    
    conn           = PG.connect( dbname: 'test' )
    table_name     = 'issue_119'
    field_list     = %w[name body_weight brain_weight]
    method         = 0
    options        = { truncate: true }
    sql_parameters = ''
    
    conn.set_error_verbosity( PG::PQERRORS_VERBOSE )
    conn.exec( "DROP TABLE IF EXISTS #{table_name}" )
    conn.exec( "CREATE TABLE #{table_name} ( id SERIAL, name TEXT, body_weight REAL, brain_weight REAL )" )
    
    text = <<-END_DATA
    Mountain beaver 1.35    465
    Cow 465 423
    Grey wolf   36.33   119.5
    Goat    27.66   115
    Guinea pig  1.04    5.5
    Dipliodocus 11700   50
    Asian elephant  2547    4603
    Donkey  187.1   419
    Horse   521 655
    Potar monkey    10  115
    Cat 3.3 25.6
    Giraffe 529 680
    Gorilla 207 406
    Human   62  1320
    African elephant    6654    5712
    Triceratops 9400    70
    Rhesus monkey   6.8 179
    Kangaroo    35  56
    Golden hamster  0.12    1
    Mouse   0.023   0.4
    Rabbit  2.5 12.1
    Sheep   55.5    175
    Jaguar  100 157
    Chimpanzee  52.16   440
    Brachiosaurus   87000   154.5
    Mole    0.122   3
    Pig 192 18
    END_DATA
    
    #ActiveRecord::Base.connection_pool.with_connection do |conn|
        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)
            errmsg = nil # scope this outside of the rescue below so it's visible later
            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
                st = res.res_status( res.result_status )
                puts "Result of COPY is: %s" % [ st ]
                if res.result_status != PG::PGRES_COPY_IN
                    puts res.error_message
                end
            end
            puts "end"
        end #transaction
    #end #connection
    
    conn.exec( "SELECT name, brain_weight FROM #{table_name}" ) do |res|
        p res.values
    end
    

    Running it looks like:

    rvm 1.9.3 do ruby issue-119.rb 
    NOTICE:  00000: CREATE TABLE will create implicit sequence "issue_119_id_seq" for serial column "issue_119.id"
    LOCATION:  transformColumnDefinition, parse_utilcmd.c:384
    "COPY issue_119 (name,body_weight,brain_weight) FROM STDIN  "
    Result of COPY is: PGRES_COMMAND_OK
    
    end
    [["Mountain beaver", "465"], ["Cow", "423"], ["Grey wolf", "119.5"], ["Goat", "115"], ["Guinea pig", "5.5"], ["Dipliodocus", "50"], ["Asian elephant", "4603"], ["Donkey", "419"], ["Horse", "655"], ["Potar monkey", "115"], ["Cat", "25.6"], ["Giraffe", "680"], ["Gorilla", "406"], ["Human", "1320"], ["African elephant", "5712"], ["Triceratops", "70"], ["Rhesus monkey", "179"], ["Kangaroo", "56"], ["Golden hamster", "1"], ["Mouse", "0.4"], ["Rabbit", "12.1"], ["Sheep", "175"], ["Jaguar", "157"], ["Chimpanzee", "440"], ["Brachiosaurus", "154.5"], ["Mole", "3"], ["Pig", "18"]]
    

    Hopefully this will help a little.

  3. Log in to comment