Thread: FW: Noticed a Bug with stored procedures

Hi,
=20
Please ignore my previous mail, there were few spelling mistakes. Now
you can go through below mail and respond to it.

When I send update query command from front end then PostGreSql is
responding with number of rows affected. But when I send same update
query which is embedded in stored procedure (as listed below) then
PostGreSql respond with a value of -1.
=20
=20
CREATE  OR REPLACE FUNCTION samplepro5(deptid int)
RETURNS void AS
$BODY$
UPDATE EmailLoginUsers SET LoginID =3D 'abc@sample.com Where
UserCheckedMailID =3D $1;
$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
=20
=20
=46rom front end, I have called above listed stored procedure by using
below listed code:
=20
objNpgSQLCommand.CommandText =3D "samplepro5";

objNpgSQLCommand.CommandType =3D CommandType.StoredProcedure;

NpgsqlParameter objParameter;

objparameter.ParameterName =3D "deptid";

objparameter.NpgsqlDbType =3D NpgsqlTypes.NpgsqlDbType.Integer;

objparameter.Value =3D 1;

objparameter.Direction =3D ParameterDirection.Input;

objNpgSQLCommand.Parameters.Add(objParameter);

int numberOfAffectedRows =3D objNpgSQLCommand.ExecuteNonQuery();

After excuting above code, the value of numberOfAffectedRows  was -1 but
database was updated with one record.

=20

Please tell me what's wrong.

=20

Thanks,

Sridhar G

=20

> Please ignore my previous mail, there were few spelling mistakes.
> Now you can go through below mail and respond to it.
>
> When I send update query command from front end then PostGreSql is
> responding with number of rows affected. But when I send same update
> query which is embedded in stored procedure (as listed below) then
> PostGreSql respond with a value of -1.
>
>
> CREATE  OR REPLACE FUNCTION samplepro5(deptid int)
> RETURNS void AS
> $BODY$
> UPDATE EmailLoginUsers SET LoginID = 'abc@sample.com Where
> UserCheckedMailID = $1;
> $BODY$
> LANGUAGE 'sql' VOLATILE
> COST 100;
>
>
> From front end, I have called above listed stored procedure by using
> below listed code:
>
> objNpgSQLCommand.CommandText = "samplepro5";
>
> objNpgSQLCommand.CommandType = CommandType.StoredProcedure;
>
> NpgsqlParameter objParameter;
>
> objparameter.ParameterName = "deptid";
>
> objparameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
>
> objparameter.Value = 1;
>
> objparameter.Direction = ParameterDirection.Input;
>
> objNpgSQLCommand.Parameters.Add(objParameter);
>
>
> int numberOfAffectedRows = objNpgSQLCommand.ExecuteNonQuery();
>
> After excuting above code, the value of numberOfAffectedRows  was -1
> but database was updated with one record.
>
>
> Please tell me what's wrong.
>

The reason that you don't see that number of affected rows is that
your client application is *not* executing the UPDATE statement; your
client application is executing a SELECT statement (which calls the
samplepro5() function, which returns void).  What would you expect to
see if, for example, samplepro5() contained two or three UPDATE
statements?  Which "numberOfAffectedRows" would you expect to see?

If you want to capture the number of rows affected by the UPDATE
statement, modify samplepro5() so that it returns the row count
(instead of returning void).  You can get the row count with the GET
DIAGNOSTICS statement:

     UPDATE ...;
     GET DIAGNOSTICS myRowCountVariable = ROW COUNT;
     RETURN myRowCountVariable;


                -- Korry


-----------------------------------------------------------------------
Korry Douglas
Senior Database Dude
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: (804)241-4301
Mobile: (620) EDB-NERD