Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Migration - need to create primary field


This question is answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Feb 19, 2017 3:17 AM Last Post By: John MacDonald Threads: [ Previous | Next ]
John MacDonald

Posts: 179
Registered: 3/22/98
Migration - need to create primary field  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2017 2:05 AM
Hello everyone:

During my migration of the data there are some fields where the data is duplicated.
I'm trying to add a new unique key field to each record.

I thought I would just cycle through the records one at a time...

When I use the post it throws an error "rows affected 0 while 1 was requested" ( I'm paraphrasing )

I'm using FireDac and InterBase

There must be a way to work with each record and then do a post?
During migration this is very important.

--
JMAC out
Jan Dijkstra

Posts: 206
Registered: 11/4/99
Re: Migration - need to create primary field
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2017 3:06 AM   in response to: John MacDonald in response to: John MacDonald
John MacDonald wrote:
Hello everyone:

During my migration of the data there are some fields where the data is duplicated.
I'm trying to add a new unique key field to each record.

I thought I would just cycle through the records one at a time...

When I use the post it throws an error "rows affected 0 while 1 was requested" ( I'm paraphrasing )

I'm using FireDac and InterBase

There must be a way to work with each record and then do a post?
During migration this is very important.

--
JMAC out

What I did was to create a little data pump program that read via the BDE each record in the source table, and used FireDAC to create records in the target PostGress table, field by field. As an extra, before pumping the data over, I had added a number generator, and tied that to a new, extra field in the target table. Which ensures that each row gets a unique number that's to be used as the primary key.

The PostGress engine assigns the unique value it obtains from the generator when the row is inserted in the database.

But, to be fair, I'm also updating my application(s) to use this new field as the unique, internal key instead of the field(s) I used for that in the old, BDE controlled DBase tables. Everything is now setup to use the generators to provide unique numbers for the primary keys, and my code has been updated to use these new key values when referring to specific records/rows in the tables.
John MacDonald

Posts: 179
Registered: 3/22/98
Re: Migration - need to create primary field  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2017 3:17 AM   in response to: Jan Dijkstra in response to: Jan Dijkstra
Jan Dijkstra wrote:
John MacDonald wrote:
Hello everyone:

During my migration of the data there are some fields where the data is duplicated.
I'm trying to add a new unique key field to each record.

I thought I would just cycle through the records one at a time...

When I use the post it throws an error "rows affected 0 while 1 was requested" ( I'm paraphrasing )

I'm using FireDac and InterBase

There must be a way to work with each record and then do a post?
During migration this is very important.

--
JMAC out

What I did was to create a little data pump program that read via the BDE each record in the source table, and used FireDAC to create records in the target PostGress table, field by field. As an extra, before pumping the data over, I had added a number generator, and tied that to a new, extra field in the target table. Which ensures that each row gets a unique number that's to be used as the primary key.

The PostGress engine assigns the unique value it obtains from the generator when the row is inserted in the database.

But, to be fair, I'm also updating my application(s) to use this new field as the unique, internal key instead of the field(s) I used for that in the old, BDE controlled DBase tables. Everything is now setup to use the generators to provide unique numbers for the primary keys, and my code has been updated to use these new key values when referring to specific records/rows in the tables.

Thanks for your help Jan:
I have sorted this out now. Sometimes I wished I had converted my database with my own pump. I used the clever components pump.
For the most part it works. I have to be careful, but, occasionally I have to fix something it created. I've learned quite a bit about interbase due to this.

The fact is that everything works out in the end. I understand the need for data integrity, but, sometimes its not so friendly.

The fields I want as original fields were not identified as primary. So I have to create them. Sometimes they have nulls and sometimes they have duplicates. In the BDE, as you know, this would have been very easy to fix.

Have a great day and thanks again.

John

--
JMAC out
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02