You should be using placeholders.

You don't have quotes around your strings, so the db is interpreting cash (etc.) as column names. If you used placeholders instead of generating a new insert statement each time, you wouldn't have to worry about quoting.

Your code should look something like this (some error checking would probably be good to add):

my $insert_h = $dbh->prepare('INSERT INTO sales VALUES(?, ?, ?, ?, ?, ?)');
while () {
  $insert_h->execute(split);
}

You need to quote the string for the type field, or use placeholders. So:

my $sql_fmt = "INSERT INTO sales VALUES(%d, %d, '%s', %d, %d, %d)";

might be a minimal fix to get it to work.

Please read up on strings, quoting and placeholders in the DBI docs.

Michael

Here is a working example. Look at the Insert line.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=mydb.txt","","");
die $! unless $dbh;

# customer vendor transType productCode appNumber resultCode

eval {
$dbh->do(qq{ CREATE TABLE sales
            (customer int, vendor int, type   char(10),
              product int, app    int, result int)
            });
};
my $sql_fmt = "INSERT INTO sales VALUES(?, ?, ?, ?, ?, ?)";
while() {
    $dbh->do($sql_fmt, {}, split);
}

__DATA__
10112    6768    cash    01020    00780    1
10112    6768    cash    01020    00780    0
10112    6768    credit    00040    01010    1
10112    3650    cash    01840    01200    0
14100    2410    credit    02840    00910    0
14100    5220    credit    01020    00780    1

I don't know why I can't insert for the type field.

Maybe if you print $sql before executing it, you see what's wrong. I did:

INSERT INTO sales VALUES(10112, 6768, cash, 1020, 780, 1)

"cash" isn't quoted, so the SQL engine thinks it's a column or variable name. The solution to your problem is to either use quotes in the format string (bad), or to use placeholders (good). Placeholders were designed for this problem. Placeholders work much like [sprintf], so understanding how they work shouldn't be a problem for you. Still, it's a good idea to read in [cpan://DBI|DBI's documentation] what they have to say about it.

In this example I will use [cpan://DBIx::Simple], because typing (??) is much easier than typing (?, ?, ?, ?, ?, ?) and I am lazy.

#!/usr/bin/perl -w
use strict;
use DBIx::Simple;

my $db = DBIx::Simple->new('dbi:SQLite:mydb.txt', '', '', { RaiseError => 1 });

eval {
    $db->query(
        'CREATE TABLE sales (customer, vendor, type, product, app, result)'
    );
};

while () {
    $db->query('INSERT INTO sales VALUES (??)', split);
}

__DATA__
...

RaiseError lets the program die on errors. This saves a lot of "or die" typing. The [eval] is there to allow the CREATE TABLE statement to fail. (It'll fail if the table already exists.)

hth