Thread: 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