From: Grant Gray <grantg@...>
Date: 2009-07-11T17:30:25+09:00
Subject: Mysql to Sqlite Update SET not working

Hi,

I have been trying to get UPDATE SET working to move data from MYSQL to
SQLite3.

The code is as follows:

#!/usr/bin/env ruby

require 'dbi'
require 'rubygems'
require 'sqlite3'
require 'mysql'

my1 = Mysql.real_connect("server", "username", "password", "db")
my2 = SQLite3::Database.open( 'myapp/db/development.sqlite3' )
my2.results_as_hash = true

#stmt = my2.prepare("insert into usages ( device_id, bytes_sourced )
values( ?, ? )" )

stmt = my2.prepare("UPDATE usages SET bytes_sourced='row[1]' where
device_id='row[0]'")

result = my1.query("SELECT device_id, bytes_sourced FROM odma_unit")

result.each do |row|
  stmt.execute(row[0], row[1])

my1.close
end


When I run the code:

print print key + ' = ' + value
print "\n"

I get a print out to screen of all the items.
If I run:

stmt = my2.prepare("insert into usages ( device_id, bytes_sourced )
values( ?, ? )" )

I get entries in the database, obviously if i run it again i get
duplicate entries.

I just want the code to compare the field device_id in the MYSQL to
device_id in the SQLite database and thenif there is one to simply
update the bytes_sourced field. I have tried all sorts of combos for the
stmt and the script runs without errors but does not insert. Its a hash
to database entry and I even had the following code:

result.each do |key, value|
  stmt.execute(key, value)

I tihnk I have an error in my result statement.Does anyone have any
advice?

Thanks

Gigg
-- 
Posted via http://www.ruby-forum.com/.