Checking for Additions to a .dbf File

[jhchrysler]

All,

I am wanting to build an interface of sorts for a phone messaging application. The app writes call entries to a .dbf file that I would like to monitor for additions. Once an addition is made to the table, I would like to insert a new record in to a SQL table and change a status field in .dbf table record to establish that that the new record in SQL was written.

My original thought was to use a Linked Server via SQL to the .dbf file. I did manage to get it working somewhat but not without issues. First is that the queries are VERY slow, second is that updating the status field in the .dbf file is problematic. I can update the .dbf file through an ODBC Linked Server connection but it generates an error which I believe is being caused because the .dbf file does not have a unique index.

I am hoping some of you may be able to offer me some other suggestions on how this might be accomplished. One idea I have is that maybe something like an automation server written in foxpro would be a more wise choice, problem there would be when to query the .dbf file for additions.

Any suggestions would be greatly appreciated!

 

[craigsboyd]

By creating a Visual FoxPro Database you will be able to create rules and/or insert triggers for the table. Then create a view in the database for the SQL Server table. When an insert happens for the VFP table this will fire the insert trigger which can simply fire a stored procedure in the VFP database that will add a record to the SQL table (in this case it is represented by a view). The drawback of the trigger approach is that you cannot update the status field of the row that fired the trigger, which is what you want to do.

Another idea is to use a Rule, this would simplify the task of updating the status field as rules may change the current row.

Another idea is to use a stored procedure as the default value for the status field. The stored procedure would contain code to add the record to the remote view (your SQL Server table) as well as return a value of .T. or .F. (or whatever you desire as the status) depending on the successful insertion into the SQL Server table.

Another idea is to use one of the preceding ideas in concert with a sproc on the SQL Server side. This has the added benefit of negating the need for a remote view in the local database. But ups the complexity somewhat.

In any event, you can use the database events/features that are available in Visual FoxPro to easily accomplish your goal and not have to resort to the complexities that you have been trying so far. As there are a number of ways to approach this, you'll need to pick the one that best suits your needs.

SweetPotato Software Website
My Blog

 

[jhchrysler]

Craig,

Great plan if I was building a Foxpro database from scratch. Problem is the .dbf files are from an existing OTS application built on Clipper using a flat file structure that I have no programming control over.

Is it possible to communicate with these files through a Foxpro or similar "engine" that would allow me to create views, triggers and the like on the existing tables? Forgive my ignorance as I am from the MS SQL camp and have never really dabbled in VFP and the like.

Thanks!

 

[Mike Lewis]

jhchrysler,

You didn't mention which back end database you are using.

If it is Microsoft SQL Server, would it be possible to use a SQL Server Agent task to periodically check for new data? I'm not sure if the task can run an external program, but if it can, that program could check the DBF, and if new data is present, it could send it to the server via SQL pass-through. The program in question could be written in any language, provided it can read a DBF and communicate with the server via OLE DB or ODBC.

Or, perhaps the Agent task could do the update itself, via DTS.

An alternative approach would be to write a progam in Visual FoxPro that would periodicially check for new data in the DBF, and fire off the update to the server when new data is found. The program would have to be running continuously, using a timer to run the check at a pre-defined interval.

If your back end is not Microsoft SQL Server, that second approach would still work.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Lewis]

jhchrysler,

Another thought .... When you did the original linked query, were you using a VFP ODBC driver or the specific VFP OLE DB provider (as opposed to the OLE DB provider for ODBC)?

The reason I ask is that a client has just come across another problem with a linked query, and we solved it by switching from the VFP ODBC driver to the VFP OLE DB provider.

Might not be relevant to your case, but worth considering.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jhchrysler]

Mike,

I've tried both and found that I have different problems with them. The OLEDB Provider for ODBC will allow updates, inserts, etc, but does so with an error which I believe is due to the fact that there are no unique indexes in the .dbf table. They are both extremely slow and I can only query the OLEDB provider for ODBC using OPENQUERY statements.

If there is no way to create a .dbc from the file and establish triggers through the VFP engine then I may have to do just what you suggested. Write some code in VFP that would fire at certain time intervals.

I am really hoping I can find a data engine that will allow me create triggers, stored procedures, etc, on the .dff side. Any knowledge of this type of product?

 

[Mike Lewis]

I am really hoping I can find a data engine that will allow me create triggers, stored procedures, etc, on the .dff side. Any knowledge of this type of product?

I don't know of any such solution, given that you cannot place the existing DBFs in a DBC. I'm inclined to think that the best solution would be write a program that does the updates based on the firing of a timer.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jhchrysler]

From my reading, looks like triggers in Foxpro are handled programmatically and are more like a function, unlike MS SQL where they are handled through the data engine. Can someone verify this for me?

If this is the case and I can't add the existing .dbf's to a .dbc then even programming the triggers in Foxpro is not an option. The triggers in Foxpro apparently require a .dbc...

So that puts me back at square one and would have to program a function based on a timer as you have suggested. I am concerned that I would have the same problems using OLEDB/ODBC connections to the .dbf files as I am having in MS SQL using these providers (querting slow, updates troublesome without a unique index)...Any thoughts here?

 

[alvechurchdata]

From my reading, looks like triggers in Foxpro are handled programmatically

Not quite sure what you mean by "programatically". Fox triggers are written in the Fox programming language but are held in the dbc file of the database rather than in the program files of the application. They run whenever a record is inserted, updated, or deleted and don't have to be called from a user program.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[TamarGranor]

One of the nice things in VFP is that you can mix database-contained tables with free tables. So you could put a new table into a database and set up your triggers, while leaving your existing tables out.

Tamar

 

[jhchrysler]

So if I understand you correctly, the triggers added to a .dbc will fire without an executable? I am curious how this is possible without a data engine! I am still unclear as to whether or not I can add an existing .dbf to a .dbc that will allow me to create a trigger on it. This would solve my problem if possible.

 

[Mike Lewis]

jhchrysler,

I am still unclear as to whether or not I can add an existing .dbf to a .dbc that will allow me to create a trigger on it

Unfortunately not. At least, not if you want the original Clipper application to continue to be able to access the DBFs. Although you can easily place a DBF in a DBC, and then add triggers to it, that will make the DBF unreadable by earlier versions of xBase.

looks like triggers in Foxpro are handled programmatically and are more like a function, unlike MS SQL where they are handled through the data engine.

No, they are not all that different. If you are updating the DBC via ODBC or OLE DB, it is the ODBC driver or OLE DB provider that is actually executing the trigger code. The way in which the trigger is stored is very similar to SQL Server.

There are some differences, for example in VFP a trigger is an expression rather than a procedure; in practice, the expression is simply a function call. Also, in VFP you can only have one trigger per update, insert or delete (per table), although that trigger expression can call as many functions as it likes. But the overall concept is similar.

would have to program a function based on a timer as you have suggested. I am concerned that I would have the same problems using OLEDB/ODBC connections to the .dbf files as I am having in MS SQL using these providers

No. If you wrote the program (the one with the timer) in VFP, it would access the VFP tables directly, not via ODBC or OLE DB. It's true that it would do the SQL Server inserts via ODBC, but that's much more reliable, and should give no cause for concern (many of us here have written apps that do that every day).

In this scenario, there would be no need for a trigger and no compatibility problems with the Clipper app. You would simply need a mechanism for finding the recently added records (in the DBF), then just execute the code to update the server tables and log the update in the status field in the DBF log file.

I hope this makes sense.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)