#1037 Sybase ASE 15 - tinyint displayed as <Error> if >127

[zagu]

As per http://www.sybase15.com/datatypes, tinyint range in Sybase ASE 15 is 0 to 255. Any value for a tiny-int column >127 shows as <Error>, I presume because the driver / app assumes it's a signed byte.

[Rob Manning]

Interesting - the range for some other databases is limited to 7 bits as they use a bit for the sign:

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

And this stack overflow answer does a good job of comparing:

http://stackoverflow.com/questions/2991405/what-is-the-difference-between-tinyint-smallint-mediumint-bigint-and-int-in-s

Here is the stack trace that I get which presumably led the OP to conclude a sign issue:

java.sql.SQLException: JZ00B: Numeric overflow.
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.jdbc.Convert.checkByteOflo(Unknown Source)
at com.sybase.jdbc3.tds.TdsInt.getByte(Unknown Source)
at com.sybase.jdbc3.jdbc.SybResultSet.getByte(Unknown Source)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeByte.readResultSet(DataTypeByte.java:371)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:572)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:602)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:174)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setResultSet(ResultSetDataSet.java:177)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setContentsTabResultSet(ResultSetDataSet.java:100)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:344)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:128)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.lang.Thread.run(Thread.java:662)

Bytes in Java are always signed as there are no unsigned types in Java.

Rob

[Rob Manning]

As I suspected, "unsigned smallint" is a type allowed by Sybase to have a range of 0 to 65535 (dcx.sybase.com/1100/en/dbreference_en11/smallint.html). And we overflow our storage by using a Java short:

java.sql.SQLException: JZ00B: Numeric overflow.
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.jdbc.Convert.checkShortOflo(Unknown Source)
at com.sybase.jdbc3.tds.TdsInt.getShort(Unknown Source)
at com.sybase.jdbc3.jdbc.SybResultSet.getShort(Unknown Source)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeShort.readResultSet(DataTypeShort.java:366)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:572)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:602)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:174)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setResultSet(ResultSetDataSet.java:177)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setContentsTabResultSet(ResultSetDataSet.java:100)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:344)
at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:128)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.lang.Thread.run(Thread.java:662)

[zagu]

Hi Rob - thanks for taking a look at this.

I agree, that's the exception. I use jtds (http://jtds.sourceforge.net/), but the exception is along the same lines:

java.sql.SQLException: Numeric overflow in conversion of value 150 to type TINYINT.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:251)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getByte(JtdsResultSet.java:633)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeByte.readResultSet(DataTypeByte.java:371)
at net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory.readResultSet(CellComponentFactory.java:572)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.doContentTabRead(ResultSetReader.java:602)
at net.sourceforge.squirrel_sql.fw.sql.ResultSetReader.readRow(ResultSetReader.java:174)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setResultSet(ResultSetDataSet.java:177)
at net.sourceforge.squirrel_sql.fw.datasetviewer.ResultSetDataSet.setContentsTabResultSet(ResultSetDataSet.java:100)
at net.sourceforge.squirrel_sql.client.session.mainpanel.SQLExecutionHandler.sqlResultSetAvailable(SQLExecutionHandler.java:414)
at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processResultSet(SQLExecuterTask.java:609)
at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:455)
at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:220)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
at java.lang.Thread.run(Unknown Source)

As per this http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc00359_0250/html/newfeatdw/CEGDFCIH.htm, tinyint datatype in Sybase ASE 15.x is:

tinyint = 0 to 255 (Negative numbers are not permitted)

so I guess for Sybase this is an exception and cannot be handled by Java's byte (-128 to +127).

[zagu]

Just coming back to this, do you think there's a chance for unsigned tinyints to be supported any time soon?

[James Toebes]

I vote for this to be revisited as well. I'm using SquirrelSQL 3.7.1 with SQLServer. the range for tinyint is 0-255 unsigned. https://technet.microsoft.com/en-us/library/ms187745(v=sql.105).aspx It does not appear to support the signed version.

If i use the MS ODBC drivers(com.microsoft.sqlserver.jdbc.SQLServerDriver) , it returns > 127 as a negative number. If i use the jTDS drivers (net.sourceforge.jtds.jdbc.Driver), it gives errors for the numbers >127.

Possible a configuration flag on drivers or aliases to treat tinyints as unsigned?