Persistent ODBCとOracle DatabseのDate型

Tech > Haskell > データベース

HaskellのPersistentを利用してOracleデータベースのDate型へアクセスする。

Date型とTIMESTAMP型

  • DATE型は、日付および時刻情報(年、時、秒)が格納されるとのこと、詳しくは(DATEデータ型 を参照)
  • TIMESTAMPデータ型、日付および時刻情報(年、時、秒、少数秒)が格納されDATE型の拡張となっている。

Persistent-ODBCから利用する

  • Haskell側のPersistentのモデル定義
OraMember
    regDate UTCTime Maybe
    deriving Typeable Show
  • データベース側テーブル
CREATE TABLE "DEVLOVE"."ORA_MEMBER" 
  ( "ID" NUMBER NOT NULL
  , "REG_DATE" DATE
  , PRIMARY KEY ("ID")
  );

CREATE SEQUENCE "seq_ORA_MEMBER_id";

SELECTする場合

SELECTする場合、 NLS_DATE_FORMAT をセッション中に一時的に変更しつつ、UTCTimeDiffTime 部分を 0~86399 で少数部は 0 に設定しパラメータに渡す。

alterNSLDateTimeSession :: MonadIO m => ReaderT SqlBackend m ()
alterNSLDateTimeSession = rawExecute "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'" []

selectDate :: IO ()
selectDate = do
  now <- getCurrentTime
  runSQLAction $ do
    alterNSLDateTimeSession -- ここ
    let baseQuery = E.from $ \oraMember -> do
          E.where_ $ oraMember E.^. OraMemberRegDate E.>=. (E.just $ E.val (UTCTime (utctDay now) 10.0)) -- ここ
          return oraMember
    list <- E.select baseQuery
    mapM_ (\v -> liftIO $ print $ show v) list
    return ()
ORA-01861 が発生する原因

このエラーは ORA-01861: リテラルが書式文字列と一致しません とのことで NLS_DATE_FORMAT が適切に設定されていない場合に発生する。

OraCom: SqlError {seState = "[\"HY000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"1861: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string\\n\"]"}

Oracleデータベース側のディフォルトの NLS_DATE_FORMAT の設定が会っていないことが原因

SQL> SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

DD-MON-RR になっているフォーマットが問題らしいので、セッションの間だけ alterNSLDateTimeSession 設定を変えて対応する。

ORA-01830 が発生する原因

このエラーは、 ORA-01830: 日付書式の変換で不要なデータが含まれています とのことで、 Haskellの UTCTime の値(精度)と データーベース側のDate型の精度と書式との関連で発生する。

getCurrentTime で取得した UTCTimeDiffTime 部は実際には、 Pico 型の精度 \(10^{-12}\) となっているため少数点以下を 0 に設定する必要がある。

OraCom: SqlError {seState = "[\"HY000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"1830: [Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string\\n\"]"}

解決方法は、 UTCTimeDiffTime 部を0に設定する。

UTCTime (utctDay now) 0
補足

SYSDATEは正常にINSERTが完了

INSERT INTO ORA_MEMBER (ID, REG_DATE) VALUES
  ( "seq_ORA_MEMBER_id".NEXTVAL
  , TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
  );

SYSTIMESTAMPは 以下の情報を持っているため ORA-01830 のエラーが発生

INSERT INTO ORA_MEMBER (ID, REG_DATE) VALUES
  ( "seq_ORA_MEMBER_id".NEXTVAL
  , TO_DATE(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
  );
エラー・レポート -
ORA-01830: 日付書式の変換で不要なデータが含まれています

SYSTIMESTAMPが秒以下の情報を含んでいる。

INSERTする場合

SELECT の時と同様に NLS_DATE_FORMAT を調整しINSERTする。

insertOraMember :: IO ()
insertOraMember = do
  runSQLAction $ do
    alterNSLDateTimeSession
    now <- liftIO getCurrentTime
    _ <- insert $ OraMember
      { oraMemberRegDate = Just now
      }

INSERTの場合、DiffTimeが原因でエラーになる事はない、注意するところは

oraMemberRegDate = Just now

これでINSERTされた場合、 2021-06-28 13:22:59 のように時、分、秒まで保存される

oraMemberRegDate = Just (UTCTime day 0.0)

これでINSERTされた場合、 2021-06-28 00:00:00 で登録される、秒まで指定したい場合、SELECTの時と同様に DiffTime 部で調整する。

モデルにDay型が使えない

Day型をつかえなのか? という素朴な疑問が沸くのですすが、 UTCTimeDay に変えて

OraMember
    regDate Day Maybe
    deriving Typeable Show

この場合、結果からいうと、 INSERTはどうにかなるSELECTで上手くいかない という結果にはまる

INSERT

モデルの Day型 に合わせて INSERT 部は以下の用になる

oraMemberRegDate = Just (ModifiedJulianDay 0)

ユリウス日 で指定するため ModifiedJulianDay を利用し 、モデルの型とマッチし、さらに PersistValue 型の PersistDay へ適合するのでデータベースへの保存は問題なく完了する、データの状態としては、時、分、秒のは0で保存される。

SELECT

その後にSELECTする場合、データベースの DATE型Persistent 経由で実行すると、 PersistValue 型の PersistUTCTime で取得されるため、モデルへのマッピングでランタイムエラーになる。

OraCom: PersistMarshalError "Couldn't parse field `regDate` from table `ORA_MEMBER`. Failed to parse Haskell type `Day`; expected day, integer, string or bytestring from database, but received: PersistUTCTime 1858-11-17 00:00:00 UTC. Potential solution: Check that your database schema matches your Persistent model definitions."

Day型 は、intergerstringbytestring でよこせ、受け取ったのは PersistUTCTime 1858-11-17 00:00:00 UTC じゃないか、お前のモデルを調整してどうにかしろ」 的なメッセージを受け取って不愉快になる。

この辺は、ライブラリでどうにかしろよと感じるところではありますが、 DATE型はUTCTimeで使うのが標準です と言われればそれまでとなりますので、ご注意ください。 以上、HaskellでPersistent ODBCを利用した場合の、日付型取扱い時の備忘録となります。

Posted on 2021-06-28 08:08:04

はじめまして

お茶の国静岡で、焼酎のお茶割なんか罰当たりで飲んだことはありません、常に一番搾りを嗜む静岡極東のBBQerです、最近まわりのエンジニアの方々がお料理を上手にやっている姿を恨めしそうに横目に見ながら、軟骨ピリ辛チクワを食べています、みなさんよろしく。

Posted

Amazon

tags

epub mobi calibre mask lens ワンライナー php redmine Linux Oracle Map OMap omap map BBQ カテゴリ管理 カテゴリ timestamp date oracle database string 麦焼酎 ダービー process 磨き蒸留 広井酒店、日本酒 芋焼酎 焼酎 ゆるキャン 広井酒店、日本酒池 spring framework java persistent spring session session spring hdbc-odbc persistent-odbc odbc day utctime スィート レマンの森 elm初期化 elm バイク xlr80 esqueleto database xl2tpd strongswan vpn l2tp ipsec 正月 ゲーム grub nginx systemctl portage 豚骨 圧力鍋 yesod-auth-hashdb yesod-auth yesod CentOS8 tsl ssl 設定 install gentoo ghci stackコマンド stack MathJax 数式 templete-haskell haskell とんこつラーメン プレビュー チャーシュウ python インストール CentOS7 ラーメン Houbou 置換タグ