Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Alter Domain question (firebird)


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


Permlink Replies: 5 - Last Post: Jan 18, 2017 11:57 AM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Arthur Hoornweg

Posts: 414
Registered: 6/2/98
Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 9, 2016 2:31 AM
Hello all,

I declared a domain "bit" as an alias to "smallint" in Firebird 2.5 embedded because that database didn't support booleans.

Now, firebird 3 embedded finally supports booleans and I'd like to re-define the "bit" domain as "boolean". Only I can't.... because the domain is in use in many tables. Is there a quick and dirty trick to re-declare the domain and apply it to all tables that have fields of that type?
ioan ghip

Posts: 40
Registered: 3/16/00
Re: Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 9, 2016 9:27 AM   in response to: Arthur Hoornweg in response to: Arthur Hoornweg
Arthur Hoornweg wrote:
Hello all,

I declared a domain "bit" as an alias to "smallint" in Firebird 2.5 embedded because that database didn't support booleans.

Now, firebird 3 embedded finally supports booleans and I'd like to re-define the "bit" domain as "boolean". Only I can't.... because the domain is in use in many tables. Is there a quick and dirty trick to re-declare the domain and apply it to all tables that have fields of that type?

You could recreate an empty database and then use IBPump to transfer the data from the old one.

Had to edit my response, I don't think IBPump works on embedded firebird.

Edited by: ioan ghip on Dec 9, 2016 9:29 AM
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 9, 2016 11:40 AM   in response to: Arthur Hoornweg in response to: Arthur Hoornweg
Field types get stored in the RDB$FIELDS table. While fiddling with system tables is to be done with extreme caution, my simple testing shows this will work for you:

update rdb$fields set rdb$field_type=17 where rdb$field_name='BIT';

I did my testing with InterBase, but I would assume FireBird to work the same.

Arthur Hoornweg wrote:
Hello all,

I declared a domain "bit" as an alias to "smallint" in Firebird 2.5 embedded because that database didn't support booleans.

Now, firebird 3 embedded finally supports booleans and I'd like to re-define the "bit" domain as "boolean". Only I can't.... because the domain is in use in many tables. Is there a quick and dirty trick to re-declare the domain and apply it to all tables that have fields of that type?
Arthur Hoornweg

Posts: 414
Registered: 6/2/98
Re: Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 12, 2016 11:50 PM   in response to: quinn wildman in response to: quinn wildman
quinn wildman wrote:
Field types get stored in the RDB$FIELDS table. While fiddling with system tables is to be done with extreme caution, my simple testing shows this will work for you:

update rdb$fields set rdb$field_type=17 where rdb$field_name='BIT';

Unfortunately that is not working, the error message (firebird 3) is "Update operation not allowed for system table RDB$FIELDS". A "grant update on rdb$fields to SYSDBA" does't help unfortunately.

Leocir kosvoski

Posts: 1
Registered: 11/22/09
Re: Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 18, 2017 3:23 AM   in response to: Arthur Hoornweg in response to: Arthur Hoornweg
hi

Solved problens?
I'm also having the same problem

Arthur Hoornweg wrote:
quinn wildman wrote:
Field types get stored in the RDB$FIELDS table. While fiddling with system tables is to be done with extreme caution, my simple testing shows this will work for you:

update rdb$fields set rdb$field_type=17 where rdb$field_name='BIT';

Unfortunately that is not working, the error message (firebird 3) is "Update operation not allowed for system table RDB$FIELDS". A "grant update on rdb$fields to SYSDBA" does't help unfortunately.

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Alter Domain question (firebird)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 18, 2017 11:57 AM   in response to: Leocir kosvoski in response to: Leocir kosvoski
Leocir kosvoski wrote:
hi

Solved problens?
I'm also having the same problem

Arthur Hoornweg wrote:
quinn wildman wrote:
Field types get stored in the RDB$FIELDS table. While fiddling with system tables is to be done with extreme caution, my simple testing shows this will work for you:

update rdb$fields set rdb$field_type=17 where rdb$field_name='BIT';

Unfortunately that is not working, the error message (firebird 3) is "Update operation not allowed for system table RDB$FIELDS". A "grant update on rdb$fields to SYSDBA" does't help unfortunately.

You can generate a script to do this fairly easily. Create your new domain and
then run something like this

select 'ALTER TABLE ' || RDB$RELATION_NAME || ' ALTER ' ||
rdb$field_name || ' type MyBool;'
from rdb$relation_fields where rdb$field_source = 'BIT';

Save the results off to the clipboard or file then run the script. That should
work.

--
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)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02