At 06:50 PM 16/02/2005 +0000, you wrote:

>Hi Helen. Thanks again for your answer.
>
>I know that you are a very experienced user (judging from
>your many posts) and I now I'm just a starter to firebird
>(two - three months experience means nothing). Yes I have
>read the interbase op guide but also trying to find the
>best interface for BCB (tested zeos, IBX ended up with ODBC)
>and do the actual developement in a four months period.
>Wasn't my choise but the other alternative given to me
>was MS Access !!

If your environment is BCB, you can write a separate service application
(using the InterBase service components) and just not distribute gbak,
gfix, et al. with your software. (If the users are likely to go get the
tools for themselves and wreck the database with no help from you, you
should make it a condition of use that you take no responsibility for their
actions..)

>I believe what you say that -replace is a bad
>option but as I said in my post the application
>will run in a place with users who don't now
>what is a command prompt, a database or even speak English.
>I'm not get paid for the application (I'm not complaining here)
>and I wont be available all the times to support them.

As a minimum, you should provide end-user programs for everything AND
whatever procedure documentation they would need to handle Admin tasks if
you were not available.

>I can instruct them, once a day to push a button to perform
>a backup (online) which I keep depending on the day of the week
>the month and the year.
>But In case of emergency I can't figure out an automated way
>to perform a safe restore. I have developed an app which first
>check for users connected, if no-one is ,shuts
>down the server, perform a backup, and restore (yes with replace)
>from their last backup and take the database online again. I use
>IBPP and BCB6. I couldn't use any of the ready tools out there
>cause either are too complicated for my users, or in different
>language. Thats way I keep asking about when gfix returns. I want
>to be sure that the database is realy shutdown.

This is doable, but it isn't going to be safe if you insist that gbak -r is
the only way to go. It is the *worst* way to go. You can write an Admin
application in BCB with the service components that lets the site Admin go
through a recovery procedure step by step. If you don't make gbak -r
available, then the only way the site Admin could use it would be to
download and run it himself from the command line.

I'm sorry that you have only 4 months and you are not getting paid; but a
safe disaster recovery procedure supported by well-tested, idiot-proof
software is absolutely essential for the environment you are developing for.

>Just one more thing You said "When you have a corrupt database, no-one is
>connected to it"
>Does this mean that if a database is corrupt you CANT connect?

Physical corruption, yes. For example, part of the database file's disk
space got blitzed by a power surge; or the database header got corrupted
by some local problem on the server.

>Because during my development I had a case where after
>a few DDL statements I had a strange problem:
>I couldn't define a foreign key for a table (from what I remember
>the message was that the table was in use) and only SYSDBA
>was connected (and YES only one time).
>gfix returned no error (with full validation) and I could connect
>from my app to the database and work without no problem.

That's not corruption, it's an exception. If you genuinely had exclusive
access (meaning that you didn't have more than one connection, even if
owned by you) then the exception would have occurred because your preceding
DDL had not committed for some reason.

Another cause for the exception could have been that, previously, a SP had
run that used one or both of those tables. Superserver caches SPs until
the last connection is gone. Classic caches them per connection so they
stay cached until you end the process by disconnecting.

Typically, you can't create a FK that references a PK or UNIQUE constraint
that has not been committed.

Furthermore, if that FK were allowed to proceed (which is what the
exception prevented it from doing) then you would have had *structural*
corruption. You would have been able to connect to the database, but you
would have been unable to query that table.

>Shutting down - restarting or even backing up and restoring
>didn't solve the problem (backup and restore worked fine).
>No network - no connections - just me (SYSDBA) and firebird.

If you are using a GUI utility for your maintenance work, you are very
likely to encounter problems creating FKs. For example, if you have a
window displaying a table, you won't be able to go to some other window and
create a FK involving that table, since the table is "in use".

>Was that a corrupted database or not?

No. It was a healthy database being protected from an operation that would
have caused a logical corruption if the operation had been allowed to proceed.

>I recreated the database from my original script (I prefer
>to write the DDL's that way) and then I was
>able to define the foreign key with no problem.

There is no harm in that; though it was unnecessary. All you need to do
when that exception occurs is to commit the other constraints and any other
transactions that are running (such as any table or metadata views that the
tool is currently displaying) and try again. If it still fails with the
same exception, you'll know that there is a cached SP in the picture somewhere.

>I just want to point out that you can work with
>no problem with a corrupted database for a long time (until it
>crashes on your face ofcource). So how to test if the restored
>backup is ok automated?

Look at the service components. Basically, make the gbak file, restore it
to a temp name, have the application try to connect to it and, if that
fails, report a bad backup. To test whether a shutdown has "taken", you
can use the component that performs gstat -h (TIBStatisticalService), push
its output into a TStrings and scan the strings for "shutdown".

BTW, it may be that the ODBC driver supports the Services API. Use the
firebird-odbc-devel list to ask about that. But it's still not going to be
a cool idea to provide a Services interface in your main application since,
in general, Admin tasks require SYSDBA or Owner to be logged and and for
all other work to be suspended. From long experience of troubleshooting
user applications - esp. in encapsulated environments - I know that the
"standard" approach is to start the application by opening a gazillion data
sets...meaning you immediately have a gazillion tables and their dependents
"in use"....

./heLen