Thread: BatchUpdate exception

hi,

I am trying to add the callable statement into a batch in a for loop. and then execute that batch using executeBatch. which throws the following exception.

org.postgresql.util.PSQLException: A result was returned when none was expected.

any suggestions?

thanks in advance!

On Wed, 5 Dec 2007, Kranti K K Parisa [GetSet-India] wrote:

> I am trying to add the callable statement into a batch in a for loop. and
> then execute that batch using executeBatch. which throws the following
> exception.
>
> org.postgresql.util.PSQLException: A result was returned when none was
> expected.
>

This was fixed pretty recently and is only available in the 8.3dev series
of drivers.  Please upgrade and retry.

Kris Jurka

Hi Kris,

Thanks for the reply.

We are using Postgres 8.1 version. Does this mean we need to upgrade the database version also? or can use the driver with latest release?






--
------------------
Best Regards
Kranti Kiran Kumar Parisa
Mobile: +91 - 9849 - 625 - 625
           +91 - 9391 - 438 - 738

On Wed, 5 Dec 2007, Kranti K K Parisa [GetSet-India] wrote:

> We are using Postgres 8.1 version. Does this mean we need to upgrade the
> database version also? or can use the driver with latest release?
>

You can use the 8.3 driver with any server version >= 7.2.

Kris Jurka

Hi Kris,

No Luck with that!

My Test java program look like this

------------
try{
            for (int i=0;i<200;i++){
                //System.out.println("inside outer==>>"+i);
                for(int j=0;j<4;j++){
                    proc=null;
                    //System.out.println("inside inner==>>"+j);
                    proc=con.prepareCall("{call client255271111.test_function ()}");
                    //proc.registerOutParameter(1, Types.INTEGER);
                    proc.addBatch();
                }
            }
            int []intUpdates=proc.executeBatch();
            System.out.println("intUpdates length==>>"+intUpdates.length);
            for(int i=0;i<intUpdates.length;i++){
                System.out.println("i number is==>>"+i);
                System.out.println("i value is==>>"+intUpdates[i]);
                al.add(new Integer(intUpdates[i]));
            }
        }catch(SQLException e){
            System.out.println(e.getNextException ());
        }
--------------------------

and SP look like
===========
CREATE OR REPLACE FUNCTION test_function()
  RETURNS int4 AS
$BODY$
 declare
begin
raise notice 'testing the Stored proc%','SP Tested';

return 1;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_function() OWNER TO postgres;
========================

The results when i run the java program are:

intUpdates length==>>1
i number is==>>0
i value is==>>0
test size===>>[0]


which shows the SP is not getting executed for all the times??

please suggest.





--
------------------
Best Regards
Kranti Kiran Kumar Parisa
Mobile: +91 - 9849 - 625 - 625
           +91 - 9391 - 438 - 738

On Thu, 6 Dec 2007, Kranti K K Parisa [GetSet-India] wrote:

>            for (int i=0;i<200;i++){
>                //System.out.println("inside outer==>>"+i);
>                for(int j=0;j<4;j++){
>                    proc=null;
>                    //System.out.println("inside inner==>>"+j);
>                    proc=con.prepareCall("{call
> client255271111.test_function()}");
>                    //proc.registerOutParameter(1, Types.INTEGER);
>                    proc.addBatch();
>                }
>            }
>            int []intUpdates=proc.executeBatch();


Since you're reinitializing proc inside the loop you only end up adding
one batch to it, so you only get one batch out.

Kris Jurka

Hi Kris,

I realized it after posting it and then i have changed it to the following

-----------------------------
CallableStatement proc=null;
       
        try{
            for (int i=0;i<200;i++){
                //System.out.println("inside outer==>>"+i);
                for(int j=0;j<4;j++){
                    //System.out.println("inside inner==>>"+j);
                    proc= con.prepareCall("{call test_function()}");
                    //proc.registerOutParameter(1, Types.INTEGER);
                    proc.addBatch();
                }
            }
            int []intUpdates= proc.executeBatch();
            System.out.println("intUpdates length==>>"+intUpdates.length);
            for(int i=0;i<intUpdates.length;i++){
                System.out.println("i number is==>>"+i);
                System.out.println("i value is==>>"+intUpdates[i]);
                al.add(new Integer(intUpdates[i]));
            }
        }catch(SQLException e){
            System.out.println (e.getNextException());
        }
--------------------------

But the output remains same, which is

----------
intUpdates length==>>1
i number is==>>0
i value is==>>0
test size===>>[0]

------------------

Please suggest




--
------------------
Best Regards
Kranti Kiran Kumar Parisa
Mobile: +91 - 9849 - 625 - 625
           +91 - 9391 - 438 - 738

On Thu, 6 Dec 2007, Kranti K K Parisa [GetSet-India] wrote:

> I realized it after posting it and then i have changed it to the following
>
> CallableStatement proc=null;
>        try{
>            for (int i=0;i<200;i++){
>                for(int j=0;j<4;j++){
>                    proc=con.prepareCall("{call test_function()}");

You're still doing the exact same thing, initializing proc inside the
loop.  You need to do this once outside the loops.

Kris Jurka