;; -*- Mode: Lisp; Encoding: sjis; Last modified: "2009/03/18 02:24:13"; -*-

(require "ole")

(defun odbc-sqlite3mem()
  (interactive)
  (let ((conn)(rs))
    (setq conn (ole-create-object "ADODB.Connection"))
    (ole-method conn 'Open "driver=SQLite3 ODBC Driver;database=:memory:;")
    (ole-method conn 'Execute
                (concat
                 "CREATE TABLE IF NOT EXISTS "
		 "test (id INTEGER, goods TEXT, price INTEGER, PRIMARY KEY(id));"
                 ))
    (handler-case
        (progn
          (ole-method conn 'Execute "BEGIN;")
          (ole-method conn 'Execute "INSERT INTO test(goods, price) VALUES('HOGE', 50000);")
          (ole-method conn 'Execute "INSERT INTO test(goods, price) VALUES('ほげ', 12200);")
          (ole-method conn 'Execute "COMMIT;")
          )
      (error(c)
        (ole-method conn 'Execute "ROLLBACK;")
        (message-box (format nil "SQL実行エラー: ~a~%\n" (si:*condition-string c)))
        (return-from sqlite3mem nil)
        ))
    (setq rs (ole-method conn 'Execute "SELECT id, goods, price FROM test"))
    (with-output-to-temp-buffer ("*sqlite3mem*")
      (while (not #{rs.EOF})
        (format t "~D\t~A\t~:D\n"
                #{rs.Fields[0].Value}
                #{rs.Fields[1].Value}
                #{rs.Fields[2].Value})
        (ole-method rs 'MoveNext)))
    (ole-method conn 'Execute "DROP TABLE test;")
    (ole-method rs   'Close)
    (ole-method conn 'Close)
    ))