> 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
> UPDATE EmailLoginUsers SET LoginID = 'abc(at)sample(dot)com Where
> UserCheckedMailID = $1;
> 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;
> 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
GET DIAGNOSTICS myRowCountVariable = ROW COUNT;
Senior Database Dude
The Enterprise Postgres Company
Mobile: (620) EDB-NERD
In response to
pgsql-bugs by date
|Next:||From: Craig Ringer||Date: 2010-03-24 04:06:19|
|Subject: Re: Noticed a Bug with stored procedures|
|Previous:||From: Tom Lane||Date: 2010-03-22 13:59:25|
|Subject: Re: 9.0_alpha4 Planner Bug |