Re: There's an error, but $sth->execute returns -1
[email protected] ("Martin J. Evans")
| Newsgroups | perl.dbi.users |
|---|---|
| Message-ID | <[email protected]> |
On 16/10/2010 11:03, Martin J. Evans wrote:
> 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
>
When I run your code with a different driver to MS SQL Server it works
as I'd expect:
use DBI;
use strict;
use warnings;
my $h = DBI->connect('dbi:ODBC:xx', 'xx', 'xx);
eval {$h->do(q/drop table feeds/);};
eval {$h->do(q/drop procedure api_add_feed/);};
my $table=<<'EOT';
CREATE TABLE feeds
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(400) NOT NULL,
descr VARCHAR(4000) NOT NULL,
url VARCHAR(800) NOT NULL,
last_fetch DATETIME,
min_interval INT,
next_fetch DATETIME,
enabled CHAR(1) NOT NULL DEFAULT 'N',
creator VARCHAR(100),
create_date DATETIME NOT NULL,
lastup DATETIME NOT NULL,
CONSTRAINT feed_url_uniq UNIQUE (url),
CONSTRAINT feed_name_uniq UNIQUE (name)
)
EOT
$h->do($table);
my $proc=<<'EOT';
CREATE PROCEDURE api_add_feed (@message VARCHAR(2000) OUTPUT,
@new_id INT OUTPUT,
@name VARCHAR(400),
@descr VARCHAR(4000),
@url VARCHAR(400),
@last_fetch DATETIME,
@min_interval INT,
@next_fetch DATETIME,
@enabled VARCHAR(1),
@creator VARCHAR(50),
@create_date DATETIME
)
AS
DECLARE @proc VARCHAR(50), @err INT, @loc INT, @ctxt VARCHAR(100),
@retval INT
SELECT @proc='add_feed', @err=0, @retval=0, @message='No status yet.'
INSERT INTO feeds
(name, descr, url, last_fetch, min_interval, next_fetch, enabled,
creator, create_date, lastup)
VALUES
(@name, @descr, @url, @last_fetch, @min_interval, @next_fetch,
@enabled,
@creator, @create_date, GETDATE())
SELECT @err=@@ERROR, @loc=1, @ctxt='during insert into [feeds]',
@new_id=SCOPE_IDENTITY()
IF @err = 0
BEGIN
SET @retval = 0
SET @message = 'Success. New feed id is ' + convert(varchar(6),
@new_id) + '.'
END
ELSE
BEGIN
SET @message = 'Error in ' + @proc
+ ' at ' + CONVERT(varchar(6), @loc)
+ ' ('
+ 'num ' + CONVERT(varchar(6), @err)
+ ' / '
+ 'context ' + @ctxt
+ ')'
SET @new_id = 0
SET @retval = -1
END
RETURN @retval
EOT
$h->do($proc);
my $sth = $h->prepare('{? = call api_add_feed(?,?,?,?,?,?,?,?,?,?,?)}');
#$sth->trace($sth->parse_trace_flags('SQL|odbcconnection'),
'trace-cxn.out');
#$sth->trace($sth->parse_trace_flags('4|odbcunicode'),
'trace-cxn.out');
my ($retval, $message, $new_id);
$sth->bind_param_inout( 1, \$retval, 16, DBI::SQL_INTEGER);
$sth->bind_param_inout( 2, \$message, 400, DBI::SQL_VARCHAR);
$sth->bind_param_inout( 3, \$new_id, 16, DBI::SQL_INTEGER);
$sth->bind_param ( 4, 'Some name', DBI::SQL_VARCHAR);
$sth->bind_param ( 5, 'Some desc', DBI::SQL_VARCHAR);
$sth->bind_param ( 6, 'Some url', DBI::SQL_VARCHAR);
$sth->bind_param ( 7, undef, DBI::SQL_TIMESTAMP);
$sth->bind_param ( 8, 3600, DBI::SQL_INTEGER);
$sth->bind_param ( 9, undef, DBI::SQL_TIMESTAMP);
$sth->bind_param (10, undef, DBI::SQL_VARCHAR); # Bad
argument
$sth->bind_param (11, 'Some user', DBI::SQL_VARCHAR);
$sth->bind_param (12, '2010-10-15 20:39:33', DBI::SQL_TIMESTAMP);
my $exec_retval;
eval
{
$exec_retval = $sth->execute;
};
if ($@)
{
print qq{\$@ is "$@"\n----------------\n};
}
print "Execute() returned $exec_retval.\n";
print 'errstr is"', $sth->errstr, '"', "\n";
martin@bragi:~/svn/dbd-odbc/trunk/rt_data$ perl success_with_info.pl
DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server
Driver][SQL Server
]Cannot insert the value NULL into column 'enabled', table
'master.dbo.feeds'; c
olumn does not allow nulls. INSERT fails. (SQL-23000) [state was 23000
now 01000
]
[unixODBC][Easysoft][SQL Server Driver][SQL Server]The statement has
been termin
ated. (SQL-01000) at success_with_info.pl line 103.
Use of uninitialized value $exec_retval in concatenation (.) or string
at succes
s_with_info.pl line 110.
Execute() returned .
errstr is"[unixODBC][Easysoft][SQL Server Driver][SQL Server]Cannot
insert the v
alue NULL into column 'enabled', table 'master.dbo.feeds'; column does
not allow
nulls. INSERT fails. (SQL-23000) [state was 23000 now 01000]
[unixODBC][Easysoft][SQL Server Driver][SQL Server]The statement has
been termin
ated. (SQL-01000)"
martin@bragi:~/svn/dbd-odbc/trunk/rt_data$
Which as far as I'm concerned is more evidence that your driver is
broken. Please send me the driver name, version etc you are using. At
the very least I may add a note that it is broken as there is little I
might be able to do to work around this.
Martin