VFP OLEDB 9 function is not implemented error when table has insert, update & delete triggers

Question

I am getting the 'function is not implemented' error when attempting to do an insert into a FoxPro table that has an insert trigger related to it within the table setup GUI. Using vs2012, I created a table adapter within a dataset. I added the update statement shown below. The code within my C# project that performs the insert/update is also listed below. I can get a successful insert/update if I remove the associated triggers from the FoxPro table. With the triggers, inserts and updates throw the 'function is not implemented' error. Select's work fine.

Please help

The utacc FoxPro table has triggers setup to insert, update and delete related SQL Server tables. Insert trigger at the bottom of post.

VS 2012 table adapter insert command

INSERT INTO utacc
                         (fcmodid, fcuname, fccompid, fcacclvl, fcscope)
VALUES        (?, ?, ?, ?, ?)

C# code to perform insert/update via table adapter

using (var ta = new DataSet1TableAdapters.utaccTableAdapter())
{
    foreach (var u in lUsers.GetList().Where(x => x.selected))
    {
        foreach (var screen in lScreen.GetList().Where(x => x.selected))
        {
            if (ta.GetData()
                .Any(x => x.fccompid == _fccompid 
                    && x.fcmodid.Trim() == screen.fcmodid.Trim() 
                    && x.fcuname.Trim() == u.fcuname.Trim()))
                ta.Update(perm, screen.fcmodid, _fccompid, u.fcuname);
            else
                ta.Insert(screen.fcmodid, u.fcuname, _fccompid, perm, "P");
        }
    }
}

These triggers are inserts to SQL server tables.
Here is the FoxPro trigger

PROCEDURE M2MTrig_UTACCInsert 
LOCAL Cntr, ErrCode, ConStr, ConnName, ConnHandle, RV, ErrHndlr 
SET DATA TO UTIL 
ErrHndlr = ON("ERROR") 
IF EMPTY(UTACC.FCUNAME)AND EMPTY(UTACC.FCMODID)AND EMPTY(UTACC.FCCOMPID ) THEN 
RETURN .T. 
ENDIF 
ErrCode = 0 
ON ERROR ErrCode = M2MTrig_ERR(ERROR(),MESSAGE(),ErrHndlr) 
FOR Cntr = 1 TO 99 
ConnName = "SystemDBConn" + ALLTRIM(STR(Cntr)) 
TRY 
ConStr = DBGETPROP(ConnName,"CONNECTION","ConnectString") 
CATCH TO oEx
ErrCode = oEx.ErrorNo
ENDTRY
IF ErrCode = 1562 THEN 
EXIT 
ENDIF 
IF ErrCode <> 0 THEN 
ON ERROR &ErrHndlr. 
RETURN .F. 
ENDIF 
ConnHandle = SQLCONNECT(ConnName) 
IF ErrCode <> 0 THEN 
RETURN .F. 
ENDIF 
IF ConnHandle < 1 THEN 
M2MTrig_ERR(-1, "Unable to connect using " + ConnName, ErrHndlr) 
RETURN .F. 
ENDIF 
IF ErrCode <> 0 THEN 
SQLDISCONNECT(ConnHandle) 
RETURN .F. 
ENDIF 
Select UTACC 
RV = M2MTrig_ExecSQL("EXEC UpdUTACC " + XC(FCACCLVL)+ ", "+ XC(FCCOMPID)+ ", "+ XC(FCMODID)+ ", "+ XC(FCSCOPE)+ ", "+ XC(FCUNAME), .T., ConnHandle, ErrHndlr) 
IF ErrCode <> 0 THEN 
SQLDISCONNECT(ConnHandle) 
RETURN .F. 
ENDIF 
SQLDISCONNECT(ConnHandle) 
IF ErrCode <> 0 THEN 
RETURN .F. 
ENDIF 
IF RV < 0 THEN 
RETURN .F. 
ENDIF 
IF RV < 1 THEN 
M2MTrig_ERR(-1, "Could not update System database", ErrHndlr) 
RETURN .F. 
ENDIF 
NEXT Cntr 
ON ERROR &ErrHndlr. 
RETURN .T.

DarthGizka · Accepted Answer · 2014-10-21T18:20:40.727

The Fox engine in the OLEDB provider supports only a subset of Fox commands and functions. MSDN has lists of supported language elements here, and unsupported ones here.

The remote SQL functions SQLCONNECT() etc. - are on the list of prohibited items. The error handler might use forbidden stuff as well, like MessageBox(), WAIT WINDOW, SET MESSAGE or ?.

Apart from that the code seems to have lots more problems. Starting with the fact that it ignores all guidelines for safe and clean coding, like qualifying rvalue access to memory variables with m. or m-> to avoid nasty surprises with field names in new tables, or the rules regarding what (not) to do in trigger code.

Better scrap the code completely and find a simpler, cleaner solution.

P.S.: one way of using the existing infrastructure would be to control the Fox side not via OLEDB but via COM, i.e. building a little COM server exe. That could be as simple as sending commands to Fox via DoCmd(), or as complicated as a full blown COM object. In VFP9 it is even possible to return arrays and row sets from a COM method, so the interface could be as rich as you like.

P.P.S.: if the data resides in an SQL Server database, then why are you talking to Fox?

you are talking about the FoxPro having problems, right? My second option is to create a FoxPro COM object that will perform the CRUD. Do you know if I will have the same issues with a COM object? — midohioboarder, Oct 21 '14 at 18:24
old ERP system built on FoxPro. the system is slowly being migrate over to SQL server. I am 3rd party working creating a bolt-on app. I will be going down the road of a COM object then. Thanks again. — midohioboarder, Oct 21 '14 at 18:32
There are some minor restrictions for COM servers that run without interface but in an .EXE server you have pretty much the full functionality of Fox at your disposal. — DarthGizka, Oct 21 '14 at 18:57

VFP OLEDB 9 function is not implemented error when table has insert, update & delete triggers

1 Answers1