1 Reply Latest reply: Jan 28, 2013 11:37 PM by Kgronau-Oracle RSS

    cannot query varchar field from Mysql - using hsodbc

      I have a database link in my 10g XE DB, from mysql using hsodbc, The database link works. I can query the from mysql db,
      trouble is i can only query from integer field

      SELECT "login","status" FROM "users"@DRUPAL;
      login status
      ---------- ------
      0 0
      1358129448 1
      1358079544 1
      1358129340 1
      1351831621 1
      1357696163 1
      1357700272 1
      1355390810 1
      1355710611 1
      1355795135 1
      1355903154 1
      1357877162 1
      1356579802 1
      1357889510 1
      1358135668 1
      1357892654 1

      16 rows selected

      if i query for varchar field
      SELECT "login","status","name" FROM "users"@DRUPAL;

      result is :

      Error starting at line 1 in command:
      SELECT "login","status","name" FROM "users"@DRUPAL
      Error at Command Line:1 Column:24
      Error report:
      SQL Error: ORA-00904: "name": invalid identifier
      00904. 00000 - "%s: invalid identifier"

      if all field in table is varchar then :
      SELECT "suppliername","supplierid" FROM "supplier_master"@DRUPAL;

      result is :

      ORA-00942: table or view does not exists
      [Generic Connectivity Using ODBC]Warning: index PRIMARY is ignored.
      Table supplier_master has no fields. Loading failed
      ORA-02063: preceding 3 lines from DRUPAL
      00942. 00000 - "table or view does not exist"
      Error at Line: 1 Column: 41

      please many advise from all expert..

        • 1. Re: cannot query varchar field from Mysql - using hsodbc
          When you do not see a dedicated column using the gateway, then the root cause is very often an unsupported data type. All data types of a foreign database are mapped by the ODBC driver to ODBC equivalents and DG4ODBC/HSODBC maps them to Oracle data types. In each manual there's a list of supported ODBC data types and to which Oracle data types they will be mapped.
          For HSODBC please have a look at:
          Oracle® Database
          Heterogeneous Connectivity Administrator's Guide
          10g Release 2 (10.2)
          Chapter: B.1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

          When the column data type is now mapped to an unsupported ODBC data type the column is dropped from the select list. To check this behavior, please provide an ODBC trace when selecting from the table and also the source table definition (as your table is defined in the MySQL database).