Error when running postgresql function from java

Question

We've recently switched over to using postgresql (enterpriseDB), and I moved a function over but when I run it I get:

com.edb.util.PSQLException: ERROR: bind message has 2 result formats but query has 1 columns

The function is:

CREATE OR REPLACE FUNCTION receive_item(qtyexpected integer, qtyrcvd integer, reasoncd character varying, commenttext character varying, whs character varying, rcvrnbr character varying, ponbr character varying, itemid character varying) RETURNS integer AS
$BODY$
  rcvrDtlId integer;  
begin
  rcvrDtlId := 0;

  begin
    update rcvr_dtl set 
    qty_expected = qtyExpected, qty_received = qty_received + qtyRcvd, reason_cd = reasonCd, comments = commentText 
    where warehouse = whs and rcvr_nbr = rcvrNbr and po_nbr = poNbr and item_nbr = itemId
    returning rcvr_dtl_id into rcvrDtlId;                             
  exception
     when no_data_found then
        null;
  end;                              

  return rcvrDtlId;
end$BODY$
LANGUAGE edbspl VOLATILE NOT LEAKPROOF
COST 100;

And it's being called with:

sql= "{? = CALL receive_item(?,?,?,?,?,?,?,?)}";
m_ReceiveItem = m_Conn.prepareCall(sql);
m_ReceiveItem.registerOutParameter(1, Types.INTEGER);
m_ReceiveItem.setInt(2, msg.getQtyExpected());
m_ReceiveItem.setInt(3, msg.getQtyReceived());
m_ReceiveItem.setString(4, msg.getReasonCode());
m_ReceiveItem.setString(5, msg.getComments());
m_ReceiveItem.setString(6, msg.getFacilityNbr());
m_ReceiveItem.setString(7, msg.getRcvrNbr());
m_ReceiveItem.setString(8, msg.getPoNbr());
m_ReceiveItem.setString(9, msg.getSku());
m_ReceiveItem.execute();
rcvrDtlId = m_ReceiveItem.getInt(1);

I cant seem to find any information on this error that relates in any way. Why would this error be occurring for the above code?

Actually error seems to be specific to the edb driver (that string doesn't exist in normal postgres java drivers source). If you swap out the edb driver for the vanilla postgres JDBC driver does it work? EnterpriseDB doesn't release their source I don't think for the driver, so hard to have a look to see what's going on. — Jeff U., Jan 05 '17 at 23:51

score 1 · Accepted Answer · answered Jan 06 '17 at 00:36

1

I was able to solve this issue by switching the syntax from the ? = call... syntax to select * from receive_item...

answered Jan 06 '17 at 00:36

Kynian

660
4
15
30

1

PostgreSQL doesn't distinguish between STORED PROCEDURES and USER DEFINED FUNCTIONS. They are all considered the same, and they are all accessed through SELECT. – joanolo Jan 06 '17 at 00:41

Error when running postgresql function from java

1 Answers1