Mac De Oracle Heterogeneous! #18 Tweet
前回からのつづきです。
Generic Connectivity経由でMySQLにアクセスする際、日付型ではうまく検索できないというものだった。
データ型のマッピンングについて確認していこうと思うが、その前に、日付を条件に指定した検索ができないという問題を回避する方法を探してみることにした。
対象データを日付で絞り込もうとして where句に指定してみると正しく検索できない。
CORYDORAS> list
1 select
2 *
3 from
4 oracle_emp_mysql4026_win@oracle10g_win
5 where
6* "hiredate" >= to_date('19870101','yyyymmdd')
CORYDORAS> /
empno ename job mgr hiredate sal comm deptno
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
14行が選択されました。
経過: 00:00:00.10
CORYDORAS>
上記は、Windows XP Professionalに構築したMySQL4.0.26-nt.
その他のPlatformに構築したMySQLそれにPostgreSQLでも確認してみることにした。
PowerMac G5 Dual 2.7Ghz MacOSX Tiger Serverに構築した MySQL4.1.13a及び、PowerBookに構築した PostgreSQL7.4.9の日付型について試してみた。
今回は以下のような経路でアクセスした。
dump()関数で型を確認するのもいいがDATEかTIMESTAMPのいずれかなので以下のようなフォーマットを設定して区別しやすくしておく。
どちらの形式で表示されるかでマッピングされているのがdate型かtimestamp型なのかが判る。
CORYDORAS> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
セッションが変更されました。
CORYDORAS> alter session set nls_timestamp_format = 'yyyy/mm/dd hh24:mi:ss.ff';
セッションが変更されました。
CORYDORAS>
まずは、PowerMac G5 Dual 2.7Ghz MacOSX Tiger ServerのMySQL4.1.13aで検証した。以下のようなデータを用意。
r_datetime：datetime型、
r_date：date型、
r_timestamp：timestamp型
としてある。
マニュアルによればOracle側ではdate型にマップされることになっているようだ。（実際にはODBCドライバの実装に影響されるようだが。今回 Generic Connectivityで利用しているODBCドライバについてはこちらを参照してほしい。
全データを問い合わせてみると、全てNLS_DATE_FORMATに指定した書式で表示されているのでDATE型にマッピングされているのが判る。
CORYDORAS>
CORYDORAS> select * from date_test_mysql4113a_mac_sv@oracle10g_win;
r_datetime r_date r_timestamp
------------------- ------------------- -------------------
1990/01/01 00:00:00 2006/01/20 23:04:22
2000/12/01 00:00:00 2006/01/20 23:04:35
1800/01/01 00:00:00 2006/01/20 23:04:51
1810/12/01 00:00:00 2006/01/20 23:05:06
2002/12/01 00:00:00
2006/01/20 23:05:33
6行が選択されました。
では、早速、MySQLのデータを日付を条件として検索してみる。DATE型にマッピングされているようなので、to_date()関数を利用してDATE型同士で検索してみるが１件もヒットしない。
CORYDORAS> select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5 "r_datetime" <= to_date('19910101','yyyymmdd');
レコードが選択されませんでした。
文字列として比較させると該当するデータが返される。どうやらDATE型ではうまく検索できないようである。
CORYDORAS> select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5 "r_datetime" = '19900101';
r_datetime r_date r_timestamp
------------------- ------------------- -------------------
1990/01/01 00:00:00 2006/01/20 23:04:22
1行が選択されました。
暗黙型変換ではなく、to_char()により明示的に文字列に変換して比較してみると、やはり、正しい結果が得られた。
CORYDORAS> list
1 select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5* to_char("r_datetime",'yyyymmdd') >= '19900201'
CORYDORAS> /
r_datetime r_date r_timestamp
------------------- ------------------- -------------------
2000/12/01 00:00:00 2006/01/20 23:04:35
1行が選択されました。
MySQLのdate型、及び、timestamp型についても同様の結果だった。
CORYDORAS> select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5 "r_date" <= to_date('18100101','yyyymmdd');
レコードが選択されませんでした。
CORYDORAS> select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5 "r_date" <= '18100101';
r_datetime r_date r_timestamp
------------------- ------------------- -------------------
1800/01/01 00:00:00 2006/01/20 23:04:51
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5 "r_timestamp" <= to_date('20040101','yyyymmdd');
レコードが選択されませんでした。
CORYDORAS> list
1 select *
2 from
3 date_test_mysql4113a_mac_sv@oracle10g_win
4 where
5* "r_timestamp" <= '20040101'
CORYDORAS> /
r_datetime r_date r_timestamp
------------------- ------------------- -------------------
2002/12/01 00:00:00
1行が選択されました。
CORYDORAS>
MySQLでは、date型の検索に多少難ありという結果だったが、PostgreSQL7.4.9ではどうなるかも検証してみた。
PostgreSQL7.4.9では、r_dateをdate型、r_timestampをtimestamp型として定義した。問い合わせると、NLS_DATE_FORMATに指定した書式で表示されるので、これらの型もOracle側ではdate型にマッピングされていることが判る。
CORYDORAS>
CORYDORAS> select * from date_test_postgresql749_mac@oracle10g_win;
r_date r_timestamp
------------------- -------------------
1900/01/01 00:00:00
2006/01/20 00:00:00
1981/01/01 00:00:00
1990/12/01 00:00:00
4行が選択されました。
MySQLの時と同様に、to_date()関数によるdate型での検索、文字列（暗黙型変換）、to_char()関数による明示型変換それぞれについて検証してみた。結果は、どの方法でも正しく検索できた。
Genneric Connectivity経由のアクセスでは、（Oracleデータベースに慣れている方なら）違和感無く利用できる可能性が高いPostgreSQLのほうが楽だと感じる場面が多いかもしれない。（私感）
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 "r_date" <= to_date('19900101','yyyymmdd');
r_date r_timestamp
------------------- -------------------
1900/01/01 00:00:00
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 to_char("r_date",'yyyymmdd') <= '19900101';
r_date r_timestamp
------------------- -------------------
1900/01/01 00:00:00
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 "r_date" <= '19900101';
r_date r_timestamp
------------------- -------------------
1900/01/01 00:00:00
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 "r_timestamp" <= to_date('19820101');
r_date r_timestamp
------------------- -------------------
1981/01/01 00:00:00
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 to_char("r_timestamp",'yyyymmdd') <= '19820101';
r_date r_timestamp
------------------- -------------------
1981/01/01 00:00:00
1行が選択されました。
CORYDORAS> select *
2 from
3 date_test_postgresql749_mac@oracle10g_win
4 where
5 "r_timestamp" <= '19810101';
r_date r_timestamp
------------------- -------------------
1981/01/01 00:00:00
1行が選択されました。
CORYDORAS>
まとめ。
日付型に関して、Generic Connectivity経由(ODBCドライバ経由）の検索では以下のような点に注意する必要がある。
(各ODBCドライバがアップデートされると挙動が異なる可能性が高いので、実際に利用する際には事前に実機調査されることをお勧めする）
MySQLの日付型をGeneric Connectivity経由で検索する場合には、文字型に変換して検索する。
PostgreSQLの日付型をGenneric Connecvitity経由で検索する場合には、日付型でも文字型でも検索できるので特に注意する点はない。（但し、パフォーマンス面を考えると日付型で検索したほうがよいのではないか？。未検証なので想像ですが。）
次回は、Generic Connectivity経由でリンクされているMySQL4.0.x,4.1.x及びPostgreSQL7.4.9の時間型などについて、Oracle側ではどの型にマッピングされ、どのような”癖”があるのか見てみることにする。
| 固定リンク | 0
コメント