Subject Re: [IBO] Using IB_SQL to update V5 to V6/IB metadata
Author Helen Borrie (TeamIBO)
At 01:57 AM 18-07-02 +0930, you wrote:
>I have a version 5 database that I'm converting to FB and at least
>partially to IBO in
>order to learn about IBO and FB before beginning a new big project.
>In order to do this, I need to first alter 3 tables to change the
>now-reserved word YEAR
>to TheYEAR and update all of the stored procedures that use them.

Use the Browse tool (button with a pair of spectacles) to explore your
source. Unless you have deleted the source (which is possible) you will
see all of the sources to your SPs. The Source tab for the tables will
show you the Create Table statement....and so on.

SQL database objects are created with CREATE statements and altered with
ALTER statements. Your best bet when performing a number of related ALTER
statements is to run a script. IB_SQL has a script tool as well.

To "alter" the name of a column, you will need to
1. Add a new column of the same specs as the original YEAR column
2. Perform an UPDATE statement on the entire table, viz.

update MyTable set TheYEAR = YEAR
where YEAR is not null;

Because of dependencies, you might find you need to DROP the SPs before you
alter the table metadata. Just copy/paste the SP sources to a script
before you do; then you can CREATE (rather than ALTER) the SPs over again.

If you want to do the changes interactively in IB_SQL, use the DSQL tab to
submit and execute the DDL statements.

>IB_SQL doesn't show me the source to these tables so that I can change them.

No, IB_SQL doesn't provide an interactive interface to SP or trigger
sources. You always need to submit a DDL statement (interactively or in a
script) to alter metadata.

>Also, I haven't been able to work out how to update metadata with IB_SQL,
>so I'd appreciate some direct info on that.

Understand that an SQL database (unlike Paradox, etc.) doesn't have a
physical structure to which metadata are mapped. Database objects are
themselves described in tables which most tools (including IB_SQL) query
just like any other table to display and update metadata. Some tools go
the route of updating these "system tables" directly to alter metadata,
providing you with an apparent "physical interface" to the database
schema. It's an illusion - underneath, all is achieved with SQL statements.

>I've tried IBOConsole, but I don't know how to get started in that because
>of the need to 'Register'. What do I do with it to get it into a state in
>which I can view and alter metadata?

I suspect that IBOConsole won't work with the IB 5.x client, though others
will confirm or deny. When you are performing your pre-conversion changes,
make sure you are using the 5.x client; otherwise you will bump into
problems with this illegal column name. Once the changes are done, gbak
the database using the 5.x version of gbak; and restore it using the
Firebird version. This will change the on-disk structure from 9 to 10 and
generate a Dialect 1 database.

fwiw, IB_SQL has a fairly comprehensive help file, which should be in the
kit if you downloaded it from the website...

Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at