Re: SQL 2014 Support

To expose the bug requires using dblib. I found the bug by using tds_fdw to
connect PostgreSQL to MS SQL Server, so it's not trivial to set up. I've
looked around for a dblib-based client that I could use. Perl has one, but
I couldn't get it to compile. Following are instructions to follow what I
did to set up PostgreSQL to use FreeTDS to select from MS SQL Server. I
used SQL Server 2008 R2, PostgreSQL 9.4, and CentOS 7 as the PostgreSQL
host OS.

Create a table in ms sql in a database.

CREATE TABLE ms_table (id int IDENTITY(1,1) PRIMARY KEY, something
varchar(max))

In CentOS 7:

sudo bash
yum groupinstall "Development Tools"
yum install
http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
yum install postgresql94-server postgresql94-contrib python-pip
python-devel freetds-devel postgresql94-devel
easy_install pgxnclient
pgxn install tds_fdw
pgxn install multicorn
echo 'export PATH=/usr/pgsql-9.4/bin:$PATH' > /etc/profile.d/postgres.sh

Edit your /etc/freetds.conf to look like this:

[global]
tds version = 7.1
#The following will prevent large text and xml fields from being truncated.
text size = 4294967295

[sqlserver]
host = insert host name here
port = 1433

Setup and start postgresql.

chown -R postgres:postgres /var/lib/pgsql
su postgres
initdb --encoding=UTF8 --pwprompt ~/9.4/data
exit
systemctl start postgresql-9.4
su postgres
psql

CREATE EXTENSION tds_fdw;
CREATE FOREIGN SERVER servername FOREIGN DATA WRAPPER tds_fdw OPTIONS
(servername 'sqlserver from freetds.conf');
CREATE FOREIGN TABLE ms_table (id int, something text) SERVER servername
OPTIONS (database 'ms_database', table 'ms_table');

Now you should be able to select from ms_table in MS SQL Server and
PostgreSQL. Insert some rows that have NULL for 'something', and in my
experience they come through as empty strings rather than null. EG
char_length(something) gives 0 rather than NULL.

Jeff

On Tue, May 12, 2015 at 10:47 AM, Marc Abramowitz <[email protected]>
wrote:

> Folks who are interested in quickly setting up a SQL Server 2014 Express
> Edition instance for testing might be interested in this little Vagrant VM
> that I put together:
>
> https://github.com/msabramo/vagrant_sql_server_express
>
> You can send pull requests or file issues if you have problems,
> suggestions, etc.
>
> On Mon, May 11, 2015 at 5:13 PM, Sergio NNX <[email protected]> wrote:
>
> > > I did my testing with 2008 R2, but the key I think is the tds
> > version set in freetds.conf. I forget the specific versions,
> > > but set it
> > too low, and it won't support long strings. Set it to support long
> > strings, and null strings are selected as empty strings.
> >
> > I see. Do you happen to have a test or a way I can reproduce that issue
> on
> > my box? I may have a workaround.
> >
> > Cheers.
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>