ODBCConn/ja

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │

データベースのポータル

参照: Databases 概要 ライブラリ フィールドの型 コントロール FAQ SqlDBHowto/ja TSQLQuery の利用 メモリ上で動作するデータベースの作り方 チュートリアル/練習となる記事: 概要 0 - データベースのセットアップ 1 - 開始 2 - 編集 3 - クエリ 4 - データモジュール SQLdb Programming Reference/ja 各種データベース Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

ODBCConn ユニットはOpen Database Connectivity (ODBC)データソースにSQLbの接続を実装している。

概要

ODBC

ODBC (Open Database Connectivity) とは単純なAPIである、ODBC APIを用いて、さまざまなデータベース全体に接続することのできる技術である。

さまざまなプラットフォームに向けて、ODBCの実装があり、ほとんどのデータベースマネジメントシステムにドライバがある。 公式のODBCドキュメンテーションはMSDN ODBC siteである。

TODBCConnection

FreePascalには odbcsql と odbcsqldyn ユニットが付属する。

TSQLConnection の派生、TODBCConnection はSQLDBフレームワークを用いるときにODBCによいOOPラッパを提供する。

LazarusではSQLdbコンポーネントタブで TODBCConnection が見つかる。 Uses句でODBCConnをコードに加えることによりTODBCConnectionを用いることもできる。

実装されているもの:

• クエリを実行し結果セットを取得する
• Blobを含むほとんどのフィールド型
• クエリのパラメータ(文字列と整数型)
• クエリを準備する
• UpdateIndexDefs (このためApplyUpdatesを用いることができる)

実装されていないもの:

• 適当なトランザクションサポート; 現在は各々の接続が1つのトランザクションに対応している
• いくつかのフィールド型
  • SQL_TYPE_UTC* (ODBCのドキュメントには触れられているが、実装はされていないようだ)
  • SQL_INTERVAL_* (どのTFieldTypeに対応するのだろうか?)
  • SQL_GUID (TGUIDFieldは近年まで実装されていなかった)

なぜ ODBC なのか ?

Free Pascalには、例えばMySQL、PostgreSQL、Firebird、Microsoft SQL Server (2.6.1以降)、Oracleなどのいくつかのデータベースに接続するためのコンポーネントが同梱されている。

これらのデータベースから漏れているもの、例えば MS Access そしておそらくMicrosoft SQL Serverのようなものにとって、ODBCは広く受け入れられ、よく確立したソリューションである (VB.Net/C#開発者はOLEDBもしくはODBCよりもSQLClientを最速のパフォーマンスのために用いることが薦められているが、LazarusのODBCは十分で、信頼性に足るものである)。

TODBCConnectionはアプリケーションに対するGLPではない厳格なMySQLのライセンスを回避して独自に開発され、MySQL ABには従わない。 FLOSS exception.

ODBC と ODBC ドライバをインストールする

ODBCを用いてデータベースに接続する前に、以下をインストールする必要がある

• ODBCドライバマネージャ
• 接続したいデータベースに対する特定のODBCドライバ

この節ではこれに関する仕方を概観するが、それぞれのドキュメントを参照されたい。

ODBC ドライバマネージャ

Windows

WindowsはODBCドライバマネージャが組み込まれており、それを用いてDSNをセットアップ、設定できる。これは近年のWindowsでは コントロールパネル の Windowsツール に移動している。もしくはスタートボタンを押し、ODBC と入力する(Windows 7ではこれは"管理ツール"で見つかる)

もちろん頻繁に使うのであればODBCダイアログをデスクトップのショートカットにしてもよい。

Unices

Unixベースのプラットフォームには2つのよく知られたODBCドライバマネージャがあり、それらは unixODBC と iODBC である。ODBCConn はunixODBCと動くことが知られており、iODBCの互換性はまだテストが必要である。

Debian

Debianでは unixodbc をインストールすることができる。

aptitude install unixodbc
aptitude install unixodbc-bin # もしGUIツールを望むなら

odbcsqldyn ユニットそして odbcconnはlibodbc.soというファイルを探す。 それはlibodbc.so.1 や libodbc.so.1.0.0といったファイル名を 許容しない。

Debianのunixodbcパッケージは/usr/lib/libodbc.soというシンボリックリンクを生成しないので、

• 自分自身でシンボリックリンク: ln -s libodbc.so.1 /usr/lib/libodbc.so を作るか、
• unixodbc-dev パッケージをインストールするそれはシンボリックリンクを生成してくれる。

もしunixodbc-binパッケージをインストールしたなら、ODBCConfigプログラムを実行してODBCドライバとDSNの設定ができる。

Ubuntu

UbuntuではDebianでのやり方に従うこと。 Note: unixodbc-bin パッケージはデフォルトのパッケージレポジトリからは得られないかもしれない。

ODBC Drivers

最新のODBCドライバは以下のリンクからダウンロードできる。訳注:以下にリンクがありますが、保存の時にはじかれましたので、https://は省いてあります

www.devart.com/odbc/sqlserver/download.html Download SQL Server 32/64 bit ODBC driver - latest version of SQL Server ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with SQL Server 2014\2012\2008 R2\ 2008\2005 (including Express edition), SQL Server 2000 (including MSDE), SQL Server 7, SQL Server Compact 4.0\3.5\3.1. Works on all Lazarus versions.

www.devart.com/odbc/oracle/download.html Download Oracle 32/64 bit ODBC driver - latest version of Oracle ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with Oracle servers: 12c, 11g, 10g, 9i, 8i, 8.0, including Oracle Express Edition 11g and 10g. Oracle Clients: 12c, 11g, 10g, 9i, 8i, 8.0. Works on all Lazarus versions.

www.devart.com/odbc/mysql/download.html Download MySQL 32/64 bit ODBC driver - latest version of MySQL ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with MySQL servers: 6.0, 5.6, 5.5, 5.1, 5.0, 4.1, 4.0, and 3.23. MariaDB 5.x Works on all Lazarus versions.

www.devart.com/odbc/postgresql/download.html Download PostgreSQL 32/64 bit ODBC driver - latest version of PostgreSQL ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with PostgreSQL server versions since 7.1 up to 9.4. Works on all Lazarus versions.

www.devart.com/odbc/postgresql/download.html Download SQLite 32/64 bit ODBC driver - latest version of SQLite ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with SQLite versions since 3.0 and higher. Works on all Lazarus versions.

www.devart.com/odbc/firebird/download.html Download Firebird 32/64 bit ODBC driver - latest version of Firebird ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with Firebird versions 3, 2.x, 1.x. Works on all Lazarus versions.

www.devart.com/odbc/interbase/download.html Download Interbase 32/64 bit ODBC driver - latest version of Interbase ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with all Interbase versions. Works on all Lazarus versions.

www.devart.com/odbc/sqlazure/download.html Download SQL Azure 32/64 bit ODBC driver - latest version of SQL Azure ODBC driver which supports Windows, Mac OS X, Linux both on 32 and 64 versions. Compatible with all SQL Azure versions. Works on all Lazarus versions.

ODBC データソースに接続する

ODBCデータソースに接続するためのパラメータは接続文字列と呼ばれる。 これはNAME=VALUE;NAME=VALUEの形の文字列である。

TODBCConnection はこの接続文字列に対するラッパを提供する。 接続文字列の中でいくつかのプロパティは名前-値の組で表される。独自のパラメータは Params プロパティ(それはTStringsである)で指定される。

このラッパに進む前に、どのようにODBCデータソースが認識されるか基本的な理解が必要となる。

DSN 経由で ODBC 接続

ODBCドライバマネージャはいくつかのパラメータに代わって、DSNショートカットを作る別の手段を提供する:

• DSN (DataSource Name): (固有の)名前によって識別されるシステム、もしくはユーザーによって定義されたデータソースである。DSNはODBCデータソースアドミニストレータを用いて、手動で odbc.ini ファイル(またはレジストリ)を編集することで設定できる。
• File DSN: 接続パラメータを含むファイルである。ODBCデータソースアドミニストレータはたいていGUIによってファイルDSNを作れるようにする。

DSNやファイルDSNは接続文字列に追加のパラメータを結合することができ、例えば、パスワードがあげられる。

DSNを設定することで、接続の細目がデータソースアドミニストレータのダイアログで確認することができ、そして名付けられたDSNが、のちの接続に用いるのに必要な唯一のものとなる。ゆえに接続の細目は、アプリケーションより 分離 されソフトウエアにおいてはDSN名だけが用いられる - DSNは仲介者としてふるまう。

名前DSNを用いる利点はODBCマネージャを用いて、コードを変えることなく、データベースを簡単に切り替えられる点にある。これは顧客の多くのデータを用いながら、バグテストを巣つために業務用の開発を行うときに便利である。

DSN なしで

またDSNなしでODBC経由で接続もでき、DSNで設定させる接続文字列(ドライバ、サーバ、データベース、ログイン情報)のすべての詳細を単に示してやればよい。この直接的方法はアプリケーションを新しい端末にインストールするときにDSNを設定する必要をなくす。

ODBCの仕様は接続文字列の中で用いられるようにいくつかのパラメータを定義している:

• 2つの特別なパラメータ DSN と FILEDSN は上述のあらかじめ定義されたパラメータを選択することを可能にする
• DRIVER はどのODBCドライバが用いられるか特定する。これは明らかに重要なパラメータである。
• UID と PWD はユーザー名とパスワードを供給するために用いられる。

その他のパラメータはドライバ固有のものである。 用いられるパラメータとその名前についての詳細を知るには、そのドライバのドキュメントを参照されたい。

TODBCConnection プロパティ

以下はODBC接続文字列に対する TODBCConnection プロパティで用いられるのマッピングを現した表である:

プロパティ	型	接続文字列パラメータ
Driver	string	DRIVER
DatabaseName	string	DSN, ODBC標準の一部である DATABASENAME, ではない
FileDSN	string	FILEDSN
Password	string	PWD
UserName	string	UID
HostName	string	none; ODBC標準にはないパラメータであるthere is no corresponding parameter in the ODBC standard
Params	TStrings	独自のパラメータとして用いること。それぞれの項目はNAME=VALUE型になってなければならない。 ここで用いられる1つの重要なパラメータは AUTOCOMMIT で、これはSQL文が直接実行される(ロールバックの可能性なしに)(デフォルト値、もしくは明示的に AUTOCOMMIT=1が用いられる) または手動で、StartTransaction、Commit、CommitRetainingまたはRollbackしなければならない (AUTOCOMMIT=0を用いた場合)

注意: TODBCConnection は必要な時には接続文字列中のエスケープパラメータを考慮してくれる。

LoginPrompt ブール値プロパティは未だに実装されていない。 正しいウインドウハンドルを見出して、ドライバがパラメータを特定するためのGUIダイアログを示すことができる。 これは接続文字列によって制御されないことに注意すること。しかし、SQLDriverConnectODAC API関数に対する最後のパラメータによる。

例

この節ではそれぞれ特定のODBCドライバを用いて、そのDBMSに接続する方法について例示する。

Excel に接続する

おそらくWindowsでのみ動く

このようなエラーを避けるために: トランザクションを開始できませんでした! ODBCエラー: LastReturnCode: SQL_ERROR; Record 1: SqlState: HYC00; NativeError: 106; Message: [Microsoft][ODBC Excel

... 以下のようにオートコミットを有効にしなければならない、

ODBCConnection1.Params.Add('AUTOCOMMIT=1');

MySQL に接続する

MyODBCドライバにより、サポートされるパラメータリファレンスに関しては、[1] を参照されたい。

加えてここを見ること www.devart.com/odbc/mysql/docs/using_odbc_driver.htm。MySQLにODBCドライバを用いて接続する詳細がある。

ドライバの名前はプラットフォームとMyODBCのバージョンによって変わるが、例としては:

 { properties of connection object, i.e. conn: TODBCConnection; }
 conn.Driver := 'MySQL'; // (Unix) 
 conn.Driver := 'MySQL ODBC 3.51 Driver';  // (Windows)
 conn.Driver := 'MySQL Connector/ODBC v5'; // (Windows)
 // 注意: ドライバ名は他に随所あるSQL 接続文字列のように{}を必要としない。

他のパラメータ (DSNを用いない場合) :

 conn.UserName := 'myUsername';
 conn.Password := 'myPassword';
 // conn.Params (TStrings) set using .add method :
 conn.Params.Add('server=example.com');
 conn.Params.Add('port=3306');
 conn.Params.Add('database=myDatabase');
 conn.Params.Add('charset=utf8');

MS Access に接続する

Please see MS Access.

Connecting to Microsoft SQL Server

  Note: FPC 2.6.0、Lazarusより新しいバージョンではMS SQL Server に直接接続をサポートしている。MSSQLを参照されたい

MS SQLのODBCドライバ接続の詳細と接続文字列の例は www.devart.com/odbc/sqlserver/docs/using_odbc_driver.htmを参照されたい。

www.connectionstrings.com/sql-server#p7 には接続文字列の詳細がある。

Microsoft SQL Server ODBC 接続文字列例:

Server=<server name>; // または<server name>\<instance name>
Database=<database name>;

Oracle に接続する

簡便で苦痛の少ない接続のためには www.devart.com/odbc/oracle/docs/using_odbc_driver.htm を参照されたい。

Oracle ODBC 接続文字列例:

Login Prompt=False;
Data Source=ORCL;
User ID=scott;
Password=tiger

PosgreSQLに接続する

簡便で苦痛の少ない接続のためには www.devart.com/odbc/postgresql/docs/using_odbc_driver.htm を参照されたい。

PostgreSQL ODBC 接続文字列例:

Login Prompt=False;
Data Source=localhost;
User ID=postgres;
Password=postgres;
Database=postgres;
Schema=public

SQLite に接続する

簡便で苦痛の少ない接続のためには www.devart.com/odbc/sqlite/docs/using_odbc_driver.htm を参照されたい。

SQLite ODBC 接続文字列例:

Login Prompt=False;
Database=c:\test.db3

Firebird に接続する

簡便で苦痛の少ない接続のためには www.devart.com/odbc/firebird/docs/using_odbc_driver.htm を参照されたい。

Firebird ODBC 接続文字列例:

Data Source=127.0.0.1;
User ID=sysdba;
Password=masterkey;
Client Library=fbclient.dll;
Database=c:\fbd.fdb

Named DSN

Windowsではあらかじめ存在するDNSでこのように書ける:

 { properties of connection object, i.e. conn: TODBCConnection; }
 conn.DatabaseName := '<Your_DSN_Name>';
 { Leave all other conn. properties empty }

DSN-なし

DSNなし:

 { properties of connection object, i.e. conn: TODBCConnection; }
 conn.Driver := 'SQL Server';
 // 注意: 随所にあるSQL接続文字列例と異なり、ドライバ名は{}を必要としない。
 { HostnameとDatabaseNameは空にしておく}
 { set conn.Params (TStrings) : }
 conn.Params.Add('Database=<yourdatabasename>');
 conn.Params.Add('Server=.\SQLEXPRESS');
 conn.Params.Add('Trusted_Connection=Yes'); // or 'Integrated Security=SSPI'

注意: この例では、インスタンスSQLEXPRESSでローカル端末(server=)に接続している。それに代わりホスト名を用いることができ、デフォルトインスタンスを省略できる。詳しくはMSのドキュメントを参照されたい。

信頼性のある接続(統合化セキュリティまたはSSPIとして知られる)はWindowsのユーザー認証でログインすることを意味する。Trusted_Connection lineを省略できるが、ユーザ-ID、(Uid=...)、パスワード(Pwd=...)は指定する必要がある。

DSN 例

この例ではMS SQL Serverテーブル'journal_entries'という名前のレコードを選択してMemo1という名前のMemoコントロールに'journal_entries'の列の値のすべてを表示する

// uses  ODBCConn, sqldb;
procedure TForm1.Button1Click(Sender: TObject);
var
  S: String;
  conn : TODBCConnection; // uses ODBCConn
  query: TSQLQuery;       // uses sqldb
  transaction: TSQLTransaction;   // uses sqldb

begin
  conn := TODBCCOnnection.Create(nil);
  query := TSQLQuery.Create(nil);
  transaction := TSQLTransaction.Create(nil);
  try
    try
      conn.DatabaseName := 'sqlserverdsn'; {replace this with your DSN, if you use any}
      conn.UserName:= 'sa'; //自身の名前に変えること
      conn.Password:= 'thepassword'; //自身のパスワードに変えることreplace with your password
      // DSNで定義するプロパティをオーバーライドできる
      //例えば、conn.Params.Add('Database=some_other_db');

      conn.Transaction := transaction;
      query.DataBase := conn;
      { "could not retrieve primary key metadata"を避けるため:
      - query.UsePrimaryKeyAsKey:=falseと
      (好ましくは)
      - query.PacketRecords to -1
      もしSQL Server, これに代わりSQL Native Client ODBC ドライバでMARSを有効にする。
      http://bugs.freepascal.org/view.php?id=13241を参照されたい。}
      query.PacketRecords:=-1; //retrieve all data at once
      query.SQL.Text := 'select journal_entry from journal_entries';
      query.Open;
      S := '';
      while not query.EOF do
      begin
        S := S + query.FieldByName('journal_entry').AsString + #13#10;
        query.Next;
      end;
    finally
      query.Free;
      conn.Free;
      transaction.Free;
    end;
  except
    on E: Exception do
      ShowMessage(E.message);
  end;
  Memo1.Text:= S;
end;

実際接続文字列のすべての部分にconn.Paramsを用いることができる。上記を改造すると、今回はSQL Serverバージョンがポップアップメッセージを出す:

  conn.Params.Add('Driver=SQL Server');
  conn.Params.Add('Server=OURBIGSERVER');
  conn.Params.Add('Database=TestDataBase');
  conn.Params.Add('Integrated Security=SSPI'); // use Windows logon credentials
  // conn.Transaction := transaction;以外の他の conn. プロパティはセットされない

  query.DataBase := conn;
  query.SQL.Text := 'select @@version';
  query.Open;
  showmessage(query.Fields[0].AsString); // Fields are numbered starting from 0

関数がサポートされる、例えば 'SELECT * FROM MyTestFunc()'- しかし、レコードセットを返すストアドプロシージャ問題が起きるかもしれない。 例えば、'EXEC MyStoredProcedure'がデータ行を返し、(SQL Server Management StudioやSqlCmd DOSコマンドC:\>sqlcmd -S MyServer -E -d MyDataBase -Q "exec MySP")、次いでquery.openを行うと以下のエラーが出る: "Cannot open a non-select statement" (おそらくFPC 2.6以降で修正される)

行のセットを返さないストアドプロシージャまたは他のSQLコマンド(CREATE,INSERT,UPDATE,DELETE)を呼ぶときには、query.Openの代わりに query.ExecSQLを用いること。

ストアドプロシージャパラメータは例えば'EXEC MySP @param1=23'で動く。

パラメータを用いることは、SQLインジェクションの脆弱性を回避するために推奨される。この場合、SQLステートメントは値を直接生み出さないが、指名された代理人が用いられる。そしてパラメータは別個に指定される。この方法を用いることはデータベースを改竄するための試みにおける、様々なフィールドへの悪意のあるSQLコマンドを挿入することが不可能となる。そしてサーバはステートメントのどの部分が値で何がコマンドであるかが区別がつくようになる。例えば、直接95を組み立てるWHERE句に代わって、指名されたパラメータ'testval'を用いることである。

 query.SQL.Text := 'select * from mytable where field2>:testval';
 query.Params.ParamByName('testval').AsString := '95';
 query.open;

ODBCデバッグとエラー

トランザクションの問題

Lazarus 1.2.4 (FPC 2.6.4)では ODBC トランザクションの振る舞いが変わった。transaction behaviour has changed. これは以前は動いていたコードが動かなくなる可能性を示唆する。

解決にはUser_Changes_2.6.4#TODBCConnection_.28odbcconn.29_No_longer_autocommitを参照されたい。

エラーメッセージ

ODBC APIはいずれもsuccessコードを返す。もしエラーがあったら、SQLGetDiagRecを呼ぶことでより詳細な情報が取得できる。(完全性のおかげで: APIコールがSQL_SUCCES_WITH_INFOも返すときに診断レコードが利用可能である)

TODBCConnectionは各種ODBCコールの戻り値を監視し、EODBCExceptionを構築する。 この例外のmessageは

• エラーが発生したときTODBCConnectionが何をしていたか特定するメッセージ
• ODBC APIコールの戻り値
• 診断レコードの数値(SQLGetDiagRecを使って得られる); 各々のレコードは3つのフィールドからなる:
  • エラーを特定する5文字のエラーコード
  • 'ネイティブエラーコード'
  • エラーを述べるメッセージ

ファンクションシークエンスエラー

もし、ODBCConnの最終セクションでファンクションシークエンスエラー('Function sequence error')が生じたら、おそらくすべてのクエリと接続が閉じていない。

Rollback が効かない

TSQLTransaction.Rollback

の問題に遭遇しているが、SQLはそれにもかかわらずコミットしているのには、ODBC接続はおそらくAUTOCOMMITモードになっている。これをどのようにして修正するには、the remarks about the AUTOCOMMIT connection parameter を参照されたい。

追跡

ほとんどのODBCマネージャは追跡ログへすべてのODBC APIコールのログを記録する、追跡オプションを持っている。 これはODBCアプリケーションををデバッグするときに非常に有用である。 Windows、unixODBCの双方のデータソースアドミニストレーション GUIは追跡オプションを設定するタブを有している。

もちろんODBC APIに詳しい開発者だけでなく、それは問題を特定するのに役立たせることもできる。また、バグトラッカーに問題を報告するにも追跡ログを活用することができる。

以下も参照のこと

• MS Access Using ODBC to access Microsoft Access databases