Re: There's an error, but $sth->execute returns -1

  On 15/10/2010 22:20, Roode, Eric wrote:
> Hello all,
>
>
>
> DBI docs says that statement handle method execute() returns undef on
> error.  I have found a situation where that does not hold.  Was hoping
> someone could help me out, maybe there's a workaround.
>
>
>
> First, I'm running ActiveState Perl 5.10.0 on Windows 7, connecting to a
> SQL Server 2000 database on a different machine.  I have DBI version
> 1.613, and DBD::ODBC version 1.24.
>
>
>
> In a nutshell, here is what happens.  I connect to the database, using
> the ODBC driver, and setting RaiseError to 1 and PrintError to 0.  I
> prepare a statement which calls a procedure.  I bind parameters to the
> statement (some input, some output, various types), I call execute().
>
>
>
> There's a problem with one of the parameters; because of that, the
> stored procedure tries to insert a null value into a column that does
> not accept nulls.  So SQL Server gives an error ("Cannot insert the
> value NULL into column 'enabled'"), and the DBI system stores this
> string in errstr.  However, the execute() method returns -1, which is
> what it would return on success.  Also, no error is thrown (I have the
> $sth->execute call wrapped in an eval, and $@ is empty afterward).
>
>
>
> You can see the definition of the table and the stored procedure at
> http://nopaste.gamedev.pl/?id=8272.
>
> The Perl code that demonstrates the problem is at
> http://nopaste.gamedev.pl/?id=8273.
>
> And the DBD trace output is at http://nopaste.gamedev.pl/?id=8274.
>
>
>
> I hope someone can shed some light on what's going on here.  Should I
> check errstr and ignore the return value? Thanks in advance.
>
> Eric
>
>
>
(Tim Bunce, if you see this could you clarify what dbd_st_execute is 
supposed to return as I could not find the full details in DBI::DBD. It 
would appear DBD::ODBC returns -2 for error, -1 for rowcount not known 
and a positive number for rows affected).

The status returned was SQL_SUCCESS_WITH_INFO:

!!dbd_error2(err_rc=1, what=st_execute/SQLExecute, 
handles=(3194c98,2b5a848,30d4cd0)

that err_rc=1 which is not an error.

If SQLRowCount returns -1 then dbd_st_execute will return -1, that may 
be a bug but really the code should not have got to this point anyway. 
The are differences between what DBI documents for the execute method 
and what DBD::ODBC returns from the dbd_st_execute and I cannot see for 
instance the -2 (for errors) documented in DBI::DBD so I'm not sure if 
this is right or wrong (Tim?).

As far as I can see this looks like a bug in your driver (but I'll try 
and reproduce here). Surely failing to insert into a column should be an 
error not success with info (an example of the latter is say an insert 
which worked but truncated your data). What ODBC Driver manager and 
driver versions are you using - you can find these from the 
Administrative tools, data sources or in windows 7 by searching for data 
sources in the control panel top right.

Martin