Sqlserver stored procedure call 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 an error since the statement did not return resultet. 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 as shown below.

Unvan | Ulkesi | VergiKimlikNo | TCKimlikNo | BelgeSayisi | Toplam | Carikod

customer a | 052 | 19697583840 | 1 | 2323.00 | HT00084

customer b | 052 | | 2 | 2111.00 | HT01022

the stored procedure is similar to below (partial 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 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

executeQuery() Transact-SQL CallableStatement, /ResultSet. :

// 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());
}

, Statement.getUpdateCount(), .

Answer 2

(, SQL Server Management Studio), .

, . - , JDBC ( SQL Server) ( , , ). , : SET NOCOUNT ON . :

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

Answer 3

.

String SPsql = "EXEC msp_FormBsBa_yeni ?,?,?";   // for stored proc taking 3 params
Connection con = SmartPoolFactory.getConnection();   
PreparedStatement ps = con.prepareStatement(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(<timeout value>);
ps.setString(1, "val");
ps.setString(2, "val";
ps.setString(3, "val");
ResultSet rs = ps.executeQuery();

scrap JDBC

CallableStatement cStmt = conn.prepareCall("{call msp_FormBsBa_yeni (?, ?, ?) }");
cStmt.setString(1, "val");
cStmt.setString(2, "val");
cStmt.setString(3, "val");
cStmt.registerOutParameter(4, Types.INTEGER); // register the output params 
cStmt.registerOutParameter("inOutParam", Types.INTEGER); // or by param name

this this

, .