Dynamic insert of array data in mysql using DBI

[Arun Kumar]

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

 

[Arun Kumar]

Hi all,

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

Thanks in advance,
Arun....



Attachments:
http://www.ruby-forum.com/attachment/4869/output_processor1.rb

 

[Xavier Noëlle]

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.