4 Replies Latest reply on Sep 23, 2004 3:07 AM by Dabin Lin

    blob CMP MySQL EJB JBoss3.2 mapping problem

    ebende Newbie

      Hi,
      I've read all threads concerning BLOBs, CMP and MySQL.
      Yet I don't know how to read BLOBs with CMP in combination with
      - mysql-connector-java-3.0.8-stable-bin.jar (driver)
      - jboss3.2
      - mysql4.0
      By the way, I managed to read BLOB 'manually' through direct query execution. With


      ResultSet rs = ...;
      java.sql.Blob blob= rs.getBlob("myBlob");

      to get the Blob from the result set.


      My questions are:
      A.

      What should be in jbosscmp-jdbc.xml?
      This?

      <cmp-field>
      <field-name>myBlob</field-name>
      <column-name>myBlob</column-name>
      <jdbc-type>JAVA_OBJECT</jdbc-type>
      <sql-type>BLOB</sql-type>
      </cmp-field>

      Or?
      <cmp-field>
      <field-name>myBlob</field-name>
      <column-name>myBlob</column-name>
      <jdbc-type>BLOB</jdbc-type>
      <sql-type>BLOB</sql-type>
      </cmp-field>

      Or

      <cmp-field>
      <field-name>myBlob</field-name>
      <column-name>myBlob</column-name>
      <jdbc-type>VARBINARY</jdbc-type>
      <sql-type>BLOB</sql-type>
      </cmp-field>

      or something else?

      I saw several stories on this forum which where in contradiction.

      B.
      What are the field types in the CMP-EJB for the corresponding BLOB?

      Object?
      or
      byte[] ?
      or
      java.sql.Blob ?
      or
      a wrapper class with "byte[] myBytes" as field and with Serializable implementation?
      or
      Something else?

      Any help is appreciated very much.
      Thanks,

      Evert Bende
      http://212.203.14.69/topsolar/cgi-bin/climatetop50/cgi-bin/topsites.cgi?action=button&id=69
      http://www.wattabout.com/index


        • 1. Re: blob CMP MySQL EJB JBoss3.2 mapping problem
          Alexey Loubyansky Master

          Doesn't default mapping work for you? As to the Java type, any of those should work. What's the problem?

          • 2. Re: blob CMP MySQL EJB JBoss3.2 mapping problem
            ebende Newbie

            If you say default mapping I suppose you mean the one in jboss-3.2.2/server/default/conf/standardjbosscmp-jdbc.xml, right?

            Well I also had problems with the default mappings for timestamp/datetime. None of the listed mappings worked for me.
            However, what worked for me is:

            JAVA Sql type: TIMESTAMP
            Vendor type: datetime (in Mysql)
            CMP field: java.sql.Timestamp

            which was arranged by my IDE.

            If i'm correct this mapping is not in jboss-3.2.2/server/default/conf/standardjbosscmp-jdbc.xml. This one only contains:


            <java-type>java.sql.Timestamp</java-type>
            <jdbc-type>TIMESTAMP</jdbc-type>
            <sql-type>TIMESTAMP</sql-type>
            <!--
            | This type is problematical because mysql does not have any
            | date/time types that store milliseconds. You should avoid it.
            -->


            <java-type>java.util.Date</java-type>
            <jdbc-type>TIMESTAMP</jdbc-type>
            <sql-type>DATETIME</sql-type>
            <!--
            | Note that you lose granularity here
            | Use a numeric type and store milliseconds if you really need it
            -->


            <java-type>java.sql.Date</java-type>
            <jdbc-type>DATE</jdbc-type>
            <sql-type>DATETIME</sql-type>


            <java-type>java.sql.Time</java-type>
            <jdbc-type>TIME</jdbc-type>
            <sql-type>TIME</sql-type>



            As far as BLOBs are concerned, in file jboss-3.2.2/server/default/conf/standardjbosscmp-jdbc.xml you have the mapping


            <java-type>java.lang.Object</java-type>
            <jdbc-type>BLOB</jdbc-type>
            <sql-type>LONGBLOB</sql-type>
            <!--
            | Change this from LONGBLOB to BLOB if you know your
            | serialized object will be <= 2^16 bytes
            -->


            This one doesn't work for me. JBuilderX makes a scheme from the DB as follows:

            JAVA Sql type: VARBINARY
            Vendor type: blob (in Mysql)
            CMP field: byte[]

            When I use this it doesn't work either.



            The mapping done by the IDE is based on the driver, which is confirmed by the "MysqlDefs" class from Mark Matthew's driver (the official one now):


            Maybe I am misuderstanding something fundamentally.

            Any help would be highly appreciated.


            /*
            Copyright (C) 2002 MySQL AB

            This program is free software; you can redistribute it and/or modify
            it under the terms of the GNU General Public License as published by
            the Free Software Foundation; either version 2 of the License, or
            (at your option) any later version.

            This program is distributed in the hope that it will be useful,
            but WITHOUT ANY WARRANTY; without even the implied warranty of
            MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
            GNU General Public License for more details.

            You should have received a copy of the GNU General Public License
            along with this program; if not, write to the Free Software
            Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

            */
            package com.mysql.jdbc;

            import java.sql.Types;


            /**
            * MysqlDefs contains many values that are needed for communication
            * with the MySQL server.
            *
            * @author Mark Matthews
            * @version $Id: MysqlDefs.java,v 1.6.2.4 2003/03/10 13:02:48 mmatthew Exp $
            */
            final class MysqlDefs {
            //
            // Constants defined from mysql
            //
            // DB Operations
            static final int SLEEP = 0;
            static final int QUIT = 1;
            static final int INIT_DB = 2;
            static final int QUERY = 3;
            static final int FIELD_LIST = 4;
            static final int CREATE_DB = 5;
            static final int DROP_DB = 6;
            static final int RELOAD = 7;
            static final int SHUTDOWN = 8;
            static final int STATISTICS = 9;
            static final int PROCESS_INFO = 10;
            static final int CONNECT = 11;
            static final int PROCESS_KILL = 12;
            static final int DEBUG = 13;
            static final int PING = 14;
            static final int TIME = 15;
            static final int DELAYED_INSERT = 16;
            static final int CHANGE_USER = 17;
            static final int COM_BINLOG_DUMP = 18;
            static final int COM_TABLE_DUMP = 19;
            static final int COM_CONNECT_OUT = 20;
            static final int COM_REGISTER_SLAVE = 21;
            static final int COM_PREPARE = 22;
            static final int COM_EXECUTE = 23;
            static final int COM_LONG_DATA = 24;

            // Data Types
            static final int FIELD_TYPE_DECIMAL = 0;
            static final int FIELD_TYPE_TINY = 1;
            static final int FIELD_TYPE_SHORT = 2;
            static final int FIELD_TYPE_LONG = 3;
            static final int FIELD_TYPE_FLOAT = 4;
            static final int FIELD_TYPE_DOUBLE = 5;
            static final int FIELD_TYPE_NULL = 6;
            static final int FIELD_TYPE_TIMESTAMP = 7;
            static final int FIELD_TYPE_LONGLONG = 8;
            static final int FIELD_TYPE_INT24 = 9;
            static final int FIELD_TYPE_DATE = 10;
            static final int FIELD_TYPE_TIME = 11;
            static final int FIELD_TYPE_DATETIME = 12;

            // Newer data types
            static final int FIELD_TYPE_YEAR = 13;
            static final int FIELD_TYPE_NEWDATE = 14;
            static final int FIELD_TYPE_ENUM = 247;
            static final int FIELD_TYPE_SET = 248;

            // Older data types
            static final int FIELD_TYPE_TINY_BLOB = 249;
            static final int FIELD_TYPE_MEDIUM_BLOB = 250;
            static final int FIELD_TYPE_LONG_BLOB = 251;
            static final int FIELD_TYPE_BLOB = 252;
            static final int FIELD_TYPE_VAR_STRING = 253;
            static final int FIELD_TYPE_STRING = 254;
            static final int ER_WARNING_NOT_COMPLETE_ROLLBACK = 1196;

            // Limitations
            static final int MAX_ROWS = 50000000; // From the MySQL FAQ

            /**
            * Maps the given MySQL type to the correct JDBC type.
            */
            static int mysqlToJavaType(int mysqlType) {
            int jdbcType;

            switch (mysqlType) {
            case MysqlDefs.FIELD_TYPE_DECIMAL:
            jdbcType = Types.DECIMAL;

            break;

            case MysqlDefs.FIELD_TYPE_TINY:
            jdbcType = Types.TINYINT;

            break;

            case MysqlDefs.FIELD_TYPE_SHORT:
            jdbcType = Types.SMALLINT;

            break;

            case MysqlDefs.FIELD_TYPE_LONG:
            jdbcType = Types.INTEGER;

            break;

            case MysqlDefs.FIELD_TYPE_FLOAT:
            jdbcType = Types.REAL;

            break;

            case MysqlDefs.FIELD_TYPE_DOUBLE:
            jdbcType = Types.DOUBLE;

            break;

            case MysqlDefs.FIELD_TYPE_NULL:
            jdbcType = Types.NULL;

            break;

            case MysqlDefs.FIELD_TYPE_TIMESTAMP:
            jdbcType = Types.TIMESTAMP;

            break;

            case MysqlDefs.FIELD_TYPE_LONGLONG:
            jdbcType = Types.BIGINT;

            break;

            case MysqlDefs.FIELD_TYPE_INT24:
            jdbcType = Types.INTEGER;

            break;

            case MysqlDefs.FIELD_TYPE_DATE:
            jdbcType = Types.DATE;

            break;

            case MysqlDefs.FIELD_TYPE_TIME:
            jdbcType = Types.TIME;

            break;

            case MysqlDefs.FIELD_TYPE_DATETIME:
            jdbcType = Types.TIMESTAMP;

            break;

            case MysqlDefs.FIELD_TYPE_YEAR:
            jdbcType = Types.DATE;

            break;

            case MysqlDefs.FIELD_TYPE_NEWDATE:
            jdbcType = Types.DATE;

            break;

            case MysqlDefs.FIELD_TYPE_ENUM:
            jdbcType = Types.CHAR;

            break;

            case MysqlDefs.FIELD_TYPE_SET:
            jdbcType = Types.CHAR;

            break;

            case MysqlDefs.FIELD_TYPE_TINY_BLOB:
            jdbcType = Types.VARBINARY;

            break;

            case MysqlDefs.FIELD_TYPE_MEDIUM_BLOB:
            jdbcType = Types.LONGVARBINARY;

            break;

            case MysqlDefs.FIELD_TYPE_LONG_BLOB:
            jdbcType = Types.LONGVARBINARY;

            break;

            case MysqlDefs.FIELD_TYPE_BLOB:
            jdbcType = Types.LONGVARBINARY;

            break;

            case MysqlDefs.FIELD_TYPE_VAR_STRING:
            jdbcType = Types.VARCHAR;

            break;

            case MysqlDefs.FIELD_TYPE_STRING:
            jdbcType = Types.CHAR;

            break;

            default:
            jdbcType = Types.VARCHAR;
            }

            return jdbcType;
            }

            /**
            * Maps the given MySQL type to the correct JDBC type.
            */
            static int mysqlToJavaType(String mysqlType) {
            if (mysqlType.equalsIgnoreCase("TINYINT")) {
            return java.sql.Types.TINYINT;
            } else if (mysqlType.equalsIgnoreCase("SMALLINT")) {
            return java.sql.Types.SMALLINT;
            } else if (mysqlType.equalsIgnoreCase("MEDIUMINT")) {
            return java.sql.Types.SMALLINT;
            } else if (mysqlType.equalsIgnoreCase("INT")) {
            return java.sql.Types.INTEGER;
            } else if (mysqlType.equalsIgnoreCase("INTEGER")) {
            return java.sql.Types.INTEGER;
            } else if (mysqlType.equalsIgnoreCase("BIGINT")) {
            return java.sql.Types.BIGINT;
            } else if (mysqlType.equalsIgnoreCase("INT24")) {
            return java.sql.Types.BIGINT;
            } else if (mysqlType.equalsIgnoreCase("REAL")) {
            return java.sql.Types.REAL;
            } else if (mysqlType.equalsIgnoreCase("FLOAT")) {
            return java.sql.Types.FLOAT;
            } else if (mysqlType.equalsIgnoreCase("DECIMAL")) {
            return java.sql.Types.DECIMAL;
            } else if (mysqlType.equalsIgnoreCase("NUMERIC")) {
            return java.sql.Types.NUMERIC;
            } else if (mysqlType.equalsIgnoreCase("DOUBLE")) {
            return java.sql.Types.DOUBLE;
            } else if (mysqlType.equalsIgnoreCase("CHAR")) {
            return java.sql.Types.CHAR;
            } else if (mysqlType.equalsIgnoreCase("VARCHAR")) {
            return java.sql.Types.VARCHAR;
            } else if (mysqlType.equalsIgnoreCase("DATE")) {
            return java.sql.Types.DATE;
            } else if (mysqlType.equalsIgnoreCase("TIME")) {
            return java.sql.Types.TIME;
            } else if (mysqlType.equalsIgnoreCase("YEAR")) {
            return java.sql.Types.DATE;
            } else if (mysqlType.equalsIgnoreCase("TIMESTAMP")) {
            return java.sql.Types.TIMESTAMP;
            } else if (mysqlType.equalsIgnoreCase("DATETIME")) {
            return java.sql.Types.TIMESTAMP;
            } else if (mysqlType.equalsIgnoreCase("TINYBLOB")) {
            return java.sql.Types.BINARY;
            } else if (mysqlType.equalsIgnoreCase("BLOB")) {
            return java.sql.Types.VARBINARY;
            } else if (mysqlType.equalsIgnoreCase("MEDIUMBLOB")) {
            return java.sql.Types.VARBINARY;
            } else if (mysqlType.equalsIgnoreCase("LONGBLOB")) {
            return java.sql.Types.LONGVARBINARY;
            } else if (mysqlType.equalsIgnoreCase("TINYTEXT")) {
            return java.sql.Types.VARCHAR;
            } else if (mysqlType.equalsIgnoreCase("TEXT")) {
            return java.sql.Types.LONGVARCHAR;
            } else if (mysqlType.equalsIgnoreCase("MEDIUMTEXT")) {
            return java.sql.Types.LONGVARCHAR;
            } else if (mysqlType.equalsIgnoreCase("ENUM")) {
            return java.sql.Types.CHAR;
            } else if (mysqlType.equalsIgnoreCase("SET")) {
            return java.sql.Types.CHAR;
            }

            // Punt
            return java.sql.Types.OTHER;
            }
            }





            • 3. Re: blob CMP MySQL EJB JBoss3.2 mapping problem
              Alexey Loubyansky Master

              If the default doesn't work, then you have to find out it yourself. Please, let us know what worked for you. Thanks.