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

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

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@postgresql.org> wrote:

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
 

 

@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");
  }