mysql update with ruby + dbi

[Steven Price]

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:

#!/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

 

[Charles Johnson]

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
(e-mail address removed)