3 Replies Latest reply: Jun 1, 2012 3:18 AM by Kgronau-Oracle RSS

    Error fetching data from a foxpro db using Oracle Gateway for ODBC

    582007
      Hi.

      I have an 10.2.0.1 database an a 11.2.0 Oracle Gateway for ODBC installed and configured.
      The dblink test works fine, I can connect with the foxpro database because if I execute desc centros I can see the
      columns of table centros.

      The problem comes when I try to execute Select * from centros@fox1;
      A syntax error is returned ([Microsoft][ODBC Visual FoxPro Driver]Syntax error. {42000,NativeErr = 200})
      I paste down the output of the trace file with HS_FDS_TRACE_LEVEL=255

      If I run select codcen from centros@fox1; another error is shown. Tell me if you want the other trace file.
      select codcen from centros@fox1
      *
      ERROR en lÝnea 1:
      ORA-00904: "CODCEN": identificador no vßlido






      Oracle Corporation --- JUEVES MAY 31 2012 13:11:24.765


      Heterogeneous Agent Release
      11.2.0.1.0




      Oracle Corporation --- JUEVES MAY 31 2012 13:11:24.750

      Version 11.2.0.1.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
      Entered hgosdip
      setting HS_OPEN_CURSORS to default of 50
      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
      setting HS_FDS_RECOVERY_PWD to default value
      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
      setting HS_IDLE_TIMEOUT to default of 0
      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
      setting HS_NLS_NCHAR to default of "UCS2"
      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
      setting HS_FDS_DATE_MAPPING to default of "DATE"
      setting HS_RPC_FETCH_REBLOCKING to default of "ON"
      setting HS_FDS_FETCH_ROWS to default of "100"
      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
      setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
      setting HS_FDS_MAP_NCHAR to default of "TRUE"
      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
      setting HS_FDS_QUERY_DRIVER to default of "TRUE"
      setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
      Parameter HS_FDS_QUOTE_IDENTIFIER is not set
      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
      Default value of 32 assumed for HS_FDS_SQLLEN_INTERPRETATION
      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
      setting HS_FDS_DELAYED_OPEN to default of "TRUE"
      setting HS_FDS_WORKAROUNDS to default of "0"
      Exiting hgosdip, rc=0
      ORACLE_SID is "DAVID"
      Product-Info:
      Port Rls/Upd:1/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:DAVID
      Exiting hgogprd, rc=0
      hostmstr: 2057416704:      HOA After hoagprd
      hostmstr: 2057416704:      HOA Before hoainit
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=178
      HOCXU_DRV_NCHAR=1000
      HOCXU_DB_CSET=178
      HOCXU_SEM_VER=102000
      Entered hgolofn at 2012/05/31-13:11:25
      Exiting hgolofn, rc=0 at 2012/05/31-13:11:25
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
      Invalid value of 32 given for HS_FDS_SQLLEN_INTERPRETATION
      treat_SQLLEN_as_compiled = 1
      Exiting hgoinit, rc=0 at 2012/05/31-13:11:25
      hostmstr: 2057416704:      HOA After hoainit
      hostmstr: 2057416704:      HOA Before hoalgon
      Entered hgolgon at 2012/05/31-13:11:25
      reco:0, name:SYSTEM, tflag:0
      Entered hgosuec at 2012/05/31-13:11:25
      Exiting hgosuec, rc=0 at 2012/05/31-13:11:25
      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
      using SYSTEM as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      Entered hgocont at 2012/05/31-13:11:25
      HS_FDS_CONNECT_INFO = "Prueba_Foxpro"
      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
      Entered hgogenconstr at 2012/05/31-13:11:25
      dsn:Prueba_Foxpro, name:SYSTEM
      optn:
      Entered hgocip at 2012/05/31-13:11:25
      dsn:Prueba_Foxpro
      Exiting hgocip, rc=0 at 2012/05/31-13:11:25
      ##>Connect Parameters (len=39)<##
      ## DSN=Prueba_Foxpro;
      #! UID=SYSTEM;
      #! PWD=*
      Exiting hgogenconstr, rc=0 at 2012/05/31-13:11:25
      Entered hgopoer at 2012/05/31-13:11:26
      hgopoer, line 233: got native error 0 and sqlstate 01000; message follows...
      [*Microsoft][Administrador de controladores ODBC] El controlador no admite una versión de ODBC distinta de la que la necesita la aplicación (vea SQLSetEnvAttr)*. {01000}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:26
      hgocont, line 2686: calling SqlDriverConnect got sqlstate 01000
      Entered hgolosf at 2012/05/31-13:11:26
      ODBC Function-Available-Array 0xFFFE 0x00FF 0xFF00 0xAA7F 0x03B3 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0xEE00 0x395C
      Exiting hgolosf, rc=0 at 2012/05/31-13:11:26
      DriverName:VFPODBC.DLL, DriverVer:06.86.0001
      DBMS Name:Visual FoxPro, DBMS Version:03.00.0000
      Exiting hgocont, rc=0 at 2012/05/31-13:11:26 with error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
      Entered hgopoer at 2012/05/31-13:11:26
      hgopoer, line 233: got native error 0 and sqlstate HYC00; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Driver not capable {HYC00}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:26
      hgolgon, line 795: calling SQLGetInfo got sqlstate HYC00
      SQLGetInfo returns N for SQL_CATALOG_NAME
      Exiting hgolgon, rc=0 at 2012/05/31-13:11:26 with error ptr FILE:hgolgon.c LINE:795 ID:GetInfo: Support catalogs
      hostmstr: 2036506624:      HOA After hoalgon
      RPC Calling nscontrol(0), rc=0
      hostmstr: 2036506624: RPC Before Upload Caps
      hostmstr: 2036506624:      HOA Before hoaulcp
      Entered hgoulcp at 2012/05/31-13:11:26
      Entered hgowlst at 2012/05/31-13:11:26
      Exiting hgowlst, rc=0 at 2012/05/31-13:11:26
      SQLGetInfo returns 0x0 for SQL_OWNER_USAGE
      TXN Capable:1, Isolation Option:0x2
      SQLGetInfo returns 0 for SQL_MAX_SCHEMA_NAME_LEN
      SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
      SQLGetInfo returns 0 for SQL_MAX_PROCEDURE_NAME_LEN
      SQLGetInfo returns ` (0x60) for SQL_IDENTIFIER_QUOTE_CHAR
      SQLGetInfo returns Y for SQL_COLUMN_ALIAS
      16 instance capabilities will be uploaded
      capno:1964, context:0x00000000, add-info: 0
      capno:1989, context:0x00000000, add-info: 0
      capno:1991, context:0x0001ffff, add-info: 0
      capno:1992, context:0x0001ffff, add-info: 1, translation:"`"
      capno:3042, context:0x00000000, add-info: 0, translation:"42"
      capno:3047, context:0x00000000, add-info: 0, translation:"57"
      capno:3049, context:0x00000000, add-info: 0, translation:"59"
      capno:3050, context:0x00000000, add-info: 0, translation:"60"
      capno:3066, context:0x00000000, add-info: 0
      capno:3067, context:0x00000000, add-info: 0
      capno:3068, context:0x00000000, add-info: 0
      capno:3069, context:0x00000000, add-info: 0
      capno:3500, context:0x00000001, add-info: 91, translation:"42"
      capno:3501, context:0x00000001, add-info: 93, translation:"57"
      capno:3502, context:0x00000001, add-info: 107, translation:"59"
      capno:3503, context:0x00000001, add-info: 110, translation:"60"
      Exiting hgoulcp, rc=0 at 2012/05/31-13:11:26
      hostmstr: 2036506624:      HOA After hoaulcp
      hostmstr: 2036506624: RPC After Upload Caps
      hostmstr: 2036506624: RPC Before Upload DDTR
      hostmstr: 2036506624:      HOA Before hoauldt
      Entered hgouldt at 2012/05/31-13:11:27
      NO instance DD translations were uploaded
      Exiting hgouldt, rc=0 at 2012/05/31-13:11:27
      hostmstr: 2036506624:      HOA After hoauldt
      hostmstr: 2036506624: RPC After Upload DDTR
      hostmstr: 2036506624: RPC Before Begin Trans
      hostmstr: 2036506624:      HOA Before hoabegn
      Entered hgobegn at 2012/05/31-13:11:27
      tflag:0 , initial:1
      hoi:0x12f094, ttid (len 54) is ...
      00: 54455354 2E524547 52455353 2E524442 [TEST.REGRESS.RDB]
      10: 4D532E44 45562E55 532E4F52 41434C45 [MS.DEV.US.ORACLE]
      20: 2E434F4D 2E663033 63383037 372E392E [.COM.f03c8077.9.]
      30: 34342E37 3735 [44.775]
      tbid (len 10) is ...
      0: 09002C00 07030000 0104 [..,.......]
      Exiting hgobegn, rc=0 at 2012/05/31-13:11:27
      hostmstr: 2036506624:      HOA After hoabegn
      hostmstr: 2036506624: RPC After Begin Trans
      hostmstr: 2036506624: RPC Before Describe Table
      hostmstr: 2036506624:      HOA Before hoadtab
      Entered hgodtab at 2012/05/31-13:11:27
      count:1
      table: CENTROS
      Entered hgopdsc at 2012/05/31-13:11:27
      Describing procedure CENTROS
      Output hoada
      hgopdsc, line 1426: NO hoada to print
      Exiting hgopdsc, rc=942 at 2012/05/31-13:11:27
      The hoada for table CENTROS follows...
      hgodtab, line 904: NO hoada to print
      Exiting hgodtab, rc=0 at 2012/05/31-13:11:27
      hostmstr: 2036506624:      HOA After hoadtab
      hostmstr: 2036506624: RPC After Describe Table
      hostmstr: 2036506624: RPC Before SQL Bundling
      hostmstr: 2036506624:      HOA Before hoxpars
      Entered hgopars, cursor id 1 at 2012/05/31-13:11:27
      type:0
      SQL text from hgopars, id=1, len=23 ...
      00: 53454C45 4354202A 2046524F 4D202243 [SELECT * FROM "C]
      10: 454E5452 4F5322 [ENTROS"]
      Exiting hgopars, rc=0 at 2012/05/31-13:11:28
      hostmstr: 2036506624:      HOA After hoxpars
      hostmstr: 2036506624: RPC After SQL Bundling
      hostmstr: 2036506624: RPC Before SQL Bundling
      hostmstr: 2036506624:      HOA Before hoxopen
      Entered hgoopen, cursor id 1 at 2012/05/31-13:11:28
      hgoopen, line 87: NO hoada to print
      Deferred open until first fetch.
      Exiting hgoopen, rc=0 at 2012/05/31-13:11:28
      hostmstr: 2036506624:      HOA After hoxopen
      hostmstr: 2036506624:      HOA Before hoxdscr
      Entered hgodscr, cursor id 1 at 2012/05/31-13:11:28
      Allocate hoada @ 023E983C
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]*Descriptor type out of range* {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:1(codcen): dtype:1 (CHAR), prc/scl:8/0, nullbl:0, octet:8, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:2(litcen): dtype:1 (CHAR), prc/scl:45/0, nullbl:0, octet:45, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:3(codpos): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:4(codprov): dtype:1 (CHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:5(codmun): dtype:1 (CHAR), prc/scl:3/0, nullbl:0, octet:3, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:6(codecol): dtype:1 (CHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:7(codesin): dtype:1 (CHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:8(cb): dtype:1 (CHAR), prc/scl:4/0, nullbl:0, octet:4, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:9(cs): dtype:1 (CHAR), prc/scl:4/0, nullbl:0, octet:4, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:10(digitocon): dtype:1 (CHAR), prc/scl:2/0, nullbl:0, octet:2, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:11(cuenta): dtype:1 (CHAR), prc/scl:10/0, nullbl:0, octet:10, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopcda at 2012/05/31-13:11:28
      Column:12(solar): dtype:2 (NUMERIC), prc/scl:5/0, nullbl:0, octet:10, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopcda at 2012/05/31-13:11:28
      Column:13(construido): dtype:2 (NUMERIC), prc/scl:5/0, nullbl:0, octet:10, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:14(domicen): dtype:1 (CHAR), prc/scl:40/0, nullbl:0, octet:40, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:15(telef): dtype:1 (CHAR), prc/scl:11/0, nullbl:0, octet:11, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:16(fax): dtype:1 (CHAR), prc/scl:11/0, nullbl:0, octet:11, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:17(cif): dtype:1 (CHAR), prc/scl:11/0, nullbl:0, octet:11, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Descriptor type out of range {HY091}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 615: calling SQLColAttribute got sqlstate HY091
      Entered hgopcda at 2012/05/31-13:11:28
      Column:18(litloc): dtype:1 (CHAR), prc/scl:30/0, nullbl:0, octet:30, sign:1, radix:0
      Exiting hgopcda, rc=0 at 2012/05/31-13:11:28
      hgodscr, line 910: Printing hoada @ 023E983C
      MAX:18, ACTUAL:18, BRC:100, WHT=5 (SELECT_LIST)
      hoadaMOD bit-values found (0x200:TREAT_AS_CHAR)
      DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
      1 CHAR N 8 8 0/ 0 0 0 200 codcen
      1 CHAR N 45 45 0/ 0 0 0 200 litcen
      1 CHAR N 5 5 0/ 0 0 0 200 codpos
      1 CHAR N 2 2 0/ 0 0 0 200 codprov
      1 CHAR N 3 3 0/ 0 0 0 200 codmun
      1 CHAR N 2 2 0/ 0 0 0 200 codecol
      1 CHAR N 2 2 0/ 0 0 0 200 codesin
      1 CHAR N 4 4 0/ 0 0 0 200 cb
      1 CHAR N 4 4 0/ 0 0 0 200 cs
      1 CHAR N 2 2 0/ 0 0 0 200 digitocon
      1 CHAR N 10 10 0/ 0 0 0 200 cuenta
      3 DECIMAL N 7 7 5/ 0 0 0 0 solar
      3 DECIMAL N 7 7 5/ 0 0 0 0 construido
      1 CHAR N 40 40 0/ 0 0 0 200 domicen
      1 CHAR N 11 11 0/ 0 0 0 200 telef
      1 CHAR N 11 11 0/ 0 0 0 200 fax
      1 CHAR N 11 11 0/ 0 0 0 200 cif
      1 CHAR N 30 30 0/ 0 0 0 200 litloc
      Exiting hgodscr, rc=0 at 2012/05/31-13:11:28 with error ptr FILE:hgodscr.c LINE:615 ID:Transfer Octet Length
      hostmstr: 2036506624:      HOA After hoxdscr
      hostmstr: 2036506624: RPC After SQL Bundling
      hostmstr: 2036506624: RPC Before SQL Bundling
      hostmstr: 2036506624:      HOA Before hoxclse
      Entered hgoclse, cursor id 1 at 2012/05/31-13:11:28
      Exiting hgoclse, rc=0 at 2012/05/31-13:11:28
      hostmstr: 2036506624:      HOA After hoxclse
      hostmstr: 2036506624:      HOA Before hoadafr
      Entered hgodafr, cursor id 1 at 2012/05/31-13:11:28
      Free hoada @ 023E983C
      Exiting hgodafr, rc=0 at 2012/05/31-13:11:28
      hostmstr: 2036506624:      HOA After hoadafr
      hostmstr: 2036506624:      HOA Before hoxpars
      Entered hgopars, cursor id 1 at 2012/05/31-13:11:28
      type:0
      SQL text from hgopars, id=1, len=235 ...
      00: 53454C45 43542041 312E2263 6F646365 [SELECT A1."codce]
      10: 6E222C41 312E226C 69746365 6E222C41 [n",A1."litcen",A]
      20: 312E2263 6F64706F 73222C41 312E2263 [1."codpos",A1."c]
      30: 6F647072 6F76222C 41312E22 636F646D [odprov",A1."codm]
      40: 756E222C 41312E22 636F6465 636F6C22 [un",A1."codecol"]
      50: 2C41312E 22636F64 6573696E 222C4131 [,A1."codesin",A1]
      60: 2E226362 222C4131 2E226373 222C4131 [."cb",A1."cs",A1]
      70: 2E226469 6769746F 636F6E22 2C41312E [."digitocon",A1.]
      80: 22637565 6E746122 2C41312E 22736F6C ["cuenta",A1."sol]
      90: 6172222C 41312E22 636F6E73 74727569 [ar",A1."construi]
      A0: 646F222C 41312E22 646F6D69 63656E22 [do",A1."domicen"]
      B0: 2C41312E 2274656C 6566222C 41312E22 [,A1."telef",A1."]
      C0: 66617822 2C41312E 22636966 222C4131 [fax",A1."cif",A1]
      D0: 2E226C69 746C6F63 22204652 4F4D2022 [."litloc" FROM "]
      E0: 43454E54 524F5322 204131 [CENTROS" A1]
      Entered hgopoer at 2012/05/31-13:11:28
      hgopoer, line 233: got native error 200 and sqlstate 42000; message follows...
      [Microsoft][ODBC Visual FoxPro Driver]Syntax error. {42000,NativeErr = 200}
      Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
      hgopars, line 457: calling SQLPrepare got sqlstate 42000
      Exiting hgopars, rc=28500 at 2012/05/31-13:11:28 with error ptr FILE:hgopars.c LINE:487 ID:Prepare stmt
      hostmstr: 2036506624:      HOA After hoxpars
      hostmstr: 2036506624: RPC After SQL Bundling
        • 1. Re: Error fetching data from a foxpro db using Oracle Gateway for ODBC
          Kgronau-Oracle
          The first issue I see is that Oracle 10.2.0.1 was never certified with DG4ODBC 11. Only 10.2.0.4 or 10.2.0.5 can be used as the Oracle database HS kernel requires a gateway compatibility patch and this patch is included in 10.2.0.4 or 10.2.0.5 Oracle database patch set.

          Then another error in the trace is that for each column description you have an error:

          hgopoer, line 233: got native error 0 and sqlstate HY091; message follows...
          [Microsoft][ODBC Visual FoxPro Driver]*Descriptor type out of range* {HY091}
          Exiting hgopoer, rc=0 at 2012/05/31-13:11:28
          hgodscr, line 615: calling SQLColAttribute got sqlstate HY091


          It seems it is not critical as DG4ODBC continues, but a more detailed analysis would require an ODBC trace.

          When looking at the table description it seems the column names are all in lower letters, for example: solar. So commonly Dg4ODBC needs to quote the names
          00: 53454C45 43542041 312E2263 6F646365 [SELECT A1."codce]
          10: 6E222C41 312E226C 69746365 6E222C41 [n",A1."litcen",A]
          20: 312E2263 6F64706F 73222C41 312E2263 [1."codpos",A1."c]
          30: 6F647072 6F76222C 41312E22 636F646D [odprov",A1."codm]
          40: 756E222C 41312E22 636F6465 636F6C22 [un",A1."codecol"]
          50: 2C41312E 22636F64 6573696E 222C4131 [,A1."codesin",A1]
          60: 2E226362 222C4131 2E226373 222C4131 [."cb",A1."cs",A1]
          70: 2E226469 6769746F 636F6E22 2C41312E [."digitocon",A1.]
          80: 22637565 6E746122 2C41312E 22736F6C ["cuenta",A1."sol]
          90: 6172222C 41312E22 636F6E73 74727569 [ar",A1."construi]
          A0: 646F222C 41312E22 646F6D69 63656E22 [do",A1."domicen"]
          B0: 2C41312E 2274656C 6566222C 41312E22 [,A1."telef",A1."]
          C0: 66617822 2C41312E 22636966 222C4131 [fax",A1."cif",A1]
          D0: 2E226C69 746C6F63 22204652 4F4D2022 [."litloc" FROM "]
          E0: 43454E54 524F5322 204131 [CENTROS" A1]

          and according to the trace the FoxPro ODBC driver doesn't like these quotes and reports a syntax error.


          So this explains at least why select codcen from centros@fox1 will fail. In general the Oracle database is case insensitive and translates all object names to upper case and the select it will pass to the foreign database would be similar to:
          select CODCEN from CENTROS => which will fail as FoxPro is case sensitive. So the error message ORA-00904 (missing column name) is correct and the statement you need to use is:
          select "codcen" from centros@fox1;

          But this might lead again to the Syntax error you got earlier => You need to check which sign (single quote, back tick, double quote) Foxpro uses to quote object names and then set the gateway parameter HS_FDS_QUOTE_IDENTIFIER accordingly.

          To check which character can be used to quote column/table names you might use the Microsoft ODBc test utility which was distributed in the old MDAC 2.8:
          http://www.microsoft.com/downloads/details.aspx?familyid=5067faf8-0db4-429a-b502-de4329c8c850&displaylang=en

          Edited by: kgronau on Jun 1, 2012 7:27 AM

          If you know where I can download the MS FoxPro ODBC driver, please let me know and I'll check.
          • 2. Re: Error fetching data from a foxpro db using Oracle Gateway for ODBC
            582007
            After use the HS_FDS_QUOTE_IDENTIFIER parameter with false value the select * statement works perfectly.
            In the select codcen the column name needs to be quoted but works.

            Your help has been very helpful.
            • 3. Re: Error fetching data from a foxpro db using Oracle Gateway for ODBC
              Kgronau-Oracle
              Found an old VFP 6 CD which contains the 32bit ODBC driver for VFP.

              Using the MS ODBC Test returns for SQL_GetInfo, SQL_IDENTIFIER_QUOTE_CHAR:
              SQL_IDENTIFIER_QUOTE_CHAR=29, 1, "`"

              So they use the back tick to quote names and you might add to your DG4ODBC init file HS_FDS_QUOTE_IDENTIFIER="`".