Exception while executing function with CallableStatement

From: "Philip A(dot) Chapman" <pchapman(at)pcsw(dot)us>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Exception while executing function with CallableStatement
Date: 2004-03-08 04:44:15
Message-ID: 001201c404c8$3ab00140$3500a8c0@wayfarer
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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,

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Andrew Lazarus 2004-03-08 05:21:39 Re: Exception while executing function with CallableStatement
Previous Message Andrew Nelson 2004-03-06 06:38:17 Re: connection problem - why idle