This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 18, 2012 2:43 AM by IZETU RSS

Select from Oracle to MySQL returns only one row

Roel Oracle ACE Director
Currently Being Moderated
Environment:
The Oracle Developer Days Virtual Box image, so that's 11gR2 EE on OEL
MySQL 5.1.51-community
mysql-connector-odbc-5.1.7-0.i386.rpm
unixODBC 2.3.0

I got the connection working, I can insert, update and delete records in the MySQL DB from Oracle (SQL Plus).
But when I enter select * from "employees"@mysql I only get the first record returned.
If I enter select * from "employees"@mysql order by "id" desc, I only get the last one...
If I enter select count(*) from "employees"@mysql, I get the - correct - result of 3.

So, how do I get the complete set of (all) records from a MySQL table in Oracle? Is there some kind of setting or is this intended behavior and do I have to create a PL/SQL loop and put the results into a collection or something like that?
  • 1. Re: Select from Oracle to MySQL returns only one row
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    there is the gateway setting HS_FDS_FETCH_ROWS (see init.ora), this gives the number of rows back from the NON-Oracle source. Maybe this is set to 1.

    Herald ten Dam
    http://htendam.wordpress.com
  • 2. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    Straight from the trace file:
    setting HS_FDS_FETCH_ROWS to default of "100"
    So that is set to 100. If I set it to 10 in the initmysql.ora file, it appears also as "10" in the trace.

    But alas, that doesn't help... (exited SQL Plus, started a new session and ran the select again).
  • 3. Re: Select from Oracle to MySQL returns only one row
    kgronau Guru
    Currently Being Moderated
    What's the source table description as it is stored in the MySQL DB?

    Can you also please try to set:
    HS_FDS_FETCH_ROWS=1

    => this parameter is not responsible how many rows are fetched in total, it is responsible how many rows are fetch at one time in an array.
  • 4. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    The source table description in MySQL is:
    mysql> desc employees;
    +---------------+------------------+------+-----+---------+----------------+
    | Field         | Type             | Null | Key | Default | Extra          |
    +---------------+------------------+------+-----+---------+----------------+
    | id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | first_name    | varchar(30)      | YES  |     | NULL    |                |
    | last_name     | varchar(100)     | YES  |     | NULL    |                |
    | personal_code | varchar(20)      | YES  |     | NULL    |                |
    | birthday      | date             | YES  |     | NULL    |                |
    | salary        | decimal(10,2)    | NO   |     | NULL    |                |
    | is_active     | int(1)           | YES  |     | NULL    |                |
    +---------------+------------------+------+-----+---------+----------------+
    7 rows in set (0.01 sec)
    Setting HS_FDS_FETCH_ROWS to 1 didn't help...
  • 5. Re: Select from Oracle to MySQL returns only one row
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    what are the settings fro HS_RPC_FETCH_SIZE and HS_RPC_FETCH_REBLOCKING? Set the last to OFF and see if it helps.

    Herald ten Dam
    http://htendam.wordpress.com
  • 6. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    HS_RPC_FETCH_REBLOCKING was default ON, switched it to OFF - but didn't help.
    HS_RPC_FETCH_SIZE is not in the log. I set it to 10, but isn't picked up as it doesn't appear in the log.
  • 7. Re: Select from Oracle to MySQL returns only one row
    kgronau Guru
    Currently Being Moderated
    looks good - no special data types . Could you please post a gateway trace level 255?
  • 8. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    The trace files are very long, so I uploaded them to my Public Dropbox folder...:
    http://dl.dropbox.com/u/10648811/mysql_agt_5998.trc
    http://dl.dropbox.com/u/10648811/odbc3.trc
  • 9. Re: Select from Oracle to MySQL returns only one row
    kgronau Guru
    Currently Being Moderated
    the gateway trace shows it fetches 1 row and then it gets:
    Exiting hgoftch, rc=1403 at 2010/11/04-14:12:3
    => 1403 means no more data

    Examining now the ODBC Trace - it shows:
    [ODBC][5998][1288876359.865863][SQLFetch.c][162]
              Entry:
                   Statement = 0x8f7d6e8
    [ODBC][5998][1288876359.865904][SQLFetch.c][348]
              Exit:[SQL_SUCCESS]
    [ODBC][5998][1288876359.866495][SQLFetch.c][162]
              Entry:
                   Statement = 0x8f7d6e8
    [ODBC][5998][1288876359.866515][SQLFetch.c][348]
              Exit:[SQL_NO_DATA]

    The ODBC fetches only one record, then returns no data => looks like an ODBC issue.

    Edited by: kgronau on Nov 4, 2010 4:03 PM
  • 10. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    The funny thing is that isql returns the correct results... So that half of the ODBC connection seems to work...
  • 11. Re: Select from Oracle to MySQL returns only one row
    kgronau Guru
    Currently Being Moderated
    I've create a table like yours:
    create table gateway.employees(id int(10) auto_increment,
    first_name varchar(30),
    last_name varchar(100),
    personal_code varchar(20),
    birthday date,
    salary decimal(10,2),
    is_active int(1),
    PRIMARY KEY (id));


    insert into gateway.employees (first_name,last_name) values ('Name 1', 'Name 1');
    insert into gateway.employees (first_name,last_name) values ('Name 2', 'Name 2');
    insert into gateway.employees (first_name,last_name) values ('Name 3', 'Name 3');
    insert into gateway.employees (first_name,last_name) values ('Name 4', 'Name 4');
    insert into gateway.employees (first_name,last_name) values ('Name 5', 'Name 5');

    select * from gateway.employees;

    and tested it using DataDirect and MySQL ODBC.

    The DataDirect shows all 5 lines whereas MySQL 5.1.0.7 ODBC shows only 1 row.


    (isql isn't using extended fetching like DG4ODBC does).

    Edited by: kgronau on Nov 4, 2010 4:03 PM


    Here the result (fetching ID column):
    MySQl ODBC;
    SQL> select "id" from "employees"@MYSQL_EMGTW_1121_DB;

    id
    ----------
    1

    DD ODBC V6:
    SQL> select "id" from "employees"@DD60_MYSQL_EMGTW_1121_DB;

    id
    ----------
    1
    2
    3
    4
    5

    Edited by: kgronau on Nov 4, 2010 4:33 PM

    The 32bit version of MySQL ODBC (5.1.5 - no 5.1.7 release yet installed) works also:
    SQL> select "id" from "employees"@MYSQL_32;

    id
    ----------
    1
    2
    3
    4
    5

    => so it is an ODBC issue
  • 12. Re: Select from Oracle to MySQL returns only one row
    Roel Oracle ACE Director
    Currently Being Moderated
    So it seems reproducible ;-)

    There is also something called iODBC... Maybe that 'll work - any experience on that?
  • 13. Re: Select from Oracle to MySQL returns only one row
    kgronau Guru
    Currently Being Moderated
    iODBC is -as far as I know- only a driver manager. Maybe Openlink or Easysoft offers another MySQL driver.
  • 14. Re: Select from Oracle to MySQL returns only one row
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    there are more persons calling the same problems:
    - http://forums.mysql.com/read.php?37,386848,386863#msg-386863
    - http://forums.mysql.com/read.php?61,389387,389387#msg-389387

    But not many responses.

    The first one has a solution by using limit, maybe in Oracle a ROWNUM < 10 will work.

    Herald ten Dam
    http://htendam.wordpress.com
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points