PostgreSQL tables, function names are strictly case-sensitive, so when used without notice that the case of the problem is easy to find the function name error, but in the last two days we found that if the function parameters use a custom data type, this problem also occurs.
Problem Description:The following example tests the code:
Pwmis. DataProvider.Data.AdoHelper db = Mydb.getdbhelperbyconnectionname ("PostgreSQL");
Idataparameter para = db. GetParameter ();
Para. ParameterName = "@jjdm";
Para. DbType = dbtype.ansistring;
Para. Value = "KF0355";
int count= db. ExecuteNonQuery ("Updatefundattention",
System.Data.CommandType.StoredProcedure,
New system.data.idataparameter[] {para});
The following error occurred while running the stored procedure: DataBase errormessage:error: 42883:
function updatefundattention (text)
does Not exist
Sql:updatefundattention
Commandtype:storedprocedure
Parameters:
Parameter["@jjdm"] = "KF0355" //dbtype=string< /c18> in fact, PostgreSQL's function updatefundattention parameter type is not text, but a custom type Citex, the following is the function definition:
CREATE OR REPLACE FUNCTION updatefundattention (JJDM citext)
RETURNS void as
$BODY $
DECLARE
BEGIN
Update JJZB set GZD=COALESCE (gzd,0) +1 where jjzb.jjdm=$1;
--return 1;
END;
$BODY $
LANGUAGE Plpgsql VOLATILE
Cost 100;
ALTER FUNCTION updatefundattention (citext) OWNER to Postgres;
Yesterday parsing possible PostgreSQL character parameters cannot use the ansistring parameter type, you need to use the string type, but today the test finds para. DbType = dbtype.string; The problem is still unresolved. Re-establish a test function updatefundattention, except that the parameter type is varchar:
CREATE OR REPLACE FUNCTION updatefundattention2 (jjdm character varying)
RETURNS void as
$BODY $
DECLARE
BEGIN
Update JJZB set GZD=COALESCE (gzd,0) +1 where jjzb.jjdm=$1;
--return 1;
END;
$BODY $
LANGUAGE Plpgsql VOLATILE
Cost 100;
ALTER FUNCTION Updatefundattention2 (character varying) OWNER to postgres;
Run the test program, regardless of the para. DbType = dbtype.ansistring; or a para. DbType = dbtype.string; The call function Updatefundattention2 can be passed, and therefore the conclusion:
the current custom Citext type. NET program cannot set the correct dbtype, so there will be no function errors found!
Problem Impact:In wft, all use. NET program calls the code of the PostgreSQL stored procedure, which is affected if the parameters of the stored procedure use a custom type (for example, Citex).
Solution:A, recommendations
do not use the custom type in the parameters of the PostgreSQL function, if you want to convert the arguments to case, it is recommended to use another pgsql variable in the function body, the SQL statement that executes the query in the function uses this new variable instead of directly using the function parameter; b, modify the SQL statement in the SQL-MAP, for example<select commandname= "Addguanzhudu" method= "commandtype=" StoredProcedure "description=" Increase the focus "ResultClass=" ValueType "><! [cdata[
Updatefundattention
#jjdm: string#
]]></select>
Modify the following method:
<select commandname= "Addguanzhudu" method= "" commandtype= "Text" description= "Increase focus" resultclass= "ValueType" > <! [cdata[
SELECT * FROM Updatefundattention (#jjdm: string#)
]]></select>However, this modification will result in SQL Server and PostgreSQL
sql-map statements are not the same, increase the maintenance of the program,The ideal way is to sql-map statements as much as possible.
Problem calling PostgreSQL stored procedure, unable to find function name
