Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Moving BDE/DBASE to FireDac/Interbase Define Primary Key


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


Permlink Replies: 3 - Last Post: Oct 12, 2016 11:41 PM Last Post By: Jan Dijkstra Threads: [ Previous | Next ]
John MacDonald

Posts: 179
Registered: 3/22/98
Moving BDE/DBASE to FireDac/Interbase Define Primary Key  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 11, 2016 9:03 PM
I used the tool to create an interbase database from my dbase files.

I did not establish proper Primary keys and now I think this is causing issues.

To set a field as the primary key apparently it needs to be not null and set as such.
In the IBConsole I don't seem to be able to set a field to "not null" after it is populated and this is a requirement to set a primary key.

Please help

Short of starting over again with the conversion software I don't know what else to try.

John

--
JMAC out

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Moving BDE/DBASE to FireDac/Interbase Define Primary Key  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 12, 2016 9:35 AM   in response to: John MacDonald in response to: John MacDonald
John MacDonald wrote:
I used the tool to create an interbase database from my dbase files.

I did not establish proper Primary keys and now I think this is causing issues.

To set a field as the primary key apparently it needs to be not null and set as such.
In the IBConsole I don't seem to be able to set a field to "not null" after it is populated and this is a requirement to set a primary key.

Please help

Short of starting over again with the conversion software I don't know what else to try.

John

--
JMAC out


What I do in this situation is basically

1) Remove the dependencies on the column (indexes, SP, triggers) also turn off
update triggers
2) Add a new column defined like the one you need the not null on
3) copy the data over using coalesce to assign values in the case of nulls
4) drop the original column
5) rename the new column to the old name
6) Reposition the new column to the old column's location
7) reestablish indexes and dependencies and turn on update triggers

ex

ALTER TRIGGER REPLINT_FACTORYCODE_U inactive;

ALTER INDEX IDXFACTORYCODEFO inactive;
DROP INDEX IDXFACTORYCODEFO;
ALTER TABLE FACTORYCODE ADD OEMCODE_478 CHAR(30) NOT NULL;
UPDATE FACTORYCODE SET OEMCODE_478 = COALESCE(OEMCODE, '');
ALTER TABLE FACTORYCODE DROP OEMCODE;
ALTER TABLE FACTORYCODE ALTER OEMCODE_478 to OEMCODE;
ALTER TABLE FACTORYCODE ALTER OEMCODE POSITION 3;

ALTER TABLE FACTORYCODE ADD FCODE_478 CHAR(30) NOT NULL;
UPDATE FACTORYCODE SET FCODE_478 = COALESCE(FCODE, '');
ALTER TABLE FACTORYCODE DROP FCODE;
ALTER TABLE FACTORYCODE ALTER FCODE_478 to FCODE;
ALTER TABLE FACTORYCODE ALTER FCODE POSITION 2;

/* Index definitions for IDXFACTORYCODEFO */
CREATE UNIQUE INDEX IDXFACTORYCODEFO ON FACTORYCODE(FCODE, OEMCODE);
ALTER TRIGGER REPLINT_FACTORYCODE_U active;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

John MacDonald

Posts: 179
Registered: 3/22/98
Re: Moving BDE/DBASE to FireDac/Interbase Define Primary Key  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 12, 2016 11:49 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Hello Jeff:

Thanks for the response. I'm looking to help to make my firedac run much quicker.

When I read through the documentation, I came up with this idea that a primary key must be defined.

I'm experimenting with firedac and my new Interbase database through C++ Builder XE6

A simple append, edit and post is getting messed up at the post. It takes quite some time and then the app crashes.

I've spent some time now watching the videos.
I've written a small app for testing that has NO bde, things work pretty well in this app. I'm just hoping I don't have to go cold turkey and rewrite all the datamodules over again.

John


Jeff Overcash (TeamB) wrote:
John MacDonald wrote:
I used the tool to create an interbase database from my dbase files.

I did not establish proper Primary keys and now I think this is causing issues.

To set a field as the primary key apparently it needs to be not null and set as such.
In the IBConsole I don't seem to be able to set a field to "not null" after it is populated and this is a requirement to set a primary key.

Please help

Short of starting over again with the conversion software I don't know what else to try.

John

--
JMAC out


What I do in this situation is basically

1) Remove the dependencies on the column (indexes, SP, triggers) also turn off
update triggers
2) Add a new column defined like the one you need the not null on
3) copy the data over using coalesce to assign values in the case of nulls
4) drop the original column
5) rename the new column to the old name
6) Reposition the new column to the old column's location
7) reestablish indexes and dependencies and turn on update triggers

ex

ALTER TRIGGER REPLINT_FACTORYCODE_U inactive;

ALTER INDEX IDXFACTORYCODEFO inactive;
DROP INDEX IDXFACTORYCODEFO;
ALTER TABLE FACTORYCODE ADD OEMCODE_478 CHAR(30) NOT NULL;
UPDATE FACTORYCODE SET OEMCODE_478 = COALESCE(OEMCODE, '');
ALTER TABLE FACTORYCODE DROP OEMCODE;
ALTER TABLE FACTORYCODE ALTER OEMCODE_478 to OEMCODE;
ALTER TABLE FACTORYCODE ALTER OEMCODE POSITION 3;

ALTER TABLE FACTORYCODE ADD FCODE_478 CHAR(30) NOT NULL;
UPDATE FACTORYCODE SET FCODE_478 = COALESCE(FCODE, '');
ALTER TABLE FACTORYCODE DROP FCODE;
ALTER TABLE FACTORYCODE ALTER FCODE_478 to FCODE;
ALTER TABLE FACTORYCODE ALTER FCODE POSITION 2;

/* Index definitions for IDXFACTORYCODEFO */
CREATE UNIQUE INDEX IDXFACTORYCODEFO ON FACTORYCODE(FCODE, OEMCODE);
ALTER TRIGGER REPLINT_FACTORYCODE_U active;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)


--
JMAC out
Jan Dijkstra

Posts: 206
Registered: 11/4/99
Re: Moving BDE/DBASE to FireDac/Interbase Define Primary Key  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 12, 2016 11:41 PM   in response to: John MacDonald in response to: John MacDonald
John MacDonald wrote:
Hello Jeff:

Thanks for the response. I'm looking to help to make my firedac run much quicker.

When I read through the documentation, I came up with this idea that a primary key must be defined.

I'm experimenting with firedac and my new Interbase database through C++ Builder XE6

A simple append, edit and post is getting messed up at the post. It takes quite some time and then the app crashes.

I've spent some time now watching the videos.
I've written a small app for testing that has NO bde, things work pretty well in this app. I'm just hoping I don't have to go cold turkey and rewrite all the datamodules over again.

John

I've migrated my BDE over to a Postgress database. But what I've done is introduce a new field that serves as the primary key. And I'm generating the values for this field with a generator that I've also setup in the Postgress database. It does take some redefining of the relational links between the tables, but it works like a charm.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02