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
Community moderators have prevented the ability to post new answers.
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you, SaM!
I created two issues:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes that's right
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Alright Will try that, too :)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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…
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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") )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Community moderators have prevented the ability to post new answers.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.