Mysql to Sqlite Update SET not working

[Grant Gray]

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:atabase.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

 

[Roger Pack]

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

I've heard some people suggest somehow using active record to go between
two db's. Never done it myself tho.
=r

 

[Thomas Chust]

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

Hello,

I would suggest checking the SQLite3 documentation for the 'INSERT OR
REPLACE ...' syntax, which may be exactly what you need.

cu,
Thomas