SQL Server JDBC - Bug in jdbc driver...

Author

Bug in jdbc driver...

Michael Benedict

2005-07-25, 8:25 pm

Error:

SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
7 is out of range.

SQLException caught:.

-----------------------------------------------------------------------------------------------------------

The procedure follows (slightly modified for security reasons):

------------------------------------------------------------------------------------------------------------

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE dbo.xprHSPMilestoneData (@templateid int, @groupid int) AS

DECLARE @DataSQL varchar(6000)

DECLARE @HeadCount int

DECLARE @CurrentColumn datetime

DECLARE @ColorColumn varchar(11)

BEGIN

DECLARE Head_cursor CURSOR FOR

SELECT milestone

FROM dbo. vwTrackingGroupDurMS

WHERE templateid = @templateid AND groupid = @groupid

OPEN Head_cursor

SET @DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
NULL'

SET @HeadCount = 1

FETCH NEXT FROM Head_cursor INTO @CurrentColumn

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DataSQL = @DataSQL + ',''' +
convert(char(10),@Cu
rrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
@HeadCount) + 'MS'

SET @HeadCount = @HeadCount + 1

FETCH NEXT FROM Head_cursor INTO @CurrentColumn

END

CLOSE Head_cursor

DEALLOCATE Head_cursor

--Loop thru the colors for each milestone

DECLARE Head_cursor CURSOR FOR

SELECT color

FROM dbo. vwTrackingGroupDurMX

WHERE templateid = @templateid AND groupid = @groupid

OPEN Head_cursor

SET @HeadCount = 1

FETCH NEXT FROM Head_cursor INTO @ColorColumn

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DataSQL = @DataSQL + ',''' + @ColorColumn +
''' as Col' + CONVERT(varchar(2), @HeadCount) + 'Color'

SET @HeadCount = @HeadCount + 1

FETCH NEXT FROM Head_cursor INTO @ColorColumn

END

CLOSE Head_cursor

DEALLOCATE Head_cursor

EXECUTE (@DataSQL)

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

-----------------------------------------------------------------------------------------------

Microsoft Support Information for this error:

----------------------------------------------------------------------------------------------

FIX: "Column index <index> out of range" error when you try to read a field
from a JDBC ResultSet object

View products that this article applies to.

Article ID : 838610

Last Review : March 29, 2005

Revision : 2.0

SYMPTOMS

You have a Java application that uses the Java Database Connectivity (JDBC)
API to retrieve a ResultSet

object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
to read a field from the

ResultSet object by using the methods in the JDBC API, you may receive an
error message

that is similar to the following:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
index <index> is out of range.

Note <index> is a placeholder for the index of the column that is being
retrieved.

This problem occurs when all the following conditions are true:

• The Select Transact-SQL statement that is used to retrieve the
data from the SQL Server tables to the

ResultSet object contains a JOIN between different tables in the SQL
Server database.

• The Select Transact-SQL statement uses asterisk (*) to retrieve
all fields from the respective tables.

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended
to correct the problem that is described in

this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive

additional testing. Therefore, if you are not severely affected by this
problem, Microsoft recommends

that you wait for the next service pack that contains this hotfix.

How do I obtain this hotfix?

Joe Weinstein

2005-07-25, 8:25 pm

Michael Benedict wrote:

> Error:
>
>
>
> SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
> 7 is out of range.

I looked at your procedure. Are you absolutely sure
your constructed SQL string got to selecting 7 fields?

I would have the procedure return the SQL string, just for
debugging, and/or do the typical resul set metadata processing
to see how many columns were really returned. The bug you cite
seems to be about *any* row index, under conditions your
procedure doesn't have...
Joe Weinstein at BEA

>
> SQLException caught:.
>
> -----------------------------------------------------------------------------------------------------------
>
> The procedure follows (slightly modified for security reasons):
>
>
>
> ------------------------------------------------------------------------------------------------------------
>
> SET QUOTED_IDENTIFIER OFF
>
> GO
>
> SET ANSI_NULLS OFF
>
> GO
>
> CREATE PROCEDURE dbo.xprHSPMilestoneData (@templateid int, @groupid int) AS
>
>
>
> DECLARE @DataSQL varchar(6000)
>
> DECLARE @HeadCount int
>
> DECLARE @CurrentColumn datetime
>
> DECLARE @ColorColumn varchar(11)
>
>
>
> BEGIN
>
> DECLARE Head_cursor CURSOR FOR
>
> SELECT milestone
>
> FROM dbo. vwTrackingGroupDurMS

>
> WHERE templateid = @templateid AND groupid = @groupid
>
>
>
> OPEN Head_cursor
>
> SET @DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
> NULL'
>
> SET @HeadCount = 1
>
> FETCH NEXT FROM Head_cursor INTO @CurrentColumn
>
>
>
> WHILE @@FETCH_STATUS = 0
>
> BEGIN
>
> SET @DataSQL = @DataSQL + ',''' +
> convert(char(10),@Cu
rrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
> @HeadCount) + 'MS'
>
> SET @HeadCount = @HeadCount + 1
>
> FETCH NEXT FROM Head_cursor INTO @CurrentColumn
>
> END
>
> CLOSE Head_cursor
>
> DEALLOCATE Head_cursor
>
>
>
> --Loop thru the colors for each milestone
>
> DECLARE Head_cursor CURSOR FOR
>
> SELECT color
>
> FROM dbo. vwTrackingGroupDurMX

