Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
A STORE that doesn't exist even though I think it does! |
Thu, Apr 17 2014 1:09 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Thu, Apr 17 2014 1:14 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Thu, Apr 17 2014 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Apr 17 2014 7:23 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Thu, Apr 17 2014 7:56 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Fri, Apr 18 2014 2:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Apr 22 2014 5:48 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |