Prev: how to judge web exists or not with open-uri?
Next: Simple Hack TO Get $1500 To Your PayPal Account.
From: Arun Kumar on 20 Jul 2010 01:55 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/.
From: Arun Kumar on 20 Jul 2010 02:30 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 -- Posted via http://www.ruby-forum.com/.
From: Xavier Noƫlle on 30 Jul 2010 04:25
2010/7/20 Arun Kumar <jakheart001(a)gmail.com>: > 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. -- Xavier NOELLE |