ODBC vs Clarion direct access to TPS files / Architecture advice

[regilobo]

Hi, I'm hoping to get advice from some sage Clarion programmers. I'm not a Clarion developer, so I appreciate your insight and opinion here...looking to use the right tool for the job.

A little background:

App "A" is a commercial Clarion app and App "B" is a web app on a linux box and has data access methods exposed via HTTP. For simplicity, let's say both applications are for managing a contact list. We want to synchronize the data.

The db is TopSpeed (.TPS) and we have been ensured by the Clarion developers that we can access the data either via a (new) custom Clarion app or via the ODBC driver (i.e. no encryption or other impediments). However, we do not have the source to the application itself, just the raw data.

The basic architecture we are looking at is to create a proxy app that monitors changes to the TPS files in App "A". The proxy app then notifies App "B" via HTTP calls containing the modified and new data.
App "B" will make calls to the proxy app remotely (likely via HTTP) when its data changes and the proxy app will have to update the TPS files directly.

It will need to be able to:
[ul][li]Effectively monitor data changes in TPS files[/li]
[ul circle]
[li]New records need to be identified[/li]
[li]Changes to existing records need to be identified[/li]
[/ul]
[li]Transmit data as an HTTP client at regular intervals[/li]
[li]Receive data as an HTTP server on-demand[/li]
[li]Write new data and update existing data in TPS files based on the received HTTP data[/li]
[/ul]

The assumption I have is that Clarion is obviously going to be well suited for the data access portion of the app, but less suited to the remote aspects. I see there are 3rd party HTTP libs and of course we could muck around with the w32api. Is Clarion suited for writing this sort of headless application?
Is the ODBC driver robust enough for inserts and updates to where we could depend on it if we wrote the proxy app in another language?



Thanks for your help!

- Will

 

[regilobo]

Ah, I forgot to mention:

The commercial app is written in Clarion 6 and that is also the version of the IDE we own and would be using if we continue down that path.

 

[ShankarJ]

Hi Will,

From what I can understand, App "B" is maintaining a parallel copy of certain tables of App "A" in maybe a different database type for its own usage. Is that really necessary as you can access the TPS file from App "B" using the SoftVelocity ODBC driver available from

http://shop.softvelocity.net/main.sc?

Since Auditing changes to databases could be very time-intensive depending on the number of rows in each table being synched, the best options would be ::

1. Ask developers of App "A" whether they can add an auditing feature to their application (FullRecord from

http://www.huenuleufu.com.ar/FullRecord_e.html

- Cost $149/- could add that to their application in less than an hour's work). With that you need to scan the Audit File only for database changes which can be a scan for new rows only based on the last Audit ID processed.

2. Access the TPS files directly using the ODBC driver from App "B" and do away with the proxy application.

Regards

 

[regilobo]

Hi,

Thanks for your reply.

I'll look into FullRecord...the time involved to audit and do it correctly outside of the application is a bit daunting.

App "B" is on a linux box, and the SoftVelocity ODBC driver is Windows only, so a direct approach won't work.

Any comments as to the effectiveness of using ODBC for Write operations on the TPS files?

 

[ShankarJ]

Hi!

In all honesty, the TPS ODBC driver leaves a lot to be desired but it does manage the simple SELECT, INSERT, UPDATE & DELETE statements quite well. It comes in two installation modes - Read Only & Read/Write. You need to install the Read/Write driver to be able to do update statements.

Regards

 

[langt]

i have a similar situation, a supplier is tracking purchase orders from from their place with the following table fileds :

ControlNumber;trackNumber;Desc;Requestedby;ProjectNumber;DateReceived;
PucharchaseorderReceived;PuchaseOrderExternal;Warehousestatus;
e.t.c

i want to replicate their db and compare with the purchase order numbers at the customer side everyday,

so i am thinking replicate supplier db at customer then design an interface to compare tables

can you advice

 

[ShankarJ]

Hi!

Have you asked the Supplier if he has some SOAP (Web) service to return the status of the Purchase Order? To replicate the Supplier's DB, will he allow you to replicate other customer's orders as well?

Regards

 

[langt]

Thanx shankarj, The supplier has no web service to return the status of orders and the problem is at time he dosent even receive orders from the client side and it is only discovered at the time when the end user is asking for the product, i want to replicate only information pertaining to one client not other clients,

i want to replicate it and compare with what is on the customer side so that if the customer has an order that is not reflected on the supplier side then it means its not received and we send notification on that, any ideas welcome