PostgreSQL JDBC - Out Parameter Support

Author

Out Parameter Support

Kyle R Morse/Eden

2006-01-05, 9:23 am

Is there any plan to include support for OUT Parameters in Stored
Functions in PostgreSQL in the future?
Currently they may return a value, but they do not support out parameters.
I am the maintainer of a J2EE app that my company is porting to other
databases and our stored procedures rely on out parameters. I would
really like to support PostgreSQL but at this time cannot.
Does anyone know if support for these will be added in the future? Or is
this simply something that must be worked around?

Kyle R. Morse
Eden Communications

Oliver Jowett

2006-01-05, 8:25 pm

Kyle R Morse/Eden wrote:

> Is there any plan to include support for OUT Parameters in Stored
> Functions in PostgreSQL in the future?

AFAIK this is already supported with an 8.1 driver and server.

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kyle R Morse/Eden

2006-01-06, 9:24 am

Thanks for your reply.
It is 'sort of' supported. I guess I need to be more specific. Here is
an example of one of the stored functions:

CREATE OR REPLACE FUNCTION EDEN_ITPP. INSERT_COMPANY(INCOM
PANY_NAME IN
VARCHAR,
INPARENT_COMPANY_ID IN INTEGER,
INPHONE IN VARCHAR,
INFAX IN VARCHAR,
NEWID OUT INTEGER) AS $$
DECLARE
TEMPNEWID INTEGER;
BEGIN
SELECT MAX(ID) INTO TEMPNEWID FROM COMPANY;
TEMPNEWID := TEMPNEWID + 1;
IF TEMPNEWID IS NULL THEN
TEMPNEWID := 1;
END IF;
INSERT INTO
COMPANY(ID,COMPANY_N
AME,PARENT_COMPANY_I
D,PHONE,FAX,ENABLED)

VALUES(TEMPNEWID,INC
OMPANY_NAME,INPARENT
_COMPANY_ID,INPHONE,
INFAX,1);
NEWID := TEMPNEWID;
END;
$$ LANGUAGE plpgsql;

This function basically inserts a new company in the company table and
returns the new company's id in the out parameter.
This is the definition for the company table it is inserting on:

CREATE TABLE EDEN_ITPP.COMPANY (
ID INTEGER NOT NULL,
COMPANY_NAME VARCHAR(55) NOT NULL,
PARENT_COMPANY_ID INTEGER,
PHONE VARCHAR(30),
FAX VARCHAR(30),
ENABLED SMALLINT DEFAULT 1 NOT NULL );

-- DDL Statements for primary key on Table COMPANY
CREATE UNIQUE INDEX company_pkey ON EDEN_ITPP.COMPANY(ID);

This works fine from JDBC if I do this:

