Subject Re: [ib-support] Re: Case sensisitive table and field names
Author Helen Borrie
At 09:20 AM 28-08-02 +0000, you wrote:
>Hi Helen
>I noticed when I did a search of the messages that you have been asked a
>similar question before. In this reply you said.
>As Martijn said, it will be a case-sensitivity problem that will be solved
>(immediately) by using quoted delimiters and (robustly) by rebuilding the
>database without case-sensitive identifiers.
>So I have a new question?
>How do I rebuild the database with case-sensitive identifiers and will
>this solve the problem.

Did you mean to ask "How do I rebuild the database WITHOUT case-sensitive

Which problem do you want to solve - the need to use quoted identifiers in
your client queries or the one that IB won't store your CamelCase identifiers?

To rebuild the database without the quoted identifiers, grab a tool that
doesn't force you to use them. IB_SQL (free from is one
good choice. Use the Metadata Extract tool to generate a complete script
of your DB, select it and paste it into a plain text file. If the original
declarations had quoted identifiers, this tool will reproduce them. Do a
search-and-replace, e.g. with Notepad, to strip out all of the double-quote
characters in the script, and save it. Make sure there is at least one
carriage return and linefeed after the last statement in the script.

Next, shutdown the database and then rename it.

Rebuild the database using the script. IB_SQL has a script tool for this
job. It will complain if encounters any errors and you can rollback the
trans and fix the error(s).

Comment out the foreign and primary key constraints and any CREATE INDEX
statements. You can go back later (after pumping data) and run these in
another script, in the order primary keys - foreign keys - indexes.

Use IB_SQL's data pump tool - or another such tool from the worthy range
of options available in the community - to repopulate the new database from
the copy. (The source side of the pump will have to be double-quote-aware.)

It sounds like a lot of work but when you get to it, it's no big
deal. Many of us deliberately rebuild and pump our databases multiple
times during development and before deploying. It's kinda like bathing the
dog...some people never bathe their dogs and so their dogs stink.

>If I may ask, what part of australia do you come from.

I live on the Central Coast, NSW, but I'm not a native of the West Island. :-)