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
      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
            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
                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
                    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
                        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
                            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
                                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