Re: JDBC PostgreSQL Stored Procedure Meta-data

Thank you Dave.
I will build the driver and give it a go.

Do you know what the normal schedule is for code being pushed to a
supported version of the driver..? ie being available here
http://jdbc.postgresql.org/download.html

Thanks,
Peter

On 13/02/14 00:31, Dave Cramer wrote:
> 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
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Wed, Feb 12, 2014 at 6:41 AM, Dave Cramer <pg(at)fastcrypt(dot)com
> <mailto:pg(at)fastcrypt(dot)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 ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Tue, Feb 11, 2014 at 7:32 PM, Peter Damen <peter(at)peterdamen(dot)com
> <mailto:peter(at)peterdamen(dot)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.
>
>
>
>
>
>
>
>