SQL EXPRESS - calling a Microsoft SQL SERVER procedure

SQL Express von Boris Borzic

Moderator: Moderatoren

Antworten
Benutzeravatar
engindenizoglu
Cut&Paste-Entwickler
Cut&Paste-Entwickler
Beiträge: 41
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL

SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu »

I have an issue;
Fieldget(1) variable doen't come back to xbase side...What can be wrong with my code ?

xbase code;

o:= SQLSelect():new("EXEC CUSTOMER_YASLANDIRMA_TOPLAMI_TEST ? , ? ",oConn, SQL_CONCUR_READ_ONLY, SQL_CURSOR_FORWARD_ONLY)
o:execute({'300941','TRY'})
Zengin1:=o:FieldGet(1) ----> here i get error - Description: invalid field:1 - Operation: SQLSELECT:FIELDGET
o:destroy()



SQl server procedure below;

ALTER PROCEDURE [dbo].[CUSTOMER_YASLANDIRMA_TOPLAMI_TEST]
@CARIKODU NVARCHAR(30) = '300941',
@PARABIRIMI NVARCHAR(10) = 'TRY'

AS

DECLARE @CARIBAKIYE DECIMAL(19,2)
DECLARE @TODAY DATE
DECLARE @KUMULATIFTOPLAM DECIMAL(19,2)
DECLARE @MAXSIRANO INT
DECLARE @SIRANO INT
DECLARE @BORC_TUTAR DECIMAL(19,2)

SET @TODAY = GETDATE()

SET @CARIBAKIYE = dbo.fnc_CARIBAKIYE_BUL(@CARIKODU,@PARABIRIMI)

CREATE TABLE #DETAY( SIRANO INT IDENTITY(1,1),FATURATARIHI DATE,VADETARIHI DATE ,BORC_TUTAR DECIMAL(19,2))
INSERT INTO #DETAY(FATURATARIHI,VADETARIHI,BORC_TUTAR)
SELECT BUDAT,ZFBDT,0--BORC_TUTAR
FROM VW_CUSTOMER_LINEITEMS
WHERE KUNNR = @CARIKODU AND WAERS = @PARABIRIMI

SELECT TOP 1 @MAXSIRANO = SIRANO FROM #DETAY WHERE VADETARIHI <= @TODAY
SELECT TOP 1 @KUMULATIFTOPLAM = BORC_TUTAR FROM #DETAY WHERE SIRANO = @MAXSIRANO-1

IF @CARIBAKIYE < @KUMULATIFTOPLAM
SET @KUMULATIFTOPLAM = @CARIBAKIYE


SELECT @KUMULATIFTOPLAM AS BORCTUTAR ---->> this ‘red variable’ value doesn’t come back !!!

DROP TABLE #DETAY
ENGIN RENE D.

LARA ltd
http:\\www.laraelectromechanical.com
Benutzeravatar
HaPe
1000 working lines a day
1000 working lines a day
Beiträge: 995
Registriert: So, 15. Nov 2015 17:44
Wohnort: 71665 Vaihingen-Enz
Hat sich bedankt: 17 Mal
Danksagung erhalten: 15 Mal

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von HaPe »

Hello Engin !
SELECT @KUMULATIFTOPLAM AS BORCTUTAR ---->> this ‘red variable’ value doesn’t come back !!!
To return a variable from a $MS SQL-Server you have to use "RETURN"

Code: Alles auswählen

RETURN @BORCTUTAR;
--
Hans-Peter
Benutzeravatar
engindenizoglu
Cut&Paste-Entwickler
Cut&Paste-Entwickler
Beiträge: 41
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu »

I made tHe change like you have advised below ; but still get THE errror from xbase side like --> INVALID FIELD
What can be wrong ?

xbase side:
o:= SQLSelect():new("EXEC CUSTOMER_YASLANDIRMA_TOPLAMI ? , ? ",oConn, SQL_CONCUR_READ_ONLY, SQL_CURSOR_FORWARD_ONLY)
o:execute({'300941','TRY'})
Zengin1:=o:FieldGet(1)
Zengin1:=o:FieldGet("BORCTUTAR") --> THE ERROR PLACE



-------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[CUSTOMER_YASLANDIRMA_TOPLAMI_TEST]
@CARIKODU NVARCHAR(30) = '300941',
@PARABIRIMI NVARCHAR(10) = 'TRY'

AS

DECLARE @CARIBAKIYE DECIMAL(19,2)
DECLARE @TODAY DATE
DECLARE @KUMULATIFTOPLAM DECIMAL(19,2)
DECLARE @MAXSIRANO INT
DECLARE @SIRANO INT
DECLARE @BORC_TUTAR DECIMAL(19,2)
DECLARE @BORCTUTAR DECIMAL(19,2)

