Board index » delphi » Transferring data from SQL Server 2000 to Paradox

Nicki Fairweather

Delphi Developer
Mon, 13 Dec 2004 02:42:24 GMT

Transferring data from SQL Server 2000 to Paradox

This is the situation.  We are about to completely rewrite a huge Paradox
for DOS application.  Because of the size of it, we are having to do this
module by module.  This means that we still have to keep the Paradox tables
in sync with the SQL tables.  Our intention is to write a small Delphi
app/dll which can be called by the various Delphi modules as they are
developed to transfer the information.

As the main Delphi modules will use the ADO components to access/update the
data in the SQL tables, we would like to be able to use ADO components to do
the transfer (just to keep everything consistent).

Any pointers as to how to do this.  We've spent time looking at
ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot get it
to update the Paradox table.  The information within the Client Data Set is
being updated but the background table is not.  We've tried ApplyUpdates and
that doesn't work either.

Any help would be greatly appreciated - we feel like we're up against a
brick wall!

Thanks

Nicki Fairweather

 
Mike Collie

Delphi Developer
Mon, 13 Dec 2004 09:07:28 GMT

Re:Transferring data from SQL Server 2000 to Paradox


The topic is quite large but briefly from my experience:

Upsizing applications from Paradox to Oracle, with a requirement for Paradox
to be maintained for READ-ONLY purposes from legacy modules & reports, the
easiest way was to use triggers on the server to produce an update list of
tablenames and PK fields to copy in the background to paradox. For my
purposes I didn't need to apply changes in Paradox to Oracle so this may not
suit your purposes.

On a more technical note:

There is no reason why you shouldn't be able to write to Paradox using ADO
(except BLOB fields perhaps). You just need the correct installation files..
What error messages do you get when you try to post?

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@btinternet.com> wrote in message

news:3d1a0c9e_2@dnews...
Quote
> This is the situation.  We are about to completely rewrite a huge Paradox
> for DOS application.  Because of the size of it, we are having to do this
> module by module.  This means that we still have to keep the Paradox
tables
> in sync with the SQL tables.  Our intention is to write a small Delphi
> app/dll which can be called by the various Delphi modules as they are
> developed to transfer the information.

> As the main Delphi modules will use the ADO components to access/update
the
> data in the SQL tables, we would like to be able to use ADO components to
do
> the transfer (just to keep everything consistent).

> Any pointers as to how to do this.  We've spent time looking at
> ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot get it
> to update the Paradox table.  The information within the Client Data Set
is
> being updated but the background table is not.  We've tried ApplyUpdates
and
> that doesn't work either.

> Any help would be greatly appreciated - we feel like we're up against a
> brick wall!

> Thanks

> Nicki Fairweather

Mike Collie

Delphi Developer
Mon, 13 Dec 2004 09:19:01 GMT

Re:Transferring data from SQL Server 2000 to Paradox


p.s. I forgot to mention. You may have problems using Paradox BDE and
Paradox ADO at the same time. Either one is fine on its own though.
Nicki Fairweathe

Delphi Developer
Mon, 13 Dec 2004 14:58:35 GMT

Re:Transferring data from SQL Server 2000 to Paradox


Quote
"Mike Collier" <m...@adoanywhere.com> wrote in message

news:3d1a64e5_1@dnews...

Quote
> The topic is quite large but briefly from my experience:

> Upsizing applications from Paradox to Oracle, with a requirement for
Paradox
> to be maintained for READ-ONLY purposes from legacy modules & reports, the
> easiest way was to use triggers on the server to produce an update list of
> tablenames and PK fields to copy in the background to paradox. For my
> purposes I didn't need to apply changes in Paradox to Oracle so this may
not
> suit your purposes.

We will have to apply cahnges to SQL Server from Paradox as some information
will still be held and maintained in the Paradox tables until the various
modules are completed

Quote

> On a more technical note:

> There is no reason why you shouldn't be able to write to Paradox using ADO
> (except BLOB fields perhaps). You just need the correct installation
files..
> What error messages do you get when you try to post?

That's the funny thing - Delphi raises no exceptions.  It appears to apply
the updates but they don't appear in the Paradox table!
Quote

> --
> Michael Collier - www.adoanywhere.com

