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