SET @TODAY = GETDATE()

SET @CARIBAKIYE = dbo.fnc_CARIBAKIYE_BUL(@CARIKODU,@PARABIRIMI)

CREATE TABLE #DETAY( SIRANO INT IDENTITY(1,1),FATURATARIHI DATE,VADETARIHI DATE ,BORC_TUTAR DECIMAL(19,2))
INSERT INTO #DETAY(FATURATARIHI,VADETARIHI,BORC_TUTAR)
SELECT BUDAT,ZFBDT,0--BORC_TUTAR
FROM VW_CUSTOMER_LINEITEMS
WHERE KUNNR = @CARIKODU AND WAERS = @PARABIRIMI

SELECT TOP 1 @MAXSIRANO = SIRANO FROM #DETAY WHERE VADETARIHI <= @TODAY
SELECT TOP 1 @KUMULATIFTOPLAM = BORC_TUTAR FROM #DETAY WHERE SIRANO = @MAXSIRANO-1

IF @CARIBAKIYE < @KUMULATIFTOPLAM
SET @KUMULATIFTOPLAM = @CARIBAKIYE

SET @BORCTUTAR= @KUMULATIFTOPLAM

RETURN @BORCTUTAR

DROP TABLE #DETAY
ENGIN RENE D.

LARA ltd
http:\\www.laraelectromechanical.com
Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 12903
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg
Hat sich bedankt: 19 Mal
Danksagung erhalten: 44 Mal

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR »

engindenizoglu hat geschrieben: Di, 16. Jan 2018 8:20 What can be wrong ?
wrong Forum :!:

you have to ask in M§ SQL Forum while you have a M$ SQL Problem NOT Xbase++ [-X
gruss by OHR
Jimmy
Benutzeravatar
Jan
Marvin
Marvin
Beiträge: 14641
Registriert: Fr, 23. Sep 2005 18:23
Wohnort: 49328 Melle
Hat sich bedankt: 21 Mal
Danksagung erhalten: 87 Mal
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von Jan »

So no questions are allowed in this forum that are not related to Xbase++? Interesting ...

Jan
Mitglied der XUG Osnabrück
Mitglied der XUG Berlin/Brandenburg
Mitglied des Deutschsprachige Xbase-Entwickler e. V.
Benutzeravatar
Tom
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 9345
Registriert: Do, 22. Sep 2005 23:11
Wohnort: Berlin
Hat sich bedankt: 100 Mal
Danksagung erhalten: 359 Mal
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von Tom »

Looks like the result is not a cursor. What is o:FieldGet(1) returning?

Did you ask Boris?

http://xb2.net/menu.htm -> User forums -> SQLexpress
Herzlich,
Tom
Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 12903
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg
Hat sich bedankt: 19 Mal
Danksagung erhalten: 44 Mal

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR »

Jan hat geschrieben: Di, 16. Jan 2018 18:47So no questions are allowed in this forum that are not related to Xbase++? Interesting ...
if it is not related to Xbase+ who can answer, can you :?:
gruss by OHR
Jimmy
Benutzeravatar
engindenizoglu
Cut&Paste-Entwickler
Cut&Paste-Entwickler
Beiträge: 41
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu »

Boris doesnt know the answer.
Actually it's a common problem between xbase and SQLEXPRESS.
ENGIN RENE D.

LARA ltd
http:\\www.laraelectromechanical.com
Benutzeravatar
engindenizoglu
Cut&Paste-Entwickler
Cut&Paste-Entwickler
Beiträge: 41
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu »

from SQL side ;
RETURN @BORCTUTAR


I want to get the value of variable BORCTUTAR back to xbase side.
This doesn't work.
ENGIN RENE D.

LARA ltd
http:\\www.laraelectromechanical.com
Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 12903
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg
Hat sich bedankt: 19 Mal
Danksagung erhalten: 44 Mal

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR »

engindenizoglu hat geschrieben: Di, 16. Jan 2018 20:44 Boris doesnt know the answer.
if Boris have no Answer do you expect to find Answer here :roll:
engindenizoglu hat geschrieben: Di, 16. Jan 2018 20:44 Actually it's a common problem between xbase and SQLEXPRESS.
that is always what User "think" when they fail [-X

first Problem most is to find "right" ODBC Version. which M$ SQL ODBC Version are you using :?:
i do test my SQL Query with PgAdmin ( PostgreSQL) or MySQL Workbench. i guess M$ have something like that :?:
gruss by OHR
Jimmy
Benutzeravatar
engindenizoglu
Cut&Paste-Entwickler
Cut&Paste-Entwickler
Beiträge: 41
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu »

I use microsoft sql 2012 STANDART .
ENGIN RENE D.

LARA ltd
http:\\www.laraelectromechanical.com
Antworten