ruby/mysql

[nathmatt]

im creating a back up and restore for my sql it the back up does fine but when restoring i get an error on the ips there no difference that i an see on the ips then the the 1s above it here is my script

Spoiler: ShowHide

# loading settings
load './cfg.ini'

# loading classes
load './Data/Data.rb'
load './Data/Options.rb'
load './Data/Server.rb'

# loading external libraries
require './mysql'

#======================================================================
# module RMXOS
#======================================================================

module RMXOS

	#----------------------------------------------------------------------
	# Fixes strings for SQL queries and eval expressions.
	#  string - string to be converted
	# Returns: Converted string.
	#----------------------------------------------------------------------
	def self.fix_string(string)
		return string.gsub('\'') {'\\\''}
	end
  
  #----------------------------------------------------------------------
	# Backs up the data base on the back_up file
	#----------------------------------------------------------------------
  def self.back_up(server)
    sql = server.sql
    puts 'Accesing buddy_list'
    b = sql.query("SELECT * FROM buddy_list")
    buddy_list = []
    b.each_hash{|h|buddy_list.push(h)}
    puts 'Accesing guilds'
    g = sql.query("SELECT * FROM guilds")
    guilds = []
    g.each_hash{|h|guilds.push(h)}
    puts 'Accesing inbox'
    i = sql.query("SELECT * FROM inbox")
    inbox = []
    i.each_hash{|h|inbox.push(h)}
    puts 'Accesing ips'
    ip = sql.query("SELECT * FROM ips")
    ips = []
    ip.each_hash{|h|ips.push(h)}
    puts 'Accesing save_data'
    sd = sql.query("SELECT * FROM save_data")
    save_data = []
    sd.each_hash{|h|save_data.push(h)}
    puts 'Accesing user_data'
    ud = sql.query("SELECT * FROM user_data")
    user_data = []
    ud.each_hash{|h|user_data.push(h)}
    puts 'Accesing users'
    u = sql.query("SELECT * FROM users")
    users = []
    u.each_hash{|h|users.push(h)}
    puts 'Saving data'
    file = File.open('Back_Up.rxdata', 'wb')
    Marshal.dump(buddy_list, file)
    Marshal.dump(guilds, file)
    Marshal.dump(inbox, file)
    Marshal.dump(ips, file)
    Marshal.dump(save_data, file)
    Marshal.dump(user_data, file)
    Marshal.dump(users, file)
    file.close
  end
  
  #----------------------------------------------------------------------
	# Restores the data base from the back_up file
	#----------------------------------------------------------------------
  def self.restore(server)
    sql = server.sql
    puts 'Loading Data'
    file = File.open('Back_Up.rxdata', 'rb')
    puts 'Loading buddy_list'
    buddy_list = Marshal.load(file)
    puts 'Loading guilds'
    guilds = Marshal.load(file)
    puts 'Loading inbox'
    inbox = Marshal.load(file)
    puts 'Loading ips'
    ips = Marshal.load(file)
    puts 'Loading save_data'
    save_data = Marshal.load(file)
    puts 'Loading user_data'
    user_data = Marshal.load(file)
    puts 'Loading users'
    users = Marshal.load(file)
    file.close
    puts 'Updating data base'
    puts 'Updating buddy_list'
    buddy_list.each_index{|i|
    b = buddy_list[i]
    index = i+1
    b.each{|key,value|sql.query("UPDATE buddy_list SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating guilds'
    guilds.each_index{|i|
    index = i+1
    g = guilds[i]
    g.each{|key,value|sql.query("UPDATE guilds SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating inbox'
    inbox.each_index{|i|
    ib = inbox[i]
    index = i+1
    ib.each{|key,value|sql.query("UPDATE inbox SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating ips'
    ips.each_index{|i|
    ip = ips[i]
    index = i+1
    ip.each{|key,value|sql.query("UPDATE ips SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating save_data'
    save_data.each_index{|i|
    sd = save_data[i]
    index = i+1
    sd.each{|key,value|sql.query("UPDATE save_data SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating user_data'
    user_data.each_index{|i|
    ud = user_data[i]
    index = i+1
    ud.each{|key,value|sql.query("UPDATE user_data SET '#{self.fix_string(key)}' = '#{self.fix_string(value)}' WHERE user_id = '#{index}'")}}
    puts 'Updating users'
    users.each_index{|i|
    u = users[i]
    index = i+1
    u.each{|key,value|sql.query("UPDATE users SET '#{index}'")}}
  end
    
	
end

# create server instance
server = RMXOS::Server.new
# optimize the database
server.connect_to_database
puts 'Choose a command'
puts '1 Back up data base'
puts '2 Restore data base'
case gets.to_i
when 1 then RMXOS.back_up(server)
when 2 then RMXOS.restore(server)
else
  puts 'invalid command closing'
end
server.sql.close rescue nil

puts 'Press ENTER to continue.'
gets
puts 'Please wait...'

[Blizzard]

Check out the utility script (in the Utility folder) that I used to optimize the table manually.

[nathmatt]

that is what i used as a base which works fine until i try to update the ips table it gives an error

[Blizzard]

You are having SQL syntax errors.

[nathmatt]

ok so the 1s before ips are not being called because they are empty so i need a better syntax what do you suggest

[Blizzard]

Dude, fix your SQL query syntax. This is not a valid SQL query:

UPDATE inbox SET 'whatever' = 'something' WHERE user_id = 'yeah'

[nathmatt]

calm down now  i must be miss reading this site because its says that is how you update

UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}

[Blizzard]

Yeah, that guy's inconsistent. :/

UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE {condition}

This barely makes sense since you're not supposed to put " anywhere.

Use this reference instead: http://dev.mysql.com/doc/refman/5.0/en/update.html