2012年10月31日 星期三

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');