>
> WHERE templateid = @templateid AND groupid = @groupid
>
>
>
> OPEN Head_cursor
>
>
>
> SET @HeadCount = 1
>
> FETCH NEXT FROM Head_cursor INTO @ColorColumn
>
>
>
> WHILE @@FETCH_STATUS = 0
>
> BEGIN
>
> SET @DataSQL = @DataSQL + ',''' + @ColorColumn +
> ''' as Col' + CONVERT(varchar(2), @HeadCount) + 'Color'
>
> SET @HeadCount = @HeadCount + 1
>
> FETCH NEXT FROM Head_cursor INTO @ColorColumn
>
> END
>
>
>
> CLOSE Head_cursor
>
> DEALLOCATE Head_cursor
>
>
>
> EXECUTE (@DataSQL)
>
> END
>
> GO
>
> SET QUOTED_IDENTIFIER OFF
>
> GO
>
> SET ANSI_NULLS ON
>
> GO
>
>
>
> -----------------------------------------------------------------------------------------------
>
> Microsoft Support Information for this error:
>
> ----------------------------------------------------------------------------------------------
>
>
>
> FIX: "Column index <index> out of range" error when you try to read a field
> from a JDBC ResultSet object
>
> View products that this article applies to.
>
>
>
> Article ID : 838610
>
> Last Review : March 29, 2005
>
> Revision : 2.0
>
> SYMPTOMS
>
>
>
> You have a Java application that uses the Java Database Connectivity (JDBC)
> API to retrieve a ResultSet
>
> object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
> to read a field from the
>
> ResultSet object by using the methods in the JDBC API, you may receive an
> error message
>
> that is similar to the following:
>
>
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
> index <index> is out of range.
>
> Note <index> is a placeholder for the index of the column that is being
> retrieved.
>
>
>
> This problem occurs when all the following conditions are true:
>
>
>
> • The Select Transact-SQL statement that is used to retrieve the
> data from the SQL Server tables to the
>
> ResultSet object contains a JOIN between different tables in the SQL
> Server database.
>
> • The Select Transact-SQL statement uses asterisk (*) to retrieve
> all fields from the respective tables.
>
> RESOLUTION
>
>
>
> A supported hotfix is now available from Microsoft, but it is only intended
> to correct the problem that is described in
>
> this article. Only apply it to systems that are experiencing this specific
> problem. This hotfix may receive
>
> additional testing. Therefore, if you are not severely affected by this
> problem, Microsoft recommends
>
> that you wait for the next service pack that contains this hotfix.
>
> How do I obtain this hotfix?

Sponsored Links