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

On Sun, 7 Mar 2004, Philip A. Chapman wrote:

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

This is indeed a bug, but in your case the error is because the function
does not exist.  There are two possible problems that I saw in my testing:

I created three functions

CREATE FUNCTION nullfunc(a timestamptz) RETURNS timestamptz AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a int) RETURNS int AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a text) RETURNS text AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql

and java code that tries to call the int version:

CallableStatement cs = conn.prepareCall("{? = call nullfunc(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setNull(2, Types.INTEGER);
cs.execute();
int result = cs.getInt(1);

This resulted in the text version being called even though you would
expect the int version to be called, resulting in:

    A CallableStatement Function was executed and the return was of
    type (java.sql.Types=12) however type=java.sql.Types=4 was
    registered.

After deleting the text function the backend couldn't determine which
function to call and I get:

    ERROR: function nullfunc("unknown") is not unique

At the moment no casting is done for any function arguments.  We could do
something like the server side prepared statement's code in
AbstractJdbc1Statement.transformToServerPrepare for CallableStatements as
well.  Would we want that for all arguments or just for null ones?  Adding
the casts for non-null parameters would allow the selection between two
functions like f(int4) and f(int2), but could likely cause other
problems/unexpected behavior.

Kris Jurka