I am on a team that develops a business intelligence (reporting) tool. We report off many sources include Stored Procedures. We use the meta-data provided by the JDBC driver to determine the input and output paramters of a Stored Procedure.
It appears that PostgreSQL 9 JDBC drivers are incorrectly returning meta-data for the parameters of a procedure.
For instance my stored procedure looks like this:
CREATE FUNCTION person(personid int)
RETURNS TABLE(person int, name varchar(200)) AS $$
BEGIN
RETURN QUERY SELECT ipperson, firstname FROM person
WHERE ipperson = personid;
END;
$$ LANGUAGE plpgsql;
So it has one paramter in, two columns returned in a resultset.
The PostgreSQL driver is reporting that there are 3 IN parameters.
- personid (the parameter)
- person (first column returned)
- name (second column returned)
with no meta-data to distinguish between types.
I execute this with:
SELECT * FROM person(?);
(As a prepared statement, setting values for each ? token)
I know that I can filter using the columns returned like this:
SELECT * FROM person(5) where person = 5;
But I am more interested in getting only the parameters returned by the meta-data, so I can programmatically build the query string (I need to know how many ?'s to put in the query).
Not sure if this is a bug, or whether I am doing something wrong.
If I use a PostgreSQL 8 driver, it seems to return the correct number of parameters:
- personid (the parameter)
Thanks.
Specific Driver/Server versions are:
- PostgreSQL Server 9.11 (Mint 16)
- PostgreSQL "8" 8.0 JDBC3 with SSL(build 313)
- PostgreSQL "9" 9.3 JDBC4 (build 1100)
Additional Information to replicate what I am seeing:
DB Scripts:
CREATE TABLE testtable (
id integer,
name varchar
);
INSERT INTO testtable VALUES (1, 'Bob');
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;
Java Code:
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();
}
}
}
Output with different drivers (all against PostgreSQL 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
Note that the 8.0 Driver flags the return value as Type 5 .. all other drivers flag the 2nd and 3rd paramters as Type 1.
Obviously the PostgreSQL 8 driver is JDBC3 and the others JDBC4. If this is the reason that the results are different then that's great.. but I still want to distinguish between an actual input paramter and output parameter.
