Обсуждение: FW: Noticed a Bug with stored procedures
FW: Noticed a Bug with stored procedures
От
"Gudala, Sridhar (GE EntSol, Intelligent Platforms)"
Дата:
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