# TDS Foreign data wrapper * **Author:** Geoff Montee * **Name:** tds_fdw * **File:** tds_fdw/README.md ## About This is a [PostgreSQL foreign data wrapper](http://wiki.postgresql.org/wiki/Foreign_data_wrappers) that can connect to databases that use the [Tabular Data Stream (TDS) protocol](http://en.wikipedia.org/wiki/Tabular_Data_Stream), such as Sybase databases and Microsoft SQL server. This foreign data wrapper requires requires a library that uses the DB-Library interface, such as [FreeTDS](http://www.freetds.org). This has been tested with FreeTDS, but not the proprietary implementations of DB-Library. This was written to support PostgreSQL 9.1 and 9.2. It does not yet support write operations, as added in PostgreSQL 9.3. However, it should still support read operations in PostgreSQL 9.3. ## Building on CentOS 6 Building was accomplished by doing the following under CentOS 6. Other Linux platforms should be similar. ### Install EPEL In CentOS, you need the [EPEL repository installed](https://fedoraproject.org/wiki/EPEL) to install FreeTDS. ```bash wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm sudo rpm -ivh epel-release-6-8.noarch.rpm ``` ### Install FreeTDS ```bash sudo yum install freetds freetds-devel ``` ### Build for PostgreSQL 9.1 #### Install PostgreSQL 9.1 Install PostgreSQL 9.1 via [yum](https://wiki.postgresql.org/wiki/YUM_Installation). ```bash wget http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm sudo rpm -ivh pgdg-centos91-9.1-4.noarch.rpm sudo yum install postgresql91 postgresql91-server postgresql91-libs postgresql91-devel ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw PATH=/usr/pgsql-9.1/bin:$PATH make USE_PGXS=1 sudo PATH=/usr/pgsql-9.1/bin:$PATH make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql-9.1 initdb sudo /etc/init.d/postgresql-9.1 start /usr/pgsql-9.1/bin/psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ### Build for PostgreSQL 9.2 #### Install PostgreSQL 9.2 Install PostgreSQL 9.2 via [yum](https://wiki.postgresql.org/wiki/YUM_Installation). ```bash wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm sudo rpm -ivh pgdg-centos92-9.2-6.noarch.rpm sudo yum install postgresql92 postgresql92-server postgresql92-libs postgresql92-devel ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw PATH=/usr/pgsql-9.2/bin:$PATH make USE_PGXS=1 sudo PATH=/usr/pgsql-9.2/bin:$PATH make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql-9.2 initdb sudo /etc/init.d/postgresql-9.2 start /usr/pgsql-9.2/bin/psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ### Build for PostgreSQL 9.3 #### Install PostgreSQL 9.3 Install PostgreSQL 9.3 via [yum](https://wiki.postgresql.org/wiki/YUM_Installation). ```bash wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm sudo rpm -ivh pgdg-centos93-9.3-1.noarch.rpm sudo yum install postgresql93 postgresql93-server postgresql93-libs postgresql93-devel ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw PATH=/usr/pgsql-9.3/bin:$PATH make USE_PGXS=1 sudo PATH=/usr/pgsql-9.3/bin:$PATH make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql-9.3 initdb sudo /etc/init.d/postgresql-9.3 start /usr/pgsql-9.3/bin/psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ## Building on Ubuntu Building was accomplished by doing the following under Ubuntu 12.04. Other Ubuntu distributions should work too. ### Install FreeTDS ```bash sudo apt-get install libsybdb5 freetds-dev freetds-common ``` ### Build for PostgreSQL 9.1 #### Install PostgreSQL 9.1 Install PostgreSQL 9.1 via [apt](https://wiki.postgresql.org/wiki/Apt). ```bash sudo bash -c "echo \"deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main\" > /etc/apt/sources.list.d/pgdg.list" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get upgrade sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-server-dev-9.1 ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw make USE_PGXS=1 make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql start psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ### Build for PostgreSQL 9.2 #### Install PostgreSQL 9.2 Install PostgreSQL 9.2 via [apt](https://wiki.postgresql.org/wiki/Apt). ```bash sudo bash -c "echo \"deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main\" > /etc/apt/sources.list.d/pgdg.list" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get upgrade sudo apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-server-dev-9.2 ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw make USE_PGXS=1 make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql start psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ### Build for PostgreSQL 9.3 #### Install PostgreSQL 9.3 Install PostgreSQL 9.3 via [apt](https://wiki.postgresql.org/wiki/Apt). ```bash sudo bash -c "echo \"deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main\" > /etc/apt/sources.list.d/pgdg.list" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get upgrade sudo apt-get install postgresql-9.3 postgresql-client-9.3 postgresql-server-dev-9.3 ``` #### Clone and build ```bash git clone https://github.com/GeoffMontee/tds_fdw.git cd tds_fdw make USE_PGXS=1 make USE_PGXS=1 install ``` #### Start server and install extension ```bash sudo /etc/init.d/postgresql start psql -U postgres postgres=# CREATE EXTENSION tds_fdw; ``` ## Usage The usage of tds_fdw is similar to [mysql_fdw](https://github.com/dpage/mysql_fdw). ### Foreign server Foreign server parameters accepted: * *servername* Required: Yes Default: 127.0.0.1 The servername, address or hostname of the foreign server server. This can be a DSN, as specified in *freetds.conf*. See [FreeTDS name lookup](http://www.freetds.org/userguide/name.lookup.htm). * *port* Required: No The port of the foreign server. This is optional. Instead of providing a port here, it can be specified in *freetds.conf* (if *servername* is a DSN). * *database* Required: No The database to connect to for this server. * *dbuse* Required: No Default: 0 This option tells tds_fdw to connect directly to *database* if *dbuse* is 0. If *dbuse* is not 0, tds_fdw will connect to the server's default database, and then select *database* by calling DB-Library's dbuse() function. For Azure, *dbuse* currently needs to be set to 0. * *language* Required: No The language to use for messages and the locale to use for date formats. FreeTDS may default to *us_english* on most systems. You can probably also change this in *freetds.conf*. For information related to this for MS SQL Server, see [SET LANGUAGE in MS SQL Server](http://technet.microsoft.com/en-us/library/ms174398.aspx). For information related to Sybase ASE, see [Sybase ASE login options](http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1570/html/sqlug/X68290.htm) and [SET LANGUAGE in Sybase ASE](http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1572/html/commands/X64136.htm). * *character_set* Required: No The client character set to use for the connection, if you need to set this for some reason. For TDS protocol versions 7.0+, the connection always uses UCS-2, so this parameter does nothing in those cases. See [Localization and TDS 7.0](http://www.freetds.org/userguide/localization.htm). * *tds_version* Required: No The version of the TDS protocol to use for this server. See [Choosing a TDS protocol version](http://www.freetds.org/userguide/choosingtdsprotocol.htm) and [History of TDS Versions](http://www.freetds.org/userguide/tdshistory.htm). #### Foreign server example ```SQL CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test'); ``` ### Foreign table Foreign table parameters accepted: * *database* Required: No The database name that the foreign table is a part of. If specified, the foreign tables database is always selected with *dbuse()* after connecting. Do not use this with Azure. This option might eventually be phased out and replaced with the *database* option supplied to the foreign server. * *query* Required: Yes (mutually exclusive with *table*) The query string to use to query the foreign table. * *table* Required: Yes (mutually exclusive with *query*) The table on the foreign server to query. * *row_estimate_method* Required: No Default: `execute` This can be one of the following values: * `execute`: Execute the query on the remote server, and get the actual number of rows in the query. * `showplan_all`: This gets the estimated number of rows using [MS SQL Server's SET SHOWPLAN_ALL](https://msdn.microsoft.com/en-us/library/ms187735.aspx). #### Foreign table example Using a *table* definition: ```SQL CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_svr OPTIONS (table 'dbo.mytable', row_estimate_method 'showplan_all'); ``` Or using a *query* definition: ```SQL CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_svr OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all'); ``` ### User mapping User mapping parameters accepted: * *username* Required: Yes The username of the account on the foreign server. * *password* Required: Yes The password of the account on the foreign server. #### User mapping example ```SQL CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'sa', password ''); ``` ## Notes about character sets/encoding 1. If you get an error like this with MS SQL Server when working with Unicode data: > NOTICE: DB-Library notice: Msg #: 4004, Msg state: 1, Msg: Unicode data in a Unicode-only > collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC > version 3.7 or earlier., Server: PILLIUM\SQLEXPRESS, Process: , Line: 1, Level: 16 > ERROR: DB-Library error: DB #: 4004, DB Msg: General SQL Server error: Check messages from > the SQL Server, OS #: -1, OS Msg: (null), Level: 16 You may have to manually set *tds version* in *freetds.conf* to 7.0 or higher. See [The *freetds.conf* File](http://www.freetds.org/userguide/freetdsconf.htm). and [Choosing a TDS protocol version](http://www.freetds.org/userguide/choosingtdsprotocol.htm). 2. Although many newer versions of the TDS protocol will only use USC-2 to communicate with the server, FreeTDS converts the UCS-2 to the client character set of your choice. To set the client character set, you can set *client charset* in *freetds.conf*. See [The *freetds.conf* File](http://www.freetds.org/userguide/freetdsconf.htm) and [Localization and TDS 7.0](http://www.freetds.org/userguide/localization.htm). ## Support If you find any bugs, or you would like to request enhancements, please submit your comments on the [project's GitHub Issues page](https://github.com/GeoffMontee/tds_fdw/issues). Additionally, I do subscribe to several [PostgreSQL mailing lists](http://www.postgresql.org/list/) including *pgsql-general* and *pgsql-hackers*. If tds_fdw is mentioned in an email sent to one of those lists, I typically see it.