Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB
От | Dave Cramer |
---|---|
Тема | Re: BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB |
Дата | |
Msg-id | CADK3HHKf6XgH6-NoADPsz-itMN_CRS_i7nP9tF=FTdk6Tva4uQ@mail.gmail.com обсуждение исходный текст |
Ответ на | BUG #16557: getProcedureColumns() function returns columns, when there is no existing Stored Procedure in the DB (PG Bug reporting form <noreply@postgresql.org>) |
Список | pgsql-bugs |
On Tue, 1 Sep 2020 at 22:21, Adarshdeep Cheema <Adarshdeep.Cheema@ibm.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@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 inPlease upgrade the driverDave Cramerwww.postgres.rocksOn 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
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #16486: Prompted password is ignored when password specified in connection string
Следующее
От: Jehan-Guillaume de RorthaisДата:
Сообщение: [BUG v13] Crash with event trigger in extension