Menu

Procedures For Compiling Some Other FDW

Preliminary Note: Here is explained how to compile and use two additional Foreign Data Wrappers: couchdb_fdw, oracle_fdw.

The resulting compiled files can be found in the file repository of this project, and more specifically this archive.

Note: For more detailed information/explanations about preliminary settings, please refer to this page.

WARNING: Regarding oracle_fdw, this wrapper is ONLY compatible with 64-bit version of Oracle Database if compiled in 64-bit, and with 32-bit version of Oracle Database otherwise.

Not paying attention to this would result in an error message of that kind (even if the environment - ORACLE_HOME, etc - was set correctly) :

ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:  
********** Error **********

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle
SQL state :HV00N

Two tutorials, one per architecture

(If you are experiencing problems during the process described in this document, feel free to send a ticket, and I'll try to help...)


Releasing "couchdb_fdw" and "oracle_fdw" for 64-bit


Releasing "couchdb_fdw"

    export ARCH="x86_64-w64-mingw32"; export SRCDIR="/d/64/"; 
    export MGWDIR="/c/mingw64/"
    export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include


    export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"


  • Get couchdb_fdw from sources from here.

  • Compiling yajl library (couchdb_fdw depends on it):
    { Get it from here }

    • install cmake:

      http://www.cmake.org/cmake/resources/software.html,
      http://www.cmake.org/files/v2.8/cmake-2.8.12.1-win32-x86.zip

          export PATH=$PATH:/c/cmake-2.8.12.1-win32-x86/bin
      
    • unpack "lloyd-yajl-2.0.4-10-g4c539cb.tar.gz"

          cd lloyd-yajl-4c539cb/
      
    • Patch file "CMakeLists.txt" (TODO: ".patch" !!!)

      # IF (WIN32)
        # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /W4")
        # ADD_DEFINITIONS(-DWIN32)
        # SET(linkFlags "/PDB:NONE /INCREMENTAL:NO /OPT:NOREF /OPT:NOICF")
        # SET(CMAKE_EXE_LINKER_FLAGS    "${linkFlags}"
            # CACHE STRING "YAJL linker flags" FORCE)
        # SET(CMAKE_EXE_LINKER_FLAGS_DEBUG ""
            # CACHE STRING "YAJL debug linker flags" FORCE)
        # SET(CMAKE_EXE_LINKER_FLAGS_RELEASE
            # CACHE STRING "YAJL release linker flags" FORCE)
        # SET(CMAKE_SHARED_LINKER_FLAGS "${linkFlags}"
            # CACHE STRING "YAJL shared linker flags" FORCE)
        # SET(CMAKE_MODULE_LINKER_FLAGS "${linkFlags}"
            # CACHE STRING "YAJL module linker flags" FORCE)
      
        # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /wd4996 /wd4255 /wd4130 /wd4100 /wd4711")
        # SET(CMAKE_C_FLAGS_DEBUG "/D DEBUG /Od /Z7")
        # SET(CMAKE_C_FLAGS_RELEASE "/D NDEBUG /O2")
      # ELSE (WIN32)
        SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -Wall")
        IF(CMAKE_COMPILER_IS_GNUCC)
          INCLUDE(CheckCCompilerFlag)
          CHECK_C_COMPILER_FLAG(-fvisibility=hidden HAVE_GCC_VISIBILITY)
          IF(HAVE_GCC_VISIBILITY)
            SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -fvisibility=hidden")
          ENDIF(HAVE_GCC_VISIBILITY)
        ENDIF(CMAKE_COMPILER_IS_GNUCC)
        SET(CMAKE_C_FLAGS
            "${CMAKE_C_FLAGS} -std=c99 -pedantic -Wpointer-arith -Wno-format-y2k -Wstrict-prototypes -Wmissing-declarations -Wnested-externs -Wextra  -Wundef -Wwrite-strings -Wold-style-definition -Wredundant-decls -Wno-unused-parameter -Wno-sign-compare -Wmissing-prototypes")
      
        SET(CMAKE_C_FLAGS_DEBUG "-DDEBUG -g")
        SET(CMAKE_C_FLAGS_RELEASE "-DNDEBUG -O2 -Wuninitialized")
      # ENDIF (WIN32)
      
    • Tell cmake to generate makefiles for our compiler

          CC=$ARCH"-gcc" cmake -G "MSYS Makefiles" ./
      
    • Build

          make
      
    • Install all (by hand)

          cp yajl-2.0.5/bin/* /usr/local/bin
          cp -R yajl-2.0.5/include/* /usr/local/include
          cp yajl-2.0.5/lib/*.dll /usr/local/bin
          cp yajl-2.0.5/lib/*.a /usr/local/lib
          cp yajl-2.0.5/share/pkgconfig/* /usr/local/lib/pkgconfig
      
  • Compile couchdb_fdw

    cd $SRCDIR"/couchdb_fdw"
    export USE_PGXS=1
    make CC=$ARCH"-gcc" LDFLAGS="-L/usr/local/bin/ -lcurl -lyajl"
    make install
    
Testing the extension

If not done already, get Apache CouchDB Windows binary - Download.

Start your CouchDB server, and we're good to go:

Prepare CouchDB

  • create database:

    curl -X PUT http://localhost:5984/testdb
    
  • list databases:

    curl -X GET http://localhost:5984/_all_dbs
    
  • create document:

    curl -X PUT http://localhost:5984/testdb/mydoc_1 -d "{\"title\": \"My first document\", \"content\": \"Hello World!\"}"
    
  • create user:

    curl -HContent-Type:application/json -vXPUT http://127.0.0.1:5984/_users/org.couchdb.user:testuser --data-binary "{\"_id\": \"org.couchdb.user:testuser\",\"name\": \"testuser\",\"roles\": [],\"type\": \"user\",\"password\": \"secret\"}"
    

Wrap & Test

CREATE EXTENSION couchdb_fdw;

-- Then create a foreign server to connect to your CouchDB server:

CREATE SERVER couchdb_server 
    FOREIGN DATA WRAPPER couchdb_fdw 
    OPTIONS (address '127.0.0.1', port '5984');

-- Create a foreign table:

CREATE FOREIGN TABLE couchdb_table (key text, value text) 
    SERVER couchdb_server
    OPTIONS (database 'testdb', key '_id', value '_doc');

-- Create user mapping:

CREATE USER MAPPING FOR PUBLIC
    SERVER couchdb_server
    OPTIONS (username 'testuser', password 'secret');

-- And list documents...

SELECT * FROM couchdb_table;


OUTPUT
"mydoc_1";"{"_id":"mydoc_1","_rev":"1-2fe116a139a166a7f85b85387327d4bc","title":"My first document","content":"Hello World!"}"




Releasing "oracle_fdw"

IMPORTANT NOTE: The following requires a 64-bit version of the Oracle Database.

    export ARCH="x86_64-w64-mingw32"; export SRCDIR="/d/64/"; 
    export MGWDIR="/c/mingw64/"
    export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include


    export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"


    cd $SRCDIR


  • Get oracle_fdw from sources from here.

  • If not done already, install Oracle Database (12.1.0.1.0) for "Microsoft Windows x64 (64-bit)" from here.

    => ports are:
            - TNS:  1521
            - MTS:  2030
            - HTTP: 8081
    
  • Set the ORACLE_HOME environment variable (used by "oracle_fdw" Makefile)

    export ORACLE_HOME=/d/app2/golgauth2/product/12.1.0/dbhome_1
    
  • Add required oracle includes

    export C_INCLUDE_PATH=$C_INCLUDE_PATH:$ORACLE_HOME"/../OCI/include"
    
  • Build

    cd oracle_fdw/
    make CC=$ARCH"-gcc"
    make install
    

=> Results in:

    /bin/install -c -m 755  oracle_fdw.dll 'C:/mingw64/msys/local/pgsql/lib/oracle_fdw.dll'
    /bin/install -c -m 644 ./oracle_fdw.control 'C:/mingw64/msys/local/pgsql/share/extension/'
    /bin/install -c -m 644 ./oracle_fdw--1.0.sql  'C:/mingw64/msys/local/pgsql/share/extension/'
    /bin/install -c -m 644 ./README.oracle_fdw 'C:/mingw64/msys/local/pgsql/share/doc/extension/'


  • Install the "oci" dependency

    cp $ORACLE_HOME"oci.dll" /usr/local/bin/
    


Testing the extension
  • Set PATH correctly on your Oracle Database server side

    SET ORACLE_HOME=D:\app\golgauth2\product\12.1.0\dbhome_1;
    SET TNS_ADMIN=D:\app\golgauth2\product\12.1.0\dbhome_1\NETWORK\ADMIN;
    SET PATH=%PATH%;D:\app\golgauth2\product\12.1.0\dbhome_1\BIN;
    
    • { Deeper documentation here }
  • Setting up the Oracle Database

    sqlplus SYSTEM/orapwd@//localhost:1521/ORADB
    


SQL> 
    CREATE USER C##ORAUSER IDENTIFIED BY orapwd;
    GRANT connect, resource TO C##ORAUSER;

    GRANT UNLIMITED TABLESPACE TO C##ORAUSER;

    CONNECT C##ORAUSER/orapwd@//localhost:1521/ORADB;
    SHOW USER;


SQL> 
    CREATE SCHEMA AUTHORIZATION C##ORAUSER
    CREATE table ORATAB (
            ID         NUMBER(5,0) NOT NULL,
            TEXT       VARCHAR2(30),
            FLOATING   NUMBER(7,2) NOT NULL
    );


SQL> DESCRIBE ORATAB
        Name                            Null?    Type
        ------------------------------- -------- ------------
        ID                              NOT NULL NUMBER(5)
        TEXT                                     VARCHAR2(30)
        FLOATING                        NOT NULL NUMBER(7,2)

SQL> 
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (1, 'a', 1.1);
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (2, 'b', 2.2);
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (3, 'c', 3.3);

SQL> SELECT * FROM ORATAB;
SQL> QUIT;


  • Connecting to Oracle Database (PostgreSQL side)

    --DROP EXTENSION oracle_fdw CASCADE;
    CREATE EXTENSION oracle_fdw;
    
    -- Then create a foreign server to connect to your OracleDB server:
    
    CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/ORADB');
    
    -- Get privilege & Create user mapping:
    CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'C##ORAUSER', password 'orapwd');
    
    GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
    
    -- Create a foreign table:
    
    CREATE FOREIGN TABLE oratab (
            id integer NOT NULL,
            text character varying(30),
            floating double precision NOT NULL
    ) SERVER oradb OPTIONS (schema 'C##ORAUSER', table 'ORATAB');
    
    -- Show table contents:
    
    SELECT * FROM oratab;
    


OUTPUT
1;"a";1.1
2;"b";2.2
3;"c";3.3

TIP : Howto REMOVE ORACLE DATABASE from your COMPUTER !

{ READ this SO thread }

The six-step process to remove all things Oracle from a Windows machine:

  1. Delete the Oracle services: In the registry, go to \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete everything that starts with "Oracle" (+ do the same for "ControlSet001", "ControlSet002", ...)

  2. Remove other Oracle stuff from the registry: Go to \HKEY_LOCAL_MACHINE\SOFTWARE\ and delete the key ORACLE

  3. Reboot

  4. Delete all the Oracle software from the directories where you installed it

  5. Delete the Oracle software inventory: Delete the directory C:\Program Files\Oracle or C:\Program Files (x86)\Oracle. You must do this no matter where you installed your Oracle software - the Oracle installer automatically writes information here.

  6. Delete all shortcuts from your Start menu.



Releasing "couchdb_fdw" and "oracle_fdw" for 32-bit


Releasing "couchdb_fdw"

export ARCH="i686-w64-mingw32"; export SRCDIR="/d/32/"; 
export MGWDIR="/c/mingw32/"
export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include
  • Get couchdb_fdw from sources from here.

  • Compiling yajl library (couchdb_fdw depends on it):
    { Get it from here }

    • install cmake:

      http://www.cmake.org/cmake/resources/software.html,
      http://www.cmake.org/files/v2.8/cmake-2.8.12.1-win32-x86.zip

          export PATH=$PATH:/c/cmake-2.8.12.1-win32-x86/bin
      
    • unpack "lloyd-yajl-2.0.4-10-g4c539cb.tar.gz"

          cd lloyd-yajl-4c539cb/
      
    • Patch file "CMakeLists.txt" (TODO: ".patch" !!!)

      # IF (WIN32)
        # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /W4")
        # ADD_DEFINITIONS(-DWIN32)
        # SET(linkFlags "/PDB:NONE /INCREMENTAL:NO /OPT:NOREF /OPT:NOICF")
        # SET(CMAKE_EXE_LINKER_FLAGS    "${linkFlags}"
            # CACHE STRING "YAJL linker flags" FORCE)
        # SET(CMAKE_EXE_LINKER_FLAGS_DEBUG ""
            # CACHE STRING "YAJL debug linker flags" FORCE)
        # SET(CMAKE_EXE_LINKER_FLAGS_RELEASE
            # CACHE STRING "YAJL release linker flags" FORCE)
        # SET(CMAKE_SHARED_LINKER_FLAGS "${linkFlags}"
            # CACHE STRING "YAJL shared linker flags" FORCE)
        # SET(CMAKE_MODULE_LINKER_FLAGS "${linkFlags}"
            # CACHE STRING "YAJL module linker flags" FORCE)
      
        # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /wd4996 /wd4255 /wd4130 /wd4100 /wd4711")
        # SET(CMAKE_C_FLAGS_DEBUG "/D DEBUG /Od /Z7")
        # SET(CMAKE_C_FLAGS_RELEASE "/D NDEBUG /O2")
      # ELSE (WIN32)
        SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -Wall")
        IF(CMAKE_COMPILER_IS_GNUCC)
          INCLUDE(CheckCCompilerFlag)
          CHECK_C_COMPILER_FLAG(-fvisibility=hidden HAVE_GCC_VISIBILITY)
          IF(HAVE_GCC_VISIBILITY)
            SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -fvisibility=hidden")
          ENDIF(HAVE_GCC_VISIBILITY)
        ENDIF(CMAKE_COMPILER_IS_GNUCC)
        SET(CMAKE_C_FLAGS
            "${CMAKE_C_FLAGS} -std=c99 -pedantic -Wpointer-arith -Wno-format-y2k -Wstrict-prototypes -Wmissing-declarations -Wnested-externs -Wextra  -Wundef -Wwrite-strings -Wold-style-definition -Wredundant-decls -Wno-unused-parameter -Wno-sign-compare -Wmissing-prototypes")
      
        SET(CMAKE_C_FLAGS_DEBUG "-DDEBUG -g")
        SET(CMAKE_C_FLAGS_RELEASE "-DNDEBUG -O2 -Wuninitialized")
      # ENDIF (WIN32)
      
    • Tell cmake to generate makefiles for our compiler

          CC=$ARCH"-gcc" cmake -G "MSYS Makefiles" ./
      
    • Build

          make
      
    • Install all (by hand)

          cp yajl-2.0.5/bin/* /usr/local/bin
          cp -R yajl-2.0.5/include/* /usr/local/include
          cp yajl-2.0.5/lib/*.dll /usr/local/bin
          cp yajl-2.0.5/lib/*.a /usr/local/lib
          cp yajl-2.0.5/share/pkgconfig/* /usr/local/lib/pkgconfig
      
  • Compile couchdb_fdw

    cd $SRCDIR"/couchdb_fdw"
    export USE_PGXS=1
    make CC=$ARCH"-gcc" LDFLAGS="-L/usr/local/bin/ -lcurl -lyajl"
    make install
    
Testing the extension

If not done already, get Apache CouchDB Windows binary - Download.

Start your CouchDB server, and we're good to go:

Prepare CouchDB

  • create database:

    curl -X PUT http://localhost:5984/testdb
    
  • list databases:

    curl -X GET http://localhost:5984/_all_dbs
    
  • create document:

    curl -X PUT http://localhost:5984/testdb/mydoc_1 -d "{\"title\": \"My first document\", \"content\": \"Hello World!\"}"
    
  • create user:

    curl -HContent-Type:application/json -vXPUT http://127.0.0.1:5984/_users/org.couchdb.user:testuser --data-binary "{\"_id\": \"org.couchdb.user:testuser\",\"name\": \"testuser\",\"roles\": [],\"type\": \"user\",\"password\": \"secret\"}"
    

Wrap & Test

CREATE EXTENSION couchdb_fdw;

-- Then create a foreign server to connect to your CouchDB server:

CREATE SERVER couchdb_server 
    FOREIGN DATA WRAPPER couchdb_fdw 
    OPTIONS (address '127.0.0.1', port '5984');

-- Create a foreign table:

CREATE FOREIGN TABLE couchdb_table (key text, value text) 
    SERVER couchdb_server
    OPTIONS (database 'testdb', key '_id', value '_doc');

-- Create user mapping:

CREATE USER MAPPING FOR PUBLIC
    SERVER couchdb_server
    OPTIONS (username 'testuser', password 'secret');

-- And list documents...

SELECT * FROM couchdb_table;


OUTPUT
"mydoc_1";"{"_id":"mydoc_1","_rev":"1-2fe116a139a166a7f85b85387327d4bc","title":"My first document","content":"Hello World!"}"




Releasing "oracle_fdw"

IMPORTANT NOTE: The following requires a 32-bit version of the Oracle Database.

export ARCH="i686-w64-mingw32"; export SRCDIR="/d/32/"; 
export MGWDIR="/c/mingw32/"
export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include


    export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"


    cd $SRCDIR


  • Get oracle_fdw from sources from here.

  • If not done already, install Oracle Database (11.2.0.1.0) for "Microsoft Windows (32-bit)" from here.

    => ports are:
            - TNS:  1521
            - MTS:  2030
            - HTTP: 8081
    
  • Set the ORACLE_HOME environment variable (used by "oracle_fdw" Makefile)

    export ORACLE_HOME=/d/app2/golgauth/product/11.2.0/dbhome_1
    
  • Add required oracle includes

    export C_INCLUDE_PATH=$C_INCLUDE_PATH:$ORACLE_HOME"/../OCI/include"
    
  • Build

    cd oracle_fdw/
    make CC=$ARCH"-gcc"
    make install
    

=> Results in:

    /bin/install -c -m 755  oracle_fdw.dll 'C:/mingw32/msys/local/pgsql/lib/oracle_fdw.dll'
    /bin/install -c -m 644 ./oracle_fdw.control 'C:/mingw32/msys/local/pgsql/share/extension/'
    /bin/install -c -m 644 ./oracle_fdw--1.0.sql  'C:/mingw32/msys/local/pgsql/share/extension/'
    /bin/install -c -m 644 ./README.oracle_fdw 'C:/mingw32/msys/local/pgsql/share/doc/extension/'


  • Install the "oci" dependency

    cp $ORACLE_HOME"oci.dll" /usr/local/bin/
    
  • Integrate "msvcr80.dll" to "mingw32" ("oci.dll" depends on it)

    cp $ORACLE_HOME"/BIN/msvcr80.dll" $MGWDIR/$ARCH/lib/
    


Testing the extension
  • Set PATH correctly on your Oracle Database server side

    SET ORACLE_HOME=D:\app\golgauth2\product\11.2.0\dbhome_1;
    SET TNS_ADMIN=D:\app\golgauth2\product\11.2.0\dbhome_1\NETWORK\ADMIN;
    SET PATH=%PATH%;D:\app\golgauth2\product\11.2.0\dbhome_1\BIN;
    
    • { Deeper documentation here }
  • Setting up the Oracle Database

    sqlplus SYSTEM/orapwd@//localhost:1521/ORADB
    


SQL> 
    CREATE USER C##ORAUSER IDENTIFIED BY orapwd;
    GRANT connect, resource TO C##ORAUSER;

    GRANT UNLIMITED TABLESPACE TO C##ORAUSER;

    CONNECT C##ORAUSER/orapwd@//localhost:1521/ORADB;
    SHOW USER;


SQL> 
    CREATE SCHEMA AUTHORIZATION C##ORAUSER
    CREATE table ORATAB (
            ID         NUMBER(5,0) NOT NULL,
            TEXT       VARCHAR2(30),
            FLOATING   NUMBER(7,2) NOT NULL
    );


SQL> DESCRIBE ORATAB
        Name                            Null?    Type
        ------------------------------- -------- ------------
        ID                              NOT NULL NUMBER(5)
        TEXT                                     VARCHAR2(30)
        FLOATING                        NOT NULL NUMBER(7,2)

SQL> 
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (1, 'a', 1.1);
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (2, 'b', 2.2);
    INSERT INTO ORATAB (ID,TEXT,FLOATING) values (3, 'c', 3.3);

SQL> SELECT * FROM ORATAB;
SQL> QUIT;


  • Connecting to Oracle Database (PostgreSQL side)

    --DROP EXTENSION oracle_fdw CASCADE;
    CREATE EXTENSION oracle_fdw;
    
    -- Then create a foreign server to connect to your OracleDB server:
    
    CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/ORADB');
    
    -- Get privilege & Create user mapping:
    CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'C##ORAUSER', password 'orapwd');
    
    GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
    
    -- Create a foreign table:
    
    CREATE FOREIGN TABLE oratab (
            id integer NOT NULL,
            text character varying(30),
            floating double precision NOT NULL
    ) SERVER oradb OPTIONS (schema 'C##ORAUSER', table 'ORATAB');
    
    -- Show table contents:
    
    SELECT * FROM oratab;
    


OUTPUT
1;"a";1.1
2;"b";2.2
3;"c";3.3

TIP : Howto REMOVE ORACLE DATABASE from your COMPUTER !

{ READ this SO thread }

The six-step process to remove all things Oracle from a Windows machine:

  1. Delete the Oracle services: In the registry, go to \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete everything that starts with "Oracle" (+ do the same for "ControlSet001", "ControlSet002", ...)

  2. Remove other Oracle stuff from the registry: Go to \HKEY_LOCAL_MACHINE\SOFTWARE\ and delete the key ORACLE

  3. Reboot

  4. Delete all the Oracle software from the directories where you installed it

  5. Delete the Oracle software inventory: Delete the directory C:\Program Files\Oracle or C:\Program Files (x86)\Oracle. You must do this no matter where you installed your Oracle software - the Oracle installer automatically writes information here.

  6. Delete all shortcuts from your Start menu.


Releasing "odbc_fdw"

Compilation is pretty straight forward (see other tutorials), just one thing to be highlighted: this wrapper depends on the odbc driver installed on your computer. In the following example, since we want to connect to a Microsoft Access database, the Makefile has to be slightly modified as follows:

 SHLIB_LINK = -lodbc32 # instead of '-lodbc'

This will link to the odbc32.dll file corresponding to the targeted platform (from System32 for 64bit, from SysWOW64 for 32bit).

How to create the MS Access database is well explained here.


Testing the extension

WORKING sql code:

    psql -c"CREATE EXTENSION odbc_fdw;" -U postgres
    psql -c"CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'DSN_2013_OK');" -U postgres
    psql -c"CREATE USER MAPPING FOR postgres SERVER odbc_server OPTIONS (username 'glg', password 'glg');" -U postgres
    psql -c"CREATE FOREIGN TABLE odbc_table ( rubrique text, titre text, annee int) SERVER odbc_server OPTIONS ( database 'Test2013_ok', schema '', sql_query 'SELECT Rubrique, Rtitre, Annee FROM Formation', table 'Formation', rubrique 'Rubrique', titre 'Rtitre', annee 'Annee' );" -U postgres CREATE FOREIGN TABLE
    psql -c"SELECT * from odbc_table;" -U postgres

rubrique | titre | annee
----------+-----------+-------
rub_val | titre_val | 1902
(1 row)

Note: It came to work mainly at the time I added the missing option "schema ''" and probably
"sql_query '…'" as well...

01_Piwik_img


Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.