How can I log FoxPro errors to a text file?

Question

TL;DR How can I issue commands to reliably log FoxPro errors to a plain text file when running INSERTs and APPEND FROM commands through ODBC/OLE DB?

I am attempting to write some massaged data back into a FoxPro database. The actual connection to the .dbf's are done by a service written by a colleague - I'm assuming it uses ODBC or OLE DB. I only supply the path to the DBF and the query strings to run. However I am running into some problems with FoxPro, and unfortunately all my experience lies in SQL Server and friends - not FoxPro.

My problem is that simple statements like the INSERT below work fine in Visual Fp, but fail going through the service. I don't get much useful error information back other than "An error occured".

INSERT INTO "ADDRESSES" (addressid, firstname, lastname) ;
VALUES (55, 'Test', 'Testerson')

To make life easier for myself I have tried to issue more commands to make FoxPro log all error info to a text file. Again, this works in Visual FoxPro - but fails silently and does not output a file when run through the service. I've ensured the directory permissions are correct:

LOCAL lcOldOnError
lcOldOnError = ON("ERROR") && save old error handler

SET PROCEDURE TO [D:\foxpro\errhandler.prg]
ON ERROR DO ErrHandler WITH ERROR(), MESSAGE() 

&& This insert causes a duplicate primary key error: 
INSERT INTO "address book!addresses" (addressid, firstname, lastname) ;
VALUES (1, 'MyName', 'IsError')

ON ERROR &lcOldOnError && restore old error handler

The external PRG errorhandler.prg looks like this. It works inside Visual FoxPro:

PROCEDURE ErrHandler
    PARAMETERS gnError, gcMsg
    LOCAL aErrInfo[1]
    AERROR(aErrInfo)

    cErrMsg = LTRIM(STR(aErrInfo[1])) + CHR(13) + CHR(10) + aErrInfo[2] + CHR(13) + CHR(10) + aErrInfo[3] + CHR(13) + CHR(10)
    lnFileHandle = FCREATE ([D:\foxpro\errorlog.txt])
    FPUTS(lnFileHandle, cErrMsg)
    FCLOSE(lnFileHandle)
ENDPROC

Any hints or tips would be appreciated, thanks.

@DRapp, here is my C# code. It is very site specific I am afraid, so don't know if it helps anything:

static void Main(string[] args)
{
    // Test - not production code!
    var errormessage = string.Empty;
    DataSvc dataService = new DataSvc();
    dataService.AppDataPath = @"D:\foxpro\test\DATA\addresses.dbf";

    const string commandText = "INSERT INTO [address book!addresses] (addressid, firstname, lastname) VALUES (90, [Iam], [Error])";
    var returnValue = dataService.ExecuteCommands(commandText, ref errormessage);

    Console.WriteLine("\nResult:{0}\nErrorMsg: {1}", returnValue, errormessage);
    Console.ReadLine();
}

I only get errors of the kind "An error occured" from DataSvc, so that's why I would like to decorate the INSERT statements with some more commands for rerouting error info to a text file.

Answer 1

You mention a service and OleDb/ODBC. What language are YOU connecting with.. C#.net, VB.net, something else?

However, noticing one thing... if you are doing an insert to the Address Table, and the column "AddressID" is that of an auto-increment column, you CAN NOT provide the ID to be inserted, that is completely under VFP's control, and is possibly erroring-out on you due to that, or the ID already exists.

You may instead need an UPDATE statement, such as

UPDATE ADDRESSES set firstname='Test', lastname = 'Testerson'
   where addressid = 55

By looking at your answer and added code, it MAY BE as simple as your device connection. TYPICALLY for DBF stuff, the connection is to the PATH where the DBF files are found, then once connected, you can do anything to the dbfs within that path. So I would start by changing

 dataService.AppDataPath = @"D:\foxpro\test\DATA\addresses.dbf";

to

 dataService.AppDataPath = @"D:\foxpro\test\DATA\";

Then, your command should not need explicit database.dbc reference. When it is opened, it should automatically open the corresponding database container in case any such triggers and such may be attached.

const string commandText = "INSERT INTO [address book!addresses] (addressid, firstname, lastname) VALUES (90, [Iam], [Error])";

to

const string commandText = "INSERT INTO [addresses] (addressid, firstname, lastname) VALUES (90, [Iam], [Error])";

So, the way you have it is like you were trying to do the following

insert into D:\foxpro\test\DATA\addresses.dbf\address book!addresses

Answer 2

You could try building a set of VFP commands and using EXECSCRIPT maybe.

So in C#:

var myCommand = @"try" + Environment.NewLine;
myCommand    += @"  open database mydata" + Environment.NewLine;
myCommand    += @"  update addresses set firstname='test' where addressid = 55' + Environment.NewLine;
myCommand    += @"catch to loException" + Environment.NewLine;
myCommand    += @"  set textmerge on noshow" + Environment.NewLine;
myCommand    += @"  set textmerge to c:\temp\vfp.txt" + Environment.NewLine;
myCommand    += @"  \<<loException.Message>>" + Environment.NewLine;
myCommand    += @"  set textmerge off" + Environment.NewLine;
myCommand    += @"  set textmerge to" + Environment.NewLine;
myCommand    += @"finally" + Environment.NewLine;
myCommand    += @"  close all" + Environment.NewLine;
myCommand    += @"endtry" + Environment.NewLine;

You may need to do something with escaping backslashes there. Then execute in C# by setting an OleDB Command to myCommand, and executing with ExectuteNonQuery().

Having said all that, when executing in C#, if you wrap in a try ... catch, and catch a type OleDbException, does the InnerException of that not detail what the problem was at the VFP side?