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: 33
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL
Kontaktdaten:

SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu » Mo, 15. Jan 2018 20:52

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
hhtp:\\www.core-winder.com
http:\\www.laraelectromechanical.com
http:\\www.lara-plastics.com

Benutzeravatar
HaPe
Foren-Moderator
Foren-Moderator
Beiträge: 532
Registriert: So, 15. Nov 2015 17:44
Wohnort: 71665 Vaihingen-Enz

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von HaPe » Mo, 15. Jan 2018 21:07

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

Organisator der XUG Stuttgart
Beisitzer des Deutschsprachige Xbase-Entwickler e. V.

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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu » Di, 16. Jan 2018 8:20

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
hhtp:\\www.core-winder.com
http:\\www.laraelectromechanical.com
http:\\www.lara-plastics.com

Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 11421
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR » Di, 16. Jan 2018 18:32

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: 13073
Registriert: Fr, 23. Sep 2005 18:23
Wohnort: 49328 Melle
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von Jan » Di, 16. Jan 2018 18:47

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: 7287
Registriert: Do, 22. Sep 2005 23:11
Wohnort: Berlin
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von Tom » Di, 16. Jan 2018 19:33

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: 11421
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR » Di, 16. Jan 2018 19:36

Jan hat geschrieben:
Di, 16. Jan 2018 18:47
So 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: 33
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu » Di, 16. Jan 2018 20:44

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

LARA ltd
hhtp:\\www.core-winder.com
http:\\www.laraelectromechanical.com
http:\\www.lara-plastics.com

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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu » Di, 16. Jan 2018 20:47

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
hhtp:\\www.core-winder.com
http:\\www.laraelectromechanical.com
http:\\www.lara-plastics.com

Benutzeravatar
AUGE_OHR
Marvin
Marvin
Beiträge: 11421
Registriert: Do, 16. Mär 2006 7:55
Wohnort: Hamburg

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von AUGE_OHR » Di, 16. Jan 2018 21:15

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: 33
Registriert: Mi, 08. Nov 2017 20:58
Wohnort: ISTANBUL
Kontaktdaten:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Beitrag von engindenizoglu » Di, 16. Jan 2018 21:55

I use microsoft sql 2012 STANDART .
ENGIN RENE D.

LARA ltd
hhtp:\\www.core-winder.com
http:\\www.laraelectromechanical.com
http:\\www.lara-plastics.com

Antworten