Re: Exception while executing function with CallableStatement

From: Philip A. Chapman

To: pgsql-jdbc@postgresql.org

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,