Convert perl mysql script to Ruby

Tereza
Tereza used Ask the Experts™
on
Im trying to learn Ruby and do a simple conversion from perl...my issue is I know how to select the data and  iterate through the data ..however I want to take that result and have it feed a insert statement. Anyone give me an example

 sth = dbh.execute("SELECT X from from XY")
       while row = sth.fetch do
HERE

Comment
Watch Question

Do more with

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

Commented:
While very basic, and doesn't necessarily cover the issues you may find, do check the 'examples' directory from ruby DBI (which would be in the gems directory if you installed it as a gem).  It has a test script that is written in perl and ruby, so you can learn a bit from that.

Also, the documentation for ruby DBI should have been installed with a gem, but you can use http://ruby-dbi.rubyforge.org/rdoc/index.html if that would be more convenient.

With that out of the way, here is an example which should be a framework for how to add inserts.  First we prepare the insert statement that will be executed for each record returned from the SELECT.  (I just made a guess.)  Then, you execute the SELECT and use a block format so that the statement handle is handed right into the block.  Then similar to what you had, you fetch each row from the SELECT query, which is assigned to the 'row' variable in the block.  With that value, we do an 'execute' on the 'insert_stmt' we created at the beginning.  You also need to send 'finish' to insert_stmt in order to allow the database handle to be disconnected cleanly.
insert_stmt = dbh.prepare("INSERT INTO yz (<your column>) VALUES (:1)")

dbh.execute("SELECT x FROM xy") {|sth|
  sth.fetch {|row|
    insert_stmt.execute(row[0])
  }
}

insert_stmt.finish

Open in new window

Author

Commented:
cminear
thanks for your help...this is a simple section of my perl script...

sub iptarget {

print "Enter the number of ips in each ip range to generate : \n";
       chomp($numrows=<STDIN>);
        
#Select ipfrom from the ip2locsector table as starting ip
$dbquery = "SELECT ipfrom from ip2locsector";
$sth1 = $db ->prepare($dbquery);
$sth1->execute() or die "SQL Error: $DBI::errstr\n";

#Get the starting value of each range of ip addresses
while (@ipfrom = $sth1->fetchrow_array)
  {  
  $count=0;
    foreach $ipfrom (@ipfrom)
       {
        # get scalar values from the array, set startip to each
             $startip=$ipfrom;
             #  print $startip . "\n";
 # Loop through each value $numrows adding 1 to the IP address and insert into ips table              
          while ($count<$numrows)
               {
                    $startip++;
              $query  = "INSERT INTO ips (ip) VALUES ('$startip')";
              $sth = $db->prepare($query);
              $sth->execute();
           #   print $query . "\n";
                    $count++;
                  }  
        }
  }
  exit;
}

******************************************************************************************
THE SELECT AND HAVING IT IN AN ARRAY SEEMS  STRAIGHT FORWARD BUT WHAT ABOUT THE LOOPING AND ANY ALTERS TO DATA....iM VERY NEW TO THIS...IVE ALSO LOOKED AT ACTIVERECORD ....CAN YOU HELP WITH THIS SECTION....IM GOING TO CHECK THE SITES YOU SUGGESTED AS WELL
Commented:
I'll comment on your last issue first, RE: ActiveRecord.

To be honest, I personally do have more experience with ActiveRecord than with Ruby DBI.  ActiveRecord is very easy to use in a Rails environment, making accessing, creating and modifying data in the database very straight-forward.  I've also used ActiveRecord some outside of Rails, for similar reasons: I could easily create my ActiveRecord object and put it into the database, then repeat with the next one.

With all that, Ruby DBI still serves a purpose.  First, it is very similar to perl DBI and other similar frameworks, so it would make migration a little easier.  Not flawless, as you are finding, but you don't necessarily need to fully change to a new paradigm.  Also, if you are just more comfortable manipulating raw SQL, you probably would prefer DBI.  (Yes, there are ways to do SQL directly with ActiveRecord, but they are not the normal usage within ActiveRecord.)  Finally, if your application doesn't really require every record to be treated as a different object, ActiveRecord can be overkill.  (I was also going to say that DBI probably has a performance edge over ActiveRecord, but I have no evidence to back that up.  And well-written code using ActiveRecord can beat poorly-written code using DBI, so there are no absolutes, anyways.)

Now, here is a stab at translating your Perl DBI code.  (I am focusing on the database stuff.)  I haven't tested this so it may have bugs, but it should be a good framework for translating your Perl to Ruby.
# "db" is assumed to have been set previously as the database handle;
# 'numrows' is also assumed to have been previously set
#
# since there are no variables involved, you don't need to prepare
# the statement, just execute
ins_sth = db.prepare("INSERT INTO ips (ip) VALUES (:1)")

db.execute("SELECT ipfrom from ip2locsector") {|sth1|
  sth1.fetch_array() {|ipfrom|
    count = 0
    # the way you are using 'foreach' seems overkill, as the @ipfrom
    # variable should be a list of 1 item; the following uses 
    # that logic
    startip = ipfrom[0]
    # you want to perform actions 'numrows' times for each 
    # startip, so do that
    numrows.times {|i|
      startip = startip + 1
      ins_sth.execute(startip)
    }
  }
}

ins_sth.finish

Open in new window

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