472,373 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,373 software developers and data experts.

Stored Proc error after modifying field type

Hi,
I've a problem with some of my stored procs. My config is: RH7.1, Postgres
7.3.2

I had converted a few fields of a few tables from one type to another and
after this I made all the necessary changes on the functions and recreated
all my types and functions.
It seemd to be all right, but the newly created functions won't work
anymore.

The error message says after running one of them:
-----------------------------------------------------------------------
ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown",
"unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown",
double precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
-----------------------------------------------------------------------

This error rises only with newly created functions, old functions are workig
fine.

Should anybody suggest me something to avoid this problem.

Thanks a lot,

-- Csaba


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #1
7 3330
=?iso-8859-1?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:
ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown",
"unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown",
double precision) does not exist
Unable to identify a function that satisfies the given argument types


The error message may be misleading you --- that same error will be
reported when there is no possible match in pg_proc, and when there
are multiple matches and the system can't figure out which to pick.
(I plan to try to make this better in 7.4...) Given all the unknowns
you've got there, multiple matches seems like a likely problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2
Hi Tom,
querying my functions results this:

alumil=# \df addincominginvoice
List of functions
Result data type | Schema | Name |
Argument data types
------------------+--------+--------------------+---------------------------
---------------------------------------
integer | public | addincominginvoice | integer, integer, text,
text, text, text, integer, text, text, text, text, numeric
(1 row)

This means that I have exactly one addincominginvoice function, in addition
with the appropriate parameter list.

----------------------------------------------------------------------------
--------------------------------------
Calling this procedure from the console, it goes well.
alumil=# select addincominginvoice (2, -1, 'xxxx', '2003.07.01',
'2003.07.01', 'DDDD', 25, '2003.09.01', 'f', ' ', 'f', 2332.334);
addincominginvoice
--------------------
10
----------------------------------------------------------------------------
--------------------------------------

Calling it from the win32 client it fails. (The client is a Delphi 7
application using BDE)
The traffic between the client and the backend is the following:
----------------------------------------------------------------------------
--------------------------------------
1 17:59:12 SQL Prepare: PostgreSQL - { call "addincominginvoice"
(?,?,?,?,?,?,?,?,?,?,?,?)}
2 17:59:12 SQL Misc: PostgreSQL - Set stored procedure on or off
3 17:59:12 SQL Data In: PostgreSQL - Param = 1, Name = PartnerID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 19
4 17:59:12 SQL Data In: PostgreSQL - Param = 2, Name = OrderNumber,
Type = fldINT32, Precision = 0, Scale = 0, Data = -1
5 17:59:12 SQL Data In: PostgreSQL - Param = 3, Name = SN, Type =
fldZSTRING, Precision = 12, Scale = 0, Data = xxxxxxxxxxxx
6 17:59:12 SQL Data In: PostgreSQL - Param = 4, Name = PerformanceDat
e, Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.02
7 17:59:12 SQL Data In: PostgreSQL - Param = 5, Name = IssuingDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.02
8 17:59:12 SQL Data In: PostgreSQL - Param = 6, Name = LedgerAccount,
Type = fldZSTRING, Precision = 7, Scale = 0, Data = YYYYYYY
9 17:59:12 SQL Data In: PostgreSQL - Param = 7, Name = UserID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 25
10 17:59:12 SQL Data In: PostgreSQL - Param = 8, Name =
PaymentDeadLine, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.02
11 17:59:12 SQL Data In: PostgreSQL - Param = 9, Name = Payed, Type =
fldZSTRING, Precision = 1, Scale = 0, Data = f
12 17:59:12 SQL Data In: PostgreSQL - Param = 10, Name = PayedDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data =
13 17:59:12 SQL Data In: PostgreSQL - Param = 11, Name = NoStock, Type
= fldZSTRING, Precision = 1, Scale = 0, Data = f
14 17:59:12 SQL Data In: PostgreSQL - Param = 12, Name = GrossValue,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 0.000000
15 17:59:12 SQL Execute: PostgreSQL - { call "addincominginvoice"
(:PartnerID,:OrderNumber,:SN,:PerformanceDate,:Iss uingDate,:LedgerAccount,:U
serID,:PaymentDeadLine,:Payed,:PayedDate,:NoStock, :GrossValue)}
16 17:59:12 SQL Vendor: ODBC - SQLAllocStmt
17 17:59:12 SQL Vendor: ODBC - SQLBindParameter
18 17:59:12 SQL Vendor: ODBC - SQLBindParameter
19 17:59:12 SQL Vendor: ODBC - SQLBindParameter
20 17:59:12 SQL Vendor: ODBC - SQLBindParameter
21 17:59:12 SQL Vendor: ODBC - SQLBindParameter
22 17:59:12 SQL Vendor: ODBC - SQLBindParameter
23 17:59:12 SQL Vendor: ODBC - SQLBindParameter
24 17:59:12 SQL Vendor: ODBC - SQLBindParameter
25 17:59:12 SQL Vendor: ODBC - SQLBindParameter
26 17:59:12 SQL Vendor: ODBC - SQLBindParameter
27 17:59:12 SQL Vendor: ODBC - SQLBindParameter
28 17:59:12 SQL Vendor: ODBC - SQLBindParameter
29 17:59:12 SQL Vendor: ODBC - SQLExecDirect
30 17:59:12 SQL Vendor: ODBC - SQLError
31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
32 17:59:12 SQL Vendor: ODBC - SQLError
33 17:59:12 SQL Stmt: PostgreSQL - Reset
34 17:59:12 SQL Vendor: ODBC - SQLFreeStmt

