When you set up the export settings in the GUI, the same query will be ran each time, and most likely the same data set(s) will be returned. Access OLE Export / SQL Blob Export supports dynamic queries, where the SQL statement can be modified at run time.
Let's use the following query as a starting point.
Option 1 - provide a file that contains a SQL query
You do this using the <file> tag. Instead of explicitly providing the SQL command to run, you can provide the name of a file that contains the SQL command to use. You can use this option when you have other applications that can dynamically generate the SQL command. Note that the query needs to contain any columns that you may have used to name the exported files.
E.g. if your SQL command is stored in the f:\export\sql.txt file, you would do this:
Option 2 - provide a list of integer values from a file
You do this using the <integerlist> tag. Say that you only want to export items with specific ProductPhotoID values, and another application will provide this list of values. The requirement is that the application provides one value per line e.g.
56
75
89
and stores this list in a file. For our example, let's assume the list is stored in the f:\export\list.txt file. You would then need to modify your SQL to the following:
When you run the export, the SQL query that is used will contain the values from the file i.e.
SELECT ProductPhotoID, LargePhoto FROM Production.ProductPhoto WHERE ProductPhotoID IN (56, 75, 89)
To use a list of text values, use the <stringlist> tag e.g.
Note that each text value in the file need not be enclosed in single quotes.
Option 3 - use self-incrementing values
In situations where you have a column that auto-increments each time, and you only need to export new items, the command line interface can maintain a file that contains the last processed value for you. That file can then be used as input to the SQL command.
Assuming that ProductPhotoID is a self-incrementing value in our example. Say we only want to export new items each time the export runs. First, we need to modify the query to use the <integerlist> tag, to point to a file that will hold the last ProductPhotoID value.
We also need to ensure that our data set is ordered, so that the last record holds the latest ProductPhotoID value.
Next, we need to store the last ProductPhotoID value in the f:\export\LastID.txt file. To do that, click on the Advanced button.
This brings up the Incremental Export Settings window. Next, we need to store the last ProductPhotoID value in the f:\export\LastID.txt file. We do this by entering the values in the Stored values area.
