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
···
--
Posted via http://www.ruby-forum.com/.