Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Querying a dataset over a WAN

We are running D1 developer edition with paradox databases. We want to query our paradox (5.0) databases over a WAN pretty speedily (client-server?). Do we need ODBC/SQL links, C?s edition, 32 -bit Delphi? In other wors what are our options for achieving our goal? Thanks. Tom. [BTW, how do I get extra points so I can award this question more?]

After calls to Borland tech support we weren't provided with a satisfactory answer.
0
boardtc
Asked:
boardtc
1 Solution
 
anilmsCommented:
1. The first thing you have to remember is that Paradox and Foxpro DBFs are not true C/S databases, ie., when you fire a query, the whole file comes to your computer where it is processed. Therefore you will have to simulate a server.

2. While it is very easy to do it using Delphi 3.0 with its support for ActiveX DLLs, the same can still be achieved using D1. We had a similar need as yours using DBF tables where the records used to run to a million sometimes. I will explain below how we managed to get satisfactory improvement in speed.

C - the central server which stores all the DBF files.
U - a user node some 40 kms away - a dial up link.
QDB - a query DBF on C - contains only two fields - QUERY c 150 and RESULT c 1. It is shared and can be accessed from U.
QDBP - a program written in delphi running on C. It has a timer and continously scans QDB for records in which RESULT is blank (which means a new query record has been added). It puts 'P' (Processing) in the RESULT field, picks up the QUERY string, processes it and creates a new DBF called QRESULT. It puts a 'O' in the RESULT field to indicate that the processing is over. The QUERY can be any valid SQL statement allowed for DBF files. QDBP also checks for RESULT = 'D' and deletes these records.

UPD - An user program in Delphi running on U which has to query C and the result could be a combination of two or more DBFs on C. The UDP constructs a query string and adds a QUERY record in QDB on C. Then it starts checking the RESULT for that QUERY in the QDB file itself (using a timer, of course). As soon as the RESULT turns to O, it picks up the QRESULT data and directly displays it in the DBGrid or whatever display component is needed. It also sets RESULT on QDB to 'D' so that the QFBP program can delete this.

UFP - Similar program on user node - running in Foxpro 2.5. Here, as we do not have a timer, we keep looping continously till the RESULT field turns to 'O' in the QDB DBF.

We have found that the following speed increases :
1. Only 1 DBF involved in query : 2-3 times
2. 2 or more DBFs : 7-8 times
The increase in speed is basically because the query is processed on the server and only the result is picked up from the QRESULT DBF.
0
 
boardtcAuthor Commented:
Thanks for the response. There has to be a better system here, I would like more comments on this but grading an answer stops that right (the above did not deserve an F)?

So does sql links automate the above? Is it advisable to use them with D1 - tech support said we had to convert to 32 bit Delphi.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now