getSimpleJdbcTemplate().query() problem

[vilaca]

Hello,

When I execute the following code, on Oracle:

public static String QUERY_DATOS_LINEA="SELECT LIN_ID,LIN_IDF FROM CVI_LINEAS@SAE_DB";

List<DatosLinea> lista = getSimpleJdbcTemplate().query(QUERY_DATOS_LINEA, new ParameterizedRowMapper<DatosLinea>(){
			public DatosLinea mapRow(ResultSet rs, int rowNum) throws SQLException {
				DatosLinea lin = new DatosLinea();
				lin.setId(rs.getInt("LIN_ID"));
				lin.setIdf(rs.getString("LIN_IDF"));
				return lin;
			}

I get the following error:

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT LIN_ID,LIN_IDF FROM CVI_LINEAS@SAE_DB]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02041: client database did not begin a transaction
ORA-02063: preceding line from SAE_DB


When I execute the query directly on TOAD, it runs ok, but with the getSimpleJdbcTemplate().query method, it doesn't work.

I know the problem is in the '@SAE_DB' part, maybe it's not compatible with this method, but that's the only way I know to access the database, since it is in another server and I can't access directly from the dblink I was given. If I don't use it, it tells me that the table CVI_LINEAS does not exist.

Anyone can help? If you need more info, just ask.

PS: sorry for the lack of details, but I'm not used to work with Oracle

[Marten Deinum]

Judging from the stacktrace you are running without transactions and apparently this feature needs transactions. So configure transactions (even if they are readonly).

[vilaca]

I'm not the DB Admin. Is there any way to configure it directly in my code?

[Marten Deinum]

It has nothing to do with your db it has all to do with the absence of proper tx configuration in your application. I suggest a read of the spring reference guide.

[vilaca]

In my applicationContext.xml file I have the following configuration:

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>

I'm using the oracle.jdbc.driver.OracleDriver driver contained in the ojdbc6-11.2.0.3.jar

Shouldn't the transactions be managed automatically?

I'm I seeing it wrong?

[Marten Deinum]

Again read the reference guide... Only a transaction manager isn't enough. You only configured the how (transactionmanager) not the WHEN (transaction attributes/configuration).

[vilaca]

Can you please point me to any transaction management/configuration example? I've been reading the reference guide since this morning and still I can't figure it out.