○ 날짜 컬럼을 문자(char, varchar)로 설계
문자로 설계한 날짜 컬럼에서 날짜형식으로 변경하면 쿼리 에러가 발생 함. (예 : 20120132, 20110a03 등)
○ 해결방법
- 데이터 입력시점에 정확한 날짜 데이터만 들어가도록 함
- 기존의 오류 데이터는 사용자정의 함수를 작성하여 데이터를 찾고, 변환 함
○ 테스트 테이블생성
- create table tmp_test(tmp_date varchar(8));
○ 테스트 데이터
insert into tmp_test VALUES('20120108');
insert into tmp_test VALUES('20120109');
insert into tmp_test VALUES('20120110');
insert into tmp_test VALUES('20120111');
insert into tmp_test VALUES('20120112');
insert into tmp_test VALUES('20120113');
insert into tmp_test VALUES('20120114');
insert into tmp_test VALUES('20120115');
insert into tmp_test VALUES('20120132');
○ 오류 확인(날짜 관련 함수 사용 불가), 어떤 데이타가 오류이지 확인불가
SELECT to_date(tmp_date,'yyyymmdd')+1 from tmp_test;
○ 사용자정의 함수 작성(isdate.java)
import java.sql.*;
public class isdate{
public static String chkDate(String strDate, String strDateFormat) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
conn = DriverManager.getConnection("jdbc:CUBRID:localhost:33000:demodb:::","","");
String sql="SELECT to_date('" + strDate + "', '" + strDateFormat + "')" ;
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rs.close();
stmt.close();
conn.close();
return "YES";
} catch ( SQLException e ) {
if ( conn != null ) conn.close();
return "NO";
} catch ( Exception e ) {
return "NO";
} finally {
if ( conn != null ) conn.close();
}
}
}
○ 자바 컴파일 및 등록
- 자바컴파일 : Javac isdate.java
- CUBRID에 등록 : loadjava demodb isdate.class
○ 사용자정의 펑션 생성
create function isdate( strDate varchar, strDateFormat varchar) return string
as language java
name 'isdate.chkDate(java.lang.String, java.lang.String) return java.lang.String';
○ 날짜검사 테스트
call isdate('20120131', 'YYYYMMDD') ;
call isdate('20120132', 'YYYYMMDD') ;
select isdate(tmp_date,'YYYYMMDD'), decode(isdate(tmp_date,'YYYYMMDD'), 'YES', tmp_date, '') as err_chk,tmp_date
from tmp_test ;