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