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

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

Post by lbrtch » Sun, 22 Feb 2004 08:40:47


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

http://www.yqcomputer.com/

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://www.yqcomputer.com/ #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?
 
 
 

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

Post by Mark Matth » Mon, 23 Feb 2004 02:40:08


[snip]

Albretch,

The column COLUMN_SIZE is not a storage length specifier, it specifies
precision (i.e. number of digits), according to the JDBC spec.


This has been fixed in the source repository for the driver, so that
when you retrieve TYPE_NAME, it will end with 'unsigned' for the
unsigned types. Therefore it will show up in Connector/J 3.0.12 and
Connector/J 3.1.2. Thanks for pointing out the bug!

Regards,

-Mark


--
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com

Meet the MySQL Team! April 14-16, 2004 http://www.yqcomputer.com/