ActiveObjects with Oracle Database

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2012

I created an plugin for Confluence 3.5 which uses ActiveObjects (0.19.9).

The plugin worked fine on development hsql database and on a MySQL test system. But it did not work on any Oracle database (we tried 10g and 11g).

Some queries were executed without errors, but simple queries on a specific Objects failed with the following error:

2012-08-23 18:18:39,859 DEBUG [http-8090-6] [net.java.ao.sql] onSql SELECT * FROM "AO_321B13_TOPIC" ORDER BY "CREATION_DATE" DESC
2012-08-23 18:18:39,878 ERROR [http-8090-6] [plugin.macro.TopicListMacro] execute exception while processing macro occured.
 -- referer: http://192.168.100.153:8090/dashboard.action | url: /display/DK/Home | userName: admin
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Oracle
	- version:Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
	- minor version:2
	- major version:10
Driver:
	- name:Oracle JDBC driver
	- version:10.2.0.1.0

java.sql.SQLException: Invalid column name

Full stack trace as attachment. (oracle-error.txt)

I got the same error in Oracle 11g.

The class which is selected in the query Topic.java

@Preload
@Table("TOPIC")
@Implementation(TopicImpl.class)
public interface Topic extends RawEntity<Integer> {

	@AutoIncrement
	@NotNull
	@PrimaryKey("TOPIC_PK")
	Integer getTopicPk();

	@Ignore
	Integer getID();

	@Indexed
	String getTitle();
	void setTitle(String title);

	@OneToOne
	MainResponsiblePerson getMainResponsiblePerson();

	@OneToMany
	public ResponsiblePerson[] getResponsiblePersons();

	@Indexed
	int getPriority();
	void setPriority(final int priority);

	@OneToMany
	Category[] getCategories();

	@OneToMany
	Activity[] getActivities();

	@Indexed
	Long getConfluencePageId();
	void setConfluencePageId(final Long confluencePageId);

	@Indexed
	Date getCreationDate();
	void setCreationDate(final Date creationDate);

	@Indexed
	Date getModificationDate();
	void setModificationDate(final Date modificationDate);
}

My guess is, that the relations of this class to others (OneToMany/OneToOne) are troublesome with Oracle. But I can't resolve this problem.

I would be glad about a hint how to solve this or maybe some explanation what the reason for this behaviour is. I couldn't find any known issues with ActiveObjects and Oracle which relate to this problem.

Many Thanks!

Theresa

11 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

0 votes
Answer accepted
resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 2, 2012

Yeah, I solved it.

I try to explain for a simple example with a query over one single table. But actually it will only cause a problem when you query over more tables.

I wanted to create a query, like that:

SELECT AO_000000_TOPIC.TOPIC_PK
FROM AO_000000_TOPIC;

Since I can't know the exact table name beforehand, I used the TableNameConverter to resolve the table name and created the query. This should work with ActiveObjects query like that:

ao.find(Topic.class, Query.select(topicTableName + ".TOPIC_PK"));

HSQL and MySQL work perfectly with that, but not Oracle - or is the fault with ActiveObjects?.

The created query looks like that:

SELECT "AO_321B13_ACTIVITY.TOPIC_PK" FROM "AO_321B13_TOPIC"

And Oracle wants a query like that:

SELECT "AO_321B13_ACTIVITY"."TOPIC_PK" FROM "AO_321B13_TOPIC"

I was lucky that I could just remove the table name from the select clause (I kept table names in where clause, however). That worked for me, but I expect this to be a problem if columns in different tables have the same name. At least the ID column, right?

So my final workaround is: Use unique column names over ALL tables ;)

Thanks for all your advices, you helped me a lot to corner the problem!

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 2, 2012

But still not sure why ActiveObjects debug message showed not the SQL query, which was actually executed. It logged

2012-08-23 18:18:39,859 DEBUG [http-8090-6] [net.java.ao.sql] onSql SELECT * FROM "AO_321B13_TOPIC" ORDER BY "CREATION_DATE" DESC

When the actual query would be something like

SELECT "AO_321B13_TOPIC.TOPIC_PK"
FROM "AO_321B13_TOPIC" 
ORDER BY "CREATION_DATE" DESC

Maybe the cause for the error would have been more obvious.

Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 3, 2012

FYI, the qeury object has an alias method where you can easily set alias for table names in the query, the method takes 2 arguments IIRC, the name of the alias and the class/interface representing the table name.

As for the SQL logging this is weird indeed. Probably a good idea to log an issue against the AO project, https://ecosystem.atlassian.net/browse/AO.

Thanks.

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 3, 2012

Hi SaM,

A table name alias itself wouldn't fix the problem with oracle. It seems to me the escaping of the table and column name combination is a little odd for oracle and should probably handled differently by ActiveObjects.

I tried

Query query = Query.select("TOPIC.TOPIC_PK").alias(Topic.class, "TOPIC");

But the resulting query had the same problem with the escaping in Oracle as mentioned above.

As for raising an issue on https://ecosystem.atlassian.net/browse/AO , I created an account there, but I can't choose ActiveObjects as target project.

But I created a minified version of my AO test plugin on https://bitbucket.org/resah/ao-oracle-test/src

I guess for the future I will create testing profiles like you mentioned at https://answers.atlassian.com/questions/20954/unit-testing-active-objects-for-multiple-databases

Thanks

Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 3, 2012

Ok it looks like the first issue deserves some attention, and probably a bug filed as well. This is odd as I was sure the aliasing was tested against all databases…

I should have fixed the rights of the AO project, you should now be able to create issues.

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 3, 2012
1 vote
Andy Brook [Plugin People]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

I think your accessors are too long. AO converts 'getMainResponsiblePerson' to MAIN_RESPONSIBLE_PERSON which is 23chars, but is prefixed by your plugin key AO_ABC123_ that exceeds 30chars http://stackoverflow.com/questions/756558/what-is-the-maximum-length-of-a-table-name-in-oracle , probably.

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Ah sorry, I forgot to mention, that I set the table name annotation, like that

@Preload
@Table("MAIN_PERSON")
@Implementation(MainResponsiblePersonImpl.class)
public interface MainResponsiblePerson extends RawEntity<Integer> {
...
}

The other entities have similar short name. So table names shouldn't be a problem, is there a similar problem with column names, too?

Andy Brook [Plugin People]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Yes that's right

0 votes
resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Alright Will try that, too :)

0 votes
Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 29, 2012

If the query worked fine in the Oracle SQL client by copying as is, then I'd suspect the problem might be coming from the JDBC driver, maybe… What JDBC driver are you using?

Actually might be good idea to try to run that _same_ query through JDBC directly (removing AO from the equation) see what happens…

0 votes
resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

The query - as logged by ActiveObjects - worked fine in Oracle SQL client.

I will try to shorten column names and see what I can trim from the classes. I will write how that worked out.

Thanks so far for answering.

0 votes
Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 29, 2012

Well this seems strange.

Have you tried running the query in your Oracle SQL client or DB visualizer for example? Does it yield the same result? Does the table definition read from there match the one issued by the AO SQL logs?

Also as Andy suggest you might want to trim down you table to the bear minium (just that creation date column?) and issue the query, and then add columns one by one… see which one might be actual culprit.

0 votes
resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

All tables were generated as expected:

2012-08-23 18:18:03,522 DEBUG [http-8090-6] [net.java.ao.sql] onSql CREATE TABLE "AO_321B13_TOPIC" (
    "CONFLUENCE_PAGE_ID" NUMBER(20),
    "CREATION_DATE" TIMESTAMP,
    "MODIFICATION_DATE" TIMESTAMP,
    "PRIORITY" NUMBER(11) DEFAULT 0,
    "TITLE" VARCHAR(255),
    "TOPIC_PK" NUMBER(11) NOT NULL,
    PRIMARY KEY("TOPIC_PK")
)

0 votes
Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 29, 2012

Sorry Theresa, I must be tired this morning…

The query looks ok to me. What does your table definition looks like? I.e. what does the table that AO created looks like? Is the CREATION_DATE column present?

0 votes
resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Hi SaM,

SQL logging was already enabled: The error occured as mentioned above with following query:

2012-08-23 18:18:39,859 DEBUG [http-8090-6] [net.java.ao.sql] onSql SELECT * FROM "AO_321B13_TOPIC" ORDER BY "CREATION_DATE" DESC

I could execute this query in SQL console of oracle without problem, though I am not sure this is the actual query as generated by ActiveObjects and executed by Oracle database.

0 votes
Samuel Le Berrigaud
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 29, 2012

Hey Theresa, any chance you can reproduce the issue with SQL logging enabled? https://developer.atlassian.com/display/AO/Enabling+SQL+logging

This might help understand what's going on and which column is to blame.

0 votes
Martin Seibert
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Andy: Do you mean "Yes that's right. There is a similar problem with column names." Or do you mean "Yes that's right. You shouldn't have a problem."

Is there any hint you can give us on how to proceed from here on?

Andy Brook [Plugin People]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Heh, I mean column names are limited in size at a db level, meaning you either rename your interface accessors or annotate. See https://developer.atlassian.com/display/AO/Column+names for more.

Andy Brook [Plugin People]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Possibly, in any case, locating the problem is achieved easily enough by just commenting out half the methods, deploying to check its still present and repeating or if no problem is found, re-enable more. Once you know the field things should be more clear

resah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 29, 2012

Hi Andy,

I will try and shorten these column names. But since getters annotated with @OneToOne and @OneToMany are not translated to actual database columns, I didn't think this would be the problem.

Brendan Patterson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 31, 2012

I would try running the query through DBVisualizer rather than using "Oracle SQL client". It's a better approximation of the ActiveObject queries being run against Oracle and will exercise the jdbc driver you're using in the plugin. I think there is at least a chance that will be illuminating. It's also a great tool to know. That's all I've got. You're in the best hands with SaM and Andy.

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events