Jonathan Lalou's blog

Posts Tagged ‘SQLException’

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE … (… NUMBER]

Case

In a JDBC DAO, I execute a query to retrieve an object. I get this error:

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]

Stacktrace

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]
 at org.hsqldb.jdbc.Util.throwError(Unknown Source)
 at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
 at com.bnpp.pb.risklayer.services.dao.jdbc.JdbcPrimeRiskServerDaoUnitTest.getDataSet(JdbcPrimeRiskServerDaoUnitTest.java:57)
 at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:154)

Explanation and fix

My original DB is under Oracle, but my DBUnit tests works on HSQL. Yet, types NUMBER and VARCHAR2 are not available under HSQL, this is why the exception is raised.
To fixe the issue, rewrite your scripts, replacing NUMBER and VARCHAR2 with NUMERIC and VARCHAR.

Posted in en-US | Tags: DBunit, HSQL, JDBC, Oracle, SQLException | 1 Comment »

Operand type clash: java.util.Date is incompatible with DATETIME

Author: Jonathan Lalou

Case

With Mule ESB, I try to integrate an object of type java.util.Date into a column of type datetime in a Sybase DB table.
I get the following error:

 java.sql.SQLException: Operand type clash: java.util.Date is incompatible with DATETIME

Explanation – Fix

The stacktrace is explicit: java.util.Dates are not compatible with Sybase datetime, as Java’s Strings are not compatible with Sybase numeric(10, 3) for instance.
To fix the issue, you have to cast your java.util.Date as java.sql.Date or java.sql.Timestamp. The cast is very easy to perform:

private static java.sql.Timestamp javaDate2SqlTimestamp(java.util.Date javaDate){
final java.sql.Timestamp answer = new java.sql.Timestamp(javaDate.getTime());
return answer;
}

The method is quite similar for java.sql.Dates cast.

Posted in en-US | Tags: Java, Operand type clash, SQLException, Sybase | No Comments »

Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java.

Author: Jonathan Lalou

Context

An object is marshallized and sent on TibcoRV 8.1. An instance of Mule ESB 2.2.1 listens to TibcoRV, reads the message, unmarshalls the object and builds an SQL insert query.

The query is very simple:

insert into myTable(dateColumn, stringColumn) values(...)

It is executed in a Jdbc connector declared on Mule configuration file:

    <jdbc:connector name="jdbcConnector" dataSource-ref="mySybaseDB" pollingFrequency="1000">
        <jdbc:query key="writeTest"
                    value="INSERT INTO myTable(dateColumn, stringColumn)
                              VALUES(#[map-payload:myJavaDate],#[map-payload:myJavaString])"/>
    </jdbc:connector>

The DB is Sybase. The first column is of type datetime, the second one is varchar. The values are retrieved from a java.util.Date and a java.lang.String.

Case

When the query is run, I get the error:

java.sql.SQLException: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java.

(you may get a com.sybase.jdbc2.jdbc.SybSQLException instead of java.sql.SQLException)

Explanation – Fix

The error is related only to Sybase, and not to TibcoRV and Mule: by default, Sybase cannot manage Java Dates (java.util.Date). You have to start explicitly the Java services. To perform that:

login with an username owing the rights “sa_role“
run the SQL query:
```
sp_configure 'enable java', 1
```
the restart the Sybase server

Now it should work. A similar error may occur with JBoss.

Posted in en-US | Tags: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java, ESB, Java, JBoss, jdbc:connector, jdbc:query, Mule, SQL, SQLException, Sybase, SybSQLException, Tibco, TibcoRV | No Comments »

“Position” is a reserved word in SQL!

Author: Jonathan Lalou

Very interesting case. At firt, I spotted these errors:

SQL Error: -22, SQLState: S0002

and:

java.sql.SQLException: Table not found in statement

But here is the complete stacktrace:

"2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unsuccessful: create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position double, primary key (myOracleTableId))
2009-10-13 12:29:17,934 ERROR hbm2ddl.SchemaExport - Unexpected token: POSITION in statement [create table MY_ORACLE_TABLE (myOracleTableId integer not null, optimisticTimestamp timestamp not null, position]
(...)
2009-10-13 12:29:17,949 ERROR hbm2ddl.SchemaExport - Table not found: MY_ORACLE_TABLE in statement [alter table MY_ORACLE_TABLE]
2009-10-13 12:29:17,980 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:17,996 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,355 WARN util.JDBCExceptionReporter - SQL Error: -22, SQLState: S0002
2009-10-13 12:29:18,355 ERROR util.JDBCExceptionReporter - Table not found in statement [select (...) where myOracleTable0_.myOracleTableId=?]
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest-testFindmyOracleTableByPK_OK.xml
2009-10-13 12:29:18,371 DEBUG util.HibernatePersistenceTestCase - ++++++++++++++ Using Pattern HibernateMyOracleTableDAOUnitTest.xml
2009-10-13 12:29:18,387 DEBUG util.HibernatePersistenceTestCase - --------------- Using dataset : HibernateMyOracleTableDAOUnitTest.xml

org.hibernate.exception.SQLGrammarException: could not load an entity: [com.(...)myOracleTable#147]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:161)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:781)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:774)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
at $Proxy0.load(Unknown Source)
(...)HibernateMyOracleTableDAOUnitTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Table not found in statement [select (...) where myOracleTable0.myOracleTableId=?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 42 more"

I have helped you with red bold words… Can you see the issue?

Yes, indeed position is a reserved keyword in SQL! To fix this issue, you have no choice but to rename your column.

Posted in en-US | Tags: Hibernate, Java, Jonathan Lalou, JUnit, Oracle, position, SQL, SQLException | No Comments »

April 2024
S	M	T	W	T	F	S
« Sep
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30