Calling Sqlserver stored procedure from JDBC,Java

Question

I can run this query from toad sql server.

exec msp_FormBsBa_yeni 0,'20150101','20150131',5000,0,2,0,null,1,null,0

from my application in java, it generated error as the statement did not return resultset. i tried many things , but i could not solve it. Thank you for your help.

    // Connection,Resultset ve PrepStatement Declaration
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rsa = null;
    List<EntCari> listrows = new ArrayList<EntCari>();

    try {
        connection = ConnectionFactory.getConnection();

        CallableStatement callableStatement = connection
                .prepareCall("{call msp_FormBsBa_yeni(0,N'20150101',N'20150131',5000,0,2,0,null,1,null,0)}");

        rsa = callableStatement.executeQuery();

        // 2:resultset check
        if (!rsa.next()) {
            System.out.println("no data");
        } else {
            do {
                // System.out.println("data exists");
                // Statements
                EntCari row = new EntCari();
                row.setMusteriadi(rsa.getString("Unvan"));// rsa.getxxx("column")
                listrows.add(row);

            } while (rsa.next());
        }

        // 2---

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        DbUtil.close(rsa);
        DbUtil.close(pstmt);
        DbUtil.close(connection);
    }

result from toad sql server is like below.

Unvan |Ulkesi |VergiKimlikNo| TCKimlikNo| BelgeSayisi|Toplam| CariKod

customer a | 052 | 19697583840 | 1 | 2323,00 | HT00084

customer b | 052 | | 2 | 2111,00 | HT01022

stored procedure is like below (partial of sp , it is long )

/****** Object: Procedure [dbo].[msp_FormBsBa_yeni]   Script Date: 18.04.2015 13:43:44 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.msp_FormBsBa_yeni
@vFirmaNo    as integer,
@IlkTarih    as datetime,
@SonTarih    as datetime,
@MinTutar    as float,
@Bs_Ba_tip   as bit,
@BirlestimeTuru as tinyint,
@SonradanMuhasebelesenSeriDahilEdilmesin_fl as bit,
@SonradanMuhasebelesenSeriStr nvarchar(MAX),
@Aylik_BsBa_fl as bit,
@PerakendeCariKodu AS nvarchar(25),
@EvrakDetayliRapor_fl as bit
AS
BEGIN  /*dbo.fn_GetByteParam(1048)=1 ÖTV stok maliyetine eklensinmi*/
Declare @otv_vergino as tinyint
set @otv_vergino = dbo.fn_GetByteParam(855) /*ÖTV vergi tipi*/
Declare @otv_kdv_orani as FLOAT
set @otv_kdv_orani = 0.0
if @otv_vergino between 1 and 10
set @otv_kdv_orani = dbo.fn_VergiYuzde(@otv_vergino)
Declare @kontrol_belge_tarihinden as integer
set @kontrol_belge_tarihinden = dbo.fn_GetByteParam(4173) /*BsBa_Kontrol_Belge_tarihinden_fl*/
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaEvrakDetayliTablo%') Drop Table dbo.#BsBaEvrakDetayliTablo
select
TABLONO,
MIN(CHRECNO) AS CHRECNO,
CARI,
MAX(TARIH) AS TARIH,
TIP,
SERI,
SIRA,
MAX(CINS)AS CINS,
MAX(BELNO)AS BELNO,
MAX(BELTAR)AS BELTAR...

and this is the error information

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:394)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283) 

Answer 1

You cannot call executeQuery() on a Transact-SQL CallableStatement that returns cursors / ResultSet. The proper way to call that procedure is this:

// You should really use bind variables here...
CallableStatement call = connection
    .prepareCall("{call msp_FormBsBa_yeni(?, ?, ?, ...)}");
call.setInt(1, ...);
call.setString(2, ...);

// This returns true if there are any remaining ResultSets to fetch
if (call.execute()) {
    do {
        try (ResultSet rs = call.getResultSet()) {
            // Consume the result set here...
        }
    }

    // Move on to the next result set, if any
    while (call.getMoreResults());
}

If you want to be completely generic, you'll also have to check the Statement.getUpdateCount(), in case there are update counts interwoven with result sets in your procedure.

Answer 2

when I execute the query in a database client (e.g. SQL Server Management Studio), a table is returned.

My stored procedure is quite long, there are a few insert and update statements. If the stored procedure performs any inserts or updates prior to the final select, JDBC (the SQL Server driver) is getting confused with the row counts (and the exception the statement did not return a result set will be thrown). Luckily the fix is very easy: i just have to add SET NOCOUNT ON at the start of the stored procedure. So my code would look like:

CREATE PROCEDURE dbo.msp_FormBsBa_yeni
@vFirmaNo as integer,
@IlkTarih as datetime,
@SonTarih as datetime,
@MinTutar as float,
@Bs_Ba_tip as bit,
@BirlestimeTuru as tinyint,
@SonradanMuhasebelesenSeriDahilEdilmesin_fl as bit,
@SonradanMuhasebelesenSeriStr nvarchar(MAX),
@Aylik_BsBa_fl as bit,
@PerakendeCariKodu AS nvarchar(25),
@EvrakDetayliRapor_fl as bit
AS
BEGIN
  SET NOCOUNT ON

i thank you very much for contributions