Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB

On Tue, 1 Sep 2020 at 22:21, Adarshdeep Cheema <Adarshdeep(dot)Cheema(at)ibm(dot)com>
wrote:

>
> 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(at)postgres(dot)rocks>
> To: adarshdeep(dot)cheema(at)ibm(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)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
> https://github.com/pgjdbc/pgjdbc/pull/1723
> Please upgrade the driver
> Dave Cramer
> www.postgres.rocks
>
> On Mon, 27 Jul 2020 at 13:47, PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 16557
> Logged by: Adarshdeep Cheema
> Email address: adarshdeep(dot)cheema(at)ibm(dot)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
>
>
>
>
>

I just tried this:

@Test
public void testGetProcuresWithSchema() throws SQLException {
try {
conn.createStatement().execute("CREATE FUNCTION findec(pdec
numeric) RETURNS numeric\n" +
" LANGUAGE plpgsql\n" +
" AS $$\n" +
"declare r decimal(7,2);\n" +
"begin\n" +
" r = PDEC;\n" +
" return r;\n" +
"end;\n" +
"$$;");
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getProcedures(null, "public", "findec");
if (rs.next()) {
fail();
}
}finally {
conn.createStatement().execute("DROP FUNCTION findec");
}

}

And it does not find the function ?

Dave Cramer
www.postgres.rocks