----------------------------------------------------------------------------
--------------------------------------
This trace is provided by the psqlODBC driver.
Can you find anything in this log, which can cause the error?

Thank you,
-- Csaba

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Egyd Csaba" <cs*****@freemail.hu>
Cc: <pg***********@postgresql.org>
Sent: Wednesday, July 02, 2003 5:41 PM
Subject: Re: [GENERAL] Stored Proc error after modifying field type

=?iso-8859-1?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:
ERROR: Function addincominginvoice(integer, integer, "unknown", "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown", double precision) does not exist
Unable to identify a function that satisfies the given argument types


The error message may be misleading you --- that same error will be
reported when there is no possible match in pg_proc, and when there
are multiple matches and the system can't figure out which to pick.
(I plan to try to make this better in 7.4...) Given all the unknowns
you've got there, multiple matches seems like a likely problem.

regards, tom lane

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #3
=?iso-8859-2?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:
alumil=# \df addincominginvoice
List of functions
Result data type | Schema | Name |
Argument data types
------------------+--------+--------------------+---------------------------
---------------------------------------
integer | public | addincominginvoice | integer, integer, text,
text, text, text, integer, text, text, text, text, numeric
(1 row) 31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist


IIRC, there's no implicit cast from double precision to numeric, only
the other direction. You'll need to do something to persuade the ODBC
driver to select numeric not float as the parameter type. This is
outside my field --- you might get some help in pgsql-odbc.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4
Sorry for posting many times!
-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #5
Thank you Tom,
I'll check it out. But I must say that I have many other functions using
numeric fields and working well.
This error has been rising up since I converted my fields from double
precision to numeric.
May be the problem is in my ODBC settings but I'm sceptic a bit about this.
I had set the
"Int8 As numeric" option before converting my fields.

Anyway, I subscript pgsql-odbc list.

Thank you very much.

-- Csaba

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Egyd Csaba" <cs*****@freemail.hu>
Cc: <pg***********@postgresql.org>
Sent: Wednesday, July 02, 2003 6:23 PM
Subject: Re: [GENERAL] Stored Proc error after modifying field type

=?iso-8859-2?Q?Egy=FCd_Csaba?= <cs*****@freemail.hu> writes:
alumil=# \df addincominginvoice
List of functions Result data type | Schema | Name |
Argument data types


------------------+--------+--------------------+-------------------------

--
---------------------------------------
integer | public | addincominginvoice | integer, integer, text, text, text, text, integer, text, text, text, text, numeric
(1 row)

31 17:59:12 SQL Error: PostgreSQL - ERROR: Function
addincominginvoice(integer, integer, "unknown", "unknown", "unknown",
"unknown", integer, "unknown", "unknown", "unknown", "unknown", double
precision) does not exist


IIRC, there's no implicit cast from double precision to numeric, only
the other direction. You'll need to do something to persuade the ODBC
driver to select numeric not float as the parameter type. This is
outside my field --- you might get some help in pgsql-odbc.

