SQL EXPRESS - calling a Microsoft SQL SERVER procedure
Moderator: Moderatoren
- engindenizoglu
- Cut&Paste-Entwickler
- Beiträge: 41
- Registriert: Mi, 08. Nov 2017 20:58
- Wohnort: ISTANBUL
SQL EXPRESS - calling a Microsoft SQL SERVER procedure
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
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
LARA ltd
http:\\www.laraelectromechanical.com
- HaPe
- 1000 working lines a day
- Beiträge: 996
- 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
Hello Engin !
To return a variable from a $MS SQL-Server you have to use "RETURN"SELECT @KUMULATIFTOPLAM AS BORCTUTAR ---->> this ‘red variable’ value doesn’t come back !!!
Code: Alles auswählen
RETURN @BORCTUTAR;
--
Hans-Peter
Hans-Peter
- engindenizoglu
- Cut&Paste-Entwickler
- Beiträge: 41
- Registriert: Mi, 08. Nov 2017 20:58
- Wohnort: ISTANBUL
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
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
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
LARA ltd
http:\\www.laraelectromechanical.com
- AUGE_OHR
- Marvin
- Beiträge: 12913
- Registriert: Do, 16. Mär 2006 7:55
- Wohnort: Hamburg
- Hat sich bedankt: 19 Mal
- Danksagung erhalten: 46 Mal
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
wrong Forum
you have to ask in M§ SQL Forum while you have a M$ SQL Problem NOT Xbase++
gruss by OHR
Jimmy
Jimmy
- Jan
- Marvin
- Beiträge: 14662
- Registriert: Fr, 23. Sep 2005 18:23
- Wohnort: 49328 Melle
- Hat sich bedankt: 21 Mal
- Danksagung erhalten: 88 Mal
- Kontaktdaten:
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
So no questions are allowed in this forum that are not related to Xbase++? Interesting ...
Jan
Jan
Mitglied der XUG Osnabrück
Mitglied der XUG Berlin/Brandenburg
Mitglied des Deutschsprachige Xbase-Entwickler e. V.
Mitglied der XUG Berlin/Brandenburg
Mitglied des Deutschsprachige Xbase-Entwickler e. V.
- Tom
- Der Entwickler von "Deep Thought"
- Beiträge: 9394
- Registriert: Do, 22. Sep 2005 23:11
- Wohnort: Berlin
- Hat sich bedankt: 104 Mal
- Danksagung erhalten: 364 Mal
- Kontaktdaten:
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
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
Did you ask Boris?
http://xb2.net/menu.htm -> User forums -> SQLexpress
Herzlich,
Tom
Tom
- AUGE_OHR
- Marvin
- Beiträge: 12913
- Registriert: Do, 16. Mär 2006 7:55
- Wohnort: Hamburg
- Hat sich bedankt: 19 Mal
- Danksagung erhalten: 46 Mal
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
if it is not related to Xbase+ who can answer, can you
gruss by OHR
Jimmy
Jimmy
- engindenizoglu
- Cut&Paste-Entwickler
- Beiträge: 41
- Registriert: Mi, 08. Nov 2017 20:58
- Wohnort: ISTANBUL
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
Boris doesnt know the answer.
Actually it's a common problem between xbase and SQLEXPRESS.
Actually it's a common problem between xbase and SQLEXPRESS.
ENGIN RENE D.
LARA ltd
http:\\www.laraelectromechanical.com
LARA ltd
http:\\www.laraelectromechanical.com
- engindenizoglu
- Cut&Paste-Entwickler
- Beiträge: 41
- Registriert: Mi, 08. Nov 2017 20:58
- Wohnort: ISTANBUL
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
from SQL side ;
RETURN @BORCTUTAR
I want to get the value of variable BORCTUTAR back to xbase side.
This doesn't work.
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
LARA ltd
http:\\www.laraelectromechanical.com
- AUGE_OHR
- Marvin
- Beiträge: 12913
- Registriert: Do, 16. Mär 2006 7:55
- Wohnort: Hamburg
- Hat sich bedankt: 19 Mal
- Danksagung erhalten: 46 Mal
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
if Boris have no Answer do you expect to find Answer here
that is always what User "think" when they failengindenizoglu hat geschrieben: ↑Di, 16. Jan 2018 20:44 Actually it's a common problem between xbase and SQLEXPRESS.
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
Jimmy
- engindenizoglu
- Cut&Paste-Entwickler
- Beiträge: 41
- Registriert: Mi, 08. Nov 2017 20:58
- Wohnort: ISTANBUL
Re: SQL EXPRESS - calling a Microsoft SQL SERVER procedure
I use microsoft sql 2012 STANDART .
ENGIN RENE D.
LARA ltd
http:\\www.laraelectromechanical.com
LARA ltd
http:\\www.laraelectromechanical.com