3 Replies Latest reply: Feb 20, 2014 11:37 PM by Kgronau-Oracle RSS

    Problem selecting Text column from PostgreSQL database via dg4odbc

    ZGibson_BZ

      Hi Guys,

       

      I have a database link to a PostgreSQL database using dg4odbc.  When selecting text columns, the value in the first row is duplicated for all subsequent rows.  Below you can find more details.  Any assistance in correcting this would be appreciated.

       

      Thanks

       


      PostgreSQL Database Table

       

      CREATE TABLE "TEST"

      (

        "ID" integer,

        "Name" text,

        "Dummy" character varying(25)

      )

       

      Query within PostgreSQL

      IDNameDummy
      5IS2Test2
      4IS
      1VALUETest
      2VALUE2Test

       

       

      Executing SQL from Oracle to PostgreSQL Database

       

      Query:

      select *

      from "PUBLIC"."TEST"@POSTGRES_TEST

       

      Result: 

      IDNameDummy
      5IS2Test2
      4IS2
      1IS2Test
      2IS2Test

      The value for column "Name" from row 1 is repeated for all other rows.



      Output from ODBC trace

           Line 149 shows where the value "IS2" is retrieved for  column "Name".

       

      [ODBC][28380][1392910720.923635][SQLPrepare.c][196]
                      Entry:
                              Statement = 0x1d23f3e0
                              SQL = [SELECT A1."ID",A1."Dummy",A1."Name" FROM "TEST" A1][length = 50]
      [ODBC][28380][1392910720.923662][SQLPrepare.c][371]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.923686][SQLNumResultCols.c][156]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Count = 0x1d1f7360
      [ODBC][28380][1392910720.924564][SQLNumResultCols.c][248]
                      Exit:[SQL_SUCCESS]
                              Count = 0x1d1f7360 -> 3
      [ODBC][28380][1392910720.924787][SQLDescribeCol.c][247]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 1
                              Column Name = 0x7fffeb26d480
                              Buffer Length = 31
                              Name Length = 0x7fffeb26d5c4
                              Data Type = 0x7fffeb26d5c8
                              Column Size = 0x7fffeb26d560
                              Decimal Digits = 0x7fffeb26d5cc
                              Nullable = 0x7fffeb26d5d0
      [ODBC][28380][1392910720.924836][SQLDescribeCol.c][497]
                      Exit:[SQL_SUCCESS]
                              Column Name = [ID]
                              Data Type = 0x7fffeb26d5c8 -> 4
                              Column Size = 0x7fffeb26d560 -> 10
                              Decimal Digits = 0x7fffeb26d5cc -> 0
                              Nullable = 0x7fffeb26d5d0 -> 1
      [ODBC][28380][1392910720.924864][SQLColAttribute.c][293]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 1
                              Field Identifier = SQL_DESC_UNSIGNED
                              Character Attr = (nil)
                              Buffer Length = 0
                              String Length = (nil)
                              Numeric Attribute = 0x7fffeb26d570
      [ODBC][28380][1392910720.924889][SQLColAttribute.c][664]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.924986][SQLDescribeCol.c][247]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 2
                              Column Name = 0x7fffeb26d480
                              Buffer Length = 31
                              Name Length = 0x7fffeb26d5c4
                              Data Type = 0x7fffeb26d5c8
                              Column Size = 0x7fffeb26d560
                              Decimal Digits = 0x7fffeb26d5cc
                              Nullable = 0x7fffeb26d5d0
      [ODBC][28380][1392910720.925021][SQLDescribeCol.c][497]
                      Exit:[SQL_SUCCESS]
                              Column Name = [Dummy]
                              Data Type = 0x7fffeb26d5c8 -> 12
                              Column Size = 0x7fffeb26d560 -> 25
                              Decimal Digits = 0x7fffeb26d5cc -> 0
                              Nullable = 0x7fffeb26d5d0 -> 1
      [ODBC][28380][1392910720.925047][SQLColAttribute.c][293]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 2
                              Field Identifier = SQL_DESC_OCTET_LENGTH
                              Character Attr = (nil)
                              Buffer Length = 0
                              String Length = (nil)
                              Numeric Attribute = 0x7fffeb26d568
      [ODBC][28380][1392910720.925071][SQLColAttribute.c][664]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.925151][SQLDescribeCol.c][247]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 3
                              Column Name = 0x7fffeb26d480
                              Buffer Length = 31
                              Name Length = 0x7fffeb26d5c4
                              Data Type = 0x7fffeb26d5c8
                              Column Size = 0x7fffeb26d560
                              Decimal Digits = 0x7fffeb26d5cc
                              Nullable = 0x7fffeb26d5d0
      [ODBC][28380][1392910720.925191][SQLDescribeCol.c][497]
                      Exit:[SQL_SUCCESS]
                              Column Name = [Name]
                              Data Type = 0x7fffeb26d5c8 -> -1
                              Column Size = 0x7fffeb26d560 -> 8190
                              Decimal Digits = 0x7fffeb26d5cc -> 0
                              Nullable = 0x7fffeb26d5d0 -> 1
      [ODBC][28380][1392910720.925222][SQLColAttribute.c][293]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 3
                              Field Identifier = SQL_DESC_OCTET_LENGTH
                              Character Attr = (nil)
                              Buffer Length = 0
                              String Length = (nil)
                              Numeric Attribute = 0x7fffeb26d568
      [ODBC][28380][1392910720.925246][SQLColAttribute.c][664]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.925331][SQLSetStmtAttr.c][265]
                      Entry:
                              Statement = 0x1d23f3e0
                              Attribute = SQL_ATTR_ROW_ARRAY_SIZE
                              Value = 0x1
                              StrLen = 0
      [ODBC][28380][1392910720.925361][SQLSetStmtAttr.c][925]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.934076][SQLExecute.c][187]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.936535][SQLExecute.c][348]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.936583][SQLBindCol.c][236]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 1
                              Target Type = -16 SQL_C_SLONG
                              Target Value = 0x1d24b5b0
                              Buffer Length = 4
                              StrLen Or Ind = 0x1d24b618
      [ODBC][28380][1392910720.936613][SQLBindCol.c][341]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.936656][SQLBindCol.c][236]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 2
                              Target Type = 1 SQL_CHAR
                              Target Value = 0x1d24b500
                              Buffer Length = 151
                              StrLen Or Ind = 0x1d24b620
      [ODBC][28380][1392910720.936683][SQLBindCol.c][341]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.936759][SQLFetch.c][162]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.936804][SQLFetch.c][348]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.936972][SQLGetData.c][237]
                      Entry:
                              Statement = 0x1d23f3e0
                              Column Number = 3
                              Target Type = 1 SQL_CHAR
                              Buffer Length = 65537
                              Target Value = 0x1d270ca0
                              StrLen Or Ind = 0x1d24b628
      [ODBC][28380][1392910720.937008][SQLGetData.c][501]
                      Exit:[SQL_SUCCESS]
                              Buffer = [IS2]
                              Strlen Or Ind = 0x1d24b628 -> 3
      [ODBC][28380][1392910720.937423][SQLFetch.c][162]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.937463][SQLFetch.c][348]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.937879][SQLFetch.c][162]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.937919][SQLFetch.c][348]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.938375][SQLFetch.c][162]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.938415][SQLFetch.c][348]
                      Exit:[SQL_SUCCESS]
      [ODBC][28380][1392910720.938865][SQLFetch.c][162]
                      Entry:
                              Statement = 0x1d23f3e0
      [ODBC][28380][1392910720.938914][SQLFetch.c][348]
                      Exit:[SQL_NO_DATA]
      

       

       

      Agent Initialization File for PostgreSQL connection

       

      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC
      
      
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = POSTGRES_TEST
      HS_FDS_TRACE_LEVEL = 255
      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2
      HS_FDS_SQLLEN_INTERPRETATION=64
      HS_LANGUAGE = ENGLISH_AMERICA.WE8MSWIN1252
      #HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
      #HS_FDS_FETCH_ROWS=1
      #HS_NLS_NCHAR=UCS2
      #HS_KEEP_REMOTE_COLUMN_SIZE=ALL
      #HS_NLS_LENGTH_SEMANTICS=CHAR
      #HS_LANGUAGE=AMERICAN_AMERICA.AL64UTF8
      
      
      
      
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/u01/oracle/.odbc.ini
      set ODBCINSTINI=/etc/unixODBC/odbcinst.ini
      
      
      
      
      
      
      #
      # Environment variables required for the non-Oracle system
      #
      set LD_LIBRARY_PATH=/usr/lib64
      

       

      Oracle Database Gateway Trace for PostgreSQL connection

       

      Entered hgopars, cursor id 2 at 2014/02/20-09:38:40
       type:0
      SQL text from hgopars, id=2, len=50 ...
           00: 53454C45 43542041 312E2249 44222C41  [SELECT A1."ID",A]
           10: 312E2244 756D6D79 222C4131 2E224E61  [1."Dummy",A1."Na]
           20: 6D652220 46524F4D 20225445 53542220  [me" FROM "TEST" ]
           30: 4131                                 [A1]
      Exiting hgopars, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoxpars
      hostmstr:          0:   HOA Before hoxopen
      Entered hgoopen, cursor id 2 at 2014/02/20-09:38:40
      hgoopen, line 86: NO hoada to print
       Deferred open until first fetch.
      Exiting hgoopen, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoxopen
      hostmstr:          0:   HOA Before hoxdscr
      Entered hgodscr, cursor id 2 at 2014/02/20-09:38:40
       Allocate hoada @ 0x1d23d690
      Entered hgopcda at 2014/02/20-09:38:40
       Column:1(ID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2014/02/20-09:38:40
      Entered hgopcda at 2014/02/20-09:38:40
       Column:2(Dummy): dtype:12 (VARCHAR), prc/scl:25/0, nullbl:1, octet:150, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2014/02/20-09:38:40
      Entered hgopcda at 2014/02/20-09:38:40
       Column:3(Name): dtype:-1 (LONGVARCHAR), prc/scl:8190/0, nullbl:1, octet:8190, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2014/02/20-09:38:40
      hgodscr, line 880: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y        150        150   0/  0    0   0  200 Dummy
       -1 LONGVARCHAR Y          0          0   0/  0    0   0  220 Name
      Exiting hgodscr, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoxdscr
      hostmstr:          0: RPC After SQL Bundling
      hostmstr:          0: RPC Before Fetch Row
      hostmstr:          0:   HOA Before hoaftch
      Entered hgoftch, cursor id 2 at 2014/02/20-09:38:40
      hgoftch, line 130: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y        150        150   0/  0    0   0  200 Dummy
       -1 LONGVARCHAR Y      65536 2147483647   0/  0    0   0  230 Name
       Performing delayed open.
       SQLBindCol: column 1, cdatatype: -16, bflsz: 4
       SQLBindCol: column 2, cdatatype: 1, bflsz: 151
       SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
       SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)
        0: 05000000                             [....]
       SQLFetch: row: 1, column 2, bflsz: 151, bflar: 5
       SQLFetch: row: 1, column 2, bflsz: 151,  bflar: 5, (bfl: 150, mbl: 150)
        0: 54657374 32                          [Test2]
       SQLFetch: row: 1, column 3, bflsz: 65537, bflar: 0
       SQLFetch: row: 1, column 3, bflsz: 65537,  bflar: 0, (bfl: 65536, mbl: 2147483647)
       1 rows fetched
      Exiting hgoftch, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoaftch
      hostmstr:          0: RPC After Fetch Row
      hostmstr:          0: RPC Before Fetch Row
      hostmstr:          0:   HOA Before hoaftch
      Entered hgoftch, cursor id 2 at 2014/02/20-09:38:40
      hgoftch, line 130: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y          5        150   0/  0    0   0  200 Dummy
       -1 LONGVARCHAR Y      65536 2147483647   0/  0    0   0  230 Name
       SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
       SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)
        0: 04000000                             [....]
       SQLFetch: row: 1, column 2, bflsz: 151, bflar: -1
       SQLFetch: row: 1, column 2, bflsz: 151,  bflar: SQL_NULL_DATA
       SQLFetch: row: 1, column 3, bflsz: 65537, bflar: 3
       SQLFetch: row: 1, column 3, bflsz: 65537,  bflar: 3, (bfl: 65536, mbl: 2147483647)
        0: 495332                               [IS2]
       1 rows fetched
      Exiting hgoftch, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoaftch
      hostmstr:          0: RPC After Fetch Row
      hostmstr:          0: RPC Before Fetch Row
      hostmstr:          0:   HOA Before hoaftch
      Entered hgoftch, cursor id 2 at 2014/02/20-09:38:40
      hgoftch, line 130: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y          0        150   0/  0    0  -1  200 Dummy
       -1 LONGVARCHAR Y      65536 2147483647   0/  0    0   0  230 Name
       SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
       SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)
        0: 01000000                             [....]
       SQLFetch: row: 1, column 2, bflsz: 151, bflar: 4
       SQLFetch: row: 1, column 2, bflsz: 151,  bflar: 4, (bfl: 0, mbl: 150)
        0: 54657374                             [Test]
       SQLFetch: row: 1, column 3, bflsz: 65537, bflar: 3
       SQLFetch: row: 1, column 3, bflsz: 65537,  bflar: 3, (bfl: 65536, mbl: 2147483647)
        0: 495332                               [IS2]
       1 rows fetched
      Exiting hgoftch, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoaftch
      hostmstr:          0: RPC After Fetch Row
      hostmstr:          0: RPC Before Fetch Row
      hostmstr:          0:   HOA Before hoaftch
      Entered hgoftch, cursor id 2 at 2014/02/20-09:38:40
      hgoftch, line 130: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y          4        150   0/  0    0   0  200 Dummy
       -1 LONGVARCHAR Y      65536 2147483647   0/  0    0   0  230 Name
       SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
       SQLFetch: row: 1, column 1, bflsz: 4,  bflar: 4, (bfl: 4, mbl: 4)
        0: 02000000                             [....]
       SQLFetch: row: 1, column 2, bflsz: 151, bflar: 4
       SQLFetch: row: 1, column 2, bflsz: 151,  bflar: 4, (bfl: 4, mbl: 150)
        0: 54657374                             [Test]
       SQLFetch: row: 1, column 3, bflsz: 65537, bflar: 3
       SQLFetch: row: 1, column 3, bflsz: 65537,  bflar: 3, (bfl: 65536, mbl: 2147483647)
        0: 495332                               [IS2]
       1 rows fetched
      Exiting hgoftch, rc=0 at 2014/02/20-09:38:40
      hostmstr:          0:   HOA After hoaftch
      hostmstr:          0: RPC After Fetch Row
      hostmstr:          0: RPC Before Fetch Row
      hostmstr:          0:   HOA Before hoaftch
      Entered hgoftch, cursor id 2 at 2014/02/20-09:38:40
      hgoftch, line 130: Printing hoada @ 0x1d23d690
       MAX:3, ACTUAL:3, BRC:1, WHT=5 (SELECT_LIST)
       hoadaMOD bit-values found (0x200:TREAT_AS_CHAR,0x10:PIECEWISE_PARAMETER,0x20:NEGATIVE_HOADADTY)
      DTY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
        4 INTEGER     Y          4          4   0/  0    0   0   0 ID
       12 VARCHAR     Y          4        150   0/  0    0   0  200 Dummy
       -1 LONGVARCHAR Y      65536 2147483647   0/  0    0   0  230 Name
       0 rows fetched
      

       

       

       

       

       

      Oracle Server Info and Components

       

      OS = Linux 2.6.18-238.el5 x86_64

      Application = Oracle Database Gateways for ODBC 11.2.0

      ODBC Driver Manager = unixODBC 2.3.1 (64bit compilation)

      PostgreSQL ODBC Driver = psqlodbc-09.03.0100 (64bit compilation)

       

      Remote PostgreSQL database = PostgreSQL 9.3