Getting 2 errors to access a stored procedure using JDBC

Question

First off, there are no problems accessing this stored procedure using ADO.NET. But with JDBC hitting 2 problems.

The first is what's described here. Is it accurate that for a stored procedure we need to set "SET NOCOUNT ON;"? This does get us past the first issue.

But we then get an SqlServerException with the message "The requested operation is not supported on forward only result sets." on a call to ResultSet.isLast() (a call to ResultSet.isAfterLast() is successful).

Looking at this post (from me 7 years ago) I can fix this by changing:

CallableStatement callStmt = conn.prepareCall(buf.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
// to
CallableStatement callStmt = conn.prepareCall(buf.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

I don't know why we changed from TYPE_SCROLL_INSENSITIVE to TYPE_FORWARD_ONLY but I'm wondering why this is needed. We have lots of stored procedures we call successfully with the code we have.

Is this a needed change for some stored procedures? Or should the stored procedure be changed? I want to understand rather than just making a change because "it works" (for this one case).

We call PreparedStatement.executeQuery() to get the ResultSet.

The stored procedure is:

USE [Truno]
GO
/****** Object:  StoredProcedure [dbo].[spGetCompanyHolidays]    Script Date: 8/15/2017 7:32:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetCompanyHolidays] AS
BEGIN

SET NOCOUNT ON;

    CREATE TABLE #tempPCBBHolidays(
        [id] [numeric](18, 0) NOT NULL,
        [year] [numeric](5, 0) NOT NULL,
        [holiday] [datetime] NOT NULL,
        [description] [varchar](250) NULL
    )

    INSERT INTO #tempPCBBHolidays([id], [year], [holiday], [description])
    VALUES (61, 2016, '20160118 00:00:00.000', N'Martin Luther King, Jr. Day'),
    (60, 2016, '20160101 00:00:00.000', N'New Years Day'),
    (62, 2016, '20160215 00:00:00.000', N'Presidents Day'),
    (63, 2016, '20160530 00:00:00.000', N'Memorial Day'),
    (64, 2016, '20160704 00:00:00.000', N'Independence Day'),
    (65, 2016, '20160905 00:00:00.000', N'Labor Day'),
    (66, 2016, '20161010 00:00:00.000', N'Columbus Day'),
    (67, 2016, '20161111 00:00:00.000', N'Veterans Day'),
    (68, 2016, '20161124 00:00:00.000', N'Thanksgiving Day'),
    (69, 2016, '20161226 00:00:00.000', N'Christmas Day'),
    (70, 2017, '20170102 00:00:00.000', N'New Years Day'),
    (71, 2017, '20170116 00:00:00.000', N'Martin Luther King, Jr. Day'),
    (72, 2017, '20170220 00:00:00.000', N'Presidents Day'),
    (73, 2017, '20170529 00:00:00.000', N'Memorial Day'),
    (74, 2017, '20170704 00:00:00.000', N'Independence Day'),
    (75, 2017, '20170904 00:00:00.000', N'Labor Day'),
    (76, 2017, '20171009 00:00:00.000', N'Columbus Day'),
    (77, 2017, '20171111 00:00:00.000', N'Veterans Day'),
    (78, 2017, '20171123 00:00:00.000', N'Thanksgiving Day'),
    (79, 2017, '20171225 00:00:00.000', N'Christmas Day')

    select * from #tempPCBBHolidays
END

for a forward-only resultset, you can only iterate it by repeat calling next() until it returns false. — tibetty, Aug 16 '17 at 02:06
@tibetty - we've been doing this for years where we have been able to call isLast(). Is it possible that there's something about this stored procedure that makes Sql Server need to be able to read ahead or something for isLast()? — David Thielen, Aug 16 '17 at 02:12
It's the resultSet (forward-only) type and the driver implementation that stop you from calling isLast(). Look at the jdbc doc: isLast boolean isLast() throws SQLException Retrieves whether the cursor is on the last row of this ResultSet object. Note: Calling the method isLast may be expensive because the JDBC driver might need to fetch ahead one row in order to determine whether the current row is the last row in the result set. Note: Support for the isLast method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY — tibetty, Aug 16 '17 at 02:15
Please include the entire code, and the exception stacktraces produced. — Mark Rotteveel, Aug 16 '17 at 07:24

Getting 2 errors to access a stored procedure using JDBC

0 Answers0