As long as there is an ODBC interface to the data source, it is possible to configure an SQL Adapter to query files. To configure the Adapter to read from multiple files, it is necessary to use the Segment elements in the ConnectionString element. For an example, see the previous section describing the connection string.
The way in which the SQL Adapter works with the files is as follows:
When a text ODBC driver is employed, the format of the text file is determined by the schema information file (schema.ini). This schema information file should be placed in the same directory as the text data source.
Schema.ini files are built out of entries, with each entry describing the structure of a single text file. The first line in each entry is the name of the text source file, enclosed in square brackets.
When the Adapter is working with files that were defined using wildcards, the file name changes constantly. Since the name in the schema.ini file cannot contain wildcards, the Adapter must change the schema.ini file before connecting to the database.
You therefore need to add an indicator line before the file entry line. This indicator line contains the name pattern from the connection string element in the Adapter configuration file, enclosed in square brackets. The Adapter replaces the next line with the new file name enclosed in square brackets.
Note: The schema.ini file can contain several entries. It is your responsibility to add the line(s) in the correct place.
[sqltes*.txt] [sqltest2.txt] ColNameHeader = False CharacterSet = ANSI Format = TabDelimited Col1=id Char Width 30 Col2=idname Char Width 30 ---------------------------------------- [report_200*.txt] [report_2003_09.txt] ColNameHeader = False CharacterSet = ANSI Format = TabDelimited Col1=date Char Width 30 Col2=service Char Width 30 Col3=responseTime Char Width 30 ----------------------------------------
For each query, the data source control file contains the file name pattern and the name of the current input file to continue with the same file when the Adapter restarts.
Following is an example of the control file in its XML format:
<AdapterControl Save="end" LastSaveTime="2005/03/23 09:16:15">
<Data>
<QueryCollection>
<Query QueryName="TroubleTickets (on D:\Data\Incidents*.xls)">
<KeyField Name="INC_CLOSE_DATE">
<LastValue>7/03/2005 13:06:21</LastValue>
</KeyField>
<LastFileName>IncidentsMarch.xls</LastFileName>
</Query>
</QueryCollection>
</Data>
When the Adapter has finished reading the current file it searches for the next one. The next file read is the first file that fits the name pattern and whose name is greater (in lexicographical order) than the previous file name. The Adapter does not return to previous files even if new records are added to them.
The Adapter uses the InitialFileName attribute only when these two attributes are equal to "no", and the control file does not contain the last file name.
The Adapter checks the connection string and the query only if the defined file exists. If it is defined using wildcards, the Adapter checks it only against the first file.
Errors can occur when the Adapter tries to read from a new file. In this case, the Adapter stops immediately if the critical attribute is equal to "yes". If it is equal to "no", the Adapter does not continue to perform this query, but continues with the other queries. Alternatively, the Adapter leaves the current file and moves to the next file.
There are two internal variables which can be used in the configuration file to refer to the current context of the file name. These are :file_name and :file_name_no_ext , referring to the current file name and the current file name minus the file extension, respectively.
These variables can be used in the SelectStatement element, SelectInitialValues element and in the QueryKeyField\Function attribute.
The Adapter replaces the variable with the file name in the queries.
For example:
|
Copyright © 2013 CA.
All rights reserved.
|
|