DBI with SQLite vs DBIX::Class search failing

ghenry has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I'm bashing my head around why this isn't working. Here are some tests:

#!/usr/bin/env perl
use strict;
use warnings;

use Data::Dumper;
use Test::More tests => 2;

use_ok 'API::Schema';

my $dsn           = "dbi:SQLite:api.db";
my $apidb         = API::Schema->connect($dsn);
my $ddi_available = $apidb->resultset('CompleteNumberE164View')->find(
    {
        ddi     => 441224900999,
        is_used => 0
    }
);
note 'DDI found: ' . $ddi_available->ddi;
ok( $ddi_available->ddi eq '441224900999', 'Found available DDI.' );
[download]

Running it gives:

DBIC_TRACE=1 prove -l t/47-find_unused_number.t 
t/47-find_unused_number.t .. 1/2 SELECT me.ddi, me.area_code, me.local
+_number, me.number_quality, me.is_used FROM complete_number_e164 me W
+HERE ( ( me.ddi = ? AND me.is_used = ? ) ): '441224900999', '0'
Can't call method "ddi" on an undefined value at t/47-find_unused_numb
+er.t line 18, <DATA> line 1.
# Looks like your test exited with 2 just after 1.
t/47-find_unused_number.t .. Dubious, test returned 2 (wstat 512, 0x20
+0)
Failed 1/2 subtests 

Test Summary Report
-------------------
t/47-find_unused_number.t (Wstat: 512 Tests: 1 Failed: 0)
  Non-zero exit status: 2
  Parse errors: Bad plan.  You planned 2 tests but ran 1.
Files=1, Tests=1,  1 wallclock secs ( 0.02 usr  0.01 sys +  0.62 cusr 
+ 0.03 csys =  0.68 CPU)
Result: FAIL
[download]

with DBI_TRACE=1

DBI_TRACE=1 prove -l t/47-find_unused_number.t 
t/47-find_unused_number.t .. 1/2     DBI 1.630-ithread default trace l
+evel set to 0x0/1 (pid 6958 pi eda010) at DBI.pm line 288 via DBI.pm 
+line 1516
    -> DBI->connect(dbi:SQLite:api.db, , ****, HASH(0x2e04be0))
    -> DBI->install_driver(SQLite) for linux perl=5.014002 pid=6958 ru
+id=500 euid=500
       install_driver: DBD::SQLite version 1.46 loaded from /usr/local
+/lib/perl/5.14.2/DBD/SQLite.pm
    <- install_driver= DBI::dr=HASH(0x3158860)
    <- default_user(undef, undef, ...)= ( undef undef ) [2 items] at D
+BI.pm line 659
    <- connect('api.db', undef, ...)= ( DBI::db=HASH(0x31590b8) ) [1 i
+tems] at DBI.pm line 671
    <- STORE('RaiseError', 1)= ( 1 ) [1 items] at DBI.pm line 723
    <- STORE('PrintError', 0)= ( 1 ) [1 items] at DBI.pm line 723
    <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 723
    <- STORE('ShowErrorStatement', 1)= ( 1 ) [1 items] at DBI.pm line 
+726
    <- STORE('Username', undef)= ( 1 ) [1 items] at DBI.pm line 726
    <- connected('dbi:SQLite:api.db', undef, ...)= ( undef ) [1 items]
+ at DBI.pm line 733
    <- connect= DBI::db=HASH(0x31590b8)
    <- STORE('dbi_connect_closure', CODE(0x31586c8))= ( 1 ) [1 items] 
+at DBI.pm line 742
    <- FETCH('Active')= ( 1 ) [1 items] at DBI.pm line 1522
    <- FETCH('HandleError')= ( undef ) [1 items] at DBI.pm line 1533
    <- FETCH('RaiseError')= ( 1 ) [1 items] at DBI.pm line 1540
    <- STORE('HandleError', __DBIC__DBH__ERROR__HANDLER__=CODE(0x31b80
+c0))= ( 1 ) [1 items] at DBI.pm line 1508
    <- FETCH('AutoCommit')= ( 1 ) [1 items] at DBI.pm line 1561
    <- prepare_cached('SELECT me.ddi, me.area_code, me.local_number, m
+e.number_quality, me.is_used FROM complete_number_e164 me WHERE ( ( m
+e.ddi = ? AND me.is_used = ? ) )', HASH(0x31c5130), ...)= ( DBI::st=H
+ASH(0x31c8ee8) ) [1 items] at DBI.pm line 1850
    <- bind_param(1, 441224900999, ...)= ( 1 ) [1 items] at DBI.pm lin
+e 1891
    <- bind_param(2, 0, ...)= ( 1 ) [1 items] at DBI.pm line 1891
    <- execute= ( '0E0' ) [1 items] at DBI.pm line 1836
    <- fetchrow_array= ( ) [0 items] row-1 at DBI.pm line 2587
    <- finish= ( 1 ) [1 items] at DBI.pm line 2593
