view release on metacpan or  search on metacpan

t/12placeholders.t  view on Meta::CPAN

$dbh->rollback();

SKIP: {
    skip 'Cannot adjust standard_conforming_strings for testing on this version of Postgres', 4
        if $pgversion < 80200 or $pgversion >= 19000;
    $t='Backslash quoting inside single quotes is parsed correctly with standard_conforming_strings off';
    $dbh->do(q{SET standard_conforming_strings = 'off'});
    eval {
        local $dbh->{Warn} = '';
        $sth = $dbh->prepare(q{SELECT '\', ?});
        $sth->execute();
        $sth->finish();
    };
    like ($@, qr{unterminated quoted string}, $t);
    $dbh->rollback();

    $t=q{Backslash quoting inside E'' is parsed correctly with standard_conforming_strings = 'off'};
    eval {
        $sth = $dbh->prepare(q{SELECT E'\'?'});
        $sth->execute();
        $sth->finish;
    };
    is ($@, q{}, $t);
    $dbh->rollback();

    $t='Backslash quoting inside single quotes is parsed correctly with standard_conforming_strings on';
    eval {
        $dbh->do(q{SET standard_conforming_strings = 'on'});
        $sth = $dbh->prepare(q{SELECT '\', ?::int});
        $sth->execute(1);
        $sth->finish();
    };
    is ($@, q{}, $t);

    $t=q{Backslash quoting inside E'' is parsed correctly with standard_conforming_strings = 'on'};
    eval {
        $sth = $dbh->prepare(q{SELECT E'\'?'});
        $sth->execute();
        $sth->finish;
    };
    is ($@, q{}, $t);
}


$t='Valid integer works when quoting with SQL_INTEGER';
my $val;
$val = $dbh->quote('123', SQL_INTEGER);
is ($val, 123, $t);

$t='Invalid integer fails to pass through when quoting with SQL_INTEGER';
$val = -1;
eval {
    $val = $dbh->quote('123abc', SQL_INTEGER);
};
like ($@, qr{quote_integer: invalid input}, $t);
is ($val, -1, $t);

my $prefix = 'Valid float value works when quoting with SQL_FLOAT';
$count = 0;
$problems = 0;
for my $float ('123','0.00','0.234','23.31562', '1.23e04','6.54e+02','4e-3','NaN','Infinity','-infinity') {
    $count++;
    $t = "$prefix (value=$float)";
    $val = -1;
    eval { $val = $dbh->quote($float, SQL_FLOAT); };
    if ($@ ne q{}) {
        $problems++;
        is ($@, q{}, "$t: error");
    }
    elsif ($val ne ($float =~ /[ai]/ ? qq{'$float'} : $float)) {
        $problems++;
        is ($val, ($float =~ /[ai]/ ? qq{'$float'} : $float), $t);
    }

    next unless $float =~ /[ai]/;

    my $number = lc $float;
    $t = "$prefix (value=$number)";
    $val = -1;
    eval { $val = $dbh->quote($number, SQL_FLOAT); };
    if ($@ ne q{}) {
        $problems++;
        is ($@, q{}, "$t: error");
    }
    elsif ($val ne qq{'$number'}) {
        $problems++;
        is ($val, qq{'$number'}, $t);
    }

    $number = uc $float;
    $t = "$prefix (value=$number)";
    $val = -1;
    eval { $val = $dbh->quote($number, SQL_FLOAT); };
    if ($@ ne q{}) {
        $problems++;
        is ($@, q{}, "$t: error");
    }
    elsif ($val ne qq{'$number'}) {
        $problems++;
        is ($val, qq{'$number'}, $t);
    }
}

$prefix = 'Invalid float value fails when quoting with SQL_FLOAT';
for my $float ('3abc','123abc','','NaNum','-infinitee') {
    $count++;
    $t = "$prefix (value=$float)";
    $val = -1;
    eval { $val = $dbh->quote($float, SQL_FLOAT); };
    if ($@ !~ qr{quote_float: invalid input}) {
        $problems++;
        like ($@, qr{quote_float: invalid input}, $t);
    }
    elsif ($val != -1) {
        $problems++;
        is ($val, -1, $t);
    }
}

$t = "Quoting worked for type float, checked $count variants";
$problems ? fail ($t) : pass ($t);

$dbh->rollback();

## Test placeholders plus binding
$t='Bound placeholders enforce data types when not using server side prepares';
$dbh->trace(0);
$dbh->{pg_server_prepare} = 0;
$sth = $dbh->prepare('SELECT (1+?+?)::integer');
$sth->bind_param(1, 1, SQL_INTEGER);
eval {
    $sth->execute('10foo',20);
};
like ($@, qr{quote_integer: invalid input}, $t);

$t='Bound placeholders works when quoting text-based float values';
$sth = $dbh->prepare('SELECT ?::float');
$sth->bind_param(1, 1, SQL_FLOAT);
$sth->execute('infinity');
pass ($t);

$t='Bound placeholders fails when quoting invalid text-based float values';
eval { $sth->execute('infinity3'); };
like ($@, qr{quote_float: invalid input}, $t);

## Test quoting of the "name" type
$prefix = q{The 'name' data type does correct quoting};
$count = 0;
$problems = 0;
for my $word (qw/User user USER trigger Trigger user-user/) {
    $count++;
    $t = qq{$prefix for the word "$word"};
    my $got = $dbh->quote($word, { pg_type => PG_NAME });
    $expected = qq{"$word"};
    if ($got ne $expected) {
        $problems++;
        is ($got, $expected, $t);
    }
}

for my $word (qw/auser userz/) {
    $count++;
    $t = qq{$prefix for the word "$word"};
    my $got = $dbh->quote($word, { pg_type => PG_NAME });
    $expected = qq{$word};