Ruby--construct a insert query with each_row, fetch_hash or others

Tereza
Tereza used Ask the Experts™
on
I am trying to speed up a data generator I wrote in perl...which is the best/fastest way to iterate through the data in Ruby.. and how to construct the insert statement with proper sql syntax...I have this

db.execute("SELECT cvss, signature, type, classID, port FROM classification ORDER BY classID DESC") do |res|
   values=[]
      while res.each_row do |row|
         values << "('#{row.map{|d,x| db.quote(x) }.join("', '")}')"
       end
       puts values.join(",")
            insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES
            (current_timestamp,UNIX_TIMESTAMP(),row[0],row[1],row[2],row[3],row[4],(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP())"
           db.execute(insert_stmt)
*********************************
This doesnt work...Im not sure if i need to concatenate...or how to designate that the values from select statement should be injected into statement..
** whats the best website..book..etc to view database and ruby examples..
Thanks

Comment
Watch Question

Do more with

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

Author

Commented:
Another try:

#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'

numrows=0

print("Enter the number of multiplier value to generate rows: ");
numrows = gets.chomp.to_i

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

# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port FROM classification ORDER BY classID DESC")
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport) VALUES ("
        res.fetch_hash do |row|
        insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),(#{row["cvss"]},#{row["signature"]},#{row["type"]},#{row["classID"]},#{row["port"]})"
                 insert_stmt << ")"    
                     
             puts insert_stmt
           db.execute(insert_stmt)
 end

RESULT****************************************************************************************************
This results in the insert statement with no quotes around the strings....therefore invalid insert statement...
I can see I can use that trailing redirect to complete the statement...

Make sure you use the code option so we get the formatting.  Easier to read!

Single quotes in your insert_stmt won't be interpolated:
insert_stmt << "current_timestamp,UNIX_TIMESTAMP(), '(#{row["cvss"]}' ..."

Open in new window

Author

Commented:
WesG

I dont see whats wrong with this......its prints correctly..but only inserts one then throws sql exception
******************************************************************************8
# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port FROM classification ORDER BY classID DESC")
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES ("
        res.fetch_hash do |row|
         ip_val=row.first
            numrows.times do
        insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),#{row["cvss"]},'#{row["signature"]}','#{row["type"]}',#{row["classID"]},#{row["port"]}"
        insert_stmt << ",(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()\n"    
           print("#{ insert_stmt}\n")
           db.execute(insert_stmt)
         end  
         end
*****************************************************************************

also which would be faster...fetch_array/hash, each.xxx or something else...

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
sorry I didnt use the code option again...my bad i will next time
What's the exception?  Kinda hard for me to know if you don't tell me!

Also, paste the print()ed out SQL before it gets executed so I can see that.

As for speed, my guess is that mysql is the limiting factor, not ruby.  We can do some EXPLAIN checking after we get it working.

Author

Commented:
INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()

INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()
current_timestamp,UNIX_TIMESTAMP(),0,'delete suspicious code files','Response',143,21,(select port from classification where 1=1 Order by RAND() limit
 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TI
MESTAMP()

Author

Commented:

Error code: 1064
Error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
current_timestamp,UNIX_TIMESTAMP(),0,'delete suspicious code files','Response',1' at line 2
error SQLSTATE: 42000
Okay, looks like you're missing a quote:
'Response',1'
... should be:
'Response', '1'
Or:
'Response', 1

Author

Commented:
Yes but it shouldn't be putting a quote there at all no?
I dunno if you have that field set there as a number or a string.

It could be in the source data, too.  

I'm not sure what changes you've made to the code, but if you'll post the latest I'll check it out.

Author

Commented:
Ok here is the code that generated that...what i dont understand is it prints the first statement correctly ..the second row should be the same...thanks for help
#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'

begin
  numrows=0

  print("Enter the multiplier value to generate rows: ");
  numrows = gets.chomp.to_i

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

# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port   FROM classification ORDER BY classID DESC") 
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES ("
        res.fetch_hash do |row| 
	ip_val=row.first
	numrows.times do
	insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),#{row["cvss"]},'#{row["signature"]}','#{row["type"]}',#{row["classID"]},#{row["port"]}"
        insert_stmt << ",(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()\n"	
           print("#{ insert_stmt}\n")
		   db.execute(insert_stmt)
	   end  
         end
rescue DBI::DatabaseError => e
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "error SQLSTATE: #{e.state}"
ensure
db.disconnect if db  

end

Open in new window

Okay, looks like I was wrong.  That ending quote is the one from the 'error near' message.

Can you paste the entire SQL string that's being output before it's being run?  You said the first one works but the second doesn't, so paste both of them in and let's see what's different.

Author

Commented:
that first sql is inserted into the database..now I notice the next insert statement is the same as the first...so it isnt looping through the data..however since I have that on duplicate key at the end it should still insert into database..this is filler data...

Enter the multiplier value to generate rows: 35
INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()

INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()
current_timestamp,UNIX_TIMESTAMP(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND
() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID =
 UNIX_TIMESTAMP()

Error code: 1064
Error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
current_timestamp,UNIX_TIMESTAMP(),0,'patch software allowing exfiltration','Res' at line 2
error SQLSTATE: 42000

Open in new window

Aha!

You have your insert_stmt initializer OUTSIDE of the loop.

So, the first time, it's fine.  The second time, though, you're appending to the previous query!

Move the "INSERT INTO" part inside numrows.times block.

Author

Commented:
OMG...how could i miss that....thanks....:-)
Meh. I missed it a couple times, too, right?  No worries.

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