Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

DBI with SQLite vs DBIX::Class search failing

by ghenry (Vicar)
on Oct 23, 2019 at 13:01 UTC ( #11107892=perlquestion: print w/replies, xml ) Need Help??

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.' );

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

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)

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

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;

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

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;

Thanks.

Replies are listed 'Best First'.
Re: DBI with SQLite vs DBIX::Class search failing
by 1nickt (Abbot) 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.
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

    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?

      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

        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
        I'm going to switch to Test::Postgresql58 to match our prod Dbs. Thanks all!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11107892]
Approved by Fletch
Front-paged by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2020-05-30 12:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If programming languages were movie genres, Perl would be:















    Results (171 votes). Check out past polls.

    Notices?