Exception while executing function with CallableStatement

Поиск
Список
Период
Сортировка
От Philip A. Chapman
Тема Exception while executing function with CallableStatement
Дата
Msg-id 001201c404c8$3ab00140$3500a8c0@wayfarer
обсуждение исходный текст
Ответы Re: Exception while executing function with CallableStatement
Список 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,

В списке pgsql-jdbc по дате отправления: