SQL Server JDBC - Possible Type Conversion Defect

Author

Possible Type Conversion Defect

Eric Molitor

2005-10-20, 8:24 pm

Seem to be getting these consistantly in certain portions of our application.
Works fine with the older JDBC driver (2000) but under the 2005 driver we
see....

com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion from
108 to INTEGER
at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)

and

com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion from
38 to SMALLINT
at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown Source)

I'm looking at the SQL and the code but I'd think 38 is a valid SMALLINT and
that 108 is a valid INTEGER.

Eric Molitor

2005-10-20, 8:24 pm

I believe I solved my own problem.

Instead of returning a short from the table I was hard coding the parameter.

eg:

select firstname, lastname, userid=0, middlename from dbo.user

So it looks like the driver isn't sure what type userid is, hence the
conversion error. However I'd hope that the driver would be smart enough to
figure it out.

"Eric Molitor" wrote:

> Seem to be getting these consistantly in certain portions of our application.
> Works fine with the older JDBC driver (2000) but under the 2005 driver we
> see....
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion from
> 108 to INTEGER
> at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
>
> and
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion from
> 38 to SMALLINT
> at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown Source)
>
> I'm looking at the SQL and the code but I'd think 38 is a valid SMALLINT and
> that 108 is a valid INTEGER.
>
>

Angel Saenz-Badillos[MS]

2005-10-20, 8:24 pm

We wanted to be very explicit with our data coercion story and as far as we
have been able we are not going to allow getting a type from the server that
would require a downcast to the client and possible loss of data. This
strategy has the advantage of high predictability with limited chance of
data loss, but it is very restrictive.

Quite frankly I was expecting to see a lot more people commenting on these.
In your case 108 is of type NUMERIC, a 38bit precission decimal and you are
trying to shove it into an INTEGER. Type 38 is an INTEGER which does not fit
on a SMALLINT.

We only have two choices here that don't involve data loss (something we are
definitelly not going to allow),
1) We can NEVER allow a conversion from a type if _the type you are trying
to convert_ does not fit into the type that you are trying to coerce it
into. This is the behavior that we have opted for in the 2005 JDBC driver.

2) We can allow a conversion from a type that does not fit into the coreced
type _only_ when the current value that you are asking for can be coerced
into the type that you are asking for. This is the behavior of the 2000 JDBC
driver.

Let's say that you have a NUMERIC column that has a value of 5, when you
call getInt on this we will throw an exception if following (1) but the
coercion will work on a driver that supports (2) since 5 does fit into an
INTEGER type. When you have a driver that provides the (1) functionality you
will realize the first time you run your code that a NUMERIC column will not
always fit into an int and change your code accordingly. When working with a
driver of the (2) type you will test and deploy your application with
getInt. When the value of the NUMERIC column goes over what an INTEGER can
handle you will get a runtime exception and you will have to go service your
deployed application.

We realize that it can be inconvenient to have this kind of issues surfaced
early, but we feel it is better to let you know up front about possible data
coercion issues, if you really wanted to get an integer from the server you
would have defined your table accordingly, or you could have requested an
integer in your query with the CONVERT function.

I think that this is going to be a common question, I am going to convert
this post into a blog and post it into the http://blogs.msdn.com/dataaccess/
with a complete data coercion table to help make this design clearer, of
course comments/suggestions are welcome.
--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Eric Molitor" < EricMolitor@discussi
ons.microsoft.com> wrote in message
news:237EAB6D-63BA-4DB2-AB0C- 7EB8D98B3E2D@microso
ft.com...
> Seem to be getting these consistantly in certain portions of our
> application.
> Works fine with the older JDBC driver (2000) but under the 2005 driver we
> see....
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion
> from
> 108 to INTEGER
> at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(Unknown Source)
>
> and
>
> com.microsoft.sqlserver.jdbc.SQLServerException: Unsupported conversion
> from
> 38 to SMALLINT
> at com.microsoft.sqlserver.jdbc.SQLServerStatement. getRowsetField(Unkno
wn
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown
> Source)
> at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getShort(Unknown
> Source)
>
> I'm looking at the SQL and the code but I'd think 38 is a valid SMALLINT
> and
> that 108 is a valid INTEGER.
>
>

Eric Molitor

2005-10-20, 8:24 pm

Right, I dug into this and was able to solve the problems both with
conversions and by fixing some bad practices in our SQL...

In several places after executing an insert we would simply...

select @@IDENTITY as identityValue

and then retrieve the value from the result set in java. Obviously we should
have been using SCOPE_IDENTITY() for one but also we should have been using
an out put parameter...

So the proc becomes

CREATE PROCEDURE spTestProc
(
@username varchar(8),
@firstname varchar(255),
@lastname varchar(255),
@UserID int OUTPUT
) as

INSERT INTO User (username, firstname, lastname)
VALUES (@username, @firstname, @lastname)

SELECT @UserID = SCOPE_IDENTITY()

and in the code we just fetch the value as an output parameter...

So once all of this shakes out I do think it will be a positive change,
however I'm sure lots of people will have some SQL and Java to cleanup.

Cheers,
Eric

"Angel Saenz-Badillos[MS]" wrote:

> We wanted to be very explicit with our data coercion story and as far as we
> have been able we are not going to allow getting a type from the server that
> would require a downcast to the client and possible loss of data. This
> strategy has the advantage of high predictability with limited chance of
> data loss, but it is very restrictive.
>
> Quite frankly I was expecting to see a lot more people commenting on these.
> In your case 108 is of type NUMERIC, a 38bit precission decimal and you are
> trying to shove it into an INTEGER. Type 38 is an INTEGER which does not fit
> on a SMALLINT.
>
> We only have two choices here that don't involve data loss (something we are
> definitelly not going to allow),
> 1) We can NEVER allow a conversion from a type if _the type you are trying
> to convert_ does not fit into the type that you are trying to coerce it
> into. This is the behavior that we have opted for in the 2005 JDBC driver.
>
> 2) We can allow a conversion from a type that does not fit into the coreced
> type _only_ when the current value that you are asking for can be coerced
> into the type that you are asking for. This is the behavior of the 2000 JDBC
> driver.
>
> Let's say that you have a NUMERIC column that has a value of 5, when you
> call getInt on this we will throw an exception if following (1) but the
> coercion will work on a driver that supports (2) since 5 does fit into an
> INTEGER type. When you have a driver that provides the (1) functionality you
> will realize the first time you run your code that a NUMERIC column will not
> always fit into an int and change your code accordingly. When working with a
> driver of the (2) type you will test and deploy your application with
> getInt. When the value of the NUMERIC column goes over what an INTEGER can
> handle you will get a runtime exception and you will have to go service your
> deployed application.
>
> We realize that it can be inconvenient to have this kind of issues surfaced
> early, but we feel it is better to let you know up front about possible data
> coercion issues, if you really wanted to get an integer from the server you
> would have defined your table accordingly, or you could have requested an
> integer in your query with the CONVERT function.
>
> I think that this is going to be a common question, I am going to convert
> this post into a blog and post it into the http://blogs.msdn.com/dataaccess/
> with a complete data coercion table to help make this design clearer, of
> course comments/suggestions are welcome.
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Eric Molitor" < EricMolitor@discussi
ons.microsoft.com> wrote in message
> news:237EAB6D-63BA-4DB2-AB0C- 7EB8D98B3E2D@microso
ft.com...
>
>
>

Angel Saenz-Badillos[MS]

2005-10-20, 8:24 pm

Thank you for your feedback, as you mention this is going to impact existing
code and we are nervous to see how specific areas are affecting customers.
We believe that this is the right story going forward but we may have to
bend it a little for specific customer scenarios. We have already received
some pushback on getObject for uniqueIdentifiers (currently returns a byte
array which is how the server stores it but is not particularily usefull)
and supporting getLong on a Numeric(Decimal) type. If you have any other
suggestions be sure to post them here or file them as bug in the msdn
product feedback site :
http://lab.msdn.microsoft.com/produ...ck/default.aspx

There is still time to integrate customer feedback into this data coercion
story, but it is running out fast.

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Eric Molitor" < EricMolitor@discussi
ons.microsoft.com> wrote in message
news:3D094877-F2AC-4E91-9FDB- 324FBB29BCD5@microso
ft.com...[color=darkred]
> Right, I dug into this and was able to solve the problems both with
> conversions and by fixing some bad practices in our SQL...
>
> In several places after executing an insert we would simply...
>
> select @@IDENTITY as identityValue
>
> and then retrieve the value from the result set in java. Obviously we
> should
> have been using SCOPE_IDENTITY() for one but also we should have been
> using
> an out put parameter...
>
> So the proc becomes
>
> CREATE PROCEDURE spTestProc
> (
> @username varchar(8),
> @firstname varchar(255),
> @lastname varchar(255),
> @UserID int OUTPUT
> ) as
>
> INSERT INTO User (username, firstname, lastname)
> VALUES (@username, @firstname, @lastname)
>
> SELECT @UserID = SCOPE_IDENTITY()
>
> and in the code we just fetch the value as an output parameter...
>
> So once all of this shakes out I do think it will be a positive change,
> however I'm sure lots of people will have some SQL and Java to cleanup.
>
> Cheers,
> Eric
>
>
>
> "Angel Saenz-Badillos[MS]" wrote:
>

David Klebanoff

2005-12-08, 8:23 pm

Angel, we have a very serious issue with this choice of type conversion
behaviour. At the moment, our application will simply not run with the Beta 2
driver.

Specifically we are breaking due to the lack of conversion from BIGINT to
int. However, I am not ruling out other type conversion issues that may
surface later (right now I can't run the app at all until I deal with this
particular one).

As you pointed out, this behaviour is inconsistent with the previous
Microsoft SQL Server 2000 JDBC driver. But it is also inconsistent with the
other major drivers, namely IBM DB2, IBM DB2 z/OS, Oracle 9i and Oracle 10g
(which incidentally, we must also support from the same codebase). All of
these drivers will narrow the type and throw if the value is out of range or
unconvertable.

I'll agree that the JDBC API specifications could be a bit more precise.
However, the documentation for the ResultSet class says: "For the getter
methods, a JDBC driver attempts to convert the underlying data to the Java
type specified in the getter method and returns a suitable Java value. The
JDBC specification has a table showing the allowable mappings from SQL types
to Java types that can be used by the ResultSet getter methods. " BIGINT->int
is shown as allowed in the JDBC specification.

But this isn't the real point. The real point is that it is critical to
preserve existing behaviour. If you want to change existing behaviour,
particularly in a way that can break deployed software, then you need to make
it parametrically controllable.

Please reconsider this strategy.

David Klebanoff

"Angel Saenz-Badillos[MS]" wrote:

> Thank you for your feedback, as you mention this is going to impact existing
> code and we are nervous to see how specific areas are affecting customers.
> We believe that this is the right story going forward but we may have to
> bend it a little for specific customer scenarios. We have already received
> some pushback on getObject for uniqueIdentifiers (currently returns a byte
> array which is how the server stores it but is not particularily usefull)
> and supporting getLong on a Numeric(Decimal) type. If you have any other
> suggestions be sure to post them here or file them as bug in the msdn
> product feedback site :
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
>
> There is still time to integrate customer feedback into this data coercion
> story, but it is running out fast.
>
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Eric Molitor" < EricMolitor@discussi
ons.microsoft.com> wrote in message
> news:3D094877-F2AC-4E91-9FDB- 324FBB29BCD5@microso
ft.com...
>
>
>

Pedro Nunes

2006-01-04, 3:23 am

Hi,

I'm developing a large application using SAP Web Application Server
with JDBC Driver Beta 2 for SQL Server 2005 and I'm getting the SAME
problem.
Our solution is an application developed in Visual Studio .NET 2005 and
another developed in Java with Hibernate.
There's any solution or I must change my strategy to don't choice SQL
Server 2005 as default database because don't have a valid and
compatibility JDBC driver?

Thanks,

Pedro

David Klebanoff escreveu:
[color=darkred]
> Angel, we have a very serious issue with this choice of type conversion
> behaviour. At the moment, our application will simply not run with the Beta 2
> driver.
>
> Specifically we are breaking due to the lack of conversion from BIGINT to
> int. However, I am not ruling out other type conversion issues that may
> surface later (right now I can't run the app at all until I deal with this
> particular one).
>
> As you pointed out, this behaviour is inconsistent with the previous
> Microsoft SQL Server 2000 JDBC driver. But it is also inconsistent with the
> other major drivers, namely IBM DB2, IBM DB2 z/OS, Oracle 9i and Oracle 10g
> (which incidentally, we must also support from the same codebase). All of
> these drivers will narrow the type and throw if the value is out of range or
> unconvertable.
>
> I'll agree that the JDBC API specifications could be a bit more precise.
> However, the documentation for the ResultSet class says: "For the getter
> methods, a JDBC driver attempts to convert the underlying data to the Java
> type specified in the getter method and returns a suitable Java value. The
> JDBC specification has a table showing the allowable mappings from SQL types
> to Java types that can be used by the ResultSet getter methods. " BIGINT->int
> is shown as allowed in the JDBC specification.
>
> But this isn't the real point. The real point is that it is critical to
> preserve existing behaviour. If you want to change existing behaviour,
> particularly in a way that can break deployed software, then you need to make
> it parametrically controllable.
>
> Please reconsider this strategy.
>
> David Klebanoff
>
> "Angel Saenz-Badillos[MS]" wrote:
>

Angel Saenz-Badillos[MS]

2006-01-10, 8:23 pm

Pedro and David,
Thank you for your feedback, we have received OVERWHELMING feedback on our
"purist" coercion story and have reconsidered this feature for the final
release. One of the main reasons why we were not able to ship on November 4
as we had planned has been the tremendous ammount of work that implementing
this feature this late in the game represents.

I am happy to say that you can expect coercion to work per JDBC specs when
we ship this month.

--
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/

"Pedro Nunes" <pedronunesbr@yahoo.com> wrote in message
news:1136344294.241442.283840@g44g2000cwa.googlegroups.com...
> Hi,
>
> I'm developing a large application using SAP Web Application Server
> with JDBC Driver Beta 2 for SQL Server 2005 and I'm getting the SAME
> problem.
> Our solution is an application developed in Visual Studio .NET 2005 and
> another developed in Java with Hibernate.
> There's any solution or I must change my strategy to don't choice SQL
> Server 2005 as default database because don't have a valid and
> compatibility JDBC driver?
>
> Thanks,
>
> Pedro
>
> David Klebanoff escreveu:
>
>

David Klebanoff

2006-01-10, 8:23 pm

Excellent. Thanks. We look forward to trying the driver.

"Angel Saenz-Badillos[MS]" wrote:
[color=darkred]
> Pedro and David,
> Thank you for your feedback, we have received OVERWHELMING feedback on our
> "purist" coercion story and have reconsidered this feature for the final
> release. One of the main reasons why we were not able to ship on November 4
> as we had planned has been the tremendous ammount of work that implementing
> this feature this late in the game represents.
>
> I am happy to say that you can expect coercion to work per JDBC specs when
> we ship this month.
>
> --
> Angel Saenz-Badillos [MS] DataWorks
> This posting is provided "AS IS", with no warranties, and confers no
> rights.Please do not send email directly to this alias.
> This alias is for newsgroup purposes only.
> I am now blogging: http://weblogs.asp.net/angelsb/
>
>
>
>
> "Pedro Nunes" <pedronunesbr@yahoo.com> wrote in message
> news:1136344294.241442.283840@g44g2000cwa.googlegroups.com...

Sponsored Links