SQL Server JDBC - Variant datatype not supported?

Author

Variant datatype not supported?

Wes Clark

2006-01-24, 8:23 pm

I'm trying to write a method that will tell me what version, 8 or 9, I'm
running on. When I connect to 2000, I use the old driver. When I connect to
2005, I use the new driver. I first used the
DatabaseMetaData. getDatabaseMajorVers
ion() method, but it is not supported in
the old driver. Then I used getDatabaseProductVe
rsion, which returns
"Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)" in the
old version, and "9.00.1399" in the new. So I noticed if I selected
SERVERPROPERTY('Prod
uctVersion'), I got 8.00.760 in the old version, and
9.00.1399.06 in the new version. Perfect. The query works in 2000, but in
2005 I get "com.microsoft.sqlserver.jdbc.SQLServerException: Data type
'variant' not supported".

Joe Weinstein

2006-01-24, 8:23 pm

Wes Clark wrote:

> I'm trying to write a method that will tell me what version, 8 or 9, I'm
> running on. When I connect to 2000, I use the old driver. When I connect to
> 2005, I use the new driver. I first used the
> DatabaseMetaData. getDatabaseMajorVers
ion() method, but it is not supported in
> the old driver. Then I used getDatabaseProductVe
rsion, which returns
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)" in the
> old version, and "9.00.1399" in the new. So I noticed if I selected
> SERVERPROPERTY('Prod
uctVersion'), I got 8.00.760 in the old version, and
> 9.00.1399.06 in the new version. Perfect. The query works in 2000, but in
> 2005 I get "com.microsoft.sqlserver.jdbc.SQLServerException: Data type
> 'variant' not supported".

Hi Wess. Try this:

"select convert(varchar(100)
, SERVERPROPERTY('Prod
uctVersion'))"

HTH,
Joe Weinstein at BEA Systems

Wes Clark

2006-01-24, 8:23 pm

VoilÃ_! That solves my immediate problem. Should this be considered a bug?
It certainly is a regression. I could not find a reference to the variant
datatype in the 2005 driver doc.

"Joe Weinstein" wrote:

>
>
> Wes Clark wrote:
>
>
> Hi Wess. Try this:
>
> "select convert(varchar(100)
, SERVERPROPERTY('Prod
uctVersion'))"
>
> HTH,
> Joe Weinstein at BEA Systems
>
>

Joe Weinstein

2006-01-24, 8:23 pm

Wes Clark wrote:

> VoilÃ_! That solves my immediate problem. Should this be considered a bug?
> It certainly is a regression. I could not find a reference to the variant
> datatype in the 2005 driver doc.

Glad to help. Yes, it's a driver bug, and it's in the GA version
of the new driver:

c = d.connect("jdbc:sqlserver://joe:1433", props );

DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );

Statement stmt = c.createStatement();
ResultSet r = stmt.executeQuery("select SERVERPROPERTY('Prod
uctVersion')");
while(r.next())
System.out.println("DBMS version is " + r.getString(1) );
gives:

Driver version is 1.0.809.102
com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException. makeFromDriverError(
Unknown Source)
at com.microsoft.sqlserver.jdbc.TypeInfo.init(Unknown Source)
at com.microsoft.sqlserver.jdbc.StreamColumns. processBytes(Unknown
Source)
at com.microsoft.sqlserver.jdbc.IOBuffer. processPackets(Unkno
wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement. getNextResult(Unknow
n Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement. doExecuteQuery(Unkno
wn Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement. executeQuery(Unknown
Source)
at one_con.main(one_con.java:26)

the convert() trick gives:
Driver version is 1.0.809.102
DBMS version is 9.00.1314.06

Joe Weinstein at BEA Systems

[color=darkred]
>
> "Joe Weinstein" wrote:
>
>

Angel Saenz-Badillos[MS]

2006-01-24, 8:23 pm

Wes,
We have made a conscious decision to not support the variant sql type on
this driver, the workarround is exactly as Joe suggests, use the tsql
CONVERT function to convert it into the format you expect to receive.

Take a look at the documentation for Understanding the JDBC Driver Data
Types->Using Basic Data Types
"Note: The SQL server sqlvariant data type is not currently supported by the
JDBC driver. If a query is used to retrieve data from a table that contains
a column of the sqlvariant data type, an exception will occur."

Looking for feedback on this decision. Supporting variant the right way is
not really possible, we basically had to choose between throwing or
converting it into a string (which may not be what you really want), in the
end we decided to leave it to developers to hack it into the format that
they expect from the variant with the CONVERT tsql function.
--
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/

"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message
news:A82CB020-99B6-4387-909A- 0100957BB9AC@microso
ft.com...[color=darkred]
> Voilà! That solves my immediate problem. Should this be considered a
> bug?
> It certainly is a regression. I could not find a reference to the variant
> datatype in the 2005 driver doc.
>
> "Joe Weinstein" wrote:
>

Sponsored Links