regards, tom lane

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #6
Tom,
what do you think about the folowing: I updated the server field of my DSN
to point to the postgres runniong under cygwin on the localhost. The error
does not appear at all.
----------------------------------
1 06:29:42 SQL Prepare: PostgreSQL - { call "addincominginvoice"
(?,?,?,?,?,?,?,?,?,?,?,?)}
2 06:29:42 SQL Misc: PostgreSQL - Set stored procedure on or off
3 06:29:42 SQL Data In: PostgreSQL - Param = 1, Name = PartnerID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 3
4 06:29:42 SQL Data In: PostgreSQL - Param = 2, Name = OrderNumber,
Type = fldINT32, Precision = 0, Scale = 0, Data = -1
5 06:29:42 SQL Data In: PostgreSQL - Param = 3, Name = SN, Type =
fldZSTRING, Precision = 20, Scale = 0, Data = AAAAAAAAAAAAAAAAAAAA
6 06:29:42 SQL Data In: PostgreSQL - Param = 4, Name =
PerformanceDate, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.03
7 06:29:42 SQL Data In: PostgreSQL - Param = 5, Name = IssuingDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = 2003.07.03
8 06:29:42 SQL Data In: PostgreSQL - Param = 6, Name = LedgerAccount,
Type = fldZSTRING, Precision = 10, Scale = 0, Data = BBBBBBBBBB
9 06:29:42 SQL Data In: PostgreSQL - Param = 7, Name = UserID, Type =
fldINT32, Precision = 0, Scale = 0, Data = 25
10 06:29:42 SQL Data In: PostgreSQL - Param = 8, Name =
PaymentDeadLine, Type = fldZSTRING, Precision = 10, Scale = 0, Data =
2003.07.03
11 06:29:42 SQL Data In: PostgreSQL - Param = 9, Name = Payed, Type =
fldZSTRING, Precision = 1, Scale = 0, Data = f
12 06:29:42 SQL Data In: PostgreSQL - Param = 10, Name = PayedDate,
Type = fldZSTRING, Precision = 10, Scale = 0, Data =
13 06:29:42 SQL Data In: PostgreSQL - Param = 11, Name = NoStock, Type
= fldZSTRING, Precision = 1, Scale = 0, Data = f
14 06:29:42 SQL Data In: PostgreSQL - Param = 12, Name = GrossValue,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 0.000000
15 06:29:42 SQL Execute: PostgreSQL - { call "addincominginvoice"
(:PartnerID,:OrderNumber,:SN,:PerformanceDate,:Iss uingDate,:LedgerAccount,:U
serID,:PaymentDeadLine,:Payed,:PayedDate,:NoStock, :GrossValue)}
16 06:29:42 SQL Vendor: ODBC - SQLAllocStmt
17 06:29:42 SQL Vendor: ODBC - SQLBindParameter
18 06:29:42 SQL Vendor: ODBC - SQLBindParameter
19 06:29:42 SQL Vendor: ODBC - SQLBindParameter
20 06:29:42 SQL Vendor: ODBC - SQLBindParameter
21 06:29:42 SQL Vendor: ODBC - SQLBindParameter
22 06:29:42 SQL Vendor: ODBC - SQLBindParameter
23 06:29:42 SQL Vendor: ODBC - SQLBindParameter
24 06:29:42 SQL Vendor: ODBC - SQLBindParameter
25 06:29:42 SQL Vendor: ODBC - SQLBindParameter
26 06:29:42 SQL Vendor: ODBC - SQLBindParameter
27 06:29:42 SQL Vendor: ODBC - SQLBindParameter
28 06:29:42 SQL Vendor: ODBC - SQLBindParameter
29 06:29:42 SQL Vendor: ODBC - SQLExecDirect
30 06:29:42 SQL Vendor: ODBC - SQLNumResultCols
31 06:29:42 SQL Vendor: ODBC - SQLDescribeCol
32 06:29:42 SQL Misc: PostgreSQL - Set rowset size
33 06:29:42 SQL Vendor: ODBC - SQLBindCol
34 06:29:42 SQL Stmt: PostgreSQL - Fetch
35 06:29:42 SQL Vendor: ODBC - SQLSetStmtOption
36 06:29:42 SQL Vendor: ODBC - SQLExtendedFetch
37 06:29:42 SQL Data Out: PostgreSQL - Column = 1, Name =
addincominginvoice, Type = fldINT32, Precision = 10, Scale = 0, Data = 18
38 06:29:42 SQL Stmt: PostgreSQL - Reset
39 06:29:42 SQL Vendor: ODBC - SQLFreeStmt
40 06:29:42 SQL Stmt: PostgreSQL - Close
41 06:29:42 SQL Stmt: PostgreSQL - Reset
42 06:29:42 SQL Vendor: ODBC - SQLFreeStmt
43 06:29:42 SQL Stmt: PostgreSQL - Close
44 06:29:42 SQL Prepare: PostgreSQL - select count(*) as count from
t_incoming_invoices;
----------------------------------
But the following function fails:
----------------------------------
1 06:33:06 SQL Prepare: PostgreSQL - { call "addinstalment" (?,?,?)}
2 06:33:06 SQL Misc: PostgreSQL - Set stored procedure on or off
3 06:33:06 SQL Data In: PostgreSQL - Param = 1, Name = InvoiceID,
Type = fldINT32, Precision = 0, Scale = 0, Data = 35
4 06:33:06 SQL Data In: PostgreSQL - Param = 2, Name = PayDate, Type
= fldZSTRING, Precision = 10, Scale = 0, Data = 2003.04.18
5 06:33:06 SQL Data In: PostgreSQL - Param = 3, Name = Instalment,
Type = fldFLOAT, Precision = 0, Scale = 0, Data = 1000.000000
6 06:33:06 SQL Execute: PostgreSQL - { call "addinstalment"
(:InvoiceID,:PayDate,:Instalment)}
7 06:33:06 SQL Vendor: ODBC - SQLAllocStmt
8 06:33:06 SQL Vendor: ODBC - SQLBindParameter
9 06:33:06 SQL Vendor: ODBC - SQLBindParameter
10 06:33:06 SQL Vendor: ODBC - SQLBindParameter
11 06:33:06 SQL Vendor: ODBC - SQLExecDirect
12 06:33:06 SQL Vendor: ODBC - SQLError
13 06:33:06 SQL Error: PostgreSQL - ERROR: Function
addinstalment(integer, "unknown", double precision) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
14 06:33:06 SQL Vendor: ODBC - SQLError
15 06:33:06 SQL Stmt: PostgreSQL - Reset
16 06:33:06 SQL Vendor: ODBC - SQLFreeStmt
17 06:33:09 SQL Stmt: PostgreSQL - Close
----------------------------------
alumil=# \df addinstalment
List of functions
Result data type | Schema | Name | Argument data types
------------------+--------+---------------+------------------------
integer | public | addinstalment | integer, text, numeric
(1 row)
----------------------------------

