how to copy live paradox tables

#1

Hello, I am very new to paradox databases but have some familiarity with
Oracle and MS SQL rdbms concepts. I would like to learn how to safely copy
paradox data files while the database is operatonal. Is this possible?
Im used to some sort of admin tool which hides the complexity involved in
preserving a valid database state. I have no idea if such a tool exists
for paradox databases. To add to the confusion, I will not be allowed to
touch the production tables myself. I will instead, be prviding a safe solution
to the administrator so that he can provide me with consistant refreshes
of the production tables. We want to do this nightly.

Our first attempt was to simply copy the data directory with Windows OS commands.
He delivered a directory of .DB, .TV, .PX files. I was able to extract
data from the .DB tables but have no idea if the data transaction integrity
is preserved. In fact, on one table there is a violation of the primary
key on the very last record. I suspect the copy is corrupt because of the
way it was copied. But honestly i have no idea. Can anyone direct me to
a resource where I can read about basic administration for paradox databases?
Specifically how to safely copy/mirror/replicate source systems without
affecting daily operations of end users.

#2

Quote:

I would like to learn how to safely copy paradox data files while the
database is operatonal. Is this possible?

externally, NO!

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales - Corel CTech Paradox

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

#3

You have to have paradox do it so the locks that may be in place are respected. You
can not do it from windows or a backup program, etc. while the tables are in use.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since 1982

#4

"Dennis Santoro" wrote:

Quote:

You can not do it from windows or a backup program, etc.
while the tables are in use.

=====================

Unless you use Windows API calls to place explicit file locks on each of the
table files... then manually unlock (through code) them when completed.

You will need to be sure to include a copy of ALL the related DB files also
(index files etc.).

While it can be done without using the BDE (ie; using Paradox or Delphi)....
It is much easier to simply use Paradox (the application) to copy the
Tables. Paradox (the application) will natively use the BDE to lock the
tables while they're being copied.... and also assure that ALL related DB
files are included in the copy process.

--
....
`·.¸¸.·´¯`·.¸¸.·´¯`·-> rodney

#5

Quote:

Paradox (the application) will natively use the BDE to lock the tables
while they're being copied

slight correction to that..

to copy complete tables, even from within Paradox the app, you must get
exclusive locks on the tables, or use queries.. it's important for somebody
that isn't familiar with the platform to know that even within Paradox you
can't COPY while others are in the tables..

--
Steven Green - Waldorf Maryland USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards
"Rodney Wise" <NSpamPlease_rodney1 (AT) bellsouth (DOT) net> wrote

Quote:

"Dennis Santoro" wrote:

You can not do it from windows or a backup program, etc.
while the tables are in use.

=====================

Unless you use Windows API calls to place explicit file locks on each of
the table files... then manually unlock (through code) them when
completed.

You will need to be sure to include a copy of ALL the related DB files
also (index files etc.).

While it can be done without using the BDE (ie; using Paradox or
Delphi).... It is much easier to simply use Paradox (the application) to
copy the Tables. Paradox (the application) will natively use the BDE to
lock the tables while they're being copied.... and also assure that ALL
related DB files are included in the copy process.

--
...
`·.¸¸.·´¯`·.¸¸.·´¯`·-> rodney

#6

Quote:

...paradox data files while the database is operatonal.
... We want to do this nightly.
... Our first attempt was to simply copy the data directory with
Windows OS commands.

are records inserted or modified nightly? If no i think there is no
problem using xcopy (and then zip). I have not tried and I expect
orrified comments

Quote:

He delivered a directory of .DB, .TV, .PX files.

..px files are indexes that i would not backup. Dont forget .mb files.

Quote:

a resource where I can read about basic administration for paradox databases?
Specifically how to safely copy/mirror/replicate source systems without
affecting daily operations of end users.

i would buy paradox (version >=7), much cheaper then any backup
software, and on this newsgroup you would get the ~20 lines of OPAL
code (with embedded SQL that you already know) to backup the database.
Here an example:

method sqlBack(const nametab string,const namealiasSorg string, const
namealiasDest string)
var
db database
sq sql
endvar
nametabledest=":"+namealiasdest+":"+nametab+".db"
db.open(namealiasdest)
tabella= "':"+namealiasSorg+":"+nametab+".db'"
sq=sql
select * from ~tabella
endsql
sq.executesql(db,nametabledest)

#7

john ligda wrote:

Quote:

Hello, I am very new to paradox databases but have some familiarity
with Oracle and MS SQL rdbms concepts. I would like to learn how to
safely copy paradox data files while the database is operatonal.
Is this possible?

Well, there is no built-in utility that permits you to make a
read-consistent snapshot of the data. The only way to do this is to make
the copy while preventing updates to all of the tables involved in the copy.
Paradox calls this a write lock. In a nutshell you want traverse through
the list of tables you want to copy and try to get a write lock on them all.
If you succeed, then hold that lock for as long as it takes to copy the
tables. If you fail, then release all of the locks and try again.

Quote:

Im used to some sort of admin tool which hides the complexity
involved in preserving a valid database state. I have no idea
if such a tool exists for paradox databases.

There is no archivelog, redo log, or anything like that, so the only way to
guarantee a consistent copy is to prevent modifications while the copy takes
place. For backup purposes, most Paradox shops have some overnight
light-out time for this.

Quote:

To add to the confusion, I will not be allowed to touch the production
tables myself. I will instead, be prviding a safe solution to the
administrator so that he can provide me with consistant refreshes
of the production tables. We want to do this nightly.

Then write the above described ObjectPAL code and have the administrator
schedule it for the overnight.

Quote:

Our first attempt was to simply copy the data directory with Windows OS
commands.

That would work if it happened that no updates were occuring during that
time.

Quote:

In fact, on one table there is a violation of the primary key on the very
last record. I suspect the copy is corrupt because of the
way it was copied. But honestly i have no idea.

If by violation of the primary key you mean a violation of the uniqueness of
the key, that's odd. If you mean a foreign key constraint violation, that's
actually pretty easy to accomplish in code with Paradox tables,
unfortunately.

Quote:

Can anyone direct me to a resource where I can read about basic
administration for paradox databases? Specifically how to safely
copy/mirror/replicate source systems without affecting daily
operations of end users.

If your goal is disaster recovery and you are used to what is possible with
Oracle and SQL Server, then be advised you can't even get close to that with
Paradox or any other file-shared database. You can write replication into
the application, but it's not something you can make happen as some sort of
background process, and obviously writing it can be a big undertaking.

A while back we had a post from Foster Glasscock who found a product that
creates a recoverable transaction log on the filesystem that could be used.
Check out this post:

From: "Foster Glascock"
Newsgroups: pnews.paradox-interactive
Subject: FYI Paradox Backups with Symantec Livestate Protector (DriveImage
or V2i Protector)
Date: Mon, 8 Nov 2004 08:50:17 -0800

If your goal is replication into a reporting system or an upstream system,
that pretty much has to be written in.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

#8

Rodney Wise wrote:

Quote:

Unless you use Windows API calls to place explicit file locks on each
of the table files... then manually unlock (through code) them when
completed.

Seems like that would just confound other BDE clients. Since the BDE writes
locks as semaphores in the .LCK files, competiting client processes would
look to the .LCK file, determine that the resource is free, then attempt to
access it and get an OS error.

BDE locking is probably the way to go. You can do a write lock and lock out
fewer users and tasks that way.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.

#9

And, even with this, which is as close as you can get while the system
is in use, let us say that you need to copy a subsystem which
involves, say, five tables - two are lookups, one is master and two
are details. So, you start by trying to lock table #1, meanwhile,
another of these tables is updated, then you lock table #2, meanwhile,
someone deletes one of the detail records, etc. By the time you've
got all 5 locked, it's possible the data is out of sync - no matter
what order you lock them in.

Really, the only ways to be absolutely certain you're getting a copy
that doesn't have incomplete transactions is:

1. Do it while no one is in the system (presumably all transactions
were completed when everyone exited the system).

OR

no, there is no or, that's really the only way. (I was gonna say
something about copying records real-time to archive tables and then
copying the archive tables, but you face the same problems as with the
live data tables - unless you're using a "disconnected" model wherein
changes happen to local tables and get "published" as a batch, you're
outta luck...)

Liz

Larry DiGiovanni wrote:

Quote:

Well, there is no built-in utility that permits you to make a
read-consistent snapshot of the data. The only way to do this is to make
the copy while preventing updates to all of the tables involved in the copy.
Paradox calls this a write lock. In a nutshell you want traverse through
the list of tables you want to copy and try to get a write lock on them all.
If you succeed, then hold that lock for as long as it takes to copy the
tables. If you fail, then release all of the locks and try again.

#10

Liz McGuire wrote:

Quote:

By the time you've got all 5 locked, it's possible the data is
out of sync - no matter what order you lock them in.

This is a function of the transaction control written in to the application.
You can accomplish this in Oracle and SQL Server too if you have poor
transaction control, but I agree that since (1) Paradox transaction control
is poorer and (2) Paradox transaction control is typically not implemented
across multi-table updates, particularly interactive ones - you are far more
likely to generate an inconsistent copy in Paradox than the others.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.