Cannot resolve caller sqlite3_bind: don't understand this error in my Perl 6 script

Question

Purpose of the script: I want to be able to use it to insert expenses into the SQLite database, and then later create custom reports to extract information so that I can better pay my expenses.

I don’t understand this error code at all.

perl6 budgetpro.p6

Apple

An apple a day holds the doctor.

Hi, I'm important, 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 what you called:

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

As a result, it calls sqlite3_bind , but there is no candidate that accepts a Date instance as the third argument.

---

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

It seems to me that this is an error or a missing feature in the database driver, that it does not accept a DateTime object, or perhaps even an Instant , and automatically translates it to what SQLite expects.

---

I would also like to point out that there is Date.today and DateTime.now . The last one will keep track of current time zone information, and DateTime.new(now) will not.
(This is intentional since there is no way to find out where Instant came from.)

Answer 2

@ Brad Gilbert is right. Statements allow only 5 kinds of data structures, and none of them is Date . TIME and Date are standard data types in SQL , but so far they do not seem to support DBIish. In this case, you can simply change line 85 to

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

and while your program handles the conversion to and from dates, you also have to change the definition of the Essential table. You can also allow the database to handle default values. You will have to eliminate “non null” because this contradicts the default clause.

There are also a number of minor errors. This, minimized expression, works and correctly inserts the default timestamp into the database (as indicated by the 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'); 

Note that your Item class is not actually created by running the insert clause, and therefore the $test.name on the last line simply returns an empty object with the default values ​​assigned to the instance variables. You might want to change the insert method to a submethod that instantiates an element and inserts it into the database.