Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #9105 > unrolled thread

About vo sqlserver

Started byjorgeaccinelli@dextrasistemas.com.ar
First post2016-03-08 05:50 -0800
Last post2016-03-09 04:19 -0800
Articles 6 — 4 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  About vo sqlserver jorgeaccinelli@dextrasistemas.com.ar - 2016-03-08 05:50 -0800
    Re: About vo sqlserver Franz Rachbauer <office@rachbauer.org> - 2016-03-08 15:20 +0100
    Re: About vo sqlserver jorgeaccinelli@dextrasistemas.com.ar - 2016-03-08 06:53 -0800
      Re: About vo sqlserver kormondi@kotech.hu - 2016-03-08 11:00 -0800
    Re: About vo sqlserver ingenierialym@gmail.com - 2016-03-08 13:16 -0800
    Re: About vo sqlserver jorgeaccinelli@dextrasistemas.com.ar - 2016-03-09 04:19 -0800

#9105 — About vo sqlserver

Fromjorgeaccinelli@dextrasistemas.com.ar
Date2016-03-08 05:50 -0800
SubjectAbout vo sqlserver
Message-ID<2add1070-2947-4af4-ba3d-a7675c63597f@googlegroups.com>
Hello everyone 

A specific issue on vo28 - vo2ado SQL Server .... A procedure performs a series of steps and one of them is to get the last ID of a table ... ... 

From my vo app
( Program that invoked the sp )
 i woluld like to get this last ID ... 

But how ?
Some ilustrative fragment ? 

Thanks 
Jorge Accinelli
Buenos Aires

[toc] | [next] | [standalone]


#9106

FromFranz Rachbauer <office@rachbauer.org>
Date2016-03-08 15:20 +0100
Message-ID<nbmn3s$ngg$1@gioia.aioe.org>
In reply to#9105
Found these solutions in web (untested):
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
or
SELECT MAX(ID) AS LastID FROM Persons
or
SELECT IDENT_CURRENT(‘MyTable’)

Please try which one meets your need.
Regards, Franz

Am 08.03.2016 um 14:50 schrieb jorgeaccinelli@dextrasistemas.com.ar:
> Hello everyone
>
> A specific issue on vo28 - vo2ado SQL Server .... A procedure performs a series of steps and one of them is to get the last ID of a table ... ...
>
>  From my vo app
> ( Program that invoked the sp )
>   i woluld like to get this last ID ...
>
> But how ?
> Some ilustrative fragment ?
>
> Thanks
> Jorge Accinelli
> Buenos Aires
>

[toc] | [prev] | [next] | [standalone]


#9107

Fromjorgeaccinelli@dextrasistemas.com.ar
Date2016-03-08 06:53 -0800
Message-ID<524cdf06-0a4e-43ab-b21c-f7177dad335e@googlegroups.com>
In reply to#9105
El martes, 8 de marzo de 2016, 10:50:07 (UTC-3), jorgeac...@dextrasistemas.com.ar escribió:
> Hello everyone 
> 
> A specific issue on vo28 - vo2ado SQL Server .... A procedure performs a series of steps and one of them is to get the last ID of a table ... ... 
> 
> From my vo app
> ( Program that invoked the sp )
>  i woluld like to get this last ID ... 
> 
> But how ?
> Some ilustrative fragment ? 
> 
> Thanks 
> Jorge Accinelli
> Buenos Aires


Thank you for answering 
These are ways to get the Last ID ... but that already occurs within the sp .. 

What I want to know is how to capture an output parameter of the store procedure in my vo app

Jorge

[toc] | [prev] | [next] | [standalone]


#9112

Fromkormondi@kotech.hu
Date2016-03-08 11:00 -0800
Message-ID<ddc2f407-38b4-48aa-a32e-8d158eb8a4f2@googlegroups.com>
In reply to#9107
Hi Jorge!

Using this example below you can capture the output parameters and the return value too of the stored procedure.
Hope this help!

István Körmöndi


