Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #9105 > unrolled thread
| Started by | jorgeaccinelli@dextrasistemas.com.ar |
|---|---|
| First post | 2016-03-08 05:50 -0800 |
| Last post | 2016-03-09 04:19 -0800 |
| Articles | 6 — 4 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
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
| From | jorgeaccinelli@dextrasistemas.com.ar |
|---|---|
| Date | 2016-03-08 05:50 -0800 |
| Subject | About 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]
| From | Franz Rachbauer <office@rachbauer.org> |
|---|---|
| Date | 2016-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]
| From | jorgeaccinelli@dextrasistemas.com.ar |
|---|---|
| Date | 2016-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]
| From | kormondi@kotech.hu |
|---|---|
| Date | 2016-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]
| From | ingenierialym@gmail.com |
|---|---|
| Date | 2016-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]
| From | jorgeaccinelli@dextrasistemas.com.ar |
|---|---|
| Date | 2016-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