> "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> news:3d1a0c9e_2@dnews...
> > This is the situation.  We are about to completely rewrite a huge
Paradox
> > for DOS application.  Because of the size of it, we are having to do
this
> > module by module.  This means that we still have to keep the Paradox
> tables
> > in sync with the SQL tables.  Our intention is to write a small Delphi
> > app/dll which can be called by the various Delphi modules as they are
> > developed to transfer the information.

> > As the main Delphi modules will use the ADO components to access/update
> the
> > data in the SQL tables, we would like to be able to use ADO components
to
> do
> > the transfer (just to keep everything consistent).

> > Any pointers as to how to do this.  We've spent time looking at
> > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot get
it
> > to update the Paradox table.  The information within the Client Data Set
> is
> > being updated but the background table is not.  We've tried ApplyUpdates
> and
> > that doesn't work either.

> > Any help would be greatly appreciated - we feel like we're up against a
> > brick wall!

> > Thanks

> > Nicki Fairweather

Mike Collie

Delphi Developer
Mon, 13 Dec 2004 20:32:39 GMT

Re:Transferring data from SQL Server 2000 to Paradox


It is just a question of establishing where the problem is, but you will
have to start from the beginning otherwise I can't say.

1) Modify the Paradox tables using ADO - without MIDAS. Paradox and ADO can
be problematic - it requires more attention to installations than Access or
SQL Server.

2) Are you using the JET OLEDB Provider or ODBC to connect to Paradox? I
suggest you use JET4: "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Common Files\Borland Shared\Data;Extended
Properties=Paradox 7.X" [change the version to suit].

3) Rule out table structure problems by modifying data in the Delphi demos
directory, they work OK with ADO and rule out problems with your table
design that can affect ability of ADO to update properly.

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@zonal.co.uk> wrote in message

news:3d1ab788$1_1@dnews...
Quote

> "Mike Collier" <m...@adoanywhere.com> wrote in message
> news:3d1a64e5_1@dnews...
> > The topic is quite large but briefly from my experience:

> > Upsizing applications from Paradox to Oracle, with a requirement for
> Paradox
> > to be maintained for READ-ONLY purposes from legacy modules & reports,
the
> > easiest way was to use triggers on the server to produce an update list
of
> > tablenames and PK fields to copy in the background to paradox. For my
> > purposes I didn't need to apply changes in Paradox to Oracle so this may
> not
> > suit your purposes.

> We will have to apply cahnges to SQL Server from Paradox as some
information
> will still be held and maintained in the Paradox tables until the various
> modules are completed

> > On a more technical note:

> > There is no reason why you shouldn't be able to write to Paradox using
ADO
> > (except BLOB fields perhaps). You just need the correct installation
> files..
> > What error messages do you get when you try to post?

> That's the funny thing - Delphi raises no exceptions.  It appears to apply
> the updates but they don't appear in the Paradox table!

> > --
> > Michael Collier - www.adoanywhere.com

> > "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> > news:3d1a0c9e_2@dnews...
> > > This is the situation.  We are about to completely rewrite a huge
> Paradox
> > > for DOS application.  Because of the size of it, we are having to do
> this
> > > module by module.  This means that we still have to keep the Paradox
> > tables
> > > in sync with the SQL tables.  Our intention is to write a small Delphi
> > > app/dll which can be called by the various Delphi modules as they are
> > > developed to transfer the information.

> > > As the main Delphi modules will use the ADO components to
access/update
> > the
> > > data in the SQL tables, we would like to be able to use ADO components
> to
> > do
> > > the transfer (just to keep everything consistent).

> > > Any pointers as to how to do this.  We've spent time looking at
> > > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot
get
> it
> > > to update the Paradox table.  The information within the Client Data
Set
> > is
> > > being updated but the background table is not.  We've tried
ApplyUpdates
> > and
> > > that doesn't work either.

> > > Any help would be greatly appreciated - we feel like we're up against
a
> > > brick wall!

> > > Thanks

> > > Nicki Fairweather

Mike Collie

Delphi Developer
Tue, 14 Dec 2004 19:27:35 GMT

Re:Transferring data from SQL Server 2000 to Paradox


Nicki,

I have posted a response here but it appears to be missing from my Outlook
window. If you have replied to the missing thread and are waiting for a
reply from me, please re-reply to this thread. Thanks.

Since yesterday it also occurred to me that you should check your lock type
on the ADO recordset. If you request Pessimistic locking then ado may give
you batchOptimistic lock instead. This will explain why updates don't apply.
You would need to call the (updateBatch / BatchUpdate?) method of the
dataset or change the requested lock type.

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@zonal.co.uk> wrote in message

