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
|
use strict;
use warnings;
use lib "t/lib";
use SQLiteTest;
use Test::More;
use if -d ".git", "Test::FailWarnings";
use DBI qw(:sql_types);
my $dbh = connect_ok();
$dbh->do('drop table if exists artist');
$dbh->do(<<'END_SQL');
create table artist (
id int not null primary key,
name text not null
)
END_SQL
ok( $dbh->do(q/insert into artist (id,name) values(1, 'Leonardo da Vinci')/), 'insert');
# length works in a select list...
my $sth = $dbh->prepare('select length(name) from artist where id=?');
ok( $sth->execute(1), 'execute, select length' );
is( $sth->fetchrow_arrayref->[0], 17, 'select length result' );
# but not in a where clause...
my $statement = 'select count(*) from artist where length(name) > ?';
# ...not with bind args
$sth = $dbh->prepare($statement);
ok( $sth->execute(2), "execute: $statement : [2]" );
TODO: {
local $TODO = 'This test is currently broken again. Wait for a better fix, or use known workarounds.';
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement : [2]" );
}
### it does work, however, from the sqlite3 CLI...
# require Shell;
# $Shell::raw = 1;
# is( sqlite3($db, "'$statement;'"), "1\n", 'sqlite3 CLI' );
# ...works without bind args, though!
$statement =~ s/\?/2/;
$sth = $dbh->prepare($statement);
ok( $sth->execute, "execute: $statement" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement" );
# (Jess Robinson discovered that it passes with an arg of 1)
$statement =~ s/2/1/;
$sth = $dbh->prepare($statement);
ok( $sth->execute, "execute: $statement" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement" );
# (...but still not with bind args)
$statement =~ s/1/?/;
$sth = $dbh->prepare($statement);
ok( $sth->execute(1), "execute: $statement : [1]" );
TODO: {
local $TODO = 'This test is currently broken again. Wait for a better fix, or use known workarounds.';
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement [1]" );
}
# known workarounds 1: use bind_param explicitly
$sth = $dbh->prepare($statement);
$sth->bind_param(1, 2, { TYPE => SQL_INTEGER });
ok( $sth->execute, "execute: $statement : [2]" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement : [2]" );
# known workarounds 2: add "+0" to let sqlite convert the binded param into number
(my $tweaked_statement = $statement) =~ s/\?/\?\+0/;
$sth = $dbh->prepare($tweaked_statement);
ok( $sth->execute(2), "execute: $tweaked_statement : [2]" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $tweaked_statement : [2]" );
# workaround 3: use sqlite_see_if_its_a_number attribute
{
local $dbh->{sqlite_see_if_its_a_number} = 1;
$sth = $dbh->prepare($statement);
ok( $sth->execute(2), "execute: $statement : [2]" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $statement : [2]" );
}
# known workarounds 4: cast()
($tweaked_statement = $statement) =~ s/\?/cast(\? as integer)/;
$sth = $dbh->prepare($tweaked_statement);
ok( $sth->execute(2), "execute: $tweaked_statement : [2]" );
is( $sth->fetchrow_arrayref->[0], 1, "result of: $tweaked_statement : [2]" );
done_testing;
|
