Hi

Just starting out with scripting and loving it (I don't think!0.

In the script below, I think that I prove that a STORE exists but I get an error trying to backup to the STORE

ElevateDB Error #401 An error occurred with the statement at line 22 and column 25 (The store STORE-DB0000-Backups' does not exist in the configuration EDBConfig)

What am I doing wrong?

Cheers

Jeff
----------------------------------------------------------------
SCRIPT
BEGIN
 DECLARE DBCursor CURSOR FOR DBStmt;
 DECLARE StoreCursor CURSOR FOR StoreStmt;
 DECLARE DBName VARCHAR DEFAULT '';
 DECLARE StoreName VARCHAR DEFAULT '';
 DECLARE FileName VARCHAR;
 PREPARE DBStmt FROM 'SELECT * FROM Databases';
 OPEN DBCursor;
 FETCH FIRST FROM DBCursor ('Name') INTO DBName;
 WHILE NOT EOF(DBCursor) DO
   SET StoreName = 'STORE-' + DBName + '-Backups''';
   PREPARE StoreStmt FROM 'SELECT * FROM Stores WHERE Name = '''
     + StoreName + '';
   OPEN StoreCursor;
   IF NOT EOF(StoreCursor) THEN  /* therefore the STORE exists <<<<<<<<<<<<<<<<<<<<<*/
     SET FileName = DBName + '=' + REPLACE(' ' WITH '_' IN
       REPLACE(':' WITH '' IN CAST(CURRENT_TIMESTAMP AS VARCHAR(17))));
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Backup of ' + DBName + ' to ' + FileName
                     + ' in ' + StoreName + ' started';
     EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' /* error here  <<<<<<<<<<<*/
       + FileName
       + '" TO STORE "' + StoreName + '" INCLUDE CATALOG';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Backup of ' + DBName + ' to ' + FileName + ' ended';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Copy of ' + FileName + ' in '
                     + StoreName + ' to ' + FileName
                     + ' in STORE-RemoteBackups Started';
     EXECUTE IMMEDIATE 'COPY FILE "' + FileName + '" IN STORE "'
                     + StoreName + '" TO "' + FileName
                     + '" IN STORE "STORE-RemoteBackups';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Copy of ' + FileName + ' in '
                     + StoreName + ' to ' + FileName
                     + ' in STORE-RemoteBackups ended';
   END IF;
   CLOSE StoreCursor;
 FETCH NEXT FROM DBCursor ('Name') INTO DBName;
 END WHILE;
 CLOSE DBCursor;
END

There was an error in this line  ('=' instead of '-') :-

     SET FileName = DBName + '=' + REPLACE(' ' WITH '_' IN
       REPLACE(':' WITH '' IN CAST(CURRENT_TIMESTAMP AS VARCHAR(17))));

But I fixed it with:-

     SET FileName = DBName + '-' + REPLACE(' ' WITH '_' IN
       REPLACE(':' WITH '' IN CAST(CURRENT_TIMESTAMP AS VARCHAR(17))));

... And I still get the same error.  

Cheers

Jeff

Jeff


Two ideas:

1.    Rather than checking if the store exists why not just try creating it in an exception block - that guarantees it exists
2.    Use IF (ROWCOUNT(DBCursor) > 0) THEN


I haven't look for other errors so I hope one of the two above works

Roy Lambert

Hi Roy

Thanks for your input.

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:280A96B0-D060-4127-A63B-D3A7FA0BE8E2@news.elevatesoft.com...

> 1.    Rather than checking if the store exists why not just try creating
> it in an exception block - that guarantees it exists

This won't work for me, at least as I planned to use the script.

When I create a new user's database I also create a Backup Store.  As the
script is designed, the presence of the store indicates that a backup is
necessary.

There other types of database, e.g. demonstartion databases that prospective
users can create (copying a "clean" database and playing around with the
system.  These will have no Backup Store so will just get skipped in the
backup script.  Also the app has a "Training Mode" whereby the user can copy
their live database and try out things if they are not sure how a particular
function works.  Once they trained themselves, they can "throw away" the
Training database and revert to the live DB.  Similarly - noBackup Store
present so no backup done.

> 2.    Use IF (ROWCOUNT(DBCursor) > 0) THEN

But the real problem is that this statement

   EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' /* error here
<<<<<<<<<<<*/
       + FileName
       + '" TO STORE "' + StoreName + '" INCLUDE CATALOG';

is failing to see the Store that I can see with EDBMngr.

It's creation SQL looks like this:-

CREATE STORE "STORE-DB0000-Backups" AS
LOCAL PATH 'C:\APMEDB\0000\BackupStore'

and the error message is:-

ElevateDB Error #401 An error occurred with the statement at line 22 and
column 25 (The store STORE-DB0000-Backups' does not exist in the
configuration EDBConfig)

.... which to me seems nutty as the store names match

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Hi Roy

I've cracked it!

> ElevateDB Error #401 An error occurred with the statement at line 22 and
> column 25 (The store STORE-DB0000-Backups' does not exist in the
> configuration EDBConfig)
>
> ... which to me seems nutty as the store names match
>

It wasn't nutty, because they don't match - see the single quote mark at the
end of STORE-DB0000-Backups'   !!!

For some reason, the SELECT statement found the store even though it had the
quote but the BACKUP statement choked on it.  Get rid of the quote and the
problem goes away.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


Told you I hadn't read the code

I HATE having to find that sort of thing. The only worse one (I'm not going to mention nulls) is spaces - either multiple spaces in a string, or worst of all space at the end of a string.

Roy Lambert

Hi All

Thanks for all the help with this.  I have posted my final code below for my
"conditional backup" just for completeness.

I have some questions about timeouts but will start a new thread to ask
those.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

SCRIPT
BEGIN
/*
Back up all databases that have backup STORE defined.

     Format of STORE name:- 'STORE-' + DBName + '-Backups'
     Backups are copied to a remote store:- 'STORE-RemoteBackups'
*/
 DECLARE DBCursor CURSOR FOR DBStmt;
 DECLARE StoreCursor CURSOR FOR StoreStmt;
 DECLARE DBName VARCHAR DEFAULT '';
 DECLARE StoreName VARCHAR DEFAULT '';
 DECLARE FileName VARCHAR;
 PREPARE DBStmt FROM 'SELECT * FROM Databases ORDER BY "Name"';
 OPEN DBCursor;
 FETCH FIRST FROM DBCursor ('Name') INTO DBName;
 WHILE NOT EOF(DBCursor) DO
   SET StoreName = 'STORE-' + DBName + '-Backups';
   PREPARE StoreStmt FROM 'SELECT * FROM Stores WHERE Name = '''
                          + StoreName + '''';
   OPEN StoreCursor;
   IF NOT EOF(StoreCursor) THEN  /* therefore the STORE exists */
     SET FileName = DBName + '-' + REPLACE(' ' WITH '_' IN
       REPLACE(':' WITH '' IN CAST(CURRENT_TIMESTAMP AS VARCHAR(17))));
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Backup of ' + DBName + ' to ' + FileName
                     + ' in ' + StoreName + ' started';
     EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "'
       + FileName
       + '" TO STORE "' + StoreName + '" INCLUDE CATALOG';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Backup of ' + DBName + ' to ' + FileName + '
ended';
     SET FileName = FileName + '.EDBBkp';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Copy of ' + FileName + ' in '
                     + StoreName + ' to ' + FileName
                     + ' in STORE-RemoteBackups Started';
     EXECUTE IMMEDIATE 'COPY FILE "' + FileName + '" IN STORE "'
                     + StoreName + '" TO "' + FileName
                     + '" IN STORE "STORE-RemoteBackups"';
     SET LOG MESSAGE TO CAST(CURRENT_TIMESTAMP AS VARChar(16))
                     + ' Copy of ' + FileName + ' in '
                     + StoreName + ' to ' + FileName
                     + ' in STORE-RemoteBackups ended';
   END IF;
   CLOSE StoreCursor;
 FETCH NEXT FROM DBCursor ('Name') INTO DBName;
 END WHILE;
 CLOSE DBCursor;
END