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,
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 |