1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
#!/usr/bin/perl -w
use strict;
use Test::More;
BEGIN {
eval "use DBD::SQLite2";
plan $@ ? (skip_all => 'needs DBD::SQLite2 for testing') : (tests => 17);
}
use File::Temp qw/tempfile/;
my (undef, $DB) = tempfile();
my @DSN = (
"dbi:SQLite2:dbname=$DB", '', '',
{ AutoCommit => 1, RootClass => "DBIx::ContextualFetch" });
my $dbh = DBI->connect(@DSN);
$dbh->do("CREATE TABLE foo (id INTEGER, name TEXT)");
my $insert = $dbh->prepare("INSERT INTO foo VALUES (?, ?)");
$insert->execute(1, "Fred");
$insert->execute(2, "Barney");
sub make_sth {
my $sql = shift;
my $sth = $dbh->prepare($sql);
return $sth;
}
{ # fetch
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my @got1 = $sth->fetch;
is $got1[1], "Fred", 'fetch @';
my $got2 = $sth->fetch;
is $got2->[1], "Barney", 'fetch $';
}
{ # Fetch Hash
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my %got1 = $sth->fetch_hash;
is $got1{name}, "Fred", 'fetch_hash %';
my $got2 = $sth->fetch_hash;
is $got2->{name}, "Barney", 'fetch_hash $';
my %got3 = eval { $sth->fetch_hash };
is keys %got3, 0, "Nothing at the end";
is $@, "", "And no error";
}
{ # fetchall @
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my @got = $sth->fetchall;
is $got[1]->[1], "Barney", 'fetchall @';
}
{ # fetchall $
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my $got = $sth->fetchall;
is $got->[1]->[1], "Barney", 'fetchall $';
}
{ # fetchall_hash @
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my @got = $sth->fetchall_hash;
is $got[1]->{name}, "Barney", 'fetchall_hash @';
}
{ # fetchall_hash $
(my $sth = make_sth("SELECT * FROM foo ORDER BY id"))->execute;
my $got = $sth->fetchall_hash;
is $got->[1]->{name}, "Barney", 'fetchall_hash @';
}
{ # select_row
my $sth = make_sth("SELECT * FROM foo WHERE id = ?");
my ($id, $name) = $sth->select_row(1);
is $name, "Fred", "select_row";
}
{ # select_col
my $sth = make_sth("SELECT name FROM foo where id > ? ORDER BY id");
my @names = $sth->select_col(0);
is $names[1], "Barney", "select_col";
}
{ # select_val
my $sth = make_sth("SELECT name FROM foo where id = ?");
my $name = $sth->select_val(1);
is $name, "Fred", "select_val";
}
{ # Execute binding
my $sth = make_sth("SELECT * FROM foo WHERE id > ? ORDER BY id");
$sth->execute([0], [ \my ($id, $name) ]);
$sth->fetch;
is $id, 1, "bound id 1";
is $name, "Fred", "name = Fred";
$sth->fetch;
is $id, 2, "bound id 2";
is $name, "Barney", "name = Barney";
}
|
