1 2 Previous Next 17 Replies Latest reply: Sep 18, 2012 4:43 AM by IZETU RSS

    Select from Oracle to MySQL returns only one row

    Roel Hartman
      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
          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 Hartman
            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-Oracle
              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 Hartman
                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
                  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 Hartman
                    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-Oracle
                      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 Hartman
                        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-Oracle
                          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 Hartman
                            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-Oracle
                              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 Hartman
                                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-Oracle
                                  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
                                    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