Database Population From Rets

#!/usr/bin/ruby # require's for the librets and mysql libraries require 'librets' require 'mysql' require 'pp' include Librets # Empty value for "values". I can into a problem with line 55 which required me to put this in. values = "" mls_number = "" # Initilizes and connects to the MySQL Database mysql = Mysql.init() mysql.connect(host='<>', user='<>', passwd='<>') mysql.select_db('Duluth') # Optional: I was going off another Ruby::DBI set of instructions. Only include if you want to start from fresh each time. mysql.query("ALTER TABLE residentialpropertyphotos DROP FOREIGN KEY residentialpropertyphotos_ibfk_1;") mysql.query("DROP TABLE IF EXISTS ResidentialProperty") mysql.query("DROP TABLE IF EXISTS ResidentialPropertyPhotos") #Creates the Database to work with. I pulled the metadata from the RETS feed to determine what the field types and lengths are mysql.query("CREATE TABLE ResidentialProperty (L_ListingID INT UNSIGNED NOT NULL, PRIMARY KEY (L_ListingID), L_AskingPrice INT NOT NULL, L_Keyword2 INT NOT NULL, L_Keyword3 INT NOT NULL, L_Keyword4 INT, L_City CHAR(50) NOT NULL, L_Area varchar(50) NOT NULL, L_AddressNumber varchar(15) NOT NULL, L_AddressDirection varchar(2), L_AddressStreet varchar(50) NOT NULL, LR_Remarks11 varchar(1000), LA1_UserFirstName varchar(15) NOT NULL, LO1_OrganizationName varchar(50) NOT NULL, LO1_PhoneNumber1Area INT, LO1_PhoneNumber1 INT) Type=INNODB;") mysql.query("CREATE TABLE ResidentialPropertyPhotos (PhotoID INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (PhotoID), L_ListingID INT UNSIGNED NOT NULL, Photo_Number varchar(5) NOT NULL, Photo LONGBLOB, INDEX (L_ListingID), FOREIGN KEY (L_ListingID) REFERENCES ResidentialProperty (L_ListingID)) TYPE=INNODB;") # Creates a RETS Session session = RetsSession.new("<>") # Changes the encodeing from ASCII to ISO session.set_default_encoding(RETS_XML_ISO_ENCODING) # Login into RETS if !session.login("<>", "<>") puts "Invalid login" exit 1 end

# Not quite sure what this does puts "Action: " + session.action # Checks the version of the RETS server version = "1.0" version = "1.5" if (session.detected_rets_version == RETS_1_5) puts "RETS Version: " + version # Your DMQL syntax for your search. This search is grabbing all Active Residential listings. request = session.create_search_request("Property", "ResidentialProperty", "(L_Status=1_0)") #request.standard_names = true # Tells RETS to use standard names request.select = "L_ListingID,L_AskingPrice,L_Keyword2,L_Keyword3,L_City,L_Area,L_AddressNumber,L_A ddressDirection,L_AddressStreet,\ LR_Remarks11,LA1_UserFirstName,LO1_OrganizationName,LO1_PhoneNum ber1Area,LO1_PhoneNumber1" # Fields returned in your search request.limit = SearchRequest::LIMIT_DEFAULT # Requests the RETS Server default limit request.offset = SearchRequest::OFFSET_NONE # Indicates no offset to the RETS server request.count_type = SearchRequest::RECORD_COUNT_AND_RESULTS # Not exactly sure on this one results = session.search(request) # Executes the search columns = results.columns # Places results in columns results.each do |result| # Iterates through each row columns.each do |column| # Iterates through each column cleaned_value = mysql.escape_string(result.string(column)) values += column + "='#{cleaned_value}'," # Creates the set expression for the INSERT statement end mysql.query("INSERT INTO ResidentialProperty SET #{values.chop};") # Writes the results to the Database values = "" end # MySQL query to return all active MLS numbers results = mysql.query("Select L_ListingID FROM ResidentialProperty"); # Loop to process each MLS listing and retreive photos results.each do |row| mls_number = row[0] # Get Object request to pull listing photos get_object_request = GetObjectRequest.new("Property", "Photo") get_object_request.add_all_objects("#{mls_number}") get_object_response = session.get_object(get_object_request) # Assigns each part of the Get Object to a variable content_type_suffixes = { "image/jpeg" => "jpg", "image/gif" => "gif", "text/xml" => "xml" } get_object_response.each_object do |object_descriptor| object_key = object_descriptor.object_key

object_id = object_descriptor.object_id content_type = object_descriptor.content_type description = object_descriptor.description photo_content = mysql.escape_string(object_descriptor.data_as_string) # Insert statement to place images in database mysql.query("INSERT INTO ResidentialPropertyPhotos SET Photo_Number='#{object_id}', Photo='#{photo_content}', L_ListingID='#{mls_number}';") end end logout = session.logout mysql.close()

# Closes the RETS session # Closes the MySQL connection.