Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: FoxPro Triggers call FoxPro Stored Proc calls SQL Server Stored Procedure

    I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

    Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

    I will do a 1 time DTS from FP into SQL Server tables.

    I then create INSERT and UPDATE triggers within FoxPro.

    These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

    In the end - the tables are local to both apps.

    If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

    Here's the FoxPro and SQL Server code for reference for the Record Insert:

    FOXPRO employee.dbf InsertTrigger:
    employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

    FOXPRO corresponding Stored Procedure:
    FUNCTION EMPLOYEE_INSERT_TRIGGER
    PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

    nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

    IF nhandle<0
    m.errclose=.f.
    IF !USED("errorlog")
    USE tisdata!errorlog IN SELECT(1)
    m.errclose=.t.
    ENDIF

    SELECT errorlog
    INSERT INTO errorlog (date, time, program,source,user) ;
    values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

    IF m.errclose
    USE IN errorlog
    ENDIF
    RETURN

    ENDIF
    nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
    nsucc=SQLEXEC(nhandle,nquery)

    SQLDISCONNECT(nhandle)

    IF nSucc<0
    m.errclose=.f.
    IF !USED("errorlog")
    USE tisdata!errorlog IN SELECT(1)
    m.errclose=.t.
    ENDIF

    SELECT errorlog
    INSERT INTO errorlog (date, time, program,source,user) ;
    values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

    IF m.errclose
    USE IN errorlog
    ENDIF
    ENDIF

    RETURN

    SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
    CREATE procedure ewo_sp_insertNewEmployee (
    @WEPK int,
    @WEFNAME char(20),
    @WELNAME char(20),
    @WEEMAIL char(50),
    @WEUSERID char(15),
    @WEPHONE char(25),
    @RETCODE int OUTPUT
    )

    AS

    insert into WO_EMP (
    WE_PK,
    WE_FNAME,
    WE_LNAME,
    WE_EMAIL,
    WE_USERID,
    WE_PHONE
    )

    VALUES (
    @WEPK,
    @WEFNAME,
    @WELNAME,
    @WEEMAIL,
    @WEUSERID,
    @WEPHONE
    )


    IF @@ERROR <> 0
    BEGIN
    SET @RETCODE=@@ERROR
    END
    ELSE
    BEGIN
    -- SUCCESS!!
    SET @RETCODE=0
    END

    return @RETCODE
    GO

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    so what is the question?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    No specific question - just putting up my findings. I figured that could be helpful to some, just like other discussions here that are not always in response to a question have been helpful to me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •