# Dynamic insert of array data in mysql using DBI

**URL:** <https://www.ruby-forum.com/t/dynamic-insert-of-array-data-in-mysql-using-dbi/191882>\
**Category:** Ruby\
**Created:** [July 20, 2010, 7:54am UTC](https://www.ruby-forum.com/t/dynamic-insert-of-array-data-in-mysql-using-dbi/191882 "2010-07-20T07:54:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![arunvoip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e8c25b/32.png) [@arunvoip](https://www.ruby-forum.com/u/arunvoip)\
**Post date:** [July 20, 2010, 7:54am UTC](https://www.ruby-forum.com/t/dynamic-insert-of-array-data-in-mysql-using-dbi/191882/1 "2010-07-20T07:54:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![arunvoip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e8c25b/32.png) [@arunvoip](https://www.ruby-forum.com/u/arunvoip)\
**Post date:** [July 20, 2010, 8:29am UTC](https://www.ruby-forum.com/t/dynamic-insert-of-array-data-in-mysql-using-dbi/191882/2 "2010-07-20T08:29:59Z")

</div>

Hi all,

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

Thanks in advance,  
Arun…

---

<div class="post-metadata">

**Author:** ![arunvoip](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/e8c25b/32.png) [@arunvoip](https://www.ruby-forum.com/u/arunvoip)\
**Post date:** [July 30, 2010, 10:26am UTC](https://www.ruby-forum.com/t/dynamic-insert-of-array-data-in-mysql-using-dbi/191882/3 "2010-07-30T10:26:08Z")

</div>

2010/7/20 Arun K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> 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.