Sunday 12 February 2012

Empty a QVD

After my post last week about the power of binary loads, I was asked how to empty a QVD and why you would do this rather than deleting the file.

Lets tackle how first. The following sub routine will empty a QVD of all records but leave the table structure definition within the QVD. We'll cover why this table definition is important in a second.

SUB EmptyQVD (Path)
   //Check file exists
   IF (NOT isNull(qvdCreateTime('$(Path)'))) THEN
      //Check if QVD is already empty
      IF (qvdNoOfRecords('$(Path)') > 0) THEN   
         //Load the structure from the existing QVD
         //Where clause returns no records
         _TableStructure:
         LOAD
             *
         FROM '$(Path)' (qvd)
         WHERE 1<>1;

         //Store table definition back to QVD
         STORE _TableStructure INTO '$(Path)' (qvd);
         DROP TABLE _TableStructure;
      END IF
   END IF
END SUB


This code first checks that the QVD file exists. If it does, it then checks to see if the QVD is already empty (no point in doing unnecessary work). Assuming it isn't it then performs a load from the QVD using the where clause "1<>1" which will always equate to false and thus the resulting table will have the same fields as the QVD but no records. This empty table is then saved back to the QVD and the temporary table is dropped. 

So why empty the QVD and not just use a macro or external batch file to delete it? Well there are 2 main reasons:
  1. Depending which method you were to use, you would either need to raise the module security to "System Access" or allow the script permission to "Execute External Programs". 
  2. The second reason is that any QVW which loads in the QVD will fail to reload if it does not exist, unless you check the file exists first. 
Checking the file exists is simple enough to do (we do so in the above code sample) but if you use the QVD in many places you may forget to perform this check. By saving an empty QVD with the correct field structure, any QVW which loads from it will still run successfully even if you forget to check the QVD exists. The resulting table will obviously just contain no data.

2 comments:

  1. Hi Matthew,

    In the Edit Script the Sub routine is not at all getting debugged, it is moving directly to END SUB.

    Please help me why it is not reading and tell me more abt the variable used in ur code - (PATH) and $(PATH)

    Thank You
    Bala

    ReplyDelete
  2. Perfect this is just what I was looking for, nice and simple truncation without messing around with macros and things, thanks Matt!

    ReplyDelete