Seite 1 von 1

SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Mo, 15. Jan 2018 20:52
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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Mo, 15. Jan 2018 21:07
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;

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 8:20
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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 18:32
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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 18:47
von Jan
So no questions are allowed in this forum that are not related to Xbase++? Interesting ...

Jan

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 19:33
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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 19:36
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 :?:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 20:44
von engindenizoglu
Boris doesnt know the answer.
Actually it's a common problem between xbase and SQLEXPRESS.

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 20:47
von engindenizoglu
from SQL side ;
RETURN @BORCTUTAR


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

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 21:15
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 :?:

Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure

Verfasst: Di, 16. Jan 2018 21:55
von engindenizoglu
I use microsoft sql 2012 STANDART .