7 Replies Latest reply: Nov 14, 2012 6:59 AM by Rydman RSS

    ORA 28528 for MYSQL column varchar(255)

    Rydman
      Hi. I am accessing an MYSQL server from my Oracle (running Windows) using ODBC and a db-link.

      Oracle version 11.2.0.3 64bit
      MySQL version 5.5.14
      MySQL ODBC drivers version 5.2w (5.02.02.00)

      Doing statements like this works fine:
      select "owner" from "data_view"@DB_MYSQL
      select * from "data_view"@DB_MYSQL
      This gives me an nice output of the data in the column(s).

      But this fails with a ORA-28528: Heterogeneous Services datatype conversion error
      create table t as select "owner" from "data_view"@DB_MYSQL
      create table t as select * from "data_view"@DB_MYSQL
      The MySQL column is defined as
      owner     varchar(255)     latin1_swedish_ci
      The hs-inifiles contains nothing more then HS_FDS_CONNECT
      And no option othen then the default is selected for the ODBC-setting

      I have, after googling, tried to add HS_FDS_SQLLEN_INTERPRETATION=64 and the ROW=1 but to no avail.

      Any tips?

      Edited by: Rydman on Nov 14, 2012 1:33 AM
        • 1. Re: ORA 28528 for MYSQL column varchar(255)
          Kgronau-Oracle
          Could you please post a describe of the table through the gateway:
          desc "data_view"@DB_MYSQL

          and the character set of the Oracle database:
          select * from v$nls_parameters;
          • 2. Re: ORA 28528 for MYSQL column varchar(255)
            Rydman
            Here are the nls_parameters and the desc of the table:
            SQL> desc "data_view"@DB_MYSQL
             Navn                                                            NULL?    Type
             --------------------------------------------------------------- -------- ------------
             id                                                              NOT NULL NUMBER(10)
             owner                                                                    RAW(255)
             ip_address                                                               RAW(255)
             slot                                                                     RAW(255)
             port                                                                     RAW(255)
            
            SQL>
            SQL> select * from v$nls_parameters;
            
            PARAMETER                                                        VALUE
            ---------------------------------------------------------------- ------------------------------
            NLS_LANGUAGE                                                     DANISH
            NLS_TERRITORY                                                    DENMARK
            NLS_CURRENCY                                                     kr
            NLS_ISO_CURRENCY                                                 DENMARK
            NLS_NUMERIC_CHARACTERS                                           ,.
            NLS_CALENDAR                                                     GREGORIAN
            NLS_DATE_FORMAT                                                  YYYY.MM.DD hh24:mi:ss
            NLS_DATE_LANGUAGE                                                DANISH
            NLS_CHARACTERSET                                                 WE8MSWIN1252
            NLS_SORT                                                         DANISH
            NLS_TIME_FORMAT                                                  HH24:MI:SSXFF
            
            PARAMETER                                                        VALUE
            ---------------------------------------------------------------- ------------------------------
            NLS_TIMESTAMP_FORMAT                                             RR-MM-DD HH24:MI:SSXFF
            NLS_TIME_TZ_FORMAT                                               HH24:MI:SSXFF TZR
            NLS_TIMESTAMP_TZ_FORMAT                                          RR-MM-DD HH24:MI:SSXFF TZR
            NLS_DUAL_CURRENCY                                                Ç
            NLS_NCHAR_CHARACTERSET                                           AL16UTF16
            NLS_COMP                                                         BINARY
            NLS_LENGTH_SEMANTICS                                             BYTE
            NLS_NCHAR_CONV_EXCP                                              FALSE
            
            19 rµkker er valgt.
            
            SQL>
            Edited by: Rydman on Nov 14, 2012 9:59 AM

            Edited by: Rydman on Nov 14, 2012 10:00 AM
            • 3. Re: ORA 28528 for MYSQL column varchar(255)
              Kgronau-Oracle
              your varchar's are mapped to Oracle RAWS instead of varchar.

              Could you please provide your odbc.ini file and also an ODBC trace?
              • 4. Re: ORA 28528 for MYSQL column varchar(255)
                Rydman
                I have tried to access two other MYSQL databases, just to see if the problem occurs there as well.

                One of the other two works fine, and the other not.
                SQL> desc "data_retention"@DB_MYSQL_I
                 Navn                                                                                NULL?    Type
                 -------------------------------------------------------------------- -------- ------------
                 customer_number                                                      NOT NULL NUMBER(10)
                 customer_name                                                                 VARCHAR2(50)
                 customer_street                                                               VARCHAR2(50)
                 customer_postal_code                                                          VARCHAR2(50)
                 customer_postal_area                                                          VARCHAR2(50)
                This data can be accessed correctly.

                The links and ODBC connections are set up identical for all three of them.

                The only thing that I can see differs is the MySQL version (the columns are all varchar(50-255) in MySQL).

                The two where I get the problem is running MySQL version 5.5.14 and the one were it's working is running MySQL version 5.0.67.

                This is my first encounter with MySQL so have really no clue...

                Edited by: Rydman on Nov 14, 2012 10:37 AM
                • 5. Re: ORA 28528 for MYSQL column varchar(255)
                  Kgronau-Oracle
                  as i said, the root cause is the mapping of the varchar column to raw instead of varchar2.
                  To diagnose a possible root cause, please upload the odbc.ini you're using as well as an ODBC trace file when you try to access the data using DG4ODBC.
                  • 6. Re: ORA 28528 for MYSQL column varchar(255)
                    Rydman
                    The ODBC.ini:

                    I have been trying install different versions of the driver, but they all report the same error.

                    [ODBC 32 bit Data Sources]
                    DB_JUKEBOX=SQL Server (32 bit)
                    DB_NAIS=MySQL ODBC 5.2w Driver (32 bit)
                    DB_IGGY=MySQL ODBC 5.2w Driver (32 bit)
                    DB_VELVET=MySQL ODBC 5.2w Driver (32 bit)
                    DB_OWL=MySQL ODBC 5.2w Driver (32 bit)
                    DB_PDB_W=MySQL ODBC 5.2w Driver (32 bit)
                    DB_PDB=MySQL ODBC 5.2a Driver (32 bit)
                    [DB_JUKEBOX]
                    Driver32=C:\Windows\system32\SQLSRV32.dll
                    [DB_NAIS]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\Unicode\myodbc5w.dll
                    [DB_IGGY]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\Unicode\myodbc5w.dll
                    [DB_VELVET]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\Unicode\myodbc5w.dll
                    [DB_OWL]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\Unicode\myodbc5w.dll
                    [DB_PDB_W]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\Unicode\myodbc5w.dll
                    [DB_PDB]
                    Driver32=C:\Program Files\MySQL\Connector ODBC 5.2\ANSI\myodbc5a.dll
                    The statement when tracing:

                    select "owner" from "data_retention"@DB_PDB

                    The full trace: http://rubberbandman.se/temp/odbc/SQL.LOG

                    Snippet of the trace:
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLFreeStmt 
                              HSTMT               0x000000000439B7B0
                              UWORD                        2 <SQL_UNBIND>
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
                              HSTMT               0x000000000439B7B0
                              UWORD                        2 <SQL_UNBIND>
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLFreeHandle 
                              SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                              SQLHANDLE           0x000000000439B7B0
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
                              SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                              SQLHANDLE           0x000000000439B7B0
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLAllocHandle 
                              SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                              SQLHANDLE           0x000000000439AE60
                              SQLHANDLE *         0x0000000005342A00
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
                              SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                              SQLHANDLE           0x000000000439AE60
                              SQLHANDLE *         0x0000000005342A00 ( 0x000000000439B7B0)
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLPrepare 
                              HSTMT               0x000000000439B7B0
                              UCHAR *             0x0000000006173448 [      42] "SELECT A1.`owner` FROM `data_retention` A1"
                              SDWORD                    42
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
                              HSTMT               0x000000000439B7B0
                              UCHAR *             0x0000000006173448 [      42] "SELECT A1.`owner` FROM `data_retention` A1"
                              SDWORD                    42
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLNumResultCols 
                              HSTMT               0x000000000439B7B0
                              SWORD *             0x0000000005342A78
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
                              HSTMT               0x000000000439B7B0
                              SWORD *             0x0000000005342A78 (1)
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLDescribeCol 
                              HSTMT               0x000000000439B7B0
                              UWORD                        1 
                              UCHAR *             0x000000000012E980 
                              SWORD                       31 
                              SWORD *             0x000000000012EA60
                              SWORD *             0x000000000012EA5C
                              SQLULEN *           0x000000000012EA28
                              SWORD *             0x000000000012EA64
                              SWORD *             0x000000000012EA68
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
                              HSTMT               0x000000000439B7B0
                              UWORD                        1 
                              UCHAR *             0x000000000012E980 [       5] "owner"
                              SWORD                       31 
                              SWORD *             0x000000000012EA60 (5)
                              SWORD *             0x000000000012EA5C (12)
                              SQLULEN *           0x000000000012EA28 (255)
                              SWORD *             0x000000000012EA64 (0)
                              SWORD *             0x000000000012EA68 (1)
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLColAttribute 
                              SQLHSTMT            0x000000000439B7B0
                              SQLSMALLINT                  1 
                              SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
                              SQLPOINTER         0x0000000000000000
                              SQLSMALLINT                  0 
                              SQLSMALLINT *       0x0000000000000000
                              SQLPOINTER          [Unknown attribute 1013]
                    
                    dg4odbcTDC_PDB  1cec-5e8     EXIT  SQLColAttribute  with return code 0 (SQL_SUCCESS)
                              SQLHSTMT            0x000000000439B7B0
                              SQLSMALLINT                  1 
                              SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
                              SQLPOINTER         0x0000000000000000
                              SQLSMALLINT                  0 
                              SQLSMALLINT *       0x0000000000000000
                              SQLPOINTER          [Unknown attribute 1013]
                    
                    dg4odbcTDC_PDB  1cec-5e8     ENTER SQLSetStmtAttr 
                              SQLHSTMT            0x000000000439B7B0
                              SQLINTEGER                  27 <SQL_ATTR_ROW_ARRAY_SIZE>
                              SQLPOINTER                 1
                              SQLINTEGER                   0 
                    Edited by: Rydman on Nov 14, 2012 2:00 PM
                    • 7. Re: ORA 28528 for MYSQL column varchar(255)
                      Rydman
                      I have now been able to access all three databases views correctly.

                      Those not working, worked using the MySQL ODBC driver version 3.51.27.

                      So these are the combinations I am running:

                      Oracle version 11.2.0.3 64bit
                      MySQL version 5.0.67
                      MySQL ODBC drivers version 5.2w (5.02.02.00)

                      Oracle version 11.2.0.3 64bit
                      MySQL version 5.5.14
                      MySQL ODBC drivers version 3.51.27

                      The original problem, why cant I use the newer version of the drivers with a newer version of the database is still unresolved, but at least I got everything working.

                      Edited by: Rydman on Nov 14, 2012 1:59 PM