Hello,
I am working on a project where i need to I test my daos against an
embedded database, I decided to use H2 as it supports oracle syntax
right out of the box. I opted to use the embedded db and my datasource
is configured as so:
<entry key="jdbcurl" value="jdbc:h2:~/test" />
<entry key="driverclass" value="org.h2.Driver"
/>
The dao works using spring-jdbc and i create and connect to the db via
springs' singleconnectdatasource object. my db schema is like so:
CREATE TABLE AUDIT_ENTRY (
AUDIT_ENTRY_ID NUMBER NOT NULL PRIMARY KEY,
AUDIT_ENTRY_DATE DATE NOT NULL,
AUDIT_ENTRY_OUTCOME VARCHAR(256) NOT NULL,
ASSET_ID NUMBER NOT NULL REFERENCES ASSETS(ASSET_ID)
);
CREATE TABLE ASSETS (
ASSET_ID NUMBER NOT NULL PRIMARY KEY,
ASSET_CODE VARCHAR(256) NOT NULL UNIQUE,
DESCRIPTION VARCHAR(256) NOT NULL,
ASSET_CLASS VARCHAR(100) NOT NULL,
ASSET_TYPE VARCHAR(100) NOT NULL
);
Now, when i run a join query via jdbctemlate.query for object like so:
public static AuditEntry getAuditEntryById(JdbcTemplate template,
Long id) {
return template.queryForObject(RETRIEVE_AUDIT_ENTRY_USING_ID,
new
Object[] {id}, new AuditEntryRowMapper());
}
where RETRIEVE_AUDIT_ENTRY_USING_ID =
SELECT AE.AUDIT_ENTRY_ID, AE.AUDIT_ENTRY_DATE, AE.AUDIT_ENTRY_OUTCOME,
AE.ASSET_ID, A.ASSET_CODE, A.DESCRIPTION,
A.ASSET_CLASS, A.ASSET_TYPE
FROM AUDIT_ENTRY as AE INNER JOIN ASSETS as A ON AE.ASSET_ID =
A.ASSET_ID
WHERE AE.AUDIT_ENTRY_ID = ?;
i get the following error:
org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [SELECT AE.AUDIT_ENTRY_ID,
AE.AUDIT_ENTRY_DATE, AE.AUDIT_ENTRY_OUTCOME, AE.ASSET_ID,
A.ASSET_CODE, A.DESCRIPTION, A.ASSET_CLASS, A.ASSET_TYPE FROM
AUDIT_ENTRY as AE INNER JOIN ASSETS as A ON AE.ASSET_ID = A.ASSET_ID
WHERE AE.AUDIT_ENTRY_ID = ?]; nested exception is
org.h2.jdbc.JdbcSQLException: Column "AE.AUDIT_ENTRY_ID" not found
[42122-161]
but when i run the same query in the web console, it works fine. I
think i'm missing something but not quite sure what. Any ideas will be
much appreciated.
thanks
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
