mysql update with ruby + dbi

Discussion in 'Ruby' started by Steven Price, Oct 21, 2009.

  1. Steven Price

    Steven Price Guest

    Hello.

    Hopefully someone can help me and stop me form smashing my skull to bits
    on the desk...

    I am trying to modify a mysql database using a file for information.

    I can read the database fine, and parse it, but it just won't update the
    column as I want.

    I have tried about 2000 different ways, but always get the same result.
    The information I want to change just doesnt update.

    The code is posted below. I know there are a lot of exchanges of data,
    but I have only really been using ruby for about 2 days. So don't shout
    at me for the coding :)

    Here is my code:

    Code:
    #!/usr/bin/ruby
    
    require 'dbi'
    
    # quit unless our script gets two command line arguments
    unless ARGV.length == 1
    puts "Wrong number of arguments\n"
    puts "Usage: ruby mysql-test*.rb csv_file\n"
    exit
    end
    
    dbh = DBI.connect('DBI:Mysql:database', 'user', 'passwd')
    
    query = dbh.prepare("SELECT field_id, node_id, value FROM
    cfield_design_values ORDER BY node_id;")
    query.execute()
    while row = query.fetch() do
    printf "field_id = %d, node_id = %d, value = %s \n", row[0], row[1],
    row[2]
    if ( row[0] == 7 )
    puts "we have a match"
    if row[2].empty?
    puts "Nothing to parse"
    else
    puts "Something to parse"
    
    # Set up an empty array
    databaseArray = []
    
    # Split the web address in the database into the array
    databaseArray = row[2].split("/")
    
    # Check the end value
    $databaseSplit = databaseArray.last
    puts "value of the last element of the array = #$databaseSplit"
    
    if $databaseSplit.include?("DispForm")
    puts "Disp form found"
    
    # set up an array for the string
    
    $fileArray = []
    $fileHTTParray = []
    
    # open the file for reading
    puts "opening the file"
    
    # Create a bool for breaking when a match is found
    $matchFound = false
    
    File.open(ARGV[0], "r") do |file|
    while line = file.gets
    
    # Break from the loop when a match is found
    break(line) if $matchFound == true
    
    #puts "matchFound in the first part of the loop is #$matchFound"
    
    # Split the line
    
    $fileArray = line.split(",")
    
    # Put into a new variable
    fileSplit = $fileArray[2]
    
    # Split it into another array
    $fileHTTParray = fileSplit.split("/")
    
    # put into a variable for comparison
    $fileCompare = $fileHTTParray.last
    
    # Compare with the value from database
    #puts "#$databaseSplit, #$fileCompare"
    
    if ( $databaseSplit == $fileCompare)
    puts "WE HAVE A MATCH !!!! \n"
    
    printf "row[2] = %s\n", row[2]
    puts "filearray[4] = #$fileArray[4]\n"
    
    row[2] = $fileArray[4]
    
    printf "row[2] = %s\n", row[2]
    printf "row[0] = %d\n", row[0]
    printf "row[1] = %d\n", row[1]
    
    $field_id = row[0]
    $node_id = row[1]
    $value = row[2]
    
    # Change the value contained in the field node
    sql = ("UPDATE cfield_design_values SET value='$value' WHERE
    field_id='$field_id' && node_id='$node_id'")
    dbh.prepare(sql)
    dbh.execute(sql)
    dbh.commit
    
    $matchFound = true
    
    else
    
    #puts "They do not match"
    $matchFound = false
    
    end
    end
    end
    else
    puts "Filename found"
    end
    end
    end
    end
    dbh.disconnect
    
    --
    Posted via http://www.ruby-forum.com/.
     
    Steven Price, Oct 21, 2009
    #1
    1. Advertising

  2. On Oct 21, 2009, at 3:58 AM, Steven Price wrote:

    > Hello.
    >
    > Hopefully someone can help me and stop me form smashing my skull to
    > bits
    > on the desk...
    >
    > I am trying to modify a mysql database using a file for information.
    >
    > I can read the database fine, and parse it, but it just won't update
    > the
    > column as I want.
    >

    Any error messages? Relevant log entries? You have the relevant write
    permissions to the database? A bit more information would be helpful.

    Cheers--

    Charles
    --
    Charles Johnson
    Advanced Computing Center for Research and Education
    Office: 615-343-2776
    Cell: 615-478-5743
     
    Charles Johnson, Oct 21, 2009
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    534
    Juha Laiho
    Jul 22, 2004
  2. Michael campbell
    Replies:
    0
    Views:
    114
    Michael campbell
    Oct 22, 2003
  3. Paul Vudmaska

    dbi:mysql mysql has gone away

    Paul Vudmaska, Apr 21, 2004, in forum: Ruby
    Replies:
    0
    Views:
    116
    Paul Vudmaska
    Apr 21, 2004
  4. Jerome Hauss
    Replies:
    0
    Views:
    177
    Jerome Hauss
    Oct 13, 2004
  5. sam
    Replies:
    2
    Views:
    271
    J. Gleixner
    Jan 18, 2005
Loading...

Share This Page