news:3d1ab788$1_1@dnews...
Quote

> "Mike Collier" <m...@adoanywhere.com> wrote in message
> news:3d1a64e5_1@dnews...
> > The topic is quite large but briefly from my experience:

> > Upsizing applications from Paradox to Oracle, with a requirement for
> Paradox
> > to be maintained for READ-ONLY purposes from legacy modules & reports,
the
> > easiest way was to use triggers on the server to produce an update list
of
> > tablenames and PK fields to copy in the background to paradox. For my
> > purposes I didn't need to apply changes in Paradox to Oracle so this may
> not
> > suit your purposes.

> We will have to apply cahnges to SQL Server from Paradox as some
information
> will still be held and maintained in the Paradox tables until the various
> modules are completed

> > On a more technical note:

> > There is no reason why you shouldn't be able to write to Paradox using
ADO
> > (except BLOB fields perhaps). You just need the correct installation
> files..
> > What error messages do you get when you try to post?

> That's the funny thing - Delphi raises no exceptions.  It appears to apply
> the updates but they don't appear in the Paradox table!

> > --
> > Michael Collier - www.adoanywhere.com

> > "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> > news:3d1a0c9e_2@dnews...
> > > This is the situation.  We are about to completely rewrite a huge
> Paradox
> > > for DOS application.  Because of the size of it, we are having to do
> this
> > > module by module.  This means that we still have to keep the Paradox
> > tables
> > > in sync with the SQL tables.  Our intention is to write a small Delphi
> > > app/dll which can be called by the various Delphi modules as they are
> > > developed to transfer the information.

> > > As the main Delphi modules will use the ADO components to
access/update
> > the
> > > data in the SQL tables, we would like to be able to use ADO components
> to
> > do
> > > the transfer (just to keep everything consistent).

> > > Any pointers as to how to do this.  We've spent time looking at
> > > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot
get
> it
> > > to update the Paradox table.  The information within the Client Data
Set
> > is
> > > being updated but the background table is not.  We've tried
ApplyUpdates
> > and
> > > that doesn't work either.

> > > Any help would be greatly appreciated - we feel like we're up against
a
> > > brick wall!

> > > Thanks

> > > Nicki Fairweather

Mike Collie

Delphi Developer
Mon, 13 Dec 2004 09:07:28 GMT

Re:Transferring data from SQL Server 2000 to Paradox


The topic is quite large but briefly from my experience:

Upsizing applications from Paradox to Oracle, with a requirement for Paradox
to be maintained for READ-ONLY purposes from legacy modules & reports, the
easiest way was to use triggers on the server to produce an update list of
tablenames and PK fields to copy in the background to paradox. For my
purposes I didn't need to apply changes in Paradox to Oracle so this may not
suit your purposes.

On a more technical note:

There is no reason why you shouldn't be able to write to Paradox using ADO
(except BLOB fields perhaps). You just need the correct installation files..
What error messages do you get when you try to post?

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@btinternet.com> wrote in message

news:3d1a0c9e_2@dnews...
Quote
> This is the situation.  We are about to completely rewrite a huge Paradox
> for DOS application.  Because of the size of it, we are having to do this
> module by module.  This means that we still have to keep the Paradox
tables
> in sync with the SQL tables.  Our intention is to write a small Delphi
> app/dll which can be called by the various Delphi modules as they are
> developed to transfer the information.

> As the main Delphi modules will use the ADO components to access/update
the
> data in the SQL tables, we would like to be able to use ADO components to
do
> the transfer (just to keep everything consistent).

> Any pointers as to how to do this.  We've spent time looking at
> ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot get it
> to update the Paradox table.  The information within the Client Data Set
is
> being updated but the background table is not.  We've tried ApplyUpdates
and
> that doesn't work either.

> Any help would be greatly appreciated - we feel like we're up against a
> brick wall!

> Thanks

> Nicki Fairweather

Mike Collie

Delphi Developer
Mon, 13 Dec 2004 09:19:01 GMT

Re:Transferring data from SQL Server 2000 to Paradox


p.s. I forgot to mention. You may have problems using Paradox BDE and
Paradox ADO at the same time. Either one is fine on its own though.
Nicki Fairweathe

Delphi Developer
Mon, 13 Dec 2004 14:58:35 GMT

