Re: JDBC PostgreSQL Stored Procedure Meta-data
От | Dave Cramer |
---|---|
Тема | Re: JDBC PostgreSQL Stored Procedure Meta-data |
Дата | |
Msg-id | CADK3HHJQapbNOFePCU8f_jkTRG+xH4bnMkp_7XXO=BLNygwYKw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: JDBC PostgreSQL Stored Procedure Meta-data (Dave Cramer <pg@fastcrypt.com>) |
Ответы |
Re: JDBC PostgreSQL Stored Procedure Meta-data
|
Список | pgsql-jdbc |
Peter,
I just ran your code against the latest driver
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 5 Data Type: 4
Parameter Name: name Paramter Type: 5 Data Type: 12
On Wed, Feb 12, 2014 at 6:41 AM, Dave Cramer <pg@fastcrypt.com> wrote:
Apparently they are returning different data. There was a recent pull request which might solve this.Are you able to build the driver from github to test ?On Tue, Feb 11, 2014 at 7:32 PM, Peter Damen <peter@peterdamen.com> wrote:Hello,
I have an issue with retrieving meta-data for Stored Procedures using the JDBC driver.
I have outlined the details of my issue here: http://stackoverflow.com/questions/21541745/postgresql-9-jdbc-driver-returns-incorrect-metadata-for-stored-procedures#
Essentially I have this example stored procedure:
CREATE FUNCTION testproc(itemid int)
RETURNS TABLE(id int, name varchar(200)) AS $$
BEGIN
RETURN QUERY SELECT ipperson, firstname FROM testtable
WHERE id = itemid;
END;
$$ LANGUAGE plpgsql;
And I want to get the meta-data for this query using the JDBC DatabaseMetaData.
I have written a Java test case, it prints out the parameters returned by the meta-data call:
package com.hof.unittest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class TestPostgres {
public static void main(String args[]) {
try {
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testdb", "admin", "admin");
ResultSet rs = conn.getMetaData().getProcedureColumns(null, null, "testproc", null);
System.out.println("Driver: " + conn.getMetaData().getDriverVersion());
while (rs.next()) {
System.out.println("Parameter Name: " + rs.getString(4) + " Paramter Type: " + rs.getShort(5) + " Data Type: " + rs.getInt(6));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
I get different results with different JDBC drivers (against the same 9.1.11 server):
Driver: PostgreSQL 8.0 JDBC3 with SSL (build 313)
Parameter Name: returnValue Paramter Type: 5 Data Type: 1111
Parameter Name: $1 Paramter Type: 1 Data Type: 4
Driver: PostgreSQL 9.0 JDBC4 (build 801)
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 1 Data Type: 4
Parameter Name: name Paramter Type: 1 Data Type: 12
Driver: PostgreSQL 9.3 JDBC4 (build 1100)
Parameter Name: itemid Paramter Type: 1 Data Type: 4
Parameter Name: id Paramter Type: 1 Data Type: 4
Parameter Name: name Paramter Type: 1 Data Type: 12
The 8.0 driver returns meta-data in a form I can use.. it says there is one IN parameter and one OUT parameter (the resultset).
The 9.0/3 drivers don't return an OUT parameter, and along with the single IN parameter, the columns returned by the resultset are also listed, and listed as a IN parameter.
Are the 9 drivers returning the wrong meta-data?
Was there change in how procedures were handled between JDBC3 and JDBC4?
I need to get meta-data about just the IN parameters for a procedure as I need to dynamically prompt for input values and build the execution string.. ie SELECT * FROM procedure(?) OR { ? = procedure(?) }
Is there any other way to get this information... querying system tables etc?
Thanks.
В списке pgsql-jdbc по дате отправления:
Следующее
От: Mike OДата:
Сообщение: Postgres 9.0.4 with Serverside PreparedStatements never returns with large tables