Replies: 2 comments 2 replies
-
I'm not sure why you have the refcursor as a dummy in out parameter.
The docs here Calling Stored Functions and Procedures | pgJDBC
(postgresql.org) <https://jdbc.postgresql.org/documentation/callproc/>
suggest declaring the function as returning a refcursor.
Dave Cramer
…On Fri, 13 Jan 2023 at 15:02, munasim ***@***.***> wrote:
My application (built on Adobe ColdFusion2021) database is migrated from
Oracle to the PostgreSQL 13.7 and application uses latest postgresql-42.5.1
as a JDBC driver for accessing database. My database contains lot of
procedure that returns REFCURSOR as query result set.
But look like PostgreSQL has compatibility issue with the Postgres JDBC
driver. When accessing the database procedures I am facing below issues-
1. INOUT parameter must need to be passed as dummy REFCURSOR reference
from calling source. It should contain Query result and and should return
to the caller without passing from the caller.
2. Procedure parameter mismatch issue- In case of a procedure without
IN parameter, no parameter should passed from the caller but, it throws
error by saying that as a Hint "No function matches the given name and
argument types. You might need to add explicit type casts. Position: 15".
Note: Same calling mechanism works well with other databases(like Oracle
and MySQL) but NOT with Postgres.
--------------------Procedure sample code--------------------
CREATE OR REPLACE PROCEDURE public.pr_testuser(
p_firstuser character varying,
INOUT p_qusers refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
OPEN p_qusers FOR
SELECT first_name,last_name,address
FROM public.test_user
WHERE UPPER(first_name) = UPPER(p_firstuser);
END;
$BODY$;
--------------------ColdFusion code: calling stored
procedure--------------------
---------------------Output message when calling from
application--------------------
ERROR: function public.pr_testuser(character varying) does not exist Hint:
No function matches the given name and argument types. You might need to
add explicit type casts. Position: 15
----------------------------------End------------------------------------------------------
I am totally stuck with the migration. Suggestions are most welcome!!
Thanks & Regards,
Nasim
—
Reply to this email directly, view it on GitHub
<#2729>, or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AADDH5ULR7LWO25QDKNCHM3WSGYF3ANCNFSM6AAAAAAT2YSFQI>
.
You are receiving this because you are subscribed to this thread.Message
ID: ***@***.***>
|
Beta Was this translation helpful? Give feedback.
-
I had to mention more finding- |
Beta Was this translation helpful? Give feedback.
-
My application (built on Adobe ColdFusion2021) database is migrated from Oracle to the PostgreSQL 13.7 and application uses latest postgresql-42.5.1 as a JDBC driver for accessing database. My database contains lot of procedure that returns REFCURSOR as query result set.
But look like PostgreSQL has compatibility issue with the Postgres JDBC driver. When accessing the database procedures I am facing below issues-
Note: Same calling mechanism works well with other databases(like Oracle and MySQL) but NOT with Postgres.
--------------------Procedure sample code--------------------$BODY$
CREATE OR REPLACE PROCEDURE public.pr_testuser(
p_firstuser character varying,
INOUT p_qusers refcursor)
LANGUAGE 'plpgsql'
AS
BEGIN
$BODY$ ;
OPEN p_qusers FOR
SELECT first_name,last_name,address
FROM public.test_user
WHERE UPPER(first_name) = UPPER(p_firstuser);
END;
--------------------ColdFusion code: calling stored procedure--------------------
---------------------Output message when calling from application--------------------
ERROR: function public.pr_testuser(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15
----------------------------------End------------------------------------------------------
I am totally stuck with the migration. Suggestions are most welcome!!
Thanks & Regards,
Nasim
Beta Was this translation helpful? Give feedback.
All reactions