Tech

ODBCの仕組み(詳細編)ツールでの利用方法

ODBCの仕組みと利用方法について紹介します。
仕組みを知っていると問題があったときに対応できるようになります。

ODBCの利用手順

1.ODBCドライバのインストール
2.データソース(DSN)を作成
3.作成したDSNをツールで指定

ODBCの基本的な仕組み

LinuxでもODBC接続可能なものもありますが、一般的な利用ケースであるWindows OSに絞って説明します。

ODBCに対応したアプリケーションから、WindowsのODBCドライバマネージャを経由して
各データベースに対応したODBCドライバが呼び出され、データベースに接続します。

ODBCドライバとデータベースサーバの間は、通常TCP/IPのネットワーク通信が行われます。
データベースサーバのOSは、どんなOS(Windows, Linux ,… )でも接続可能です。

ODBCに対応したアプリケーション

・BIツール
Power BI/Tableau/Qlik Sense/MotionBoardなど

・ExcelやAccessなどWindowsアプリ

・汎用SQL開発ツール
A5:SQL Mk-2 など(https://a5m2.mmatsubara.com/)

ちなみにBIツールのデータベースの接続方法は、JDBC接続、ODBC接続、その他の接続方法があります。

ODBCドライバについて

データベースごとにODBCドライバのインストールが必要です。

64bit Windowsで 32bitのODBCドライバを使うことも可能です。

ODBC詳細32bit-64bit

・ODBCドライバには、32ビット版と64ビット版が存在する

・利用するデータベースのバージョンによってドライバが異なる

ODBCドライバには、32ビット版と64ビット版があります。
ODBCを使うアプリケーションが32ビット版か64ビット版かで決まります。
最近のアプリケーションの主流は64ビット版ですが、たまに32ビットという場合があります。

簡単な確認方法は、アプリケーションを起動して、Windowsの「タスクマネージャー」-「プロセス」でアプリケーション名の後ろに(32ビット)と表示されているかで判断できます。

下記の場合、「Microsoft OneDrive(32ビット)」は。32ビットのアプリケーションです。

・32ビットアプリケーションは32ビットODBCドライバが必要(x86というファイル名だったりします)

・64ビットアプリケーションは64ビットODBCドライバが必要

使っているデータベースによってはデータベースのバージョンも確認する必要があります。

PostgreSQLの場合、ドライバのダウンロードサイトには以下のようにバージョンごとにファイルが用意されています。

psqlodbc_12_02_0000-x64.zip :バージョン12の64ビットODBCドライバ
psqlodbc_12_02_0000-x86.zip :バージョン12の32ビットODBCドライバ

特に歴史の長いデータソースの場合は、互換性などの問題で適切なものを選ぶ必要があります。

ODBCドライバを設定する(DSNを作成)

ODBCドライバをインストール後に接続情報の設定が必要になります。
データソース(DSN)を作成する作業が必要です。

ODBCデータソース アドミニストレーターも32ビット版と64ビット版があります。
使うODBCドライバと合わせる必要があります。

接続するデータベースサーバのマシン名(IPアドレス)、接続するユーザ名、パスワードなどを設定します。

[スタート] ページで、「ODBC」と入力します。
“ODBC データ ソース(64ビット)” と “ODBC Data Sources(32-bit)”が選択肢として表示されます。

または、[スタート] – [Windows 管理ツール] - [ODBC データ ソース(64ビット)] を選択します。

選択すると「ODBCデータソース アドミニストレーター(64ビット)」が起動されます。

[追加]ボタンを押す

データソースを新規作成します。

セットアップするデータソースのドライバーを選択します。
以降は、データベースごとに設定が異なりますので、データベース専用の設定になります。
接続サーバのホスト名(IPアドレス)、接続DBユーザ、パスワードなどを設定します。

★PostgreSQLを選択時

★MariaDBを選択時

★SQLServerを選択時

ODBCデータソース アドミニストレーター

ODBCデータソース アドミニストレーターは、ODBCの接続情報を管理するものです。

DSN(データソース)という単位で管理します。

・ユーザDSN
Windowsユーザごとに設定する場合に利用。
ログインしているユーザのみが使用する場合は、ユーザDSNを使用する。
同じPCで複数のユーザアカウントで使用していた場合、他のユーザが登録したDSNは使用できません。

・システムDSN
利用しているPCのWindowsの全ユーザが利用可能なDSNになります。

・ファイルDSN
設定情報がファイルとして作成されます。
接続情報をファイルとしてコピーできるので、複数のPCに設定が可能です。
手軽だけどどセキュリティ面は考慮する必要があります。ファイルの漏洩など。

・ドライバー
インストール済みのドライバーが表示されます。
ドライバの種類やバージョンを確認できます。
接続予定のデータベースのドライバがインストールされていない場合、ODBCドライバをダウンロードしてインストールが必要です。

・トレース
何か問題があったときに設定し、ログファイルを見ると原因が分かる場合があります。
ただし、ODBC関数の呼び出しのトレースで、どの処理でエラーになったかが分かります。

各データベースのドライバ側にもログ機能があるので、それを有効にすると更に詳細をみることができます。
PostgreSQLの場合、[全体設定]でログ出力を指定可能です。

BIツールで利用

接続テスト用に新規に「MariaDB test」「PostgreSQL-test」「SQLServer-test」というDSNを作成。

Excelで利用

BIツールではありませんが、
ExcelからもODBC経由でデータベースへアクセスが可能です。
データベースから取得したデータをExcelのシートに読み込むことが可能です。


[データ]-[データを取得]-[その他のデータソースから(O)]-[ODBCから(D)] でデータソース名(DSN)が選択可能です。

PowerBIで利用

[データ]-[データを取得]-[詳細]-[データを取得]-[ODBC] でデータソース名(DSN)が選択可能です。

Microsoftのツールなので、操作方法はExcelと同じような操作です。

Tableauで利用

[その他]-[その他のデータベース([ODBC)] でデータソース名(DSN)かドライバを選択可能です。

実は有名なDBは、専用の接続ダイアログが用意されている

ツール側で専用の接続メニューが用意されている場合は、それを使っていいと思います。

使用メモリが少ないとか、処理が高速だとか、標準のODBCドライバではできないことがサポートされているようです。

じゃ、ODBCはいつ使う?

・ツールが提供している接続設定ではうまく動かない
・専用の接続メニューが用意されていないデータベースに接続する必要がある
・新しいバージョンのデータベースにツールの接続設定が対応していない

PowerBI でPostgreSQLを使う場合


Tableau でPostgreSQLを使う場合

まとめ

ODBC自体は古いAPIでODBCを使ってプログラミングすることはないですが、仕組みを知っているとデータベースの接続で何か問題あったときに対応できるようになります。

ドライバが古いから最新版に入れ替えてみようとか、ODBCの設定を変更してみようとか。

JDBC/ODBCとは! DB接続の役割は?データベース(以下、DB)に接続する際、JDBCまたはODBCという単語が登場します。 BIツール(Power BI/Tablea...