From: Arun Kumar <jakheart001@...>
Date: 2010-07-20T14:55:15+09:00
Subject: 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
-- 
Posted via http://www.ruby-forum.com/.