SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Доступ к SAP из MSSQL (TSQL)



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Connect to External system, Unix и Perl
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Sun Oct 07, 2007 12:21 pm    Post subject: Доступ к SAP из MSSQL (TSQL) Reply with quote

TSQL функция для работы с RFC функциями SAP для MSSQL2005

Code:
ALTER PROCEDURE [dbo].[R3PersonalList]

@StartDate varchar(20) = '',
@EndDate varchar(20) = '',
@_PERNR varchar(20) = ''


AS

-- Метка в саплагоне должна быть ******** !!!   ip ********
SET NOCOUNT ON


--Declare  vDate int
--select sDate = Convert(varchar(200),GetDate(),112)

  DECLARE @object int
  DECLARE @hr int
  DECLARE @src varchar(255), @desc varchar(255)
  EXEC @hr = sp_OACreate 'SAP.Functions', @object OUT

  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
        GOTO SPEXIT
  END
  else
  begin
    declare @hprop int
    EXEC @hr = sp_OAGetProperty @object,'Connection', @hprop OUT
    EXEC @hr = sp_OASetProperty @hprop, 'Destination', '*****'
    EXEC @hr = sp_OASetProperty @hprop, 'Client', '010'
    EXEC @hr = sp_OASetProperty @hprop, 'Language', '8'
    EXEC @hr = sp_OASetProperty @hprop, 'User', '*****'
    EXEC @hr = sp_OASetProperty @hprop, 'Password', '*****'
 
    declare @ret int -- для проверки рез. Logon
    EXEC @hr = sp_OAMethod @hprop, 'Logon', @ret OUT , 1, True
    IF @hr <> 0
    BEGIN
      EXEC sp_OAGetErrorInfo @hprop
      GOTO SPEXIT
    END
   
    if @ret <> 1
     BEGIN
      Raiserror('Logon failed', 16, 1)
      EXEC @hr = sp_OADestroy @object
      IF @hr <> 0
      BEGIN
      EXEC sp_OAGetErrorInfo @object
      GOTO SPEXIT
     END
     END
 
    declare @Funct int
    EXEC @hr = sp_OAMethod @object, 'Add', @Funct OUT, 'Z_HR_LIST_WERKS'
 
 
    declare @Collection int
    EXEC @hr = sp_OAGetProperty @Funct, 'Exports',  @Collection OUT

    declare @Item int
    declare @RowCount int
    declare @Name varchar(100)
    declare @i int
    set @i = 1

    EXEC @hr = sp_OAGetProperty @Collection, 'Count', @RowCount OUT


  --declare @s varchar(20)
--  EXEC @hr = sp_OAGetProperty @Item, 'name', @S OUT
--  Select 's' = @s
   

    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 1
    EXEC @hr = sp_OASetProperty @Item, 'Value', @StartDate
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 2
    EXEC @hr = sp_OASetProperty @Item, 'Value', @EndDate
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 3
    EXEC @hr = sp_OASetProperty @Item, 'Value', @_PERNR
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 4
    EXEC @hr = sp_OASetProperty @Item, 'Value', 'BL04'



    declare @Exports int
    EXEC @hr = sp_OAMethod @Funct, 'Call'

    declare @Tables int
    EXEC @hr = sp_OAGetProperty @Funct, 'Tables', @Tables OUT

    declare @Items int
    EXEC @hr = sp_OAGetProperty @Tables, 'Item', @Items OUT, 1

    EXEC @hr = sp_OAGetProperty @Items, 'RowCount', @RowCount OUT


--


    declare @PERNR int
    declare @ENAME varchar(100)
    declare @ORGEH int
    declare @ZSTEXT_O varchar(100)
    declare @PLANS int
    declare @ZSTEXT_S varchar(100)
    declare @ENDDA datetime
    declare @TRFST varchar(2)
    declare @STELL int
    declare @STELL_NAME varchar(50)

    create table  #tValues
     (
      ID int IDENTITY(1, 1) NOT NULL,
      PERNR int,
      ENAME varchar(100),
      ORGEH int,
      ZSTEXT_O varchar(100),
      PLANS int,
      ZSTEXT_S varchar(100),
      ENDDA datetime,
      TRFST varchar(10),
      STELL int,
      STELL_NAME varchar(100)
      )


    set @i = 1
    while (@i <= @RowCount)
    begin
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @PERNR OUT, @i, 'PERNR'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ENAME OUT, @i, 'ENAME'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ORGEH OUT, @i, 'ORGEH'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ZSTEXT_O OUT, @i, 'ZSTEXT_O'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @PLANS OUT, @i, 'PLANS'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ZSTEXT_S OUT, @i, 'ZSTEXT_S'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ENDDA OUT, @i, 'ENDDA'

      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @TRFST OUT, @i, 'TRFST'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @STELL OUT, @i, 'STELL'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @STELL_NAME OUT, @i, 'STELL_NAME'


if @PERNR <>0 
 BEGIN

      insert into #tValues(PERNR, ENAME, ORGEH,ZSTEXT_O,PLANS,ZSTEXT_S,ENDDA,TRFST,STELL,STELL_NAME)
        --Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@PLANS,@ZSTEXT_S,@ENDDA,@TRFST,@STELL,@STELL_NAME)
          Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@STELL,@STELL_NAME,@ENDDA,@TRFST,@STELL,@STELL_NAME)
 END
      set @i = @i + 1

    end;



--delete  @tValues where [email protected] < (Select Max(t2.ENDDA) from @tValues  t2 where t2.PERNR = t.PERNR)
--delete  from #tValues  where ENDDA < (Select Max(t2.ENDDA) from #tValues t2   where t2.PERNR =PERNR)

IF @_PERNR <>''
BEGIN
--Update #tValues Set ENDDA = NULL  where YEAR(ENDDA) = 9999
SELECT   *  FROM     #tValues  Order by ENDDA desc --desc --where ENDDA = NULL Order by desc
 EXEC @hr = sp_OADestroy @object
  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object
    GOTO SPEXIT
  END
 GOTO SPEXIT
END

--==================================================================================================
--[Синхронизация с таблицей Personal]

--   Select * from @tValues where PERNR = 8818
-- Select count(*) from @tValues


Select @I = 0
Declare @iCount int
Select @iCount = 0
Declare @inPersonal int
select @inPersonal = 0

Select @iCount = (Select count(*) from #tValues)

WHILE @I<=@iCount
BEGIN

    Select @PERNR     =(Select isNull(PERNR,0) from #tValues where ID = @i )
    Select @ENAME     =(Select ENAME from #tValues where ID = @i )
    Select @ORGEH     =(Select ORGEH from #tValues where ID = @i )
    Select @ZSTEXT_O  =(Select ZSTEXT_O  from #tValues where ID = @i )
    Select @PLANS     =(Select PLANS from #tValues where ID = @i )
    Select @ZSTEXT_S  =(Select ZSTEXT_S from #tValues where ID = @i )
    Select @ENDDA     =(Select ENDDA from #tValues where ID = @i )

    Select @TRFST          =(Select TRFST from #tValues where ID = @i )
    Select @STELL          =(Select STELL from #tValues where ID = @i )
    Select @STELL_NAME     =(Select STELL_NAME from #tValues where ID = @i )


if Year(@ENDDA) = 9999 select @ENDDA = null

IF (Select Count(isNull(PERNR,0)) from Personal where PERNR = @PERNR) = 0
   BEGIN
      insert into Personal(PERNR, ENAME, ORGEH,ZSTEXT_O,PLANS,ZSTEXT_S,ENDDA,TRFST,STELL,STELL_NAME)
        Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@PLANS,@ZSTEXT_S,@ENDDA,@TRFST,@STELL,@STELL_NAME)
   END
   ELSE
   BEGIN
        UPDATE Personal
        SET
           ENAME    =     @ENAME,
           ORGEH    =     @ORGEH,
           ZSTEXT_O =     @ZSTEXT_O,
           PLANS    =     @PLANS,
           ZSTEXT_S =     @ZSTEXT_S,
           ENDDA    =     @ENDDA,
      TRFST    =     @TRFST,
           STELL    =     @STELL,
           STELL_NAME =   @STELL_NAME
        WHERE PERNR = @PERNR
   END

Select @I = @I+1
END

--==================================================================================================



  EXEC @hr = sp_OADestroy @object
  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object
   GOTO SPEXIT
  END
end

SPEXIT:
exec sp_OADestroy @hprop
exec sp_OADestroy @Funct
exec sp_OADestroy @Collection
exec sp_OADestroy @Items
exec sp_OADestroy @Funct
exec sp_OADestroy @Tables
exec sp_OADestroy @RowCount
exec sp_OADestroy @Item

exec sp_OADestroy @Src
exec sp_OADestroy @desc
exec sp_OADestroy @I
exec sp_OADestroy @Item
exec sp_OADestroy @object
exec sp_OADestroy @ret

delete from personal where PERNR is null or PERNR = 0

SET NOCOUNT OFF
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Connect to External system, Unix и Perl All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.