Rubyselect data out,  loop through data , perform action on data and insert into another table

Tereza
Tereza used Ask the Experts™
on
I am very new to ruby ..I am trying to convert some of my perl scripts ...I am having problems with looping through data ....this is what I have

#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'
require 'dbi'
numrows=0

print("Enter the number of ips to be generated from each Source range ");
numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db = DBI.connect("DBI:Mysql:sampe:localhost","root","pswd")

# select all the ipfrom values from the ip2locsector table
 ins_sth = db.prepare("INSERT INTO ips (ip) VALUES (?)")
 
  sth=db.execute("select ipfrom from ip2locsector")
  sth.fetch do |row|
   # Prints the correct number of ip address
    print("'#{row}'")  
# should loop though each IP address numrows times adding
# one and inserting that into the ips table
 numrows.times {|i|
      startip = startip + 1
      ins_sth.execute(startip)
        }
end


sth.finish

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Ive changed the code just to see how the data is manipulated  and this is my result...
#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'

numrows=0

print("Enter the number of ips to be generated from each Source range ");
numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db = DBI.connect("DBI:Mysql:sampe:localhost","root","admin")
     db["AutoCommit"]=true

# select all the ipfrom values from the ip2locsector table
 ins_sth = db.prepare("INSERT INTO ips (ip) VALUES (?)")
 
   rows = db.execute("SELECT ipfrom FROM ip2locsector")
    rows.fetch_array do |row|
     print ("Insert into ips (ip) values ('#{row}')\n")
            
         end

RESULT*******************************************
Insert into ips (ip) values ('["0056996352"]')
Insert into ips (ip) values ('["0128763904"]')
Insert into ips (ip) values ('["0168427776"]')
Insert into ips (ip) values ('["1051304880"]')
Insert into ips (ip) values ('["1066027360"]')
Insert into ips (ip) values ('["1072025176"]')
Insert into ips (ip) values ('["1568667648"]')
Insert into ips (ip) values ('["3343412080"]')
Insert into ips (ip) values ('["3396096608"]')
Insert into ips (ip) values ('["3502437888"]')
Insert into ips (ip) values ('["3524098896"]')
Insert into ips (ip) values ('["3585842528"]')
Insert into ips (ip) values ('["3653472280"]')
although its better that its iterating through..how do I remove those brackets..
and then of course what I really want is to have a loop that adds 1 to each of those values ( as many times as desired) and then insert into the database...

Author

Commented:
Ive changed it again to:
#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'
begin
numrows=0

print("Enter the number of ips to be generated from each Source range ");
numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db=DBI.connect("DBI:Mysql:sampe:localhost","root","")
     db["AutoCommit"]=true

# select all the ipfrom values from the ip2locsector table
 ins_sth=db.prepare("INSERT INTO ips (ip) VALUES (?)")
 
   db.execute("SELECT ipfrom FROM ip2locsector") do |res|
      res.fetch_array do |row|
        
      puts row.join(", ")
   
   end
 
 
   
         end
      rescue DBI::DatabaseError => e
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
db.disconnect if db  
end
RESULT***********************************************************
0056996352
0128763904
0168427776
1051304880
1066027360
1072025176
1568667648
3343412080
3396096608
3502437888
3524098896
3585842528
3653472280
**********************************
lists my data out...but i still am getting errors when I try to add the code that takes each one of thos values adds 1 to it numrows times then insert that new value in an insert statement..

Author

Commented:
IM CLOSER....have the original 13 inserting into db....just need to perform work on them...


#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'
begin
numrows=0

print("Enter the number of ips to be generated from each Source range ");
numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db=DBI.connect("DBI:Mysql:sampe:localhost","root","pwsd")
     db["AutoCommit"]=true

# select all the ipfrom values from the ip2locsector table
 ins_sth=db.prepare("INSERT INTO ips (ip) VALUES (?)")
 
   db.execute("SELECT ipfrom FROM ip2locsector") do |res|
      res.fetch_array do |row|
        istmt="INSERT INTO ips (ip) VALUES (#{row.join(", ")})"
         puts istmt
      db.execute(istmt)
     
        puts istmt
   
   end
 
 
   
         end
      rescue DBI::DatabaseError => e
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
db.disconnect if db  
end

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Okay, here are some comments on your code:

row.join(",")
# => You're only selecting one field. You don't need to do anything with it. It's in an Array, so just get the value with row.first or row[0]

Adding one:
insert_stmt = "INSERT INTO ips (ip) VALUES (#{row.first.to_i + 1})"


# Inserting multiple records per source

# get list of IPs 
db.execute("SELECT ipfrom FROM ip2locsector") do |result|
  res.fetch_array do |row|
    ip_val = row.first
    numrow.times do |i|
      insert_stmt ="INSERT INTO ips (ip) VALUES (#{ip_val + i})"
      puts istmt
      db.execute(istmt)
    end
  end # of each ip
end

Open in new window

Author

Commented:
wesG

This section
# select all the ipfrom values from the ip2locsector table
 
   db.execute("SELECT ipfrom FROM ip2locsector") do |res|
      res.fetch_array do |row|
        
        istmt="INSERT INTO ips (ip) VALUES (#{row.join(", ")})"
         puts istmt
      db.execute(istmt)
      end
   end
RESULT************************************************************


Enter the number of ips to be generated from each Source range 10
INSERT INTO ips (ip) VALUES (0056996352)
INSERT INTO ips (ip) VALUES (0128763904)
INSERT INTO ips (ip) VALUES (0168427776)
INSERT INTO ips (ip) VALUES (1051304880)
INSERT INTO ips (ip) VALUES (1066027360)
INSERT INTO ips (ip) VALUES (1072025176)
INSERT INTO ips (ip) VALUES (1568667648)
INSERT INTO ips (ip) VALUES (3343412080)
INSERT INTO ips (ip) VALUES (3396096608)
INSERT INTO ips (ip) VALUES (3502437888)
INSERT INTO ips (ip) VALUES (3524098896)
INSERT INTO ips (ip) VALUES (3585842528)
INSERT INTO ips (ip) VALUES (3653472280)

So I get these values in ...I tried to do your code but the numrows.times..tried to insert the same value
10 times...I want it to take 0056996352+1 , then 0056996353+1, 0056996354 +1..and so on ..10 times
then move to the next ip address...
I keep running into that same issue ..the numrows.times...I want a while loop..while numrows<count..keep working on one value before moving to next...
thanks for helping
Right.  That's what the numrows block does.
numrow.times do |i|

# If numrows is 10, then the inner statement will be done 10 times,
# with i starting at 0 and going up to 9

insert_stmt ="INSERT INTO ips (ip) VALUES (#{ip_val + i})"
# this adds the i value (0,1,2,...9) to the ip_val for this row.

# Let's make it easier.  Only select one IP value:
db.execute("SELECT ipfrom FROM ip2locsector LIMIT 1") do |result|

# Put the numrow code in and run it.
# Paste all your code (so I can double check what you're working with)
# AND the console output here

Open in new window

Author

Commented:
wesG

It was my typo..it worked fine...is there a faster way to do this.seems a little slow and my next part needs to be faster...Im going to start a new question because you answered this for me with success..
Thanks
T

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial