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

3.3.0
open
nobody
None
5
2016-09-13
2012-07-23
zagu
No

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.

Discussion

  • Rob Manning

    Rob Manning - 2012-08-11

    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

    Rob Manning - 2012-08-11

    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

    zagu - 2012-08-13

    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

    zagu - 2016-09-02

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

     
  • James Toebes

    James Toebes - 2016-09-13

    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?

     

Log in to post a comment.