http://www.perlmonks.org?node_id=996263

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

using ActivePerl
v5.10.0 built for MSWin32-x86-multi-thread
Executing following sql produces no error from DBI
it completely ignores division by zero.
create table #t (
v int not null
) insert #t values (1)
insert #t values (10)
insert #t values (12)
insert #t values (0)
select 10/v from #t

Replies are listed 'Best First'.
Re: DBI ignoring devide by zero
by erix (Prior) on Sep 28, 2012 at 18:39 UTC

    It's the DBMS (the database backend) that calculates (or effects) results of SQL statements, not DBI.

    What DBMS are you using, and what does its errorlog say when you do something that it does not allow?

    (The sql you use there is not valid (no semi-colons terminating the statements, invalid insert-syntax), so perhaps it's simply not executed; do you catch errors / follow the database log? )

    ( update: FWIW, postgres (9.3devel) gives the result you expect. Using commandline client psql: 

    
echo "
create table t (v int not null);
insert into t values (1) ;
insert into t values (10) ;
insert into t values (12) ;
insert into t values (0) ;
select 10/v from t;
" | psql -a          #  -a => echo all input from script

-- yields:

create table t (v int not null);
CREATE TABLE
insert into t values (1) ;
INSERT 0 1
insert into t values (10) ;
INSERT 0 1
insert into t values (12) ;
INSERT 0 1
insert into t values (0) ;
INSERT 0 1
select 10/v from t;
ERROR:  division by zero

    [download]

    )

[reply]
[d/l]
Re: DBI ignoring devide by zero
by runrig (Abbot) on Sep 28, 2012 at 18:48 UTC
    Did you feed that sql script to a command line query tool? What does that query tool do? I fed it to Sybase's isql, and did not get an error there either. It's not DBI, it's the database ignoring the error. In general, do not execute more than one SQL statement at a time. If you do them one at a time, you do get an error.
[reply]
      its not DBMS for sure
      i tried same sql code with C++ and ODBC and i get error message
      i tried it with command line osql
      and i get Msg 8134, Level 16, State 1, Server FIENYSQL, Line 6 Divide by zero error encountered.
[reply]

        Do you check for errors?

        When creating the database connection, pass the { RaiseError => 1 } attribute or do manual checks for errors whenever you run an SQL statement.

[reply]
[d/l]
Re: DBI ignoring devide by zero
by mpeppler (Vicar) on Oct 05, 2012 at 15:15 UTC
    Which database server?

    If this is Sybase 12.5 or earlier, the error is unfortunately at severity 10 - if you are using DBD::Sybase then errors with severity 10 are ignored.... bit of a bug, that, but it's been that way for almost 15 years :-)

    Michael

[reply]

Back to Seekers of Perl Wisdom