A bug in the parameter object in the. NET driver npgsql of postgresql

Recently, I migrated my project from sqlserver to the PostgreSQL database. When I called the database Stored Procedure (User-Defined Function), I found a strange problem. I always reported that the function could not be found.

First look at a pgsql stored procedure:

 

Create   Or   Replace   Function Updateattention (DM citext)
Returns Void As
$ Body $
Declare

Begin
Update ZB Set Gzd = Coalesce (Gzd, 0 ) + 1   Where ZB. DM = $ 1 ;
End ;
$ Body $
Language plpgsql volatile
Cost 100 ;
Alter   Function Updateattention (citext) Owner To Postgres;

In PostgreSQL, there is no difference between a function and a stored procedure. Here we call a function without a returned value a stored procedure. Maybe the table complaints are not accurate. I hope you can correct them.

The above defines a stored procedure updateattention. It has a custom type citext, which is used to replace the type in the string with a case-insensitive type. Its definition is as follows:

 

Create   Or   Replace   Function Citext ( Character )
Returns Citext As
' Rtrim1 '
Language internal immutable strict
Cost 1 ;
Alter   Function Citext ( Character ) Owner To Postgres;

 

The following describes how to call the updateattention stored procedure.Code:

 

// Obtain the Data Access Object of postgresql
Pwmis. dataprovider. Data. adohelper DB = Mydb. getdbhelperbyconnectionname ( " PostgreSQL " );
// Obtain the parameter object of postgresql
Idataparameter para = DB. getparameter ();
Para. parametername =   " @ DM " ;
Para. dbtype = Dbtype. ansistring;
Para. Value =   " Kf0355 " ;
DB. executenonquery ( " Updateattention " ,
System. Data. commandtype. storedprocedure,
New System. Data. idataparameter [] {para });

ProgramUse PDF. net (pwmis data development framework) Data Access Object adohelper for relevant data access operations, it uses the reflection factory mode, according to the system configuration instantiate a specific data volume class, the PostgreSQL data volume class is used here.

 

The following error occurs when you run the program:

Pdf. Net adohelper query error:
Database errormessage: Error: 42883 : FunctionUpdatefundattention (text) Does not exist
SQL: updatefundattention
Commandtype: storedprocedure
Parameters:
Parameter [ "@ Jjdm" ] = " Kf0355 " // Dbtype = String

The PDF. NET Framework has built-in log objects and exception objects, which can throw detailed error messages for you.

 

If the following method is used for calling, there is no problem:

DB. executenonquery ("Select * fromUpdateattention (@ DM)",
System. Data. commandtype. Text,
NewSystem. Data. idataparameter [] {para });

Bytes ------------------------------------------------------------------------------------

Although this method can be used as an alternative, it is awkward to use select * from to call the stored procedure and find the real cause of the problem.

 

This problem of "function... does not exist" is hard to find, and finally finds an article abroadArticleDiscuss similar issues:

Http://pgfoundry.org/forum/forum.php? Thread_id = 637 & forum_id = 519

Some people in this article said that it may be a type conversion problem for the parameter, but here I only convert the case of the parameter, there should be no such problems as int32 to int64.

 

ROGUE: only the. Net program code that calls the stored procedure can be checked one by one.

Para. dbtype=Dbtype. ansistring;

The program can run normally!

I have been wondering whether dbtype is a problem before, but when I put the mouse in the vs2010 editorParaDbtype = "{string}" is displayed }".

By default, the dbtype attribute value of the parameter object is

Dbtype. String

Isn't it

Dbtype. ansistring=Dbtype. String ??

The definitions are different. dbtype. ansistring indicates a variable-length string other than Unicode, and dbtype. String indicates a variable-length string of Unicode.

In general, ANSI encoding indicates the current system encoding, so I guess ansistring is gb2312 encoding on my machine, check the database encoding, it is in UTF-8 format, no wonder, PostgreSQL prompts me That the updatefundattention (text) function cannot be found. Note that the parameter of this function is notTextType, it should actually beCharacterType. PostgreSQL can define a function with the same name, but the function can have different parameter types, a bit like method overloading of C.

At this point, the problem seems to have been solved, but it is not complete:

 

Is there a bug in the smart prompt of vs2010?

For the first time, I thought this was incredible because I used to debug similar code in vs2008. net code to replace the parameter object with another database type parameter object:

// Obtain the Data Access Object of postgresql
Pwmis. dataprovider. Data. adohelper DB = Mydb. getdbhelperbyconnectionname ( " PostgreSQL " );
// Use the sqlserver parameter object
Idataparameter para =   New Sqlparameter ();
Para. parametername =   " @ DM " ;
Para. dbtype = Dbtype. ansistring;
Para. Value =   " Kf0355 " ;
DB. executenonquery ( " Updateattention " ,
System. Data. commandtype. storedprocedure,
New System. Data. idataparameter [] {para });

Then place the cursor on para. dbtype. The prompt is "{ansistring }";

Put the above Code in vs2008 for verification again, and the smart prompt is correct. It seems that it is not a bug of vs2010.

 

Therefore, the conclusion is as follows:

The dbtype attribute of the parameter object of the. NET data access driver of postgresql has a bug in viewing the result of setting it to ansistring!

PS: Although there is indeed such a bug in viewing properties, it seems that the program has done the correct processing, or my program cannot run and pass.

 

Postscript

PostgreSQL. the problem caused by the net data driver has plagued me for about two days. I have to post a post to describe this process. Currently, there are too few documents about PostgreSQL in China, write something for your reference.