Thread: Utilizing executeBatch() with stored procedures

I was wondering what the status is for getting
CallableStatments to run via executeBatch() to get
inserts done in bulk. I've been attempting to get this
to work on 8.1.4 with the  8.2 503 driver. Despite my
return type being void, this fails to execute
returning a syntax error on "{" which is alluding to
the "{call func(? . . ."

Are there any known workarounds?

Thanks,
stchen

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Yukino Miyazawa wrote:
> I was wondering what the status is for getting CallableStatments to
> run via executeBatch() to get inserts done in bulk. I've been
> attempting to get this to work on 8.1.4 with the  8.2 503 driver.
> Despite my return type being void, this fails to execute returning a
> syntax error on "{" which is alluding to the "{call func(? . . ."
>
> Are there any known workarounds?

I've tried this myself and didn't get a syntax error.  However, invoking
stored functions in executeBatch currently does not work.  Even though
you use "call func" in your SQL command, the PostgreSQL JDBC driver maps
this to "select func" because that is what PG requires.  Now, even
though your stored function returns void, the result of a select is
always a ResultSet, empty in this case.  But that is enough to cause
executeBatch to generate an error complaining that no return value is
allowed.

I discussed with Kris Jurka a way to address this issue, but I haven't
had time to try his solution yet.

--
Guy Rouillier

Guy, Kris,

What was the suggested fix ?

Dave
On 30-Jun-06, at 12:37 PM, Guy Rouillier wrote:

> Yukino Miyazawa wrote:
>> I was wondering what the status is for getting CallableStatments to
>> run via executeBatch() to get inserts done in bulk. I've been
>> attempting to get this to work on 8.1.4 with the  8.2 503 driver.
>> Despite my return type being void, this fails to execute returning a
>> syntax error on "{" which is alluding to the "{call func(? . . ."
>>
>> Are there any known workarounds?
>
> I've tried this myself and didn't get a syntax error.  However,
> invoking
> stored functions in executeBatch currently does not work.  Even though
> you use "call func" in your SQL command, the PostgreSQL JDBC driver
> maps
> this to "select func" because that is what PG requires.  Now, even
> though your stored function returns void, the result of a select is
> always a ResultSet, empty in this case.  But that is enough to cause
> executeBatch to generate an error complaining that no return value is
> allowed.
>
> I discussed with Kris Jurka a way to address this issue, but I haven't
> had time to try his solution yet.
>
> --
> Guy Rouillier
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Mon, 5 Mar 2007, Dave Cramer wrote:

> What was the suggested fix ?

Simply to ignore the error if we're in a CallableStatement.

Kris Jurka