DBI under the MAC

[Sean Murphy]

Hi All.

I am having some strange behaviours. I have created a simple script to insert data into a SQLite database. When I use the prepare are
statement. The DBI driver complains that there is no such table.

The code looks like thus:

#!/usr/bin/perl

# Combining credit and savings sheets
# to find expenses and income.


use strict;
use DBI;

my $db_driver = "SQLite";
my $db_file = "budget.db";
my $dns = "DBI:$db_driver:database=$db_file";

my $dbh = DBI->connect ($dns, '', '',
{ RaiseError => 1, AutoCommit => 0});

my $sth1 = $dbh->prepare("insert into cat (name) values (?);")
or die("Cannot prepare table: " . DBI::errstr() );

my $sth2 = $dbh->prepare("insert into trans (accounts, transaction_date, description, amount, amount_type, transaction_type, serial, category_id) values (?, ?, ?, ?, ?, ?, ?, ?);")
or die("Cannot prepare: " . DBI::errstr() );


When the above is executed in the full script. We get the following error:

DBD::SQLite::db prepare failed: table trans has no column named accounts at ./insert_budget.pl line 60.
Cannot prepare: table trans has no column named accounts at ./insert_budget.pl line 60.

The Schema for the table shows trans being present. As follows:

sqlite> .schema trans
CREATE TABLE trans (transaction_id int primary key, account int, transaction_date date, description varchar(80), amount decimal (11,2), amount_type varchar(3) not null, transaction_type varchar(40), serial varchar(40), category_id int);
sqlite>

Any ideas what might be going on here? The drivers are being found. I have tested this by using the perl -d option with the script.

Sean

[Keary Suska]

On Dec 9, 2011, at 4:42 AM, Sean Murphy wrote:

> my $sth2 = $dbh->prepare("insert into trans (accounts, transaction_date, description, amount, amount_type, transaction_type, serial, category_id) values (?, ?, ?, ?, ?, ?, ?, ?);")
> or die("Cannot prepare: " . DBI::errstr() );
>
>
> When the above is executed in the full script. We get the following error:
>
> DBD::SQLite::db prepare failed: table trans has no column named accounts at ./insert_budget.pl line 60.
> Cannot prepare: table trans has no column named accounts at ./insert_budget.pl line 60.

"accounts" (plural)

> sqlite> .schema trans
> CREATE TABLE trans (transaction_id int primary key, account int, transaction_date date, description varchar(80), amount decimal (11,2), amount_type varchar(3) not null, transaction_type varchar(40), serial varchar(40), category_id int);

"account" (singular)

HTH,

Keary Suska

[Sean Murphy]

Hi Keary

Ouch, it was right in front of my nose and I didn't see it.

Thanks.