Hi,

We have a named database that was created with SQL using primary keys and
foreign keys.

We tried PvModifyDatabase() and although the function returned success, the
resulting database cannot be opened due to RI (error 71) errors.

We read somewhere that you need to remove all the referential integrity
statements first, so we tried that.
eg. ALTER TABLE DROP CONSTRAINT ...
We removed all the foreign keys and then tried PvModifyDatabase() again. It
now fails with error code 7004 - P_E_FAIL, Failed for other reasons (a
truely usless error message).

Does anyone know the correct procedure for renaming a named database?

Thanks,
- Greg

Hi Again,

I did some further research and found that I had asked this question once
before (a couple of years ago) and was told that I needed to unbind the
database before renaming it, so I set up the following procedure:

1.) Remove foreign key constraints:
ALTER TABLE DROP CONSTRAINT ... for each of the foreign keys.
2.) Turn off RI and unbind:
PvModifyDatabase(hConnection, dbNameExisting, NULL, dictPath, NULL, 0);
3.) Rename the database:
PvModifyDatabase(hConnection, dbNameExisting, dbNameNew, dictPath, NULL, 0);
4.) Turn RI back on:
PvModifyDatabase(hConnection, dbNameNew, NULL, dictPath, NULL, P_DBFLAG_RI);
5.) Re-establish the foreign keys:
ALTER TABLE ... for each foreign key.

I get to step 3 (Rename the database) and it fails with error 7004 as it did
in my previous post.

Can someone help me with this?

Thanks,
- Greg

"Greg D" <nospam (AT) nospam (DOT) com> wrote

Hi Greg,

But in this message you said the return status is 7004, which indicates
you don't have adequate permissions to perform the rename. In your
first post, you said it returned a 71, whic refers to a violation of
the RI definitions.

So it looks like you've made a little progress, anyway. I'd look at
your security next.

Wayne Freeman
www.analyticabiz.com

Greg D ha scritto:

Hi Wayne,

Thanks for your suggestion, but I know security is not an issue as I am able
to perform steps 1 and 2. The database is on a local workstation and I
don't use any of the new security features.

Thanks,
- Greg

"Wayne" <sales (AT) analyticabiz (DOT) com> wrote

For anyone who is interested, the problem turned out to be owner names. You
need to remove the owner names (if you use them) from all the tables before
you rename.

So, the procedure is:

1.) Clear Owner name from each table.
2.) Remove foreign key constraints:
ALTER TABLE DROP CONSTRAINT ... for each of the foreign keys.
3.) Turn off RI and unbind:
PvModifyDatabase(hConnection, dbNameExisting, NULL, dictPath, NULL, 0);
4.) Rename the database:
PvModifyDatabase(hConnection, dbNameExisting, dbNameNew, dictPath, NULL, 0);
5.) Turn RI back on:
PvModifyDatabase(hConnection, dbNameNew, NULL, dictPath, NULL, P_DBFLAG_RI);
6.) Re-establish the foreign keys:
ALTER TABLE ... for each foreign key.
7.) Set owner name on each table.

- Greg

"Greg D" <nospam (AT) nospam (DOT) com> wrote

Further research shows that if you use RI but the database is not bound,
then all you need to do is:

1.) Clear Owner name from each table.
2.) Rename the database:
PvModifyDatabase(hConnection, dbNameExisting, dbNameNew, dictPath, NULL,
P_DBFLAG_RI);
3.) Set owner name on each table.

That's it.

Regards,
- Greg

"Greg D" <nospam (AT) nospam (DOT) com> wrote