Re:Transferring data from SQL Server 2000 to Paradox


Quote
"Mike Collier" <m...@adoanywhere.com> wrote in message

news:3d1a64e5_1@dnews...

Quote
> The topic is quite large but briefly from my experience:

> Upsizing applications from Paradox to Oracle, with a requirement for
Paradox
> to be maintained for READ-ONLY purposes from legacy modules & reports, the
> easiest way was to use triggers on the server to produce an update list of
> tablenames and PK fields to copy in the background to paradox. For my
> purposes I didn't need to apply changes in Paradox to Oracle so this may
not
> suit your purposes.

We will have to apply cahnges to SQL Server from Paradox as some information
will still be held and maintained in the Paradox tables until the various
modules are completed

Quote

> On a more technical note:

> There is no reason why you shouldn't be able to write to Paradox using ADO
> (except BLOB fields perhaps). You just need the correct installation
files..
> What error messages do you get when you try to post?

That's the funny thing - Delphi raises no exceptions.  It appears to apply
the updates but they don't appear in the Paradox table!
Quote

> --
> Michael Collier - www.adoanywhere.com

> "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> news:3d1a0c9e_2@dnews...
> > This is the situation.  We are about to completely rewrite a huge
Paradox
> > for DOS application.  Because of the size of it, we are having to do
this
> > module by module.  This means that we still have to keep the Paradox
> tables
> > in sync with the SQL tables.  Our intention is to write a small Delphi
> > app/dll which can be called by the various Delphi modules as they are
> > developed to transfer the information.

> > As the main Delphi modules will use the ADO components to access/update
> the
> > data in the SQL tables, we would like to be able to use ADO components
to
> do
> > the transfer (just to keep everything consistent).

> > Any pointers as to how to do this.  We've spent time looking at
> > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot get
it
> > to update the Paradox table.  The information within the Client Data Set
> is
> > being updated but the background table is not.  We've tried ApplyUpdates
> and
> > that doesn't work either.

> > Any help would be greatly appreciated - we feel like we're up against a
> > brick wall!

> > Thanks

> > Nicki Fairweather

Mike Collie

Delphi Developer
Mon, 13 Dec 2004 20:32:39 GMT

Re:Transferring data from SQL Server 2000 to Paradox


It is just a question of establishing where the problem is, but you will
have to start from the beginning otherwise I can't say.

1) Modify the Paradox tables using ADO - without MIDAS. Paradox and ADO can
be problematic - it requires more attention to installations than Access or
SQL Server.

2) Are you using the JET OLEDB Provider or ODBC to connect to Paradox? I
suggest you use JET4: "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Common Files\Borland Shared\Data;Extended
Properties=Paradox 7.X" [change the version to suit].

3) Rule out table structure problems by modifying data in the Delphi demos
directory, they work OK with ADO and rule out problems with your table
design that can affect ability of ADO to update properly.

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@zonal.co.uk> wrote in message

news:3d1ab788$1_1@dnews...
Quote

> "Mike Collier" <m...@adoanywhere.com> wrote in message
> news:3d1a64e5_1@dnews...
> > The topic is quite large but briefly from my experience:

> > Upsizing applications from Paradox to Oracle, with a requirement for
> Paradox
> > to be maintained for READ-ONLY purposes from legacy modules & reports,
the
> > easiest way was to use triggers on the server to produce an update list
of
> > tablenames and PK fields to copy in the background to paradox. For my
> > purposes I didn't need to apply changes in Paradox to Oracle so this may
> not
> > suit your purposes.

> We will have to apply cahnges to SQL Server from Paradox as some
information
> will still be held and maintained in the Paradox tables until the various
> modules are completed

> > On a more technical note:

> > There is no reason why you shouldn't be able to write to Paradox using
ADO
> > (except BLOB fields perhaps). You just need the correct installation
> files..
> > What error messages do you get when you try to post?

> That's the funny thing - Delphi raises no exceptions.  It appears to apply
> the updates but they don't appear in the Paradox table!

> > --
> > Michael Collier - www.adoanywhere.com

> > "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> > news:3d1a0c9e_2@dnews...
> > > This is the situation.  We are about to completely rewrite a huge
> Paradox
> > > for DOS application.  Because of the size of it, we are having to do
> this
> > > module by module.  This means that we still have to keep the Paradox
> > tables
> > > in sync with the SQL tables.  Our intention is to write a small Delphi
> > > app/dll which can be called by the various Delphi modules as they are
> > > developed to transfer the information.

