Skip site navigation (1) Skip section navigation (2)

Re: FW: Noticed a Bug with stored procedures

From: Korry Douglas <korry(dot)douglas(at)enterprisedb(dot)com>
To: "Gudala, Sridhar (GE EntSol, Intelligent Platforms)" <Sridhar(dot)Gudala(at)ge(dot)com>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: FW: Noticed a Bug with stored procedures
Date: 2010-03-22 14:13:38
Message-ID: 17350FE3-A7AE-41CA-9F54-D3358F5575A0@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
> 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(at)sample(dot)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


In response to

pgsql-bugs by date

Next:From: Craig RingerDate: 2010-03-24 04:06:19
Subject: Re: Noticed a Bug with stored procedures
Previous:From: Tom LaneDate: 2010-03-22 13:59:25
Subject: Re: 9.0_alpha4 Planner Bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group