. Net Access to the PostgreSQL database. "function name not found" problem tracking

Document directory

• Supported Data Types
•  

PostgreSQL is a widely used free and open source database. Compared with MySQL, PostgreSQL is more suitable for complicated enterprise computing tasks. MySQL is more widely used in the Internet field, postgreSQL supports the most data types, including array and IP address types. You can use C, SQL, PL/pgsql, phython, and other methods to write powerful custom functions, therefore, it is particularly suitable for handling complex computing problems. If you want to migrate SQL Server databases to other types of databases, PostgreSQL is a good choice.

Although PostgreSQL is widely used, there are too few materials in China. We encountered many problems during database migration, such as my previous article PostgreSQL. net driver npgsql parameter object in a bug about the "function name not found" problem, it is more difficult to solve, you can use"Tracking. This article further explores this issue.
 

1. Problem Review:
As mentioned in the previous article, there is a PostgreSQL function updateattention, which has a custom function parameter. below is the function header:

 

Create or replace function updateattention (DM citext)
Returns void
$ Body $
-- Function body

 

The type of the parameter DM is citex, a custom data type, which is used as the type of function parameters or variables. It can be case insensitive during data query, it is defined:

 

Create or replace function citext (character)
Returns citext
'Rtrim1'
Language internal immutable strict
Cost 1;
Alter function citext (character) owner to S;

 

The following code calls the updateattention stored procedure using C:

 

// 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 });

The program uses 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. For more information, see "pdf. Net SQL logs"

 

2. Problem focus

At first, I thought it was a case-insensitive function name problem. I checked it carefully and found that there was no problem. Then I tried to check the code carefully.

Set 6th lines of code in the above program

Para. dbtype =Dbtype. ansistring;

Comment out and the program runs successfully. If you suspect that the parameter type cannot be set to ansistring, set it to the following method:

Para. dbtype =Dbtype. String;

If the program still fails to run, the same error is thrown. This error can be passed only after the code line is commented out, so yesterday I wrote the article at the beginning of this article (from PostgreSQL. net driver npgsql parameter object bug ).

 

Today, I will focus again on the function parameters of the error message:

Updatefundattention (text)

Is the. Net program type corresponding to PostgreSQL's data type text neither string nor ansistring?

Search again, in the http://npgsql.projects.postgresql.org/docs/manual/UserManual.html to find a data type table:

 

Supported Data Types

Npgsql supports the following data types:

PostgreSQL type	Npgsqldbtype	System. dbtype Enum	. Net System Type
Int8	Bigint	Int64	Int64
Bool	Boolean	Boolean	Boolean
Box, circle, line, lseg, path, point, Polygon	Box, circle, line, lseg, path, point, Polygon	Object	Object
Bytea	Bytea	Binary	Byte []
Date	Date	Date	Datetime, npgsqldate
Float8	Double	Double	Double
Int4	Integer	Int32	Int32
Money	Money	Decimal	Decimal
Numeric	Numeric	Decimal	Decimal
Float4	Real	Single	Single
Int2	Smallint	Int16	Int16
Text	Text	String	String
Time	Time	Time	Datetime, npgsqltime
Timetz	Time	Time	Datetime, npgsqltimetz
Timestamp	Timestamp	Datetime	Datetime, npgsqltimestamp
Timestamptz	Timestamptz	Datetime	Datetime, npgsqltimestamptz
Interval	Interval	Object	Timespan, npgsqlinterval
Varchar	Varchar	String	String
Inet	Inet	Object	Npgsqlinet, IPaddress (There Is An Implicity cast operator to convert npgsqlinet objects into IPaddress if you need to use IPaddress and have only npgsqlinet)
Bit	Bit	Boolean	Boolean, int32 (If you use an int32 value, odd values will be translated to bit 1 and even values to bit 0)
UUID	UUID	Guid	Guid
Array	Array	Object	Array In order to explicitly use array type, specify npgsqldbtype as an 'or' ed type: npgsqldbtype. array | npgsqldbtype. Integer for an array of int32 for example.

 

The text type of the database can correspond to the string type of the. NET program.The key to the problem is indeed the type of function parameters..

To verify this idea, change the parameter type of the function to varchar:

 

 

Create or replace function updateattention (DM varchar)
Returns void
$ Body $
-- Function body

 

Run the. NET data access program again!

Therefore, the conclusion is as follows:

When "Custom Data Type" is used in PostgreSQL database functions, you may not be able to set the correct dbtype In the. NET program, so the function name cannot be found!

 

3. Analysis of "spiritual phenomenon"

As mentioned above

 Para. dbtype =Dbtype. ansistring;
The code can be commented out, that is, if no value is set for npgsqlparameter. dbtype, what is the default value of dbtype?

In the "instant window" of vs2010, we printed the unset para. dbtype and found that its value is:

String

 

Since the parameter object dbtype of npgsql has been verified in the previous article, no matter how it is set, it is string when the attribute value is obtained, so you still cannot know what its default attribute value is.

 

So a very accidental thought emerged:

Does the default value of npgsqlparameter object belong to the object type?

 

In addition, our function uses the custom citext type, so we may need to use the dbtype. Object type.

Modify the Code as follows:

 

// 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. Object;
Para. value = "kf0355 ";
DB. executenonquery ("updateattention ",
System. Data. commandtype. storedprocedure,
New system. Data. idataparameter [] {para });

 

The program runs normally. It seems that the problem is found. In PostgreSQL's user-defined function parameters, the stored procedure call parameter of the. NET program should be set to dbtype. object!