Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Remote Update Problem
Tue, Sep 25 2007 3:16 PMPermanent Link

Stuart Kelly
Hi,

We are currently having problems with our EPOS software and data polling.

A few years ago, we copied tables, record by record, to a remote DBISAM server.  
The problem was, the remote connection often dropped and corrupted the remote table.

Currently, we copy DBISAM 3.30 product tables files (DAT, IDX) over the internet, via VPN
to a number of remote computers.
Customers have recently complained that this is too slow.

One solution I am considering is compressing the product.dat and product.idx and then
sending the compressed file.  
However, I will need to update the remote software to decompress this file at some point.

Another solution could be replication, where by the remote database is updated for every
insert/update/delete.

Ideas and opinions appreciated.
Kind regards, Stu
Tue, Sep 25 2007 5:36 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Stuart,

<< A few years ago, we copied tables, record by record, to a remote DBISAM
server.  The problem was, the remote connection often dropped and corrupted
the remote table. >>

A dropped connection should not cause corruption with a DBISAM database
server.  Was this a remote connection to a DBISAM database server or were
you connecting directly to the remote DBISAM tables ?

<< Currently, we copy DBISAM 3.30 product tables files (DAT, IDX) over the
internet, via VPN to a number of remote computers.  Customers have recently
complained that this is too slow.

One solution I am considering is compressing the product.dat and
product.idx and then sending the compressed file.   However, I will need to
update the remote software to decompress this file at some point. >>

DBISAM 4's server-side procedures would be ideal for this type of
functionality since it would allow you to implement such functionality
directly in the database server.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Sep 26 2007 11:44 AMPermanent Link

Stuart Kelly
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Stuart,

>
> A dropped connection should not cause corruption with a DBISAM database
> server.  Was this a remote connection to a DBISAM database server or were
> you connecting directly to the remote DBISAM tables ?
>
No we used a remote server, basically as follows.

* Connect to remote server
* query for table changes (local db)
* for each record
*** Find Record in remote DB
*** start transaction
*** Table->Edit
*** Copy all fields
*** Table->Post
*** end transaction
* disconnect

I think the problem was the if the broadband connection dropped, the remote table could be
locked in a transaction.


>
> DBISAM 4's server-side procedures would be ideal for this type of
> functionality since it would allow you to implement such functionality
> directly in the database server.
>

Cool, I'll look into that.  Can ElevateDB do the same?

Thanks Stu
Thu, Sep 27 2007 11:34 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Stuart,

<< I think the problem was the if the broadband connection dropped, the
remote table could be locked in a transaction. >>

Yes, but that won't cause an issue either, even if the server machine went
down in the middle of the transaction.  The only way it could cause an issue
would be if the server machine went down during, or shortly after, a
physical update to the database without a corresponding OS flush of the data
to the hard drive.  However, a transaction commit will, by default, perform
an OS flush.

<< Cool, I'll look into that.  Can ElevateDB do the same? >>

Yes, but in a different way.  EDB requires that you implement the procedure
in an external module (DLL) that is then defined via the SQL CREATE
PROCEDURE statement:

http://www.elevatesoft.com/edb1sql_create_procedure.htm

along with the EXTERNAL NAME clause.

You create the external module using the template project that is installed
in the Delphi IDE (by default) during the installation process.  You can see
this by using File/New/Other/ElevateDB/ElevateDB External Module.  Just fill
in this event handler:

procedure TEDBExternalModule1.EDBExternalModuleRoutine(
 const RoutineName: String);

using the instructions in the comments of the event handler.

Once you are done with the external module, just compile it and place it in
the same directory as the configuration file (TEDBEngine.ConfigPath) and
then it will be available to ElevateDB as a module.  You can verify this by
querying or opening this table in the Configuration database:

http://www.elevatesoft.com/edb1sql_modules_table.htm

This may seem a bit complicated at first, but it is rather easy to do and
doesn't require a re-compile of the application when the external module is
changed.  DBISAM, by default, requires that you compile the server-side
procedure into the database server.  However, you can also come up with a
different scheme yourself for loading DLLs or packages dynamically in order
to accomplish the same thing.

--
Tim Young
Elevate Software
www.elevatesoft.com


Image