Mysql to Sqlite Update SET not working

Discussion in 'Ruby' started by Grant Gray, Jul 11, 2009.

  1. Grant Gray

    Grant Gray Guest


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

    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 = '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

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

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


    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


    Grant Gray, Jul 11, 2009
    1. Advertisements

  2. Grant Gray

    Roger Pack Guest

    I've heard some people suggest somehow using active record to go between
    two db's. Never done it myself tho.
    Roger Pack, Jul 12, 2009
    1. Advertisements

  3. Grant Gray

    Thomas Chust Guest


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

    Thomas Chust, Jul 13, 2009
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.