ActiveObjects and PostgreSQL - column names case sensitivity problems

[Tibor Hegyi [META-INF]]

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

[Tibor Hegyi [META-INF] · Accepted solution]

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...

[Jobin Kuruvilla [Go2Group]]

interesting! Thanks for sharing it..

[Pawel Zienkiewicz]

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.

[Adrian Moerchen]

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.

[Dmitry Miroshnichenko]

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

[Tuncay Senturk [Snapbytes]]

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

[Tibor Hegyi [META-INF]]

I think order("SORT_ORDER") is enough

[Tuncay Senturk [Snapbytes]]

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.

[Stanislav Bashkyrtsev]

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.