On 16/10/2010 14:43, Martin Gainty wrote:
Eric
take a look at the the procedure declaration:
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
)
#now synchronise perls bind_param to the declared parameters of the procedure
$sth->bind_param_inout( 1, \$retval, 16, DBI::SQL_INTEGER);
# useless statement comment this out
$sth->bind_param_inout( 2, \$message, 400, DBI::SQL_VARCHAR); # should be
parameter 1
$sth->bind_param_inout( 3, \$new_id, 16, DBI::SQL_INTEGER); # should be
parameter 2
$sth->bind_param ( 4, 'Some name', DBI::SQL_VARCHAR);
#description should be 'name' parameter 3
$sth->bind_param ( 5, 'Some desc', DBI::SQL_VARCHAR);
#description should be 'url' should be parameter 4
$sth->bind_param ( 6, 'Some url', DBI::SQL_VARCHAR);
#description should be 'url' should be parameter 5
$sth->bind_param ( 7, undef, DBI::SQL_TIMESTAMP); #description
should be 'last_fetch" parameter 6
$sth->bind_param ( 8, 3600, DBI::SQL_INTEGER); #should
be parameter 7
$sth->bind_param ( 9, undef, DBI::SQL_TIMESTAMP);
#description should be 'next_fetch' parameter 8
$sth->bind_param (10, undef, DBI::SQL_VARCHAR);
#description should be 'enabled' parameter 9
$sth->bind_param (11, 'Some user', DBI::SQL_VARCHAR); #description
should be creator parameter 10
$sth->bind_param (12, '2010-10-15 20:39:33', DBI::SQL_TIMESTAMP);
#description should be 'create_date' parameter 11
please follow Martins advice
*Mit Freundlichen Gruben*
Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Martin,
I would have thought that would have errored beforehand if the
parameters were mismatched. The procedure in question is called like this:
$sth = $dbh->prepare('{? = call api_add_feed(?,?,?,?,?,?,?,?,?,?,?)}');
so in reality the current param 1 is an output parameter and the
procedure seems to return a value:
RETURN @retval
I'm spending way too much in Oracle at the moment and cannot remember if
MS SQL Server allows parameters to return a value without declaring it
(as you have to in Oracle) but I'd guess they do or it would not have
compiled.
BTW, Eric, trace level 15 (instead of 4) would have been slightly more
informative.
Martin
Date: Sat, 16 Oct 2010 11:03:08 +0100
From: martin.ev...@easysoft.com
To: ero...@barrack.com
CC: dbi-users@perl.org
Subject: 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
