Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
sa28-00 [2019/10/18 10:49] – [Moving Blobs Back Into The Database] Justin Willey | sa28-00 [2021/11/26 12:45] (current) – Michael Scott | ||
---|---|---|---|
Line 72: | Line 72: | ||
==== Validating the External Storage ==== | ==== Validating the External Storage ==== | ||
- | Once the blobs are stored outside the database, the normal database validation cannot check that everything is present and correct. The stored procedure | + | Once the blobs are stored outside the database, the normal database validation cannot check that everything is present and correct. The stored procedure |
- | + | ||
- | <sxh SQL> | + | |
- | CREATE PROCEDURE " | + | |
- | RESULT (" | + | |
- | BEGIN | + | |
- | DECLARE " | + | |
- | DECLARE " | + | |
- | -- create temp table to hold errors | + | |
- | DECLARE LOCAL TEMPORARY TABLE BlobStoreCheckIssue(" | + | |
- | " | + | |
- | -- regularise Standard and Alternative locations | + | |
- | SET " | + | |
- | IF " | + | |
- | -- @AlternativeLocation now holds the actual location of the files we want to check | + | |
- | IF right(" | + | |
- | IF right(" | + | |
- | -- loop through external blobs | + | |
- | FOR BlobLoop as BlobCursor NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | from BlobStore where " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | if (select byte_substr(xp_read_file(BExtPath, | + | |
- | then insert into BlobStoreCheckIssue(" | + | |
- | END FOR; | + | |
- | -- recheck errors in case files were in use | + | |
- | FOR BlobLoop2 as BlobCursor2 NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | if (select byte_substr(xp_read_file(BExtPath, | + | |
- | then update BlobStoreCheckIssue set " | + | |
- | else update BlobStoreCheckIssue set " | + | |
- | end if; | + | |
- | END FOR; | + | |
- | -- check existence of remaining issues | + | |
- | FOR BlobLoop3 as BlobCursor3 NO SCROLL CURSOR | + | |
- | FOR select " | + | |
- | FOR READ ONLY | + | |
- | DO | + | |
- | set " | + | |
- | set " | + | |
- | if exists (select * from sp_list_directory(" | + | |
- | then update BlobStoreCheckIssue set " | + | |
- | where " | + | |
- | else update BlobStoreCheckIssue set " | + | |
- | where " | + | |
- | end if; | + | |
- | END FOR; | + | |
- | select " | + | |
- | when " | + | |
- | when " | + | |
- | as " | + | |
- | END; | + | |
- | </ | + | |
- | + | ||
- | The validation | + | |
< | < | ||
Line 136: | Line 79: | ||
|O|TI01FQSS161120130006|i: | |O|TI01FQSS161120130006|i: | ||
- | Orphaned Blobs in the file system can be identified with this stored procedure: | + | Orphaned Blobs in the file system can be identified with this stored procedure: |
<sxh SQL> | <sxh SQL> |