Re: Out Parameter Support - Mailing list pgsql-jdbc
| From | Kyle R Morse/Eden |
|---|---|
| Subject | Re: Out Parameter Support |
| Date | |
| Msg-id | OF26534732.7937D8DE-ON852570EE.004FBF06-852570EE.0051FE9C@eden.com Whole thread Raw |
| In response to | Re: Out Parameter Support (Oliver Jowett <oliver@opencloud.com>) |
| Responses |
Re: Out Parameter Support
|
| List | pgsql-jdbc |
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(INCOMPANY_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,PARENT_COMPANY_ID,PHONE,FAX,ENABLED)
VALUES(TEMPNEWID,INCOMPANY_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/documentation/81/callproc.html
Thanks for your help,
Kyle
pgsql-jdbc by date: