From: Steven Price <priceey316@...>
Date: 2009-10-21T17:58:41+09:00
Subject: mysql update with ruby + dbi

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
[/code]
-- 
Posted via http://www.ruby-forum.com/.