Import foreign schema from ASE 15.7.0 fails

[aleszeleny]

I have only TDS access (no access to host) and when I've tried to import foreign schema. I've got error:

ERROR:  syntax error at or near "x08"
LINE 1: ... 10, 255) AS column_default,   CASE (sc.status & 0x08)     W...
                                                             ^
QUERY:  SELECT so.name AS table_name,  sc.name AS column_name,   st.name AS data_type,   SUBSTRING(sm.text, 10, 255) AS column_default,   CASE (sc.status & 0x08)     WHEN 8 THEN 'YES' ELSE 'NO'   END AS is_nullable,   sc.length,   sc.prec,   sc.scale FROM sysobjects so   INNER JOIN sysusers su ON su.uid = so.uid  LEFT JOIN syscolumns sc ON sc.id = so.id   LEFT JOIN systypes st ON st.usertype = sc.usertype   LEFT JOIN syscomments sm ON sm.id = sc.cdefault WHERE so.type = 'U' AND su.name = 'dbo' ORDER BY so.name, sc.colid
) SERVER dsos_equity
OPTIONS (schema_name 'dbo', table_name '');

ERROR:  DB-Library error: DB #: 195, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

Sybase version: Adaptive Server Enterprise/15.7.0/EBF 21677 SMP SP60 /P/Solaris AMD64/OS 5.10/ase157x/3329/64-bit/FBO/Thu Dec 12 05:02:04 2013

In /etc/freetds/freetds.conf I've configure use tds version = 5.0 I had to configure locales.conf:

[default]
        date format = %b %e %Y %I:%M:%S.%z%p

[en_US]
        date format = %b %e %Y %I:%M:%S.%z%p
        language = us_english
        charset = utf8

Otherwise I was getting error on datetime columns: ERROR: invalid input syntax for type timestamp: "Sep 25 2018 03:03:00:000AM"

WARNING: apt does not have a stable CLI interface yet. Use with caution in scripts.

freetds-bin/trusty,now 0.91-5 amd64 [installed]
freetds-common/trusty,now 0.91-5 all [installed]
freetds-dev/trusty,now 0.91-5 amd64 [installed]
tdsodbc/trusty,now 0.91-5 amd64 [installed]

tds_fdw compiled from git: * master 210299b Merge pull request #173 from laurenz/standby

Database:

postgres=# select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg14.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit
(1 row)

postgres=# \dx
                                               List of installed extensions
  Name   |    Version    |   Schema   |                                    Description
---------+---------------+------------+-----------------------------------------------------------------------------------
 plpgsql | 1.0           | pg_catalog | PL/pgSQL procedural language
 tds_fdw | 2.0.0-alpha.2 | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(2 rows)

Freetds:

postgres@finn:~$ apt list --installed | grep tds

WARNING: apt does not have a stable CLI interface yet. Use with caution in scripts.

freetds-bin/trusty,now 0.91-5 amd64 [installed]
freetds-common/trusty,now 0.91-5 all [installed]
freetds-dev/trusty,now 0.91-5 amd64 [installed]
tdsodbc/trusty,now 0.91-5 amd64 [installed]

Workaround: the only I've found is to create foreign tables manually one by one.

Anyway - great piece of work!

[DenisBessa]

I came to this exact same issue when building freeTDS and tds_fdw on Docker.

For some reason, it only works when building the image in the arm64 architecture.

Here is an example DockerFile:

# For some reason, this image only works on arm64
FROM --platform=arm64 postgres:latest

RUN apt update && \
    apt install -y --no-install-recommends \
    automake \
    autoconf \
    libtool \
    make \
    gcc \
    perl \
    gperf \
    pkg-config \
    build-essential \
    libsybdb5 \
    gnupg \
    postgresql-server-dev-16 \
    gettext

# Git nees to be installed separately as it is not compatbile with the --no-install-recommends flag
RUN apt install git -y && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

WORKDIR /opt

RUN git clone https://github.com/FreeTDS/freetds.git && \
    cd freetds && \
    ./autogen.sh && \
    make && \
    make install && \
    cd ..

RUN git clone https://github.com/tds-fdw/tds_fdw.git && \
    cd tds_fdw && \
    make USE_PGXS=1 && \
    make USE_PGXS=1 install && \
    cd ..

# Remove unnecessary build dependencies (perl is needed for pgxs)
RUN apt-get purge -y \
    automake \
    autoconf \
    libtool \
    make \
    gcc \
    gperf \
    git \
    pkg-config \
    build-essential \
    postgresql-server-dev-16 \
    gettext \
    && \
    apt-get autoremove -y && \
    apt-get clean && \
    rm -rf /opt/freetds && \
    rm -rf /opt/tds_fdw && \
    rm -rf /var/lib/apt/lists/*

RUN echo '[default]' >> /etc/freetds/locales.conf && \
    echo 'date format = %b %e %Y %I:%M:%S.%z%p' >> /etc/freetds/locales.conf

EXPOSE 5432
CMD ["postgres"]

[aleszeleny]

Thx for the comment, I've been using it on Ubuntu directly, but currently no longer used it for the last few years.