> > > As the main Delphi modules will use the ADO components to
access/update
> > the
> > > data in the SQL tables, we would like to be able to use ADO components
> to
> > do
> > > the transfer (just to keep everything consistent).

> > > Any pointers as to how to do this.  We've spent time looking at
> > > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot
get
> it
> > > to update the Paradox table.  The information within the Client Data
Set
> > is
> > > being updated but the background table is not.  We've tried
ApplyUpdates
> > and
> > > that doesn't work either.

> > > Any help would be greatly appreciated - we feel like we're up against
a
> > > brick wall!

> > > Thanks

> > > Nicki Fairweather

Mike Collie

Delphi Developer
Tue, 14 Dec 2004 19:27:35 GMT

Re:Transferring data from SQL Server 2000 to Paradox


Nicki,

I have posted a response here but it appears to be missing from my Outlook
window. If you have replied to the missing thread and are waiting for a
reply from me, please re-reply to this thread. Thanks.

Since yesterday it also occurred to me that you should check your lock type
on the ADO recordset. If you request Pessimistic locking then ado may give
you batchOptimistic lock instead. This will explain why updates don't apply.
You would need to call the (updateBatch / BatchUpdate?) method of the
dataset or change the requested lock type.

--
Michael Collier - www.adoanywhere.com

Quote
"Nicki Fairweather" <nic...@zonal.co.uk> wrote in message

news:3d1ab788$1_1@dnews...
Quote

> "Mike Collier" <m...@adoanywhere.com> wrote in message
> news:3d1a64e5_1@dnews...
> > The topic is quite large but briefly from my experience:

> > Upsizing applications from Paradox to Oracle, with a requirement for
> Paradox
> > to be maintained for READ-ONLY purposes from legacy modules & reports,
the
> > easiest way was to use triggers on the server to produce an update list
of
> > tablenames and PK fields to copy in the background to paradox. For my
> > purposes I didn't need to apply changes in Paradox to Oracle so this may
> not
> > suit your purposes.

> We will have to apply cahnges to SQL Server from Paradox as some
information
> will still be held and maintained in the Paradox tables until the various
> modules are completed

> > On a more technical note:

> > There is no reason why you shouldn't be able to write to Paradox using
ADO
> > (except BLOB fields perhaps). You just need the correct installation
> files..
> > What error messages do you get when you try to post?

> That's the funny thing - Delphi raises no exceptions.  It appears to apply
> the updates but they don't appear in the Paradox table!

> > --
> > Michael Collier - www.adoanywhere.com

> > "Nicki Fairweather" <nic...@btinternet.com> wrote in message
> > news:3d1a0c9e_2@dnews...
> > > This is the situation.  We are about to completely rewrite a huge
> Paradox
> > > for DOS application.  Because of the size of it, we are having to do
> this
> > > module by module.  This means that we still have to keep the Paradox
> > tables
> > > in sync with the SQL tables.  Our intention is to write a small Delphi
> > > app/dll which can be called by the various Delphi modules as they are
> > > developed to transfer the information.

> > > As the main Delphi modules will use the ADO components to
access/update
> > the
> > > data in the SQL tables, we would like to be able to use ADO components
> to
> > do
> > > the transfer (just to keep everything consistent).

> > > Any pointers as to how to do this.  We've spent time looking at
> > > ADOConnection with ADOTable/ADOQuery/ADOClientDataSet but we cannot
get
> it
> > > to update the Paradox table.  The information within the Client Data
Set
> > is
> > > being updated but the background table is not.  We've tried
ApplyUpdates
> > and
> > > that doesn't work either.

> > > Any help would be greatly appreciated - we feel like we're up against
a
> > > brick wall!

> > > Thanks

> > > Nicki Fairweather

Other Threads

1. Using SQL Server 2000 DTS to retrieve Paradox data

2. Transferring from Paradox to SQL 2000

3. SQL-Server 2000 Client fetches all data

4. BDE support of the Text data type in SQL Server 2000

5. Sql Server 2000 & Sql Server 7.0

6. transferring data to access 2000 database from text file

7. transferring data from text file to access 2000 database

8. Paradox Bytes field to SQL Server 2000 int field

9. paradox binary type to sql server 2000 ...

10. Importing Paradox 5.x Blob field into SQL Server 2000