From: Damjan Rems <d_rems@...>
Date: 2009-06-30T20:09:25+09:00
Subject: Re: MySQL to SQlite data movement

Grant Gray wrote:
> Hi,
> 
> I hope someone can help. I am new to ruby and struggling with a hash and
> some database connections. What I need to do is copy data from table in
> a mysql database (2 columns), into a sqlite database (2 columns). I can
> take the data out of the mysql database with this command:
> 
> my = Mysql.real_connect("server", "username", "password", "cnms")
> ##first db
> db = SQLite3::Database.open( 'simo/db/development.sqlite3' ) ##second db
> myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")
> 
> I then put it into a hash like this:
> 
> myresult.each_hash do |row|
> 
> row.each do |key, value|
> 
> end
> If I run a print command on it it spits out the correct data.
> 
> If i then insert into sqlite3 with the following commands:
> 
> stmt = db.prepare("insert into usages ( device_id, bytes_sourced )
> values( 'key', 'value' )" )
> stmt = stmt.execute
> 
> my.close
> #db.close   ##this throws out an error
> end
> 
> This inserts key and value in the correct columns and does the correct
> amount of record entries.
> 
> Can some put me out of my misery and tell me where I am going wrong. I
> know key and value in the sqlite3 statement may need to be variables,
> but i am not sure what to make them. As you can see I am still new to
> ruby so I am lost as to what i do and the hundreds of websites i have
> read don't clear the mist in my brain.
> 
> Any help is really, really, really appreciated.
> 
> Thanks
> 
> Gigg

I guess you aldo have some problems with SQL too ;-)

my = Mysql.real_connect("server", "username", "password", "cnms")
db = SQLite3::Database.open( 'simo/db/development.sqlite3' ) ##second db
stmt = db.prepare("insert into usages ( device_id, bytes_sourced ) 
values( ?, ? )" )
myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")
myresult.each_hash do |row|
  row.each do |key, value|
    stmt.execute(key, value)
  end
end

But maybe:
 myresult.each do |row|
   stmt.execute(row['device_id'], row['bytes_sourced')
 end

would be better. I am not clear what did you want to do with all the 
hashing.


by
TheR
-- 
Posted via http://www.ruby-forum.com/.