MySQL unsigned int data types and ResultSet.getInt()

How can one retrieve a value from a column of the unsigned int type, using
getInt() method of the ResultSet? It cannot handle values above +2G, and the
ResultSet interface does not provide getUnsignedInt() method. Ditto for
unsigned short and unsigned byte.

Thank you for any advice.

Alex Molochnikov
Gestalt Corporation
www.gestalt.com

On Mon, 17 May 2004 06:15:46 GMT, Alex Molochnikov <NOBODY@NOSPAM.COM>
wrote:

> How can one retrieve a value from a column of the unsigned int type,
> using
> getInt() method of the ResultSet? It cannot handle values above +2G, and
> the
> ResultSet interface does not provide getUnsignedInt() method. Ditto for
> unsigned short and unsigned byte.

Use getLong()?
Michiel

Alex Molochnikov wrote:
> How can one retrieve a value from a column of the unsigned int type, using
> getInt() method of the ResultSet? It cannot handle values above +2G, and t
he
> ResultSet interface does not provide getUnsignedInt() method. Ditto for
> unsigned short and unsigned byte.

Though I haven't tried it with MySQL, it should be possible to use
getLong(...) for that (and getInt(...) for unsigned short, etc.)

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

"Chris Smith" <cdsmith@twu.net> wrote in message
news:MPG.1b1270e6c00ce71f989adf@news.pop4.net...
> Though I haven't tried it with MySQL, it should be possible to use
> getLong(...) for that (and getInt(...) for unsigned short, etc.)

I thought about it. Unfortunately, there is also unsigned long ("bigint
unsigned"), and the buck stops here.

A universal solution could be to use getObject(), and then morph it into the
desired type, but there is another catch: MySQL's JDBC driver does not tell
whether the data type of the given column has the unsigned attribute. So,
unless the programmer of the application that retrieves data from MySQL also
happens to know the exact data type of the column, there is no way for the
app to handle it.

Besides, using getObject() on primitive data types would mean a break
between the data type and the method designated for the retrieval (int ==
getInt(), short == getShort() etc.), and tax the performance by having to
create object wrappers, and then extract their content.

It seems to me that the standard ResultSet interface is not prepared to deal
with those databases that use "unsigned" attribute for numeric types.

Alex.

Alex Molochnikov wrote:
> I thought about it. Unfortunately, there is also unsigned long ("bigint
> unsigned"), and the buck stops here.

Hmm.  getBigDecimal should work, but it would be a pain.

Incidentally, it may also be worth trying getLong(), and seeing if you
get the correct 2's complement result, and if you can deal with it in
that form (perhaps by converting immediately to BigInteger).

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

Alex Molochnikov wrote:

> "Chris Smith" <cdsmith@twu.net> wrote in message
> news:MPG.1b1270e6c00ce71f989adf@news.pop4.net... 
>
> I thought about it. Unfortunately, there is also unsigned long ("bigint
> unsigned"), and the buck stops here.
>
> A universal solution could be to use getObject(), and then morph it into t
he
> desired type, but there is another catch: MySQL's JDBC driver does not tel
l
> whether the data type of the given column has the unsigned attribute. So,
> unless the programmer of the application that retrieves data from MySQL al
so
> happens to know the exact data type of the column, there is no way for the
> app to handle it.
>
> Besides, using getObject() on primitive data types would mean a break
> between the data type and the method designated for the retrieval (int ==
> getInt(), short == getShort() etc.), and tax the performance by having to
> create object wrappers, and then extract their content.
>
> It seems to me that the standard ResultSet interface is not prepared to de
al
> with those databases that use "unsigned" attribute for numeric types.

Alex,

No it is not, however MySQL's JDBC driver tries to make the best attempt
it can...i.e. if you call getObject() on an unsigned int, you will get a
long, if you call getObject() on an unsigned long you will get a big
decimal...Unfortunately, the driver has to remain within the 'realms' of
supported JDBC types (and in fact Java types), which is why these
mappings are used.

-Mark


--
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

"Mark Matthews" <mark@mysql.com> wrote in message
news:ZMqdnfAvZpOgczXdRVn-tw@speakeasy.net...
> Alex Molochnikov wrote: 
deal 
>
> Alex,
>
> No it is not, however MySQL's JDBC driver tries to make the best attempt
> it can...i.e. if you call getObject() on an unsigned int, you will get a
> long, if you call getObject() on an unsigned long you will get a big
> decimal...Unfortunately, the driver has to remain within the 'realms' of
> supported JDBC types (and in fact Java types), which is why these
> mappings are used.

Mark,

Somewhere on MySQL website I saw a note from you that version 3.0.12 of the
driver will add the word "unsigned" to the column TYPE_NAME property (as
returned by getColumns() method of DatabaseMetaData, in position 6 of the
ResultSet). I could use this to internally promote the data types such that
every TINYINT is treated as SMALLINT, SMALLINT is treated as INTEGER etc. I
am going to do this now for the lack of better choice, but when the type
name adds "unsigned", I could do this promotion selectively on only those
columns that use unsigned numeric types.

Any idea when this feature will be implemented, and/or when this version of
the driver will become available as beta?

Thanks,

Alex.

"Chris Smith" <cdsmith@twu.net> wrote in message
news:MPG.1b12971fa3e34dc989ae2@news.pop4.net...
> Alex Molochnikov wrote: 
>
> Hmm.  getBigDecimal should work, but it would be a pain.
>
> Incidentally, it may also be worth trying getLong(), and seeing if you
> get the correct 2's complement result, and if you can deal with it in
> that form (perhaps by converting immediately to BigInteger).

The problem is that even if the 2's complement is formed properly, there is
no way to tell whether the value carried by the "long int" variable is the
original one, or a mangled remainder of it. One would have to know if the
respective table column was created as "bigint unsigned", but at this moment
the JDBC driver does not provide this info. See my response to Mark Matthews
in this regard.

Alex.

"Alex Molochnikov" <NOBODY@NO_SPAM.com> wrote in message
news:7L8qc.11413$RM.10253@edtnps89...
> Somewhere on MySQL website I saw a note from you that version 3.0.12 of
the
> driver will add the word "unsigned" to the column TYPE_NAME property ...
>
> Any idea when this feature will be implemented, and/or when this version
of
> the driver will become available as beta?

Wow, I did not know the release of this version was only one day away when I
wrote this.
Thanks for the fix.

Alex.