Found the issue, user error. :)
On Nov 17, 1:06 pm, dman <[email protected]> wrote: > 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.
