Cannot resolve caller sqlite3_bind: Do not understand this error in my Perl 6 script

Question

Purpose of script: I want to be able to use it to insert expenses into a SQLite database and then make custom reports later to pull out information so I can budget my expenses better.

I do not understand this error code at all.

perl6 budgetpro.p6

Apple

An apple a day keeps the doctor away

Hi, I am Essential, nice to meet you, Eggman

Cannot resolve caller sqlite3_bind(DBDish::SQLite::Native::STMT, Int,
Date); none of these signatures match:
    (DBDish::SQLite::Native::STMT $stmt, Int $n, Blob:D $b)
    (DBDish::SQLite::Native::STMT $stmt, Int $n, Real:D $d)
    (DBDish::SQLite::Native::STMT $stmt, Int $n, Int:D $i)
    (DBDish::SQLite::Native::STMT $stmt, Int $n, Any:U)
    (DBDish::SQLite::Native::STMT $stmt, Int $n, Str:D $d)   
    in method execute at /home/jon/opt/rakudo-star/share/perl6/site/sources/2D749062AA6D5641452CDA214FC7C566E7E3E2A1
(DBDish::SQLite::StatementHandle) line 38
    in method insert at budgetpro.p6 line 54   in block <unit> at budgetpro.p6 line 86`

  2
  3 use v6;
  4 use DBIish;
  5
  6 constant DB = 'budgetpro.sqlite3';
  7 my $dbh = DBIish.connect('SQLite', database => DB);
  8
  9 #$dbh.do('drop table if exists Essential, Savings, Personal');
 10
 11 sub create-schema {
 12     $dbh.do(qq:to/SCHEMA/);
 13         create table if not exists Essential(
 14             id          integer primary key not null,
 15             name        varchar not null,
 16             quantity    integer not null,
 17             price       numeric(5,2) not null,
 18             description varchar not null,
 19             date        timestamp not null default (datetime('now'))
 20         );
 21         create table is not exists Savings(
 22             id          integer primary key not null,
 23             name        varchar not null,
 24             quantity    integer not null,
 25             price       numeric(5,2) not null,
 26             description varchar not null,
 27             date        timestamp not null default (datetime('now'))
 28         );
 29         create table if not exists Personal(
 30             id          integer primary key not null,
 31             name        varchar not null,
 32             quantity    integer not null,
 33             price       numeric(5,2) not null,
 34             description varchar not null,
 35             date        timestamp not null default (datetime('now'))
 36         );
 37     SCHEMA
 38 }
 39
 40 create-schema;
 41
 42 class Item {
 43     has $!table = 'Essential';
 44     has $.name;
 45     has $.quantity;
 46     has $.price;
 47     has $.description is rw;
 48     has $.timestamp;
 49     has Str $.notes is rw;
 50     method notes() { "$!notes\n" };
 51
 52     method insert($name, $quantity?, $price?, $description?, $timestamp?) {
 53         my $sth = $dbh.prepare("insert into Essential(name,quantity,price,description,date) values (?,?,?,?,?)");
 54         $sth.execute($name, $quantity, $price, $description, $timestamp);
 55
 56         say "Inserted $name, $quantity, $price, $description, $timestamp";
 57     }
 58 }
 59
 60 class Essential is Item {
 61     method greet($me: $person) {
 62         say "Hi, I am $me.^name(), nice to meet you, $person";
 63     }
 64 }
 65
 66 class Savings is Item {
 67 }
 68
 69 class Personal is Item {
 70 }
 71 
 72 my $food = Essential.new(
 73     name => 'Apple',
 74     price => .99,
 75     quantity => 2,
 76     notes => 'An apple a day keeps the doctor away'
 77 );
 78
 79 say $food.name;
 80 say $food.notes;
 81 Essential.new.greet('Eggman');
 82 say '';
 83
 84 my $test = Item.new();
 85
 86 $test.insert("Cheese", 2, 1.99, 'Block of cheddar', Date.new(now));
 87
 88 say $test.name;

Answer 1

The problem is that you called:

$test.insert("Cheese", 2, 1.99, 'Block of cheddar', Date.new(now));

That eventually calls sqlite3_bind, but there isn't a candidate that accepts an instance of Date as the third argument.

---

It would accept an instance of a Blob, Int, Real (Numeric but not Complex), or Str. It would also accept an undefined value (Any:U).

It seems to me that it is a bug or missing feature in the database driver that it doesn't accept a DateTime or perhaps even an Instant object, and automatically translates that to what SQLite expects.

---

I would also like to point out there is Date.today and DateTime.now. The latter of which will keep track of the current timezone information while DateTime.new(now) won't.
(This is intentional, as there is no way to know where the Instant came from.)

Answer 2

@Brad Gilbert is right. Statements admit only 5 kinds of data structures, and none of them is Date. TIME and DATE are standard data types in SQL, but for the time being they don't seem to have support in DBIish. In this case, you can just change line 85 to

$test.insert("Cheese", 2, 1.99, 'Block of cheddar', Date.new(now).Str);

and let your program handle conversion to and from dates, you'll have to change the definition of the Essential table, too. You can also let the database handle default values. You will have to eliminate "not null", because it conflicts with the default sentence.

There are also a number of minor errors. This, reduced to the minimum expression, works and inserts the default timestamp correctly into the database (as checked by using sqlite3 command line):

use v6;
use DBIish;

constant DB = 'budgetpro.sqlite3';
my $dbh = DBIish.connect('SQLite', database => DB);

#$dbh.do('drop table if exists Essential, Savings, Personal');

sub create-schema {
    $dbh.do(qq:to/SCHEMA/);
        create table if not exists Essential(
            id          integer primary key not null,
            name        varchar not null,
            quantity    integer not null,
            price       numeric(5,2) not null,
            description varchar not null,
            date        timestamp default (datetime('now'))
        );
    SCHEMA
}

create-schema;

class Item {
    has $!table = 'Essential';
    has $.name;
    has $.quantity;
    has $.price;
    has $.description is rw;
    has $.timestamp;
    has Str $.notes is rw;
    method notes() { "$!notes\n" };

    method insert($name, $quantity?, $price?, $description?, $timestamp?) {
        my $sth = $dbh.prepare("insert into Essential(name,quantity,price,description) values (?,?,?,?)");
        $sth.execute($name, $quantity, $price, $description);

        say "Inserted $name, $quantity, $price, $description";
    }
}

Item.new().insert("Cheese", 2, 1.99, 'Block of cheddar');

Please note that your Item class is not actually instantiated by running an insert sentence, and thus $test.name in the last line would just return an empty object with default values assigned to instance variables. You might want to change the insert method to a submethod that instantiates the item and inserts it into the database.