• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Calling Sqlserver stored procedure from JDBC - "the statement does not return a result set" problem

 
Sophie Stone
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can run this query from toad sql server.



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.




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 )



and this is the error information



and this is full stored procedure (below)

/****** 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,
sum(BS_BA_TUTAR) AS BS_BA_TUTAR,
cast(1 as integer) AS BELGE_SAYISI
INTO dbo.#BsBaEvrakDetayliTablo
from
(SELECT
cast (51 as integer) AS TABLONO,
cha_RECno AS CHRECNO,
case when ISNULL(cha_tpoz,0)=0 then ISNULL(cha_kod,'') else ISNULL(cha_ciro_cari_kodu,'') end as CARI,
cha_tarihi AS TARIH,
cha_evrak_tip as TIP,
cha_evrakno_seri AS SERI,
cha_evrakno_sira AS SIRA,
cha_satir_no AS SATIR,
cha_cinsi AS CINS,
cha_belge_no AS BELNO,
cha_belge_tarih AS BELTAR,
case
when cha_evrak_tip=61 /*gider pusulası*/
then ISNULL(cha_aratoplam,0.0) * ISNULL(cha_d_kur,1.0)
when (NOT cha_cinsi IN (12,24,35))AND /*fason,hal,kabzımal faturaları aşağıda okunsun*/
(NOT (cha_evrak_tip=0 and dbo.fn_malfaturasimi (cha_cinsi,cha_kasa_hizmet)=1 and cha_cinsi=29))AND /*ithalat faturaları aşağıda okunsun*/
((dbo.fn_malfaturasimi (cha_cinsi,cha_kasa_hizmet)=0)OR (ISNULL(cha_otvtutari,0.0)=0.0)) /*ÖTV li stok faturaları aşağıda okunsun*/
then
(ISNULL(cha_meblag,0.0) -
(ISNULL(cha_vergi1,0.0)+ISNULL(cha_vergi2,0.0)+ISNULL(cha_vergi3,0.0)+ISNULL(cha_vergi4,0.0)+ISNULL(cha_vergi5,0.0)+
ISNULL(cha_vergi6,0.0)+ISNULL(cha_vergi7,0.0)+ISNULL(cha_vergi8,0.0)+ISNULL(cha_vergi9,0.0)+ISNULL(cha_vergi10,0.0)+
ISNULL(cha_HalHamaliyeKdv,0.0)+ISNULL(cha_HalKomisyonu,0.0)+ISNULL(cha_HalRehinFuture/*navlun kdvsi*/,0.0)+ISNULL(cha_HalSandikKDVTutari,0.0))+ /*bunlar kabzımal faturasındaki masraflara ait vergiler*/
(ISNULL(cha_stopaj,0.0) + ISNULL(cha_savsandesfonu,0.0) + ISNULL(cha_mustahsil_borsa,0.0) + ISNULL(cha_mustahsil_bagkur,0.0) + ISNULL(cha_mustahsil_diger,0.0)/* - cha_yuvarlama*/)-
(ISNULL(cha_otvtutari,0.0) * case when cha_cinsi=14 and @otv_kdv_orani>0.0 then 0.0 else 1.0 end)- /*demirbaş için ÖTV vergi konusu olmuş ise matrahtan düşsün*/
-- (ISNULL(cha_oivtutari,0.0) * case when @Bs_Ba_tip=1 then 0.0 else 1.0 end)+ /*ÖİV Bs de matrahtan düşsün, Ba da eklensin*/
(ISNULL(cha_oivtutari,0.0))+ /*ÖİV daima matrahtan düşsün*/
ISNULL(dbo.fn_ToplamTevkifat(cha_RECno),0.0))*ISNULL(cha_d_kur,1.0)
when @Bs_Ba_tip=0 AND cha_evrak_tip=0 AND cha_cinsi=24 -- bs için alis_faturasi,Hal_fat içindeki komisyon
then ISNULL(cha_HalKomisyon,0.0) * ISNULL(cha_d_kur,1.0)
else
( SELECT SUM(((ISNULL(sth_tutar,0.0) * case when sth_cins=8 AND sth_tip=0 AND sth_normal_iade=0 then 0.0 else 1.0 end)- /*Fasondan malzeme maliyetini düşelim*/
(ISNULL(sth_iskonto1,0.0)+ISNULL(sth_iskonto2,0.0)+ISNULL(sth_iskonto3,0.0)+ISNULL(sth_iskonto4,0.0)+ISNULL(sth_iskonto5,0.0)+ISNULL(sth_iskonto6,0.0))+
(ISNULL(sth_masraf1,0.0)+ISNULL(sth_masraf2,0.0)+ISNULL(sth_masraf3,0.0)+ISNULL(sth_masraf4,0.0))+
(ISNULL(sth_otvtutari,0.0) * case when ISNULL(sth_otv_vergi,0.0) > 0.0 then 1.0 else 0.0 end)+ /*ÖTV vergi konusu olmuş ise matraha eklensin*/
-- (ISNULL(sth_oivtutari,0.0) * case when @Bs_Ba_tip=1 then 1.0 else 0.0 end)+ /*ÖİV Ba da matraha eklensin, Bs de eklenmesin*/
(ISNULL(sth_halrehfiyati,0.0) * ISNULL(sth_halrehmiktari,0.0) * case when sth_cins in(13,16) then 1.0 else 0.0 end)+
(ISNULL(sth_halsandikfiyati,0.0) * ISNULL(sth_halsandikmiktari,0.0) * case when sth_cins in(13,16) then 1.0 else 0.0 end)
)* ISNULL(sth_har_doviz_kuru,1.0))+
(case when cha_cinsi=24 and ISNULL(MAX(sth_RECno),0)>0 /*aşağıdaki where e uyan kayıt varsa bu değeleri toplasın*/
then ((ISNULL(cha_HalHamaliye,0.0)+ISNULL(cha_HalStopaj,0.0)+ISNULL(cha_HalKomisyonu,0.0)+ISNULL(cha_HalRusum,0.0)+
ISNULL(cha_HalNavlunTut,0.0)+ISNULL(cha_mustahsil_bagkur,0.0))*ISNULL(cha_d_kur,1.0))
else 0.0 end)
FROM dbo.STOK_HAREKETLERI With (NOLOCK, INDEX=NDX_STOK_HAREKETLERI_07)
LEFT OUTER JOIN dbo.DEPOLAR on (dep_no=sth_giris_depo_no)
WHERE (sth_fat_recid_dbcno = cha_RECid_DBCno) AND
(sth_fat_recid_recno = cha_RECid_RECno) AND
((cha_evrak_tip=0 and cha_cinsi=29 and dep_tipi<>5) OR /*ithalat girişi antrepo depoya giriş değilse faturanın kendisini okuyalım. Antrepo ise aşağıda antrepodan millileştirmeleri okuyalım*/
((not (cha_evrak_tip=0 and cha_cinsi=29))and
( (cha_cinsi<>24)OR
((@Bs_Ba_tip=0 AND cha_evrak_tip=63 AND ISNULL(sth_karsikons_recid_recno,0)=0)OR -- karşı konsinye olarak satılanlar, çiftçinin satışı olduğundan firmanın kendi satışı olarak değerlendirilmiyor.
(@Bs_Ba_tip=1 AND cha_evrak_tip=0 AND ISNULL(sth_HalSatisRecid_recno,0)=0)) -- çiftçinin satışlarından oluşturulan alış faturası ise, firmanın kendi alışı olarak değerlendirilmiyor.
)
)
)
)
end AS BS_BA_TUTAR
FROM dbo.CARI_HESAP_HAREKETLERI WITH (NOLOCK)
WHERE (cha_firmano=@vFirmaNo) AND
(
((@kontrol_belge_tarihinden=0) and (cha_tarihi between @IlkTarih AND @SonTarih)) or
((@kontrol_belge_tarihinden=1) and (cha_belge_tarih between @IlkTarih AND @SonTarih))
)AND
((cha_tpoz=0 AND cha_cari_cins=0)or(cha_tpoz=1 AND cha_ciro_cari_kodu<>''))AND
((@Bs_Ba_tip=0 and (cha_evrak_tip in (63,88) or (cha_evrak_tip = 0 and cha_cinsi=24 and ISNULL(cha_HalKomisyon,0.0) > 0.0)))or-- satis_faturasi,Satis_serbest_meslek_makbuzu , alis_faturasi,Hal_fat
(@Bs_Ba_tip=1 and cha_evrak_tip in (0,55,56,61))) AND--alis_faturasi,Giris_gider_makbuzu,Giris_serbest_meslek_makbuzu,Stok_gider_pusulasi
(@SonradanMuhasebelesenSeriDahilEdilmesin_fl=0 or dbo.fn_str_ok(cha_evrakno_seri,@SonradanMuhasebelesenSeriStr)=0)
UNION
SELECT
cast (16 as integer) AS TABLONO,
sth_RECno*-1 AS CHRECNO,
ISNULL(ith_satici,'') as CARI,
sth_tarih AS TARIH,
sth_evraktip as TIP,
sth_evrakno_seri AS SERI,
sth_evrakno_sira AS SIRA,
sth_satirno AS SATIR,
sth_cins AS CINS,
sth_belge_no AS BELNO,
sth_belge_tarih AS BELTAR,
ISNULL(sth_tutar,0.0) * ISNULL(sth_har_doviz_kuru,1.0) AS BS_BA_TUTAR
FROM dbo.STOK_HAREKETLERI WITH (NOLOCK, INDEX=NDX_STOK_HAREKETLERI_05)
LEFT OUTER JOIN dbo.ITHALAT_DOSYALARI on (ith_kodu=sth_exim_kodu)
LEFT OUTER JOIN dbo.DEPOLAR on (dep_no=sth_cikis_depo_no)
WHERE (@Bs_Ba_tip=1) AND
(sth_evraktip=10) AND /*Ba için antrepodan mal millileştirme*/
(sth_firmano=@vFirmaNo) AND
(
((@kontrol_belge_tarihinden=0) and (sth_tarih between @IlkTarih AND @SonTarih)) or
((@kontrol_belge_tarihinden=1) and (sth_belge_tarih between @IlkTarih AND @SonTarih))
)AND
(dep_tipi=5) AND /*antrepodan kendi depomuza aldığımız mal ise*/
(@SonradanMuhasebelesenSeriDahilEdilmesin_fl=0 or dbo.fn_str_ok(sth_evrakno_seri,@SonradanMuhasebelesenSeriStr)=0)
)as DETAYSIZ
WHERE ISNULL(BS_BA_TUTAR,0.0) >= 0.0
group by CARI,TABLONO,TIP,SERI,SIRA
IF @EvrakDetayliRapor_fl=1
BEGIN
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaEvraklar%') Drop Table dbo.#BsBaEvraklar
select
case when @BirlestimeTuru =2 then [msg_S_1259]+' - '+[msg_S_1949]
when @BirlestimeTuru =1 then [msg_S_1259]+' - '+[msg_S_1949]+' - '+ [msg_S_0905]
else [msg_S_0200]
end as [msg_S_3103], /*TC.KİM. - VER.KİM. - ÜLKE - GENEL CARİ İSMİ*/
*
into dbo.#BsBaEvraklar
from(
select
CARI AS [msg_S_0200], /*CARİ KODU*/
case when cari_unvan2<>'' then substring(cari_unvan1+' '+cari_unvan2,1,60) else cari_unvan1 end AS [msg_S_0201],/*CARİ İSMİ*/
case when len(REPLACE(cari_vdaire_no,' ','')) < 11 then '' else REPLACE(cari_vdaire_no,' ','') end AS [msg_S_1259] /* TC KIMLIK NO */,
case when len(REPLACE(cari_vdaire_no,' ','')) < 11 then REPLACE(cari_vdaire_no,' ','') else REPLACE(cari_VergiKimlikNo,' ','') end AS [msg_S_1949] /*VERGİ KİMLİK NO */,
dbo.fn_FormBsUlkeKodu(cari_kod,cari_fatura_adres_no) AS [msg_S_0905] /* ÜLKE KODU */,
TARIH AS [msg_S_0089], -- TARİH
CASE WHEN TABLONO=51 THEN dbo.fn_CariHarEvrTipUzun(TIP) ELSE dbo.fn_StokHarEvrTip(TIP) END AS [msg_S_0094], -- EVRAK TİPİ,
SERI AS [msg_S_0090], -- SERİ
SIRA AS [msg_S_0091], -- SIRA
CASE WHEN TABLONO=51 THEN dbo.fn_CariHarCins(CINS) ELSE dbo.fn_StokHarCins(CINS) END AS [msg_S_0003], -- CİNSİ
BELNO AS [msg_S_0093], -- BELGE NO
BELTAR AS [msg_S_0092], -- BELGE TARİHİ
BS_BA_TUTAR AS [msg_S_1951] /* BS-BA YA KONU TUTAR */,
CHRECNO AS [#msg_S_0777] /* PKAYIT NO */
from dbo.#BsBaEvrakDetayliTablo
LEFT OUTER JOIN dbo.CARI_HESAPLAR ON cari_kod=CARI
)as HT
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaToplamlar%') Drop Table dbo.#BsBaToplamlar
SELECT [msg_S_3103]AS TVU,MAX([msg_S_0201])AS UNVAN
INTO dbo.#BsBaToplamlar
FROM dbo.#BsBaEvraklar
GROUP BY [msg_S_3103]
HAVING sum([msg_S_1951]) >= @MinTutar
select
[#msg_S_0777] as [#msg_S_0088],
[msg_S_3103]+ case when @BirlestimeTuru > 0 then ' - '+ UNVAN else '' end as [msg_S_3103\G], /*TC.KİM. - VER.KİM. - ÜLKE - GENEL CARİ İSMİ*/
[msg_S_0200], /*CARİ KODU*/
[msg_S_0201], /*CARİ İSMİ*/
[msg_S_1259], /* TC KIMLIK NO */
[msg_S_1949], /*VERGİ KİMLİK NO */
[msg_S_0905], /* ÜLKE KODU */
[msg_S_0089], /* TARİH */
[msg_S_0094] AS [msg_S_0094\S], /* EVRAK TİPİ*/
[msg_S_0090], /* SERİ */
[msg_S_0091], /* SIRA */
[msg_S_0003], /* CİNSİ */
[msg_S_0093], /* BELGE NO */
[msg_S_0092], /* BELGE TARİHİ */
[msg_S_1951] AS [msg_S_1951\T] /* BS-BA YA KONU TUTAR */
from dbo.#BsBaEvraklar
LEFT OUTER JOIN dbo.#BsBaToplamlar ON TVU=[msg_S_3103]
WHERE ISNULL(TVU,'')<>''
ORDER BY [msg_S_3103\G]
END
ELSE
BEGIN
if exists (select * from tempdb..sysobjects where name LIKE '#BsBaTablosu%') Drop Table dbo.#BsBaTablosu
select
*,
cari_RECno AS RECNO,
case when cari_unvan2<>'' then substring(cari_unvan1+' '+cari_unvan2,1,60) else cari_unvan1 end AS UNVAN,
REPLACE(cari_vdaire_no,' ','') as TCNO,
REPLACE(cari_VergiKimlikNo,' ','') as VKNO,
dbo.fn_FormBsUlkeKodu(cari_kod,cari_fatura_adres_no) as ULKE
into dbo.#BsBaTablosu
from
( select
CARI AS CARI,
sum(BS_BA_TUTAR) AS BS_BA_TUTAR,
sum(BELGE_SAYISI)AS BELGE_SAYISI,
CASE WHEN (@PerakendeCariKodu<>'' AND CARI like @PerakendeCariKodu)THEN 1 ELSE 0 END AS PERAKENDE
from dbo.#BsBaEvrakDetayliTablo
group by CARI
)as CARIGRUPLU
LEFT OUTER JOIN dbo.CARI_HESAPLAR ON cari_kod=CARI
/*Vergi numarası olmayan kayıtları birleştirmeyelim*/
DECLARE @tanimsizno as nvarchar(5)
set @tanimsizno = '#@#'
UPDATE dbo.#BsBaTablosu SET TCNO = @tanimsizno+CONVERT(NVARCHAR(12),RECNO) WHERE TCNO=''
UPDATE dbo.#BsBaTablosu SET VKNO = @tanimsizno+SUBSTRING(CONVERT(NVARCHAR(12),RECNO),1,7) WHERE VKNO=''
if exists (select * from tempdb..sysobjects where name LIKE '#FormBsBa%') Drop Table dbo.#FormBsBa
CREATE Table dbo.#FormBsBa
(Unvan nvarchar(60) COLLATE database_default,
Ulkesi nvarchar(10) COLLATE database_default,
VergiKimlikNo nvarchar(10) COLLATE database_default,
TCKimlikNo nvarchar(15) COLLATE database_default,
BelgeSayisi integer,
Toplam float,
CariKod nvarchar(25) COLLATE database_default)
if (@BirlestimeTuru =2)
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),MAX(ULKE),MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),MAX(CARI)
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY TCNO
ELSE
if (@BirlestimeTuru =1)
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),ULKE,MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),MAX(CARI)
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY TCNO,ULKE
ELSE
INSERT INTO dbo.#FormBsBa SELECT MAX(UNVAN),ULKE,MAX(VKNO),TCNO,SUM(BELGE_SAYISI),sum(BS_BA_TUTAR),CARI
FROM dbo.#BsBaTablosu
WHERE PERAKENDE=0
GROUP BY CARI,TCNO,ULKE
UPDATE dbo.#FormBsBa SET TCKimlikNo = '' WHERE TCKimlikNo LIKE @tanimsizno+'%'
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '' WHERE VergiKimlikNo LIKE @tanimsizno+'%'
UPDATE dbo.#FormBsBa SET VergiKimlikNo = TCKimlikNo WHERE LEN(TCKimlikNo)<11
UPDATE dbo.#FormBsBa SET TCKimlikNo = '' WHERE LEN(TCKimlikNo)<11
IF @Bs_Ba_tip = 0
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '2222222222',TCKimlikNo = '22222222222' WHERE (ISNUMERIC(Ulkesi)=1)AND (CAST(Ulkesi as integer) <>52)
ELSE
UPDATE dbo.#FormBsBa SET VergiKimlikNo = '1111111111',TCKimlikNo = '11111111111' WHERE (ISNUMERIC(Ulkesi)=1)AND (CAST(Ulkesi as integer) <>52)
IF @Aylik_BsBa_fl=1
BEGIN
declare @perakendetoplam as float
select @perakendetoplam = sum(BS_BA_TUTAR) FROM dbo.#BsBaTablosu WHERE PERAKENDE=1
if isnull(@perakendetoplam,0.0) > 0.0
INSERT INTO dbo.#FormBsBa SELECT '---','---','---','---',0,@perakendetoplam,'---'
END
SELECT * FROM dbo.#FormBsBa
WHERE (Toplam>=@MinTutar)OR(TCKimlikNo='---')
ORDER BY VergiKimlikNo,TCKimlikNo,Unvan,Ulkesi
END
END
GO
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi tunc ay,

First of all, a warm welcome to CodeRanch!

tunc ay wrote: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.

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

Your stored procedure is quite long, but I was able to spot 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 rouw counts (and the exception the statement did not return a result set will be thrown). Luckily the fix is very easy: you just have to add SET NOCOUNT ON at the start of the stored procedure. So your code would look like:When SET NOCOUNT is ON, the count is not returned. And JDBC (SQL Server driver) is not confused anymore with the row counts.

Hope it helps!
Kind regards,
Roel
 
Sophie Stone
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you very much ,

it solved the problem
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad to hear your problem is solved!

Nice to know: instead of making a "thank you" post, you could also the post(s) which you liked. It's easier, faster and other ranchers will see immediately which are the "starred" posts.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic