Not being one of the list gurus, I don't know why it throws (sounds like a bug), but as a workaround does it work to use casts like
("{ ? = call InsTimeLog ( ?, ?, ?, ?, ?::integer, ?, ?::varchar, ?::int ) }");
(or whatever is the correct type ordering).
----- Original Message -----
Sent: Sunday, March 07, 2004 8:44 PM
Subject: [JDBC] Exception while executing function with CallableStatement
Everyone,
I have the following function set up in the database:
CREATE FUNCTION
InsTimeLog
(
INTEGER, -- ProjectID
INTEGER, -- PersonellID
INTEGER, -- FeeTypeCode
DATE, -- LogDate
INTEGER, -- Hours Logged
INTEGER, -- Minutes Logged
INTEGER, -- InvoiceID
VARCHAR(30) -- Comment
) RETURNS INT4 -- ProjectID
AS
'DECLARE
pProjectID ALIAS FOR $1;
pPersonellID ALIAS FOR $2;
pFeeTypeCode ALIAS FOR $3;
pLogDate ALIAS FOR $4;
pHoursLogged ALIAS FOR $5;
pMinutesLogged ALIAS FOR $6;
pInvoiceID ALIAS FOR $7;
pComment ALIAS FOR $8;
vID INT4;
BEGIN
--- Insert the new record
INSERT INTO
TimeLog
(
ProjectID,
PersonellID,
FeeTypeCode,
LogDate,
HoursLogged,
MinutesLogged,
InvoiceID,
Comment
) VALUES (
pProjectID,
pPersonellID,
pFeeTypeCode,
pLogDate,
pHoursLogged,
pMinutesLogged,
pInvoiceID,
pComment
);
--- Return the ID of the newly inserted record
SELECT last_value INTO id FROM timelog_timelogid_seq;
RETURN vID;
END;'
LANGUAGE 'plpgsql';
The following Java code is used to execute the function:
CallableStatement proc = con.prepareCall
("{ ? = call InsTimeLog ( ?, ?, ?, ?, ?, ?, ?, ? ) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setInt(2, getProjectID());
proc.setInt(3, getPersonellID());
proc.setInt(4, getFeeTypeCode().getLookupID());
proc.setDate(5, getLogDate());
proc.setInt(6, getHours());
proc.setInt(7, getMinutes());
if (getInvoiceID() > 0) {
proc.setInt(8, getInvoiceID());
} else {
proc.setNull(8, Types.INTEGER);
}
if (getComment() == null) {
proc.setNull(9, Types.VARCHAR);
} else {
proc.setString(9, getComment());
}
proc.execute();
newID = proc.getInt(1);
proc.close();
When the 8th and 9th parameters are set to null, I get the following error:
org.postgresql.util.PSQLException: ERROR: function instimelog(integer, integer,
integer, "unknown", integer, integer, "unknown", "unknown") does not exist
Since I'm setting the type using Types.INTEGER and Types.VARCHAR, I do not know why the exception is listing the types for the eight and nineth fields as "unknown". I would appreciate any help.
Thanks,
