PSQLException: ERROR: column "qualifier" does not exist

[Michael Zronek]

Dear Atlassian Answers,

we are currently developing a plugin which utilized the JIRA intern Database and creates new tables/ columns etc.

When querying a column we get the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "qualifier" does not exist
  Position: 91
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
	at net.java.ao.EntityManager.find(EntityManager.java:671)
	at net.java.ao.EntityManager.find(EntityManager.java:621)
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:140)
	... 279 more

The reason for this is that the columns in JIRA are always saved in upper case letters ("QUALIFIER") while the query itself utilized lower case letters. Is there any workaround which helps in this case?

Best regards,

Mike

[Michael Zronek]

we use something like 

ActiveObjectImporterConfiguration importerConfiguration = ao.create(ActiveObjectImporterConfiguration.class);

which does not allow us to change the select/ create query. It always uses the lowercase variant.

We know that H2 and MySQL is not case sensitive, but our plugin also has to work for Postgres databases

[Claudio Kirchhoff]

That's odd, are you certain that it's a create() that triggers the exception?

[Michael Zronek]

It is actually the find who triggers the exception, you are right (we need to find the object first, eventually delete it and recreate it as an update mechanism, ao does not provide update functionality otherwise)

We fixed the problem now though, the reason where Missing Quotes in the following line:

Arrays.asList(ao.find(ActiveObjectImporterConfiguration.class, Query.select()
        .where("QUALIFIER=? AND IDENTIFIER=?",c...

which needed to be changed to

Arrays.asList(ao.find(ActiveObjectImporterConfiguration.class, Query.select()
        .where("\"QUALIFIER\"=? AND \"IDENTIFIER\"=?",c....

Furthermore we applied

@Accessor("COLUMN")
@Mutator("COLUMN")

fields to our application

[Claudio Kirchhoff]

Still a little strange, I wasn't required to apply quotes so far including when testing with postgres. Anyhow, glad you fixed it!

[Claudio Kirchhoff]

I'm guessing the query worked fine with the H2 DB and now that you're testing in an environment with a postgres DB - is that correct? That's because the latter is case sensitive in that regard while H2 is not and neither is MySQL by the way.

Use uppercase in your SQL query and you should be fine!