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";
mar...@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)"
mar...@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
