Can't insert into for one column

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

The error messages I'm getting from the attempted insert are:

C:\perlp>perl t.pl
DBD::SQLite::db do failed: no such column: cash at t.pl line 17, <DATA
+> line 1.
DBD::SQLite::db do failed: no such column: cash at t.pl line 17, <DATA
+> line 2.
DBD::SQLite::db do failed: no such column: credit at t.pl line 17, <DA
+TA> line 3.
DBD::SQLite::db do failed: no such column: cash at t.pl line 17, <DATA
+> line 4.
DBD::SQLite::db do failed: no such column: credit at t.pl line 17, <DA
+TA> line 5.
DBD::SQLite::db do failed: no such column: credit at t.pl line 17, <DA
+TA> line 6.
[download]

The code and the input data are:

#!/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
$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(%d, %d, %s, %d, %d, %d)";
while(<DATA>) {
    my $sql = sprintf $sql_fmt, split;
    $dbh->do($sql);
}

__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
[download]

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

Thanks, Chris

Comment on Can't insert into for one column Select or Download Code

Replies are listed 'Best First'.
Re: Can't insert into for one column by Roy Johnson (Monsignor) on Jun 15, 2004 at 18:23 UTC
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 (<DATA>) { $insert_h->execute(split); }` [download] We're not really tightening our belts, it just feels that way because we're getting fatter.	[reply] [d/l]
Re: Can't insert into for one column by borisz (Canon) on Jun 15, 2004 at 18:28 UTC
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(<DATA>) { $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 [download] Boris	[reply] [d/l]
Re: Can't insert into for one column by Juerd (Abbot) on Jun 15, 2004 at 18:33 UTC
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)` [download] "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 DBI's documentation what they have to say about it. In this example I will use 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, res +ult)' ); }; while (<DATA>) { $db->query('INSERT INTO sales VALUES (??)', split); } __DATA__ ...` [download] 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 Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply] [d/l] [select]
Re^2: Can't insert into for one column by Anonymous Monk on Jun 15, 2004 at 19:11 UTC
Thank you everyone who responded :-) I haven't done much with DBI - just reading through some examples in O'Reilly books, the 'Perl Cookbook' and 'Computer Science and Perl Programming' and the MySql book by Dubois. I do remember seeing the quote function in some of the examples but it did not click that that was causing the problem. I read a question on clpm that the poster wanted some advice and I thought a database might provide a better solution to his problem. `http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=10crcltibum94e +6%40corp.supernews.com` [download] Chris	[reply] [d/l]
Re: Can't insert into for one column by mpeppler (Vicar) on Jun 15, 2004 at 18:25 UTC
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)";` [download] might be a minimal fix to get it to work. Please read up on strings, quoting and placeholders in the DBI docs. Michael	[reply] [d/l]