Derek Smith wrote:
> I have a text file, called maillog that I want to insert into a table.
> My pseudo-code is
> 
> open mail file
> connect to database => 'require dbi' here?
> create table for maillog data
> create primary key in table
> 
> for each line in mailfile
> do
>     insert line from mailfile into rows
> done
> 
> close file
> close database
> 
> 
> Am I far off?
> Please advise with tips maybe and or actual code!
> Thank you

Not bad Derek.  If you are only ever going to use sqlite3 then perhaps 
the Amalgalite gem will suit your purposes.

1. Specify a primary key at create table time, and it will be created 
for you.
2. create an insert statement with parameter markers.  Prepare it once 
and execute it multiple times.
3. if you have a lot of rows to insert wrap your insert loop in a 
transaction. In amalgalite you start a transaction with
db.transaction(Amalgalite::Database::TransactionBehavior::IMMEDIATE)
and end with
db.commit
DBI should have some sort of equivalent.


Sample code?

# Load all required gems
require "rubygems"
require "Amalgalite"

fn="tester.sq3"
FileUtils::rm(fn) if File.exist?(fn)  # start with a clean slate
db=Amalgalite::Database.new(fn)
db.execute("create table test ( colone, coltwo, colthree, colfour )")
db.commit
insert_sql="insert into test values (?,?,?,?)"
stmt=db.prepare(insert_sql)
stmt.execute(*%w{one two three four})
stmt.execute(*%w(five six seven eight))
db.commit

Hope this helps

Steve.