This discussion is archived
7 Replies Latest reply: Nov 14, 2012 4:59 AM by Rydman RSS

ORA 28528 for MYSQL column varchar(255)

Rydman Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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