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

Source: Internet
Author: User
Tags postgresql function
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
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
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)
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:



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!



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.