Method Exec_Sp_Test() Class winShell
	Local lReturn As Logic
	Local aInput As Array
	Local aOutput As Array
	

	aInput:={{"@InputPar1","100"}, {"@InputPar2","200"}}
	aOutput:={{"@OutputPar",0 }}
	
	lReturn:=FALSE	
	IF ExecSp("sp_Test",aInput,aOutput,self)
			
		IF Alen(aOutput) == 2 .And. aOutput[2,2] == 1 .And. !Empty(aOutput[1,2]) // we have return value, and return value = 1
			?  aOutput[2,2] // return value of sp (1)
			?  aOutput[1,2] // output parameter of sp
			lReturn:=TRUE
		ELSE
			// Error handling		
		ENDIF
	
	ENDIF

	Return lReturn

Function ExecSp(cSp As String,aInput As Array, aOutput As Array, oOwner As Object, oConn:=NULL_OBJECT As AdoConnection ) As Logic Pascal 
	Local oAdoConn As AdoConnection
	Local lReturn As Logic
	
	IF Empty(oConn)
		oAdoConn:=AdoGetConnection()
	ELSE
		oAdoConn:=oConn
	ENDIF
	
	lReturn:= Execute_Sp(cSp, aInput, aOutput, oAdoConn, GetGlobal(#IDF_LogFile), oOwner)
	
	oAdoConn:=NULL_OBJECT	

	
	Return lReturn



Function Execute_Sp(cSp As String, aInput As Array, aOutput As Array, oConn AS AdoConnection, cLogFile As String,oOwner As Object) As Usual Pascal

	Local oCmd As AdoCommand
	Local oPars As AdoParameters
	//Local oPar As ADOParameter
	Local cErrMsg As String
	Local cbErr As CodeBLoc
	Local uError As Usual
	Local lReturn As Logic
	Local i As Word
	Local oErrors As AdoErrors
	Local oError As AdoError
	Local oRS As AdoRecordSet
	
	lReturn := TRUE
	
	oErrors:=oConn:Errors
	oErrors:Clear()
	
	cbErr := ErrorBlock({|oErr|_Break(oErr)})
	
	BEGIN SEQUENCE
		oCmd:= AdoCommand{}
		oCmd:ActiveConnection := oConn
		oCmd:CommandType := AdCmdStoredProc
		oCmd:CommandText := cSp
		//oCmd:CommandTimeout := 0 // NO TIMEOUT
		
		oPars := oCmd:Parameters_
		oPars:Refresh()
		
		FOR i:=1 UPTO Alen(aInput)
			oPars:[Item, aInput[i][1]]:Value := aInput[i][2]
		NEXT i
		
		//oCmd:Execute(NIL, NIL, adAsyncExecute)
		oRS:=oCmd:Execute(NIL, NIL, NIL)
		
		oError:=oErrors:[Item,1]
		
		IF oError != NULL_OBJECT
			lReturn:=FALSE
			cErrMsg:=oError:Description
			//oOwner:ShowAdoError(oError,"Adatrögzítési hiba!")
			Hiba(oOwner,cErrMsg,"SQL Végrehajtási hiba!")
		ELSE
			DO WHILE oRS != NULL_OBJECT
				oRS := oRS:NextRecordset(NIL)
			ENDDO
		ENDIF
		
		FOR i:=1 UPTO Alen(aOutput)
			 aOutput[i,2]:=oPars:[Item, aOutput[i,1]]:Value
		NEXT i
	
		IF !Empty(oPars:[Item, "@Return_Value"]:Value) // Van Return value
			Aadd(aOutput,{"@Return_Value",oPars:[Item, "@Return_Value"]:Value})
		ENDIF
		
	RECOVER USING uError
		oCmd:Cancel()
		lReturn  := FALSE

		IF IsObject(uError)
			cErrMsg := "SQL Végrehajtási hiba: " +  uError:Description + CRLF + "Tárolt eljárás: " + cSP
		ELSEIF IsString(uError)
			cErrMsg := uError + CRLF + cSP
		ENDIF
		
		Hiba(oOwner,cErrMsg,"SQL Végrehajtási hiba!")
		
	END SEQUENCE

	IF !Empty(cErrMsg)
		WriteEventLog(cLogFile,"SQL hiba", cErrMsg)	
	ENDIF
	
	ErrorBlock(cbErr)
	oCmd:=NULL_OBJECT
	
	Return lReturn








// Stored procedure on sql server side


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_Test] 
	(
		@InputPar1 varchar(12), 
		@InputPar2 varchar(50),
		@OutputPar int output 
	)
AS
begin
	SET XACT_ABORT ON
	SET NOCOUNT ON
	
	Declare @starttrancount int
	
	BEGIN TRY
	
		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	
		SELECT @starttrancount = @@TRANCOUNT
		
		IF @starttrancount = 0 
			BEGIN TRANSACTION
		-- sp block start
		
		-- stupid example (here you can manage your own stuff ie: getting last id, etc... )
		Select @OutputPar = CONVERT(int, @InputPar1) + CONVERT(int, @InputPar2)
		
		-- sp block end 
		IF @starttrancount = 0 
			COMMIT TRANSACTION
		
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED	
		Return 1
		
	END TRY
	BEGIN CATCH
		DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
		SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
		RAISERROR(@ErrMsg, @ErrSeverity, 1)
		 
		IF XACT_STATE() <> 0 AND @starttrancount = 0 
			ROLLBACK TRANSACTION
		
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED	
		Return 0    
	END CATCH 

end

[toc] | [prev] | [next] | [standalone]


#9118

Fromingenierialym@gmail.com
Date2016-03-08 13:16 -0800
Message-ID<dc91c7d7-5c67-4a98-8b9c-fbc0ecd63a02@googlegroups.com>
In reply to#9105
Jorge, yo lo hago así y funciona ok.
Le recorté lo que no hace falta, cualquier duda avisame.

*
*
		//
		aParametros := { 	ID2Sql( uID_Equipo_Padre ),;
							Caracter2Sql( cID_Owner ),;
							Caracter2Sql( oDCSLEDescripcion:TextValue ),;
							Caracter2Sql( oDCSLEUbicacion:TextValue ),;
							Caracter2Sql( cObservaciones ),;
							ID2Sql( oDCCBTipo_Equipo:Value ),;
							Caracter2Sql( oDCSLECodigo_Fabr:TextValue ),;
							ID2Sql( oDCCBUnidadCont:Value ),;
							Fecha2Sql( oDCDTPFecha_Ingreso:TextValue ),;
							Caracter2Sql( cHerencia ),;
							ID2Sql( nID_Contador_Equipo ),;
							Numerico2Sql( oDCSLEUso_Promedio:Value ),;
							ID2Sql( nUG_ID_Plano ),;
							Decimal2Sql( oDCSLECosto:TextValue ),;
							Decimal2Sql( oDCSLEPotencia:TextValue )  }

		// Ejecuta el commando
		oRS := Comando1( cProcedimiento, aParametros )
		IF oRS == NULL_OBJECT
			BREAK
		ELSE
			nID_Equipo := oRs:[ Collect, 'ID_EQUIPO' ]
		ENDIF
		oRS:Close()
*
*


////////////////////////////////////////


FUNCTION Comando1( cProcedimiento, aParametros )

	LOCAL oRS						AS AdoRecordSet
	LOCAL oAC							AS AdoCommand
	LOCAL x							AS DWORD
	LOCAL cComando, cParametros	AS STRING
	LOCAL lResultado					AS LOGIC
	LOCAL cbErrorPrevious				AS USUAL
	LOCAL nBreak
	LOCAL cComandoTrace			AS STRING
	LOCAL nPos, idTrace			AS DWORD

	Default( @aParametros, {} )
	cbErrorPrevious := ErrorBlock({ || _Break( NIL ) } )
	lResultado := TRUE
	cParametros := NULL_STRING
	idTrace := 0

	BEGIN SEQUENCE
		// Parametros
		IF IsArray( aParametros )
			FOR x = 1 TO ALen( aParametros )
				IF IsNil( aParametros[ x ] )
					cParametros := cParametros + iif( x = 1, Space( 1 ),  ', ' ) + 'NULL'
				ELSE
					cParametros := cParametros + iif( x = 1, Space( 1 ),  ', ' ) + aParametros[ x ]
				ENDIF
			NEXT x
		ELSE
			BREAK 1
		ENDIF
		// Comando
		cComando := cProcedimiento + cParametros
		//
	
		// Genero el comando
		oAC := AdoCommand{}
		oAC:ActiveConnection := oConexion
		oAC:CommandText := cComando
		oAC:CommandTimeout:= 300
		//
	
		// Ejecuta el comando y recibe el resultado
		oRS := AdoRecordSet{}
		oRS := oAC:Execute(NIL,NIL,NIL)
	RECOVER USING nBreak
		IF nBreak = 1
			cStrError := 'Los parámetros deben pasarse en un ARRAY.'
			lResultado := FALSE
		ELSEIF oAC:Error <> NULL_OBJECT
			cStrError := oAC:Error:Description
			lResultado := FALSE
		ELSEIF oRS:Error <> NULL_OBJECT
			cStrError := oRS:Error:Description
			lResultado := FALSE
		ENDIF

	END

	IF !InCollect()
		oAC := NULL_OBJECT
		CollectForced()
	ENDIF

	ErrorBlock(cbErrorPrevious)

	IF lResultado
			RETURN oRS
	ELSE
		RETURN NULL_OBJECT
	ENDIF




/////////////////////////////////////////

ALTER PROCEDURE [dbo].[sgm_Equipo_Agregar] 
*
*
*
*
*

	INSERT INTO Equipo
	(Id_Owner, Descripcion, Ubicacion, Observaciones, Id_Tipo_Equipo, Codigo_Fabr, Id_Evento, Fecha_Ingreso, Herencia, Id_equipo_hereda, Uso_Promedio, Id_Plano, Costo, Potencia)
	VALUES
	(@Id_Owner, @Descripcion, @Ubicacion, @Observaciones, @Id_Tipo_Equipo, @Codigo_Fabr, @Id_Evento, @Fecha_Ingreso, @Herencia, @Id_equipo_hereda, @Uso_Promedio, @Id_Plano, @Costo, @Potencia)
	
	SELECT @Id_Equipo = MAX(Id_Equipo)
	FROM Equipo

	UPDATE Equipo 
	SET Id_DBCad= @Id_Equipo 
	WHERE Id_Equipo= @Id_Equipo

	SELECT @Id_Equipo 'Id_Equipo', @Id_Equipo 'Id_DBCad'

/////////////////////


El martes, 8 de marzo de 2016, 10:50:07 (UTC-3), jorgeac...@dextrasistemas.com.ar escribió:
> Hello everyone 
> 
> A specific issue on vo28 - vo2ado SQL Server .... A procedure performs a series of steps and one of them is to get the last ID of a table ... ... 
> 
> From my vo app
> ( Program that invoked the sp )
>  i woluld like to get this last ID ... 
> 
> But how ?
> Some ilustrative fragment ? 
> 
> Thanks 
> Jorge Accinelli
> Buenos Aires

[toc] | [prev] | [next] | [standalone]


#9124

Fromjorgeaccinelli@dextrasistemas.com.ar
Date2016-03-09 04:19 -0800
Message-ID<c46e91e6-f43c-4afb-8919-0dc2d3cb18ad@googlegroups.com>
In reply to#9105
El martes, 8 de marzo de 2016, 10:50:07 (UTC-3), jorgeac...@dextrasistemas.com.ar escribió:
> Hello everyone 
> 
> A specific issue on vo28 - vo2ado SQL Server .... A procedure performs a series of steps and one of them is to get the last ID of a table ... ... 
> 
> From my vo app
> ( Program that invoked the sp )
>  i woluld like to get this last ID ... 
> 
> But how ?
> Some ilustrative fragment ? 
> 
> Thanks 
> Jorge Accinelli
> Buenos Aires


A lot of thanks!. This is what i was looking for 

Jorge

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web