1

I am developing a java application onWindows 10 using Eclipse Oxygen and SQL JDBC v7.2 and java 1.8 update 202. My code runs fine when I use SQL JDBC v6.0 (sqljdbc42.jar Jan 2017 and java 8), but gives the same java.lang.NullPointerException with SQL JDBC v7.2 (mssql-jdbc-7.2.1.jre8.jar with java 8u202) or (mssql-jdbc-7.2.1.jre11.jar with java 11.0.2) at line rs.next() or any command that attempts to access the recordset (rs) because the recordset value is null.

(We need to upgrade to v7.2 to be compatible with java 11 as soon as possible. I have many stored procedures that run fine and two stored procedures that cause this java.lang.NullPointerException error for one recordset in their results. I am only including the code for one here.)

Database connection works fine and output variables and some recordsets return valid results and work fine.

The attached stored procedure gives error with JDBC v7.2 when using original complex query for the recordset; it has run fine for years with JDBC v6.0.

Deploying static tables of the same data works fine in JDBC 7.2.
Java code: // Create database connection and statement Connection con = null;

    // Establish the connection.
    SQLServerDataSource ds = new SQLServerDataSource();
    ds.setUser(strDatabaseUser);
    ds.setPassword(strDatabasePassword);
    ds.setServerName(strDatabaseServerName);
    ds.setPortNumber(intDatabaseServerPort);
    ds.setDatabaseName(strDatabaseName);

    try {
        con = ds.getConnection();
    }
    catch (SQLException se) {
        do {
            System.out.println("SQL STATE: " + se.getSQLState());
            System.out.println("ERROR CODE: " + se.getErrorCode());
            System.out.println("MESSAGE: " + se.getMessage());
            System.out.println();
            se = se.getNextException();
        } while (se != null);
    }

    CallableStatement cstmt2 = null;

    if (intArchiveID == -1)
        cstmt2 = con.prepareCall("{call dbo.selProjectTractFunding(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
    else
    {
        cstmt2 = con.prepareCall("{call dbo.selProjectTractFundingFromArchive(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        cstmt2.setInt("ArchiveID",intArchiveID);
    }
    cstmt2.setInt("ProjectID",intProjectID);
    cstmt2.setInt("CurrentProposedFundingFY", intCurrentProposedFundingFY);
    cstmt2.registerOutParameter("LastStatusCD", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("LastFundingFY", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curFundingAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curCostShareAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curTotalFLPCost", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curOtherFedAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curOtherFedCostShareAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curTotalCost", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("curAcre", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalFundingAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalCostShareAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalFLPCost", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalOtherFedAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalOtherFedCostShareAmount", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalCost", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("TotalAcre", java.sql.Types.INTEGER);
    cstmt2.registerOutParameter("FundingAmountToDate", java.sql.Types.INTEGER);

    boolean bCstmt2Exe = cstmt2.execute();
    ResultSet rs2=null;
    //if (bCstmt2Exe == true)
        rs2 = cstmt2.getResultSet();


    Table tblTracts = new Table(new float[]{ 6,3,5,5,5,5 });
    tblTracts.setWidth(468f);
    tblTracts.setMarginBottom(12f);
    tblTracts.getAccessibilityProperties().addAttributes(new PdfStructureAttributes("Table").addTextAttribute("Summary","This table lists all tracts in the project and their size, cost, FLP funding, Cost Share and Status." ));

    float fltCellPaddingTop=0f;
    float fltCellPaddingRight=2f;
    float fltCellPaddingLeft=2f;
    float fltCellPaddingBottom=-1f;

    Cell c1 = new Cell().add(new Paragraph("Tract Name")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.LEFT);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);

    c1 = new Cell().add(new Paragraph("Size (ac)")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.CENTER);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);

    c1 = new Cell().add(new Paragraph("Tract Cost")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.CENTER);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);

    c1 = new Cell().add(new Paragraph("FLP Funding")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.CENTER);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);

    c1 = new Cell().add(new Paragraph("Non-Fed Cost Share")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.CENTER);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);

    c1 = new Cell().add(new Paragraph("Status")).addStyle(styleTableHeader);
    c1.setTextAlignment(TextAlignment.CENTER);
    c1.setVerticalAlignment(VerticalAlignment.BOTTOM);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    tblTracts.addHeaderCell(c1);


    while (rs2.next()){
        int intTractID = rs2.getInt("TractID");
        logger.info("\nTractID: "+
                Integer.toString(intTractID));

        c1 = new Cell().add(new Paragraph(rs2.getString("TractName")).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.LEFT);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
        c1 = new Cell().add(new Paragraph(integerFormatter.format(rs2.getInt("Acre"))).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.RIGHT);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
        c1 = new Cell().add(new Paragraph(currencyFormatter.format(rs2.getInt("FLPCost"))).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.RIGHT);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
        c1 = new Cell().add(new Paragraph(currencyFormatter.format(rs2.getInt("FundingAmount"))).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.RIGHT);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
        c1 = new Cell().add(new Paragraph(currencyFormatter.format(rs2.getInt("CostShareAmount"))).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.RIGHT);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
        c1 = new Cell().add(new Paragraph(rs2.getString("DisplayStatusFY")).addStyle(styleTableCell));
        c1.setTextAlignment(TextAlignment.CENTER);
        c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
        tblTracts.addCell(c1);
    }


    int LastFundingFY = cstmt2.getInt("LastFundingFY");
    int curFundingAmount = cstmt2.getInt("curFundingAmount");
    int curCostShareAmount = cstmt2.getInt("curCostShareAmount");
    int curTotalFLPCost = cstmt2.getInt("curTotalFLPCost");
    int curAcre = cstmt2.getInt("curAcre");
    int TotalFLPCost = cstmt2.getInt("TotalFLPCost");
    int TotalAcre = cstmt2.getInt("TotalAcre");
    int FundingAmountToDate = cstmt2.getInt("FundingAmountToDate");

    Table tblFunding = new Table(new float[]{120,60});
    float fltTableFundingCellHeight = 12f;
    tblFunding.setWidth(270F);
    tblFunding.setMarginBottom(16f);
    tblFunding.getAccessibilityProperties().addAttributes(new PdfStructureAttributes("Table").addTextAttribute("Summary","This table provides an overview of the Forest Legacy Program funding history for this project." ));

    c1 = new Cell().add(new Paragraph("FY " + LastFundingFY + " Forest Legacy Program Funding").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(currencyFormatter.format(curFundingAmount)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    c1 = new Cell().add(new Paragraph("FY " + LastFundingFY + " Non-Federal Cost Share").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(currencyFormatter.format(curCostShareAmount)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    c1 = new Cell().add(new Paragraph("FY " + LastFundingFY + " Project Costs").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(currencyFormatter.format(curTotalFLPCost)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);


    c1 = new Cell().add(new Paragraph("FY " + LastFundingFY + " Project Acres").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom + 10f, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(integerFormatter.format(curAcre)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom + 10f, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    c1 = new Cell().add(new Paragraph("Forest Legacy Funding To Date").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(currencyFormatter.format(FundingAmountToDate)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    c1 = new Cell().add(new Paragraph("Total Project Costs").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(currencyFormatter.format(TotalFLPCost)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    c1 = new Cell().add(new Paragraph("Total Project Acres").addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    c1.getAccessibilityProperties().setRole(StandardRoles.TH);
    tblFunding.addCell(c1);
    c1 = new Cell().add(new Paragraph(integerFormatter.format(TotalAcre)).addStyle(styleTableHeader));
    c1.setTextAlignment(TextAlignment.RIGHT);
    c1.setBorder(Border.NO_BORDER);
    c1.setPaddings(fltCellPaddingTop, fltCellPaddingRight, fltCellPaddingBottom, fltCellPaddingLeft);
    c1.setHeight(fltTableFundingCellHeight);
    tblFunding.addCell(c1);

    cstmt2.close();

Here is the Stored Procedure that gives error. (Please note comment of sql query that if converted to static table, resolves the error!):

/****** Object:  StoredProcedure [dbo].[selProjectTractFunding]    Script Date: 2/20/2019 3:48:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
*/

CREATE PROCEDURE [dbo].[selProjectTractFunding] ( @ProjectID int,
    @CurrentProposedFundingFY int, @LastStatusCD int out, @LastFundingFY int out,
    @curFundingAmount int out, @curCostShareAmount int out,
    @curTotalFLPCost int out, @curOtherFedAmount int out,
    @curOtherFedCostShareAmount int out, @curTotalCost int out,
    @curAcre int out, @TotalFundingAmount int out,
    @TotalCostShareAmount int out, @TotalFLPCost int out,
    @TotalOtherFedAmount int out, @TotalOtherFedCostShareAmount int out,
    @TotalCost int out, @TotalAcre int out,
    @FundingAmountToDate int out )
AS

    SET NOCOUNT ON
    declare @RC int

    /***  All works FINE if I convert this first query to a static table TEMP1 and replace it with "select * from temp1".  Does not work from temporary table #temp1 as coded here.  ***/
    select pt.ProjectID
        ,t.TractID
        ,t.TractName
        ,t.PurchaseTypeCD
        ,cast(isnull(t.Acre,0) as int) as Acre
        ,cast(isnull(t.FundingAmount,0) as bigint) as FundingAmount
        ,cast(isnull(costshare.CostShareAmount,0) as bigint) as CostShareAmount
        ,cast((isnull(t.FundingAmount,0) + isnull(costshare.CostShareAmount,0)) as bigint) as FLPCost
        ,cast(isnull(ofed.OtherFedAmount,0) as bigint) as OtherFedAmount
        ,cast(isnull(ofed.OtherFedCostShareAmount,0) as bigint) as OtherFedCostShareAmount
        ,cast((isnull(t.FundingAmount,0) + isnull(costshare.CostShareAmount,0) + isnull(ofed.OtherFedAmount,0) + isnull(ofed.OtherFedCostShareAmount,0)) as bigint) as TotalCost
        ,cast(dbo.getFY(CompleteDate) as int) as CompleteFY
        ,ts.StatusCD
        ,s.Status
        ,s.DisplayStatus
        ,cast(case when t.CompleteDate is null then s.DisplayStatus + ' ' + cast(ts.FundingFY as varchar) else s.DisplayStatus + ' ' + cast(dbo.getFY(t.CompleteDate) as varchar) end as varchar) as DisplayStatusFY
        ,ts.FundingFY
        ,ts.CreatedOn
    into #temp1
    from (select TractID, max(CreatedOn) as CreatedOn from dbo.TractStatus group by TractID ) cs
    inner join dbo.TractStatus ts on cs.TractID = ts.TractID and cs.CreatedOn = ts.CreatedOn    
    inner join dbo.Tract t on ts.TractID = t.TractID 
    inner join dbo.ProjectTract pt on t.TractID = pt.TractID 
    left outer join (select TractID, Sum(OtherFedAmount) as OtherFedAmount, sum(OtherFedCostShareAmount) as OtherFedCostShareAmount from dbo.OtherFederal group by TractID ) ofed on ofed.TractID = t.TractID 
    left outer join (select TractID, sum(CostShareAmount) as CostShareAmount from dbo.CostShare group by TractID) costshare on costshare.TractID = t.TractID 
    inner join dbo._refStatus s on ts.StatusCD = s.StatusCD
    where pt.ProjectID = @ProjectID
      and ts.StatusCD <> 120 

    set @RC = @@RowCount

    -- Return recorset of tract details
    select * from #temp1 t
    order by t.TractName

    -- Get Last or Latest Status (other than closed/complete) for latest FundingFY for Project
    select top 1 @LastStatusCD = StatusCD
        ,@LastFundingFY = isnull(FundingFY,0)
    from #temp1
    where not statuscd in (35,100,110,120) and FundingFY <= @CurrentProposedFundingFY
    order by FundingFY desc, StatusCD desc

    -- If LastStatusCD is null then set to 100
    if @LastStatusCD is null
        select top 1 @LastStatusCD = StatusCD
            ,@LastFundingFY = FundingFY
        from #temp1
        where statuscd in (100,110) and FundingFY <= @CurrentProposedFundingFY
        order by FundingFY desc, StatusCD desc

    -- Return Subtotals for Current Status and Year
    select @curFundingAmount = sum(FundingAmount)
        ,@curCostShareAmount = sum(CostShareAmount)
        ,@curTotalFLPCost = sum(FLPCost)
        ,@curOtherFedAmount = sum(OtherFedAmount)
        ,@curOtherFedCostShareAmount = sum(OtherFedCostShareAmount)
        ,@curTotalCost = sum(TotalCost)
        ,@curAcre = sum(Acre)
    from #temp1 
    where FundingFY = @LastFundingFY and StatusCD >= @LastStatusCD

    -- Return Project Totals
    select @TotalFundingAmount = sum(FundingAmount)
        ,@TotalCostShareAmount = sum(CostShareAmount)
        ,@TotalFLPCost = sum(FLPCost)
        ,@TotalOtherFedAmount = sum(OtherFedAmount)
        ,@TotalOtherFedCostShareAmount = sum(OtherFedCostShareAmount)
        ,@TotalCost = sum(TotalCost)
        ,@TotalAcre = sum(Acre)
    from #temp1 

    -- Return FLP Funding Amount To Date
    select
        @FundingAmountToDate = sum(FundingAmount)
    from #temp1
    where StatusCD >= 80 and StatusCD < 120

    -- Check for only dropped tracts
    if @RC = 0  -- this statement is true only if the first query returned no records
        begin
            select @LastFundingFY = max(ts.FundingFY)
            from (select TractID, max(CreatedOn) as CreatedOn from dbo.TractStatus group by TractID ) cs
                inner join dbo.TractStatus ts on cs.TractID = ts.TractID and cs.CreatedOn = ts.CreatedOn
                inner join dbo.ProjectTract pt on ts.TractID = pt.TractID
            where pt.ProjectID = @ProjectID
        end

    drop table #temp1;

    return 

Here is my attempt to fulfill Guidelines when posting JDBC related questions:

Client side

  1. Which OS platform are you running on? [SQL Server 2012 on Windows 2012 with dev. client Windows 10 with java 8u202)
  2. Which JVM are you running on? java 8 update 202 (trying to eventually update to java 11.0.2)
  3. What is the connection URL in you app? Rather not share; connection not an issue.
  4. If client fails to connect, what is the client error messages? N/A, connects fine.
  5. Is the client remote or local to the SQL server machine? Remote
  6. Is your client computer in the same domain as the Server computer? (Same domain)

Server side

  1. What is the MS SQL version? [ SQL Server 2012]
  2. Does the server start successfully? [YES]
  3. If SQL Server is a named instance, is the SQL browser enabled? [YES]
  4. What is the account that the SQL Server is running under?[Domain Account]
  5. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES]
  6. Do you make firewall exception for SQL Browser UDP port 1434? In SQL2000, you still need to make firewall exception for UDP port 1434 in order to support named instance.[not applicable]

Tips

  1. If you are executing a complex statement query or stored procedure, please use execute() instead of executeQuery(). [Yes, I use .execute() to execute the CallableStatement that calls the stored procedure in the database.]
  2. If you are using JDBC transactions, do not mix them with T-SQL transactions. [No we are not using transactions. Simple calls to stored procedures that return combination of output parameters and recordsets. We process the recordset information before retrieving output parameters.]
  • 1
    Welcome to DBA.SE! I wonder, why do you have so many RETURN statements at the end of the procedure? As per the documentation RETURN Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed. – Mr.Brownstone Feb 21 at 23:59
  • 1
    I had thought that each RETURN sends an output parameter from the stored procedure and that this was what allowed us to strongly type/restrict each output parameter individually from within our code, but it appears that you are correct and I just tested removing all but the first RETURN and that any variable defined within the stored procedure is automatically returned. Very cool - thanks for the new knowledge! (I will update that in the above code.) – BBSings Feb 22 at 0:16
  • 1
    In addition to @Mr.Brownstone's comment, try adding SET NOCOUNT ON as the first statement in the proc to suppress DONE_IN_PROC messages or use cstmt2.getMoreResults() to consume all result sets. – Dan Guzman Feb 22 at 0:20
  • Thanks, Dan! Yes, I agree that returning multiple recordsets with cstmt2.getMoreResults() is simpler than all these output parameters. (This particular stored procedure was written by another developer.) However, with the issue I'm having with new version of sql jdbc 7.2, I can successfully retrieve the output parameters and not the recordset for this query. :( I did not know about the SET NOCOUNT ON and the difference it could make on network traffic. Thank you!! – BBSings Feb 22 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.