hsqldb - 使用date型態時一直出現錯誤java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd...

參考：Chapter 10. Built In Functions

使用hsqldb+mybatis執行以下的sql cmd：

UPDATE TABLE      SET TRANS_NET_DIS=0,TRANS_SALES=0,TRANS_ADJ_DIS=0,TRANS_FEE=0,TRANS_DIS=0,TRANS_NET_SALES=0,TRANS_ADJ_SALES=0,TRANS_RECEIPT=0       WHERE STMT_DATE=to_date('20121230','yyyyMMdd') AND SEQE='00' AND MERCH='999160691' AND ORGG='822'

然後出現以下的錯誤：

### Cause: java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

at com.sun.proxy.$Proxy6.updateIsKeyData(Unknown Source)

at com.ctbc.transfile.sql.mybatis.UpdateIsKeyData.execute(UpdateIsKeyData.java:21)

at com.ctbc.transfile.sql.mybatis.UpdateIsKeyData.execute(UpdateIsKeyData.java:1)

at com.ctbc.util.sql.MyBatisUtil.execute(MyBatisUtil.java:42)

at com.ctbc.transfile.loadtask.check.KeyChecker.rmvAndUpdateKeyExistsBeans(KeyChecker.java:89)

at ut.com.ctbc.transfile.loadtask.check.KeyCheckerTest.testRmvKeyExistsBeans_Exists(KeyCheckerTest.java:211)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: java.sql.SQLDataException: data exception: invalid datetime format: yyyyMMdd

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)

at com.sun.proxy.$Proxy8.execute(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)

... 33 more

Caused by: org.hsqldb.HsqlException: data exception: invalid datetime format: yyyyMMdd

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.HsqlDateTime.toJavaDatePattern(Unknown Source)

at org.hsqldb.HsqlDateTime.toDate(Unknown Source)

at org.hsqldb.FunctionCustom.getValue(Unknown Source)

at org.hsqldb.FunctionSQL.getValue(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.getFirstRow(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.initialiseIterator(Unknown Source)

at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source)

at org.hsqldb.StatementDML.executeUpdateStatement(Unknown Source)

at org.hsqldb.StatementDML.getResult(Unknown Source)

at org.hsqldb.StatementDMQL.execute(Unknown Source)

at org.hsqldb.Session.executeCompiledStatement(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 47 more

後來找到hsqldb的官方說明，直接找到下方的Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements部分，可以看到允許的都是『大寫』，因此將上述執行的yyyyMMdd改為YYYYMMDD，就可以解決這個問題了．

hsqldb - 如何擴展沒有的FUNCTION以適應其他sql ?

參考：src_jrt_routines

寫Test的時候遇到一個問題，我想在我的SQL Query中使用 LEN()這個mssql function:

 
  SELECT LEN(str) FROM TABLE where name='123'

然而由於寫Test時使用的並非mssql而是使用hsqldb，hsqldb並不支援LEN()，
而是使用LENGTH()，如果將我的SQL Query改為 LENGTH()，在正式上線時又會遇到
MS-SQL並沒有LENGTH()的問題....因此該怎麼做比較好呢??

原來hsqldb有擴展function的機制，就是為了預防這個問題的產生：

1. create一個擴展的java class如下(注意function一定得為static，詳細使用對應的參數型別請參考上述參考頁)：

 
package util.extend.mssql;

public class MSSql {
 public static int len(String str) {
  return str.length();
 }
}

2. 並於實際執行有用到LEN的SQL Query的地方之前，使用下列SQL Query
   

    
         CREATE FUNCTION  len(str VARCHAR(20000))
           RETURNS INT 
    LANGUAGE JAVA DETERMINISTIC NO SQL
    EXTERNAL NAME 'CLASSPATH:util.extend.mssql.MSSql.len'
   

   
   
3. 以上若完成，執行test，應該就能成功囉

這機制真是太方便了！！！

p.s話說回來完全找不到相關的中文討論阿....是說寫Test的人真的很少還是高手都直接找英文了呢....0rz...網路上查到的方式Create alias也是過時的方式，害我花了不少時間找...