From: | Peter Damen <peter(at)peterdamen(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | JDBC PostgreSQL Stored Procedure Meta-data |
Date: | 2014-02-12 00:32:15 |
Message-ID: | 52FAC10F.5030906@peterdamen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mike O | 2014-02-12 08:10:58 | Postgres 9.0.4 with Serverside PreparedStatements never returns with large tables |
Previous Message | Michał Woś | 2014-02-11 23:07:45 | PGXAConnection - equals method returning false |