mailing.database.mysql-internals, mailing.database.mysql, mailing.database.mysql-java, comp.lang.java.programmer

From: Albretch (lbrtchx_at_hotmail.com)
Date: 02/21/04

  • Next message: Joe Weinstein: "Re: Is it possible to call find the number of columns in a JDBC query without executing it?" 
    Date: 20 Feb 2004 15:40:47 -0800

    if you check the store requirements of column types in mysql,

     http://mysql.azc.uam.mx/doc/en/Storage_requirements.html

     You will see advertised that, for example, for storing a TINYINT they
    use a byte, but when you create a table using the different types of
    data:

     CREATE TABLE mysql_data_types (
      iTINYI TINYINT,
      iTINYINTU TINYINT UNSIGNED,
      iSMALLI SMALLINT,
      iSMALLIU SMALLINT UNSIGNED,
      iMEDIUMI MEDIUMINT,
      iMEDIUMIU MEDIUMINT UNSIGNED,
      iI INT,
      iIU INT UNSIGNED,
      lBIGINT BIGINT,
      lBIGINTU BIGINT UNSIGNED,
      fFLOAT FLOAT,
      fFLOATX24 FLOAT(24),
      fFLOATX25 FLOAT(25),
      dDOUBLE DOUBLE,
      dDOUBLEP DOUBLE PRECISION,
      dREAL REAL,
      dDECIMALM1D0 DECIMAL(1,0),
      dDECIMALM1D1 DECIMAL(1,1),
      dDECIMALM1D2 DECIMAL(1,2),
      dNUMERICM1D0 NUMERIC(1,0),
      dNUMERICM1D1 NUMERIC(1,1),
      dNUMERICM1D2 NUMERIC(1,2),
      CHARM16 CHAR(16),
      CHARM16B CHAR(16) BINARY,
      aVARCHARM255 VARCHAR(255),
      aTINYTEXT TINYTEXT,
      aTEXT TEXT,
      aMEDIUMTEXT MEDIUMTEXT,
      aLONGTEXT LONGTEXT,
      bVARCHARM255 VARCHAR(255) BINARY,
      bTINYBLOB TINYBLOB,
      bBLOB BLOB,
      bMEDIUMBLOB MEDIUMBLOB,
      bLONGBLOB LONGBLOB,
      tYEAR YEAR,
      tTIME TIME,
      tDATE DATE,
      tTIMESTAMP TIMESTAMP,
      tDATETIME DATETIME);

      However when you use JDBC to query the column definitions, see:
     
    http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getColumns

      with code like:

       Cx = DriverManager.getConnection(aJDBCP + aTblDB, aUsr, aPW);
       DBMD = Cx.getMetaData();
    // __
       RSFlds = DBMD.getColumns(aTblDB, null, aTblNm, "%");
    // __
       while(RSFlds.next()){
        ++itFldIx;
        aFldNm = RSFlds.getString(4);
     // __
        iFTp = RSFlds.getInt(5); // java.sql.Types
        aFTpS = RSFlds.getString(6); // TYPE_NAME
        iFSz = RSFlds.getInt(7); // COLUMN_SIZE
        iDecDigs = RSFlds.getInt(9);
        iFNull = RSFlds.getInt(11);
        iOrdPos = RSFlds.getInt(17);
    ...
       }
    // __

     You will for example see that for a tinyint 4 bytes are being used
    and that the column type in string format, does not return the
    'unsigned' type specifier. Also, apparenly MySQL uses one byte less
    for 'unsigned' variables.

     Which is a little fishy to me. Both 'signed' and 'unsigned' variables
    should use the same storage they are just interpreted differently,
    since just one bit is used for the sign.

     MySQL returns the same DATA_TYPE in the form of an java.sql.Types int
    and TYPE_NAME as a String for 'signed' and 'unsigned' variables

     Trying to use the difference in the COLUMN_SIZE specifier does not
    work for example for 'BIGINT' and 'BIGINT UNSIGNED'.

     How can you tell from the Data Type Definition/specifiers which
    fields are unsigned ones?

     There should be a 'descriptive' way right? (Other than going the
    monkey way, inserting some data, retrieving it, comparing it and
    deleting the test record, with which, well . . ., should be definitely
    safe)

     Do you know of standard or none standard ways to do that?

  • Next message: Joe Weinstein: "Re: Is it possible to call find the number of columns in a JDBC query without executing it?"

    Relevant Pages