Hi Dave,
We are using 42.2.11 and the latest JDBC driver. We also tried 42.2.16 JDBC drivers as well.
please note that getProcedures() returns nothing, which is expected as we do not have any Stored procedure.
but when we use getProcedureColumns(catalog, schema,null,null) then it returns the columns from Functions, which is unexpected and is a bug.
Thanks
Adarshdeep
----- Original message -----
From: Dave Cramer <davecramer@postgres.rocks>
To: adarshdeep.cheema@ibm.com, pgsql-bugs@lists.postgresql.org
Cc:
Subject: [EXTERNAL] Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB
Date: Tue, Sep 1, 2020 6:36 PM
This has been fixed in
Please upgrade the driver
Dave Cramer
www.postgres.rocks
The following bug has been logged on the website:
Bug reference: 16557
Logged by: Adarshdeep Cheema
Email address: adarshdeep.cheema@ibm.com
PostgreSQL version: 12.2
Operating system: Windows
Description:
Create any user defined function using Postgres 12.3 Server, we are using
postgresql-42.2.11 JDBC driver
DDLs:
CREATE FUNCTION findec(pdec numeric) RETURNS numeric
LANGUAGE plpgsql
AS $$
declare r decimal(7,2);
begin
r = PDEC;
return r;
end;
$$;
a) When we use the following Java code, then we get nothing as we do no have
any Stored Procedure defined, which is expected
resultSet= metadata.getProcedures(null , "public", null); ,
while (resultSet.next()){
System.out.print(resultSet.getString(1)+" ");
System.out.print(resultSet.getString(2)+" ");
System.out.print(resultSet.getString(3)+" ");
System.out.print(resultSet.getString(4)+" ");
System.out.print(resultSet.getString(5)+" ");
System.out.println(resultSet.getString(6)+" ");
}
b) Now change the JAVA code to the following and you will get two columns in
the resultSet, which is a bug as there is no StoredProcedure Defined in the
database
resultSet= metadata.getProcedureColumns(null , "public", "findec", null);
,
while (resultSet.next()){
System.out.print(resultSet.getString(1)+" ");
System.out.print(resultSet.getString(2)+" ");
System.out.print(resultSet.getString(3)+" ");
System.out.print(resultSet.getString(4)+" ");
System.out.print(resultSet.getString(5)+" ");
System.out.println(resultSet.getString(6)+" ");
}
OUTPUT:
null public findec returnValue 5 2
null public findec pdec 1 2