Re: Problem about executeUpdate and stored procedure that

Han,

There are a couple of things going on here.  The first is a server bug.
 As you indicated the driver turns the "{call testfunc()}" into a
"select * from testfunc()" sql statement.  This is however returning an
error on the function you have.

So select * from testfunc() errors with (even in psql):
ERROR:  Unknown kind of return type specified for function testfunc

however select testfunc() works fine.  This is a server bug.

The second issue here is that postgres only supports functions not
stored procedures.  Thus there will always be a return value from the
function even if it is of type void.  (because as you have noticed the
only way to execute a function is by issuing a select on that function
to the server, and event the void function returns a row in that
select).  So your calling code should either expect or ignore the
return.  The easiest way to do this would be to change from calling
executeUpdate() to just plain execute().

So I got the following to work (change the function to return something
other than void to work around the server bug, and use execute() instead
of executeUpdate()):

  function:
     create function testfunc() returns integer language sql as 'update
table1 set col1=0; select 1;';
  Test.java:
     ...
     CallableStatement stmt = null;
     try
     {
         stmt = conn.prepareCall("{call testfunc()}");
         stmt.execute();
     }
     catch(Exception e)
     {
         System.out.println(e.toString());
     }
     ...


thanks,
--Barry


Han wrote:
> Barry Lind,
>  function:
>     create function testfunc() returns void language sql as 'update table1 set col1=0';
>  Test.java:
>     ...
>     CallableStatement stmt = null;
>     try
>     {
>         stmt = conn.prepareCall("{call testfunc()}");
>         stmt.executeUpdate();
>     }
>     catch(Exception e)
>     {
>         System.out.println(e.toString());
>     }
>     ...
>  output:
>     java.sql.SQLException: ERROR:  Unknown kind of return type specified for function testfunc
>
>  I checked the jdbc code, and find this in AbstractJdbc1Statement::modifyJdbcCall(string):
>     if (connection.haveMinimumServerVersion("7.3")) {
>             l_sql = "select * from " + l_sql + " as " + RESULT_ALIAS + ";";
>         } else {
>             l_sql = "select " + l_sql + " as " + RESULT_ALIAS + ";";
>         }
>  I changed "7.3" to "7.4" here. And then the error became :    A result was returned when none was expected.
>
>
>>Han,
>>
>>Could you send a more complete test case, that I could compile and run?
>>
>>thanks,
>>--Barry
>>
>>
>>Han wrote:
>>
>>>pgsql-jdbc
>>>
>>>    When using executeUpdate with {call func_name(?,?,?)}, there's an error saying that no result should be
returned.But the value is updated successfully in the database by the function. 
>>>    I know that it's because {call func_name(?,?,?)} be transfered to "select func_name(...) as result" and this sql
returnsa row. But I do need to call a function that returns no result, but do some updates. I defined the function as
"createfunction ... returns void as'...'". But it still returns a row contains one field with null value. 
>>>    I don't want to use executeQuery. Is there anyother way to do this? Or should the jdbc executeUpdate should be
fixed?
>>>    Thanks!
>>>
>>>　　　　　　　　
>>>
>>>
>>>　　　　　　　　Han
>>>　　　　　　　　zhouhanok@vip.sina.com
>>>　　　　　　　　　　2003-05-16
>>>
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>     Regards!
>
>
> 　　　　　　　　Han
> 　　　　　　　　zhouhanok@vip.sina.com
> 　　　　　　　　　　2003-05-19
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>