Get UNSIGNED attr for BIGINT in MySQL

Thread view:

RichCreamer - 15 Jun 2004 22:06 GMT

I need to use java.sql.DatabaseMetaData to discover if a table column is
signed or unsigned. Specifically, MySQL BIGINT UNSIGNED types. I was
reading post: http://tinyurl.com/3d4jk in which they indicated MySQL
JDBC support was enhanced so that the UNSIGNED attribute was included
in a column's TYPE_NAME (field 6 of
java.sql.DatabaseMetaData.getColumns() result set). But I am not
finding this to be the case for MySQL 4.1 and the latest Connector/J.
Is there any JDBC method for determining if a numeric type is
signed/unsigned via the java.sql.DatabaseMetaData interface? I saw
that java.sql.ResultSetMetaData has an isSigned( int column ) method
but this seems only useful for when reading data. But I need to
capture the metadata for the DBMS whether or not a table has data in it
which can be read. Any help would be appreciated.

--
RichCreamer
------------------------------------------------------------------------

Reply to this Message

Mark Matthews - 16 Jun 2004 03:08 GMT

> I need to use java.sql.DatabaseMetaData to discover if a table column is
> signed or unsigned. Specifically, MySQL BIGINT UNSIGNED types. I was
[quoted text clipped - 16 lines]
> ------------------------------------------------------------------------
>

Rich,

Testing it here locally shows that it works with MySQL-4.1.2 and
Connector/J 3.1.2 (i.e. the following Junit test passes):

/**
* Tests whether or not unsigned columns are reported correctly in
* DBMD.getColumns
*
* @throws Exception
*/
public void testGetColumnsUnsigned() throws Exception {
try {
this.stmt.executeUpdate("DROP TABLE IF EXISTS
testGetUnsignedCols");
this.stmt.executeUpdate(
"CREATE TABLE testGetUnsignedCols (field1 BIGINT, field2
BIGINT UNSIGNED)");

DatabaseMetaData dbmd = this.conn.getMetaData();

this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
"testGetUnsignedCols", "%");

assertTrue(this.rs.next());
// This row doesn't have 'unsigned' attribute
assertTrue(this.rs.next());
assertTrue(rs.getString(6).toLowerCase().indexOf("unsigned")
!= -1);
} finally {
this.stmt.executeUpdate("DROP TABLE IF EXISTS
testGetUnsignedCols");
}
}

Could you post the _exact_ versions of MySQL and Connector/J that you
are using, and example code that demonstrates the issue?

-Mark

Signature

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

MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php

Reply to this Message

Get UNSIGNED attr for BIGINT in MySQL

Free Magazines