Can't call method "ddi" on an undefined value at t/47-find_unused_numb
+er.t line 18, <DATA> line 1.
sqlite trace: Finalizing statement: 31d0ef8 at dbdimp.c line 1175
sqlite trace: Removing statement from list: 31d0ef8 at dbdimp.c line 1
+188
    <- DESTROY(DBI::st=HASH(0x31c5238))= ( undef ) [1 items] at DBI.pm
+ line 261
sqlite trace: Closing DB at dbdimp.c line 510
sqlite trace: rc = 0 at dbdimp.c line 512
    <- DESTROY(DBI::db=HASH(0x3159010))= ( undef ) [1 items] at DBI.pm
+ line 261
    <- disconnect_all= ( '' ) [1 items] at DBI.pm line 750
# Looks like your test exited with 2 just after 1.
!   <- DESTROY(DBI::dr=HASH(0x3158860))= ( undef ) [1 items] during gl
+obal destruction
t/47-find_unused_number.t .. Dubious, test returned 2 (wstat 512, 0x20
+0)
[download]

If I run that search by hand, it is found:

sqlite3 api.db 
sqlite> SELECT me.ddi, me.area_code, me.local_number, me.number_qualit
+y, me.is_used FROM complete_number_e164 me WHERE ( ( me.ddi = '441224
+900999' AND me.is_used = '0' ) );
441224900999|1224|900999|gold|0
[download]

I've written another test without DBIx::Class, which passes no problem, but I can't see any difference with trace on:

#t!/usr/bin/env perl
use strict;
use warnings;

use Test::More tests => 1;
use DBI;

my $dsn = "dbi:SQLite:api.db";
my $dbh = DBI->connect($dsn);

my $sth = $dbh->prepare(
'SELECT me.ddi, me.area_code, me.local_number, me.number_quality, me.i
+s_used FROM complete_number_e164 me
WHERE ( ( me.ddi = ? AND me.is_used = ? ) )'
);
$sth->bind_param(1, 441224900999);
$sth->bind_param(2, 0);
$sth->execute();

my $ddi_available;
while ( my @row = $sth->fetchrow_array ) {
    $ddi_available = $row[0];
}

note 'DDI found: ' . $ddi_available;
ok( $ddi_available eq '441224900999', 'Found available DDI.' );
$dbh->disconnect;
[download]

With the trace on, I can't see any difference with the bind params. Why does this not fail?

DBI_TRACE=1 prove -l t/48-dbi_find_unused_number.t 
t/48-dbi_find_unused_number.t ..     DBI 1.630-ithread default trace l
+evel set to 0x0/1 (pid 7128 pi 1d56010) at DBI.pm line 288 via 48-dbi
+_find_unused_number.t line 6
    -> DBI->connect(dbi:SQLite:api.db, , ****)
    -> DBI->install_driver(SQLite) for linux perl=5.014002 pid=7128 ru
+id=500 euid=500
       install_driver: DBD::SQLite version 1.46 loaded from /usr/local
+/lib/perl/5.14.2/DBD/SQLite.pm
    <- install_driver= DBI::dr=HASH(0x248ac28)
    <- default_user(undef, undef, ...)= ( undef undef ) [2 items] at D
+BI.pm line 659
    <- connect('api.db', undef, ...)= ( DBI::db=HASH(0x248b480) ) [1 i
+tems] at DBI.pm line 671
    <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 723
    <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 723
    <- STORE('Username', undef)= ( 1 ) [1 items] at DBI.pm line 726
    <- connected('dbi:SQLite:api.db')= ( undef ) [1 items] at DBI.pm l
+ine 733
    <- connect= DBI::db=HASH(0x248b480)
    <- STORE('dbi_connect_closure', CODE(0x248aa90))= ( 1 ) [1 items] 
+at DBI.pm line 742
    <- prepare('SELECT me.ddi, me.area_code, me.local_number, me.numbe
+r_quality, me.is_used FROM complete_number_e164 me
WHERE ( ( me.ddi = ? AND me.is_used = ? ) )')= ( DBI::st=HASH(0x248b7e
+0) ) [1 items] at 48-dbi_find_unused_number.t line 12
    <- bind_param(1, 441224900999)= ( 1 ) [1 items] at 48-dbi_find_unu
+sed_number.t line 16
    <- bind_param(2, 0)= ( 1 ) [1 items] at 48-dbi_find_unused_number.
+t line 17
    <- execute= ( '0E0' ) [1 items] at 48-dbi_find_unused_number.t lin
+e 18
    <- fetchrow_array= ( '441224900999' 1224 900999 'gold' 0 ) [5 item
+s] row1 at 48-dbi_find_unused_number.t line 21
    <- fetchrow_array= ( ) [0 items] row1 at 48-dbi_find_unused_number
+.t line 21
t/48-dbi_find_unused_number.t .. 1/1 sqlite trace: Closing DB at dbdim
+p.c line 510
sqlite trace: rc = 5 at dbdimp.c line 512
sqlite trace: Finalizing statement (24f07a8) at dbdimp.c line 516
sqlite trace: Trying to close DB again at dbdimp.c line 522
    <- disconnect= ( 1 ) [1 items] at 48-dbi_find_unused_number.t line
+ 27
    <- DESTROY(DBI::st=HASH(0x248b5a0))= ( undef ) [1 items]
    <- DESTROY(DBI::db=HASH(0x248b3d8))= ( undef ) [1 items]
    <- disconnect_all= ( '' ) [1 items] at DBI.pm line 750
!   <- DESTROY(DBI::dr=HASH(0x248ac28))= ( undef ) [1 items] during gl
+obal destruction
t/48-dbi_find_unused_number.t .. ok   
All tests successful.
Files=1, Tests=1,  1 wallclock secs ( 0.03 usr  0.00 sys +  0.07 cusr 
+ 0.02 csys =  0.12 CPU)
Result: PASS
[download]

The DBIx::Class schema is like so:

package API::Schema::Result::CompleteNumberE164View;

use utf8;
use strict;
use warnings;

use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
__PACKAGE__->table('complete_number_e164');
__PACKAGE__->result_source_instance->view_definition(
"SELECT '44' || area_code || local_number AS ddi, area_code, local_num
+ber, number_quality, is_used 
        FROM assigned_number;"
);

__PACKAGE__->add_columns(
    ddi => {
        data_type     => 'INTEGER',
        default_value => undef,
        is_nullable   => 0,
    },
    area_code => {
        data_type     => 'INTEGER',
        default_value => undef,
        is_nullable   => 0,
    },
    local_number => {
        data_type     => 'INTEGER',
        default_value => undef,
        is_nullable   => 0,
    },
    number_quality => {
        data_type     => 'TEXT',
        default_value => undef,
        is_nullable   => 0,
        size          => 255,
    },
    is_used => {
        data_type     => 'BOOLEAN',
        default_value => 'false',
        is_nullable   => 0,
    },
);
__PACKAGE__->belongs_to(
    'area_code_assigned_number',
    'API::Schema::Result::Area',
    { 'foreign.area_code' => 'self.area_code' }
);
__PACKAGE__->belongs_to(
    'number_quality_assigned_number',
    'API::Schema::Result::NumberQuality',
    { 'foreign.number_quality' => 'self.number_quality' }
);

1;
[download]

Thanks.

Comment on DBI with SQLite vs DBIX::Class search failing Select or Download Code

Replies are listed 'Best First'.
Re: DBI with SQLite vs DBIX::Class search failing by 1nickt (Canon) on Oct 23, 2019 at 15:53 UTC
Hi, I would double-check your use of find() and consider using the optional `key` attribute, or perhaps switching to `single()`. Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re: DBI with SQLite vs DBIX::Class search failing by ghenry (Vicar) on Oct 23, 2019 at 15:18 UTC
If I switch the dsn back to our Postgres db and not the test sqlite db, it works, but it's the same SQL generated: cat t/47-find_unused_number.t #!/usr/bin/env perl use strict; use warnings; use Data::Dumper; use Test::More tests => 2; use_ok 'API::Schema'; my $dsn = "dbi:Pg:dbname=api;host=127.0.0.1;port=5432"; my $apidb = API::Schema->connect( $dsn, 'postgres' ); my $ddi_available = $apidb->resultset('CompleteNumberE164View')->find( { ddi => 441224900999, is_used => 0 } ); note 'DDI found: ' . $ddi_available->ddi; ok( $ddi_available->ddi eq '441224900999', 'Found available DDI.' ); prove -l t/47-find_unused_number.t t/47-find_unused_number.t .. ok All tests successful. Files=1, Tests=2, 1 wallclock secs ( 0.02 usr 0.00 sys + 0.55 cusr + 0.06 csys = 0.63 CPU) Result: PASS [download] The original schema was generated for Postgres, but populated via our sql fixtures for sqlite. The search goes work, so must be DBIx::Class related?	[reply] [d/l]
Re^2: DBI with SQLite vs DBIX::Class search failing by poj (Abbot) on Oct 23, 2019 at 15:55 UTC
Your code works fine for me but I was able to recreate your fault by creating ddi as a text field in api.db and entering the value with a trailing space '441224900999 '. Maybe check your SQLite file definitions match the Schema file HTH poj	[reply]
Re^3: DBI with SQLite vs DBIX::Class search failing by ghenry (Vicar) on Oct 23, 2019 at 16:13 UTC
In #dbix-class chat it was pointed out: `<ilmari> the DBIC one ins showing bind_param(1, 441224900999, ...), wh +ile the plain DBI one shows bind_param(1, 441224900999) <ilmari> DBIC binds integer columns as SQL_BIGINT` [download] I'm going to switch to Test::Postgresql58 to match our prod Dbs. Thanks all!	[reply] [d/l]


Perl-Sensitive Sunglasses
	PerlMonks