CODEWITHSUNDEEP
rubysqlitesqlite3-ruby
Bill Dukelow
Bill Dukelow

Reputation: 464

Inserted records are disappearing shortly after insertion

I'm seeing some odd behaviour when inserting records to a table of a sqlite DB in a Ruby application.

I have an application that fetches data from a series of API's, manipulates it and stitches it back together before inserting to a sqlite table.

I'm seeing an issue on insertions for one of the tables where the same 20 or so records appear to be inserted (I can see them in the SQLite viewer in the first few seconds after they are inserted), but then vanish. It's the same records that vanish each time.

The table i'm inserting to can be replicated like this:

CREATE TABLE cont (cont_id INTEGER PRIMARY KEY, type TEXT, title TEXT, creation_date DATE, total_count INTEGER, unique_count INTEGER, uri TEXT, ml_id INTEGER, FOREIGN KEY(ml_id) REFERENCES ml(id));

The method for inserting the record looks like this:

def save(table, columns, values, alternate = "IGNORE")
    tb_conf = @tables[table.upcase]
    placeholders = values.map {|v| '?'}.join(',')
    sql = "INSERT OR #{alternate} INTO #{table} (#{columns}) VALUES (#{placeholders})"

    begin   
        db = SQLite3::Database.new(@db_path)
        statement = db.prepare(sql)
        values.each_with_index {|value, index| statement.bind_param(index + 1, value)}
        statement.execute
        statement.close
    rescue  SQLite3::Exception => e
        p "SQLITE ERROR #{e}"
    ensure 
        db.close if db
    end        
    
end

This method is called with the following:

db.save(@table,@columns.join(','),values,'REPLACE')

Where the table is a string, the columns a comma delimited string of field names and the values an array like:

[172546, "ext_link", "https://example.net/dept/sub/eur/documents/report_Final.pdf", "2023-01-24", 9, 6, "https://api.example.com/v1/account_id/31889?cont=172546", 366597]

I'm not sure why they would appear in the table for a few seconds before disappearing. There are no exceptions thrown so I assume that this might be expected behaviour as a result of something i'm missing.

Upvotes: 0

Views: 108

Answers (0)

Related Questions