PreparedStatement p = conn.prepareCall("? = call
EDEN_ITPP.INSERT_COMPANY(?, ?, ?, ?)");
p. registerOutParameter
(1, Types.INTEGER);
p.setString(2, "NewCompany");
p.setInt(3, 0);
p.setString(4, "555-555-5555");
p.setString(5, "555-555-5556");
p.execute();
System.out.println(p.getInt(1));

It returns the new company's ID as expected.

However, the query is not constructed using the posgre syntax, it uses the
standard JDBC stored procedure syntax, so as to be compatible with our
other DBs
(Right now it runs on DB2, SQL Server, Oracle, and MySQL, all of which
work with this query):

PreparedStatement p = conn.prepareCall("call EDEN_ITPP.INSERT_COMPANY(?,?,
?, ?, ?)");
p.setString(1, "NewCompany");
p.setInt(2, 0);
p.setString(3, "555-555-5555");
p.setString(4, "555-555-5556");
p. registerOutParameter
(5, Types.INTEGER);
p.execute();
System.out.println(p.getInt(5));

But Postgre barfs at this.
The exception thrown is:
PSQLException: A CallableStatement function was executed and the return
was of type java.sql.Types=4 however type java.sql.Types=0 was registered.

I am using Server 8.1.1 and JDBC driver 8.1Build 404.
Does anyone know if there is any plan to support out parameters for stored
functions in PostgreSQL in the future?

(See the Note regarding stored procedures and out parameter support)
http://jdbc.postgresql.org/document...1/callproc.html

Thanks for your help,
Kyle

Dave Cramer

2006-01-06, 1:24 pm

On 6-Jan-06, at 9:55 AM, Kyle R Morse/Eden wrote:

>
> Thanks for your reply.
> It is 'sort of' supported. I guess I need to be more specific.
> Here is an example of one of the stored functions:
>
> CREATE OR REPLACE FUNCTION EDEN_ITPP. INSERT_COMPANY(INCOM
PANY_NAME
> IN VARCHAR,
> INPARENT_COMPANY_ID IN
> INTEGER,
> INPHONE IN VARCHAR,
> INFAX IN VARCHAR,
> NEWID OUT INTEGER) AS $$
> DECLARE
> TEMPNEWID INTEGER;
> BEGIN
> SELECT MAX(ID) INTO TEMPNEWID FROM COMPANY;
> TEMPNEWID := TEMPNEWID + 1;
> IF TEMPNEWID IS NULL THEN
> TEMPNEWID := 1;
> END IF;
> INSERT INTO COMPANY
> (ID,COMPANY_NAME,PAR
ENT_COMPANY_ID,PHONE
,FAX,ENABLED)
> VALUES
> (TEMPNEWID,INCOMPANY
_NAME,INPARENT_COMPA
NY_ID,INPHONE,INFAX,
1);
> NEWID := TEMPNEWID;
> END;
> $$ LANGUAGE plpgsql;
>
> This function basically inserts a new company in the company table
> and returns the new company's id in the out parameter.
> This is the definition for the company table it is inserting on:
>
> CREATE TABLE EDEN_ITPP.COMPANY (
> ID INTEGER NOT NULL,
> COMPANY_NAME VARCHAR(55) NOT NULL,
> PARENT_COMPANY_ID INTEGER,
> PHONE VARCHAR(30),
> FAX VARCHAR(30),
> ENABLED SMALLINT DEFAULT 1 NOT NULL );
>
> -- DDL Statements for primary key on Table COMPANY
> CREATE UNIQUE INDEX company_pkey ON EDEN_ITPP.COMPANY(ID);
>
>
> This works fine from JDBC if I do this:
>
> PreparedStatement p = conn.prepareCall("? = call
> EDEN_ITPP.INSERT_COMPANY(?, ?, ?, ?)");
> p. registerOutParameter
(1, Types.INTEGER);
> p.setString(2, "NewCompany");
> p.setInt(3, 0);
> p.setString(4, "555-555-5555");
> p.setString(5, "555-555-5556");
> p.execute();
> System.out.println(p.getInt(1));
>
> It returns the new company's ID as expected.
>
> However, the query is not constructed using the posgre syntax, it
> uses the standard JDBC stored procedure syntax, so as to be
> compatible with our other DBs
> (Right now it runs on DB2, SQL Server, Oracle, and MySQL, all of
> which work with this query):

This is not specific to postgres, this is the jdbc spec. I'd have to
look into why it isn't working as expected, however you have worse
problems using max(id) to get a unique number in an mvcc database
such as postgresql or oracle.

Dave
>
> PreparedStatement p = conn.prepareCall("call
> EDEN_ITPP.INSERT_COMPANY(?,?, ?, ?, ?)");
> p.setString(1, "NewCompany");
> p.setInt(2, 0);
> p.setString(3, "555-555-5555");
> p.setString(4, "555-555-5556");
> p. registerOutParameter
(5, Types.INTEGER);
> p.execute();
> System.out.println(p.getInt(5));
>
> But Postgre barfs at this.
> The exception thrown is:
> PSQLException: A CallableStatement function was executed and the
> return was of type java.sql.Types=4 however type java.sql.Types=0
> was registered.
>
> I am using Server 8.1.1 and JDBC driver 8.1Build 404.
> Does anyone know if there is any plan to support out parameters for
> stored functions in PostgreSQL in the future?
>
> (See the Note regarding stored procedures and out parameter support)
> http://jdbc.postgresql.org/document...1/callproc.html
>
> Thanks for your help,
> Kyle
>
>

Sponsored Links