Re: JDBC PostgreSQL Stored Procedure Meta-data

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> 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> 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.
>>
>>
>>
>>
>>
>>
>>
>