Thread: postgres.stat.result exception

postgres.stat.result exception

From
tanu b
Date:
Hi
 
I am having a problem using a stored procedure/function in postgres ver 7.2.2

My JDBC code is as follows
----------------------------------------------------------------------------------------------------------
Connection conn = null;
CallableStatement cs = null;
try{
 conn = DatabaseAccess.CreateDBConnection() ;
    
 cs = conn.prepareCall("{? = call Save_Status(?)}");
      cs.registerOutParameter(1,java.sql.Types.INTEGER);
 cs.setString(2,strStatus);
 int iStatus = cs.executeUpdate();
................
}
---------------------------------------------------------------------------------------------------------
The function is written as follows
------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION save_status(varchar) RETURNS int4 AS '
Begin
update TA_REGISTRATION_DATA SET REG_STATUS = $1 WHERE REG_ID = 1;
return 0;
END;
'  LANGUAGE 'plpgsql';
------------------------------------------------------------------------------------------------------------------------------
The update to the postgresql database succeeds but a postgresql.stat.result exception is thrown.
What do I need to modify in my JAVA code/function to avoid this exception.
 
Thanks and Regards
Tanu


Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software

Re: postgres.stat.result exception

From
Kim Ho
Date:
I'm working in 7.4.

But I can do this:

(Using your function definition)

CallableStatement stmt = conn.prepareCall("{?=call Save_Status(?)}");
stmt.registerOutParameter(1, java.sql.Types.INTEGER);
stmt.setString(2,"'asdfadf'");
stmt.executeQuery();
System.out.println(stmt.getInt(1));

which outputs 0.

In your function, you return 0. My guess is that it's being treated as a
result set so that's why executeUpdate will not work.

Cheers,

Kim

On Tue, 2003-08-05 at 01:16, tanu b wrote:
> Hi
>
> I am having a problem using a stored procedure/function in postgres
> ver 7.2.2
> My JDBC code is as follows
> ----------------------------------------------------------------------------------------------------------
> Connection conn = null;
> CallableStatement cs = null;
> try{
>  conn = DatabaseAccess.CreateDBConnection() ;
>
>  cs = conn.prepareCall("{? = call Save_Status(?)}");
>       cs.registerOutParameter(1,java.sql.Types.INTEGER);
>  cs.setString(2,strStatus);
>  int iStatus = cs.executeUpdate();
> ................
> }
> ---------------------------------------------------------------------------------------------------------
> The function is written as follows
>
------------------------------------------------------------------------------------------------------------------------------
> CREATE FUNCTION save_status(varchar) RETURNS int4 AS '
> Begin
> update TA_REGISTRATION_DATA SET REG_STATUS = $1 WHERE REG_ID = 1;
> return 0;
> END;
> '  LANGUAGE 'plpgsql';
>
------------------------------------------------------------------------------------------------------------------------------
> The update to the postgresql database succeeds but a
> postgresql.stat.result exception is thrown.
> What do I need to modify in my JAVA code/function to avoid this
> exception.
>
> Thanks and Regards
> Tanu
>
>
> ______________________________________________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software