Обсуждение: sending a block through jdbc

I want to drop a constraint if it exists, and not get an error if it
doesn't exist,
ideally I'd get a jdbc error code, and silence the 'consrtaint does
not exist' exception,
but since postgress doesn't send error codes to jdbc, I'd like to put
the statement in
a block and silence the exception like this

    sb.append("begin \n");
    sb.append("alter table " + foreingKeyTable.name + " drop
constraint " + fkName + ";\n");
    sb.append("exception when true then \n");
    sb.append("end;");

 statement.execute(sb.toString)

It seems that this is either unsupported or I have bad syntax....

Any clues on ho to do this ?

On Fri, 30 Apr 2010, Maxime L?vesque wrote:

> I want to drop a constraint if it exists, and not get an error if it
> doesn't exist, ideally I'd get a jdbc error code, and silence the
> 'consrtaint does not exist' exception, but since postgress doesn't send
> error codes to jdbc, I'd like to put the statement in a block and
> silence the exception like this

PG sets SQLState in the SQLException since it is standardized and not
error code which is vendor specific.

>    sb.append("begin \n");
>    sb.append("alter table " + foreingKeyTable.name + " drop
> constraint " + fkName + ";\n");
>    sb.append("exception when true then \n");
>    sb.append("end;");
>
> It seems that this is either unsupported or I have bad syntax....

PG will only support anonymous blocks in the upcoming 9.0 release, and
then with a different syntax.

http://developer.postgresql.org/pgdocs/postgres/sql-do.html

Kris Jurka

On 1/05/2010 11:20 AM, Maxime Lévesque wrote:
> I want to drop a constraint if it exists, and not get an error if it
> doesn't exist

Check the system tables / INFORMATION SCHEMA to test if it exists before
dropping it.

> ideally I'd get a jdbc error code, and silence the 'consrtaint does
> not exist' exception,
> but since postgress doesn't send error codes to jdbc, I'd like to put
> the statement in

Yes, it does. As Kris noted, it's in SQLException. Use the "getSQLState"
method. It's often useful to test for prefixes.

http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html#JDBC209

http://www.postgresql.org/docs/current/interactive/errcodes-appendix.html


> a block and silence the exception like this
>
>      sb.append("begin \n");
>      sb.append("alter table " + foreingKeyTable.name + " drop
> constraint " + fkName + ";\n");
>      sb.append("exception when true then \n");

Exception handlers are a part of the PL/PgSQL language. They're not
supported in SQL, and don't make much sense there since there's no
procedural logic flow.

Kris pointed out that something similar is possible in 9.0. Consider
whether it's actually a good idea, though. DO blocks won't be cheap to
execute - especially compared to plain old SQL.

--
Craig Ringer