SQLGrammerException : could not execute statement

Question

I am trying to alter a postgreSQL table using the following code.

String query = "ALTER TABLE \"georbis:world_boundaries\" DROP COLUMN testcolumn";
sessionFactory.openSession().createSQLQuery(query).executeUpdate();

The problem is my table name contains colon(:) due to which i'm getting the following error.

ERROR:  unterminated quoted identifier at or near ""georbis? DROP COLUMN testcolumn" at character 13
STATEMENT:  ALTER TABLE "georbis? DROP COLUMN testcolumn

Other answers of similar questions suggest that i should put double quotes(") around the table name which i tried but still getting the same error.

Also when i run the same query from SQL editor it works perfectly fine. I also tried to run the same query for a table that does not contain colon(:) and it worked .

Solution : Thanks everyone. Got the answer from HERE I changed my query like this and it worked.

String query = "ALTER TABLE \"georbis\\:world_boundaries\" DROP COLUMN testcolumn";

It seems Java parser gets `:word_boudaries` as a placeholder for a query parameter. Maybe try to escape semicolon (I don't use Java). — klin, Aug 24 '16 at 07:52
@Neelesh You may answer your own question. It is better to add solution part as an answer instead of giving it in question. And after 48 hours later you can accept your own answer. On the other hand, if your question is duplicate of that question, mark it as so. — Nuri Tasdemir, Aug 24 '16 at 09:53

score 2 · Answer 1 · edited May 23 '17 at 12:18

The problem is that ":" is a prompt for a named parameter. Executing the query directly in the ORM Session means the ORM will try to parse it and transform it.

Assuming you are using the latest Hibernate Session, you will need to execute the query at a lower level (jdbc connection), as this answer describes:

sessionFactory.openSession().doWork(
    new Work() {
        public void execute(Connection connection) throws SQLException 
        { 
            try(Statement stmt=connection.createStatement()){
                stmt.executeUpdate("ALTER TABLE \"georbis:world_boundaries\" DROP COLUMN testcolumn");
            }
        }
    }
);

That being said, are you absolutely sure you want to execute DDL from the ORM? There are other solutions out there. If your project is Maven, you can use SQL Maven plugin to put up then tear down SQL setups for testing.

SQLGrammerException : could not execute statement

1 Answers1