Dynamic insert of array data in mysql using DBI

Hi All,

I am trying to insert records from tab separated text file to mysql
using dbi,
please go through the following code below, It is working fine in the
case of all string but not working fine for other data type for example
date, time-stamp, float, etc.

require ‘dbi’
class InsertProcessor
puts “OutputProcessor”

def initialize
puts “Initializing the db connection”
begin
@@db_conn = DBI.connect(“DBI:MYSQL:database:myip”,“root”,“xxxx”)
rescue Exception=>e
puts “ERROR :: Error in initialize #{e.to_s}”
end
end

def insertRecord(fileName,tableName)
puts “inserting the Records in db”
puts "inserting the ‘#{fileName}’ file in #{tableName}db "

    # Executing sample query

clm_names = @@db_conn.execute(“select * from #{tableName} limit 1”)

     # saving the column names array in to a variable

rec_column_names = clm_names.column_names

    # validating the names  (most case we don't insert id)
   validated_clm_names = rec_column_names.delete_if { |clm| (clm ==

“id” || clm == “ID”) }

converting array to string

    string_clm_names = validated_clm_names.join(",")

testlen=validated_clm_names.length

#qm=Array.new
#value=Array.new

    #count=-1

#for n in 1…testlen.to_i
#qm << “?”
#value << “key_value[#{count=count+1}]”
#end
#puts qm
#puts value
#puts qm1=qm.join(",")
#puts value1=value.join(",")

Checking for file

if File.file?(fileName)

open the file fro further process

f = File.new(fileName)
begin
# get the each line (lines are basically are splited by new
lines)
f.each_line do |line|

getting the column (column are splited by tap)

key_value=line.chomp.split("\t")

        #~ sth.execute(key_value[0],key_value[1])
        #~ sth.execute(value1)
        testarray=Array.new
        kv_len=key_value.length
        count=-1
        for n in 1..testlen.to_i
          qm << "?"
          testarray << key_value[count=count+1]
        end

        testarray
        testarray1=testarray.join(",")

        puts "INSERT INTO #{tableName}(#{string_clm_names})

VALUES(#{testarray1})"

Exexuting the query

sth = @@db_conn.execute(“INSERT INTO #{tableName}(#{string_clm_names})
VALUES(#{testarray1})”)
#~ puts sth.execute(testarray1)
end

    rescue Exception=>e
      puts "ERROR :: Error is #{e.to_s}"
    ensure
      f.close
    end
    #break
  end

end

Hi all,

For your reference i attached the sample code ruby file please refer it

Thanks in advance,
Arun…

2010/7/20 Arun K. [email protected]:

Hi all,

For your reference i attached the sample code ruby file please refer it

Thanks in advance,
Arun…

Hi Arun,
You should replace
@@db_conn.execute(“INSERT INTO #{tableName}(#{string_clm_names})
VALUES(#{testarray1})”)

by
@@db_conn.execute(“INSERT INTO #{tableName}(#{string_clm_names})
VALUES(?)”, testarray1)

Doing so, you’ll let DBI do all the magic conversion and I guess that
might be related to your first problem.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs