Transact-SQL SQL Server 新增、修改、刪除、查詢 PostgreSQL Windows Edition 資料 (OLE DB Provider for ODBC: MSDASQL)

本文將介紹如何以 SQL Server 指令對 PostgreSQL Windows Edition 資料進行新增、修改、刪除、查詢等作業 (OLE DB Provider for ODBC: MSDASQL)。


測試環境

(此處將 SQL Server Express、PostgreSQL 安裝於同一台電腦)
Windows XP Professional SP3、SQL Server 2008 Express、PostgreSQL 9.2.1 Windows Edition (安裝步驟請參考此篇文章)


PostgreSQL 部分

於 postgres 資料庫中，執行下列指令以設定字元集、建立測試資料表：。

alter user postgres SET client_encoding to EUC_TW;
create table member (sn int, name varchar(50));

OLE DB、ODBC 設定

1) 至網路下載 psqlodbc_09_01_0200-1.zip，將其解壓縮後，執行 psqlodbc.msi 以安裝 PostgreSQL ODBC Driver。

2) 點選 開始 --> 設定 --> 控制台 --> 系統管理工具 --> 資料來源 (ODBC)

3) 出現 "ODBC 資料來源管理員" 視窗，點選 "系統資料來源名稱" 頁籤 --> 新增 。

4) 出現 "建立新資料來源" 視窗，選擇 "PostgreSQL" 後，按下 "完成" 。

5) 出現 "PostgreSQL ODBC Driver (psqlODBC) Setup" 視窗，輸入資料後，點選 "Save"。
(以下為筆者的設定)
Data Source：myPgODBC
Database：postgres
Server：localhost     (若欲連接至遠端資料庫伺服器，於此處輸入該伺服器 IP 位址。)
User Name：postgres
Port：5432
Password：您的 PostgreSQL 登入密碼 (須為 User Name 欄位所輸入帳號的密碼)

6) 點選 "確定" 以關閉 "ODBC 資料來源管理員" 視窗。


SQL Server 部分

1) 執行以下指令以建立 "連接的伺服器"

-- 建立遠端伺服器的連接設定 Postgre_ODBC
exec sp_addlinkedserver @server     = N'Postgre_ODBC', 
      @srvproduct = N'', 
      @provider   = N'MSDASQL', 
      @datasrc    = N'myPgODBC';
      

exec sp_addlinkedsrvlogin @rmtsrvname = 'Postgre_ODBC', @useself = 'FALSE', @rmtuser = 'postgres', @rmtpassword = 'PostgreSQL 密碼' ;
-- 建立 SQL Server 伺服器與遠端伺服器的登入對應 
exec sp_addlinkedsrvlogin @rmtsrvname  = N'Postgre_ODBC', 
        @locallogin  = N'sa', 
        @useself     = N'False', 
        @rmtuser     = N'postgres',
        @rmtpassword = N'PostgreSQL 密碼';
  
-- 當上述對應關係不存在時，此二伺服器間不建立其他連接。
exec sp_droplinkedsrvlogin @rmtsrvname = N'Postgre_ODBC', @locallogin = NULL ;
go 

2) 新增

insert openquery (Postgre_ODBC, 'select sn, name from member')   values (1, 'Tom Smith') ;
insert openquery (Postgre_ODBC, 'select sn, name from member')   values (2, 'Mary Smith') ;

3) 查詢

select * from openquery(Postgre_ODBC,'select * from member where name like ''%Mary%'' ');

4) 修改

update openquery (Postgre_ODBC, 'select name from member where sn = 1') 
   SET name = 'JJJ';

-- 檢查結果
select * from openquery(Postgre_ODBC,'select * from member order by sn');

5) 刪除

delete openquery (Postgre_ODBC, 'select * from member where sn = 1');

-- 檢查結果
select * from openquery(Postgre_ODBC,'select * from member order by sn');