Do you think yet the ODBC can be balmed. I don't know what to think.

-- Csaba
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #7
Hi,
I got out of this problem, but I'm not sure I found the best way.
I changed the parameter types of my functions from decimal to text. It
results, that I can send string values for decimal fields. This works well,
but I don't know if it is leading to other problems or not.

Thank you,

-- Csaba


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: KathyB | last post by:
I'm trying to concatenate fields in SQL stored proc for use in text field in asp.net dropdownlist. I'm running into a problem when I try to use a DateTime field, but can't find the answer (so far)...
2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
0
by: Egyd Csaba | last post by:
Hi, I've a problem with some of my stored procs. My config is: RH7.1, Postgres 7.3.2 I have converted a few fields of a few tables from one type to another. After this I made all the necessary...
0
by: Egyd Csaba | last post by:
> Hi, > I've a problem with some of my stored procs. My config is: RH7.1, Postgres > 7.3.2 > > I had converted a few fields of a few tables from one type to another and > after this I made all...
4
by: Nyul | last post by:
Gurus, I have a verb big problem which I'm unable to explain. We have a DB2 V6.1.0 on AIX 4.3 I want to make a C stored procedure which at the end will be called by a PHP script. The...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
5
by: Rob Wire | last post by:
For the code below, how could I add an item in the drop down lists for both company and location to be an "All" selection that would send to the stored proc. spRptAttachments a value of "%" so...
3
by: Byron | last post by:
I'm trying to iterate stored parameters and populate any public properties of an object with the parameter value. I've suceeded in doing it the other way around, but when I try it to the object I...
0
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.