ActiveObjects and PostgreSQL - column names case sensitivity problems

Hi,

I'm developing a JIRA plugin in which I use ActiveObjects (using version 0.19.7). I have entities which I want to find using ActiveObjects queries. E.g. like this:

this.ao.find(MyEntity.class, Query.select().where("SOME_ATTRIBUTE=?", someAttributeValue);

I know that PostgreSQL handles column names case sensitively, I double checked that the database table has the physical column names in uppercase. Therefore I always use uppercase queries as in the example above.

Regardless of this, I always get the below exception:

Caused by: org.postgresql.util.PSQLException: ERROR: column "some_attribute" does not exist

Position: 55

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

at net.java.ao.EntityManager.find(EntityManager.java:729)

at net.java.ao.EntityManager.find(EntityManager.java:657)

at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:149)

Any idea how to overcome this with PostgreSQL?

Thank you,

Tibor

1 answer

1 accepted

15 votes
Accepted answer

If you phrase you query with spaces before and after the equation mark (=), then AO uses the correct column name in queries. So the correct query is (nopte the spaces before and after the = character):

this.ao.find(MyEntity.class, Query.select().where("SOME_ATTRIBUTE = ?", someAttributeValue);

Sad that I lost a few hours on this...

interesting! Thanks for sharing it..

I had similar problem with migration from local dev (mysql) to company dev environment (postgress). Exploding "QUERYCOLUMN=", with spaces solved this issue.

Thanks for tip.

Thanks for the answer, wasted a lot of time with this to.

We had the same issue,still not possible to do something like "lower(name) = ?" with PostgreSQL.

So I lost full day, but found solution.

My case is Postgresql 8, Jira 5.0.4. AO 0.18.2

In the following string column names should be in uppercase and also dont forget trailing spaces at the both sides of "=". If you want to use ORDER BY or other sql syntax you should escape them with quotes according to DB.

ao.find(AutofillerEntity.class,
" PROJECT = ? AND MASTERISSTYPE = ? ORDER BY \"ID\"", projectId, masterIssueTypeId);

Also i found that 0.18.5 and 0.19.7 versions dont give a good result. At my own opinion developers have broken something you can see those links:

https://ecosystem.atlassian.net/browse/AO-261
https://ecosystem.atlassian.net/browse/AO-321


also you can try version 0.22 and write here about results

Hi Tibor, adding spaces for equation works fine. However I am facing the same problem while ordering. For instance, here is my query ending with order statement and getting the same error for "SORT_ORDER" column .order("myTable.SORT_ORDER")); Any idea, how I can achieve this problem? Thanks Tuncay

I think order("SORT_ORDER") is enough

The SQL has join statements so that I needed to use alias. I'm gonna try to use uppercase of alias name although I do not give it a chance.

Is it possible to somehow configure HSQLDB to work in the same way as postgres for this case? So that such errors are found on early stages during automated tests run.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,523 views 15 21
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you