SQL interface section

Implementation Guide › Adapter Configuration Specifications › SQL interface section

SQL interface section

SQL Adapter can be used to retrieve data from databases using a SQL statement.

The SQL interface defines the connection to the database and the queries used to retrieve the data, detailed as follow:

XML Structure:

	< DataSourceInterface >
		<ConnectionString ConnectionTimeout="60" QueryTimeout="30">
			<![CDATA[ Driver={Microsoft Access Driver (*.mDataBase)};DataBaseq=d:\Oblicore\database1.mdatabase; ]]>
			</ConnectionString>
		<QueryCollection>
		<Query QueryName="cases" InputFormat="cases" SleepTime="3600">
			<SelectStatement AutoCompleteQuery="yes">
			select dateclosed,callid,dateopened,companyname,priority,closedmn,responsemn
			from calls where dateclosed is not NULL
			</SelectStatement>
		<QueryKeyFields>
			<KeyField Name="dateclosed" Sort="asc"/>
			<KeyField Name="callid" Sort="desc"/>
			<SelectInitialValues>
				Select min(dateclosed) , 'min date' from calls
			</SelectInitialValues>
		</QueryKeyFields>
	</Query>
	<Query QueryName="contracts" InputFormat="contracts" SleepTime="3600">
	<ConnectionString>
		<Segment Type="text"
		Text=" Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; DataBaseq="/>
		<Segment Type="File">
		<File Path="d:\Oblicore " NamePattern="Availabilty_*.XLS>
		</Segment>
		<Segment Type="text" Text=";"/>
	</ConnectionString>
<SelectStatement AutoCompleteQuery="yes">….</SelectStatement>
<QueryKeyFields>…..</QueryKeyFields>
</Query>
</QueryCollection>
</DataSourceInterface>

ConnectionString-Connection string for accessing the source database.
The ConnectionString can be defined in the DataSourceInterface element and / or in the Query elements. The ConnectionString definition in the DataSourceInterface element is the default and it is used only in a query without a ConnectionString definition.

The connection string can be defined in one string or by segments. When the ConnectionString element does not contain Segment elements the connection string is taken from the ConnectionString element text. If it contains at least one Segment element, the connection string is concatenated from that.

There are two segment types. The first one is text and contains text that is concatenated to the connection string as is. The second type is file and contains a file name with or without wildcards. The file segment can appear only once and it contains other attributes that define what to do with the read file.
- ConnectionTimeout-(optional). Positive integer number that indicates, in seconds, how long to wait for the connection to open. The value 0 indicates to wait indefinitely until the connection is opened. The default value is 15 seconds.
Note: Some of the providers do not support this functionality.
- QueryTimeout-(optional). Positive integer number that indicates, in seconds, how long to wait for the query to execute. The value 0 indicates to wait indefinitely until the execution is complete. The default value is 30 seconds.
Note: Some of the providers do not support this functionality.
- Segment-specified the Segment attributes.
- Type-(optional) (text, file) Segment type
- Text-the text of the text segment.
- File-specifies the File attributes
- Path-the path to the location of the source data files.
- NamePattern-specifies the source data file name, can use wildcards.
- InitialFileName-(optional). Tells the user from which file to start the search and the pattern to search for.
- UsePath-(optional) [yes, no] When set to yes, the file path is concatenated to the connection string.
- UseFileName-(optional) [yes/no].When set to yes, the file name is concatenated to the connection string (needed for excel files).
- UpdateSchemaFile-(optional) [yes/no]. When set to yes the adapter updates the schema.ini file with the current file name.
Note: Use this attribute only when you want the adapter to change the schema.ini file (database for text files).
QueryCollection-collection of queries (more than one query can be defined in one Adapter).
- PreliminaryCheck-(optional [yes/no]). The adapter checks the queries when it connects to the database. If this attribute set to "no" the adapter checks the queries by executing them without any change, the adapter runs on the return records later and does not read them again. If it set to "yes" the adapter replaces each "where" string in the select statement with "where 1=2 and" and only than executes the queries. Use this option if you want to check all the queries before the adapter starts to run over the records and when this addition dramatically reduces the query time. Note-Some of the providers do not optimize the query process and when the query is legal the execution takes the same time as it takes without the addition.
- ExternalCommand-(optional). external command that is executed before the adapter starts a new reading cycle.
- Command-name of batch file that is to be executed.
- SleepTime-positive integer number that indicates, in seconds, how long to wait before running the command again.
Query-specify the query attributes.
- QueryName-name for the query.
- IsActive-(optional) [yes/no]. When set to "no" the adapter does not read records from this file.
- InputFormat-the InputFormat associated with this query. The Adapter uses the InputFormat to extract the data from the source record.
- SleepTime-time in seconds in which the adapter goes down in order to wait for new data to arrive.
- Critical-(optional) [yes/no]. If set to "yes" the adapter stops immediately when it finds an error in the query. The use of the option "no" is when it is a known error which is resolved without changing the configuration file.
- TransactionMode-(optional) [implicit/explicit]. If set to explicit, the adapter initiates a database transaction before the query. This option solves several problems in huge and complicated queries.
- RollbackSegementName (optional). Defines which rollback segment the adapter uses. Otherwise the database chooses the rollback segment.
SelectStatement - contains the statement selected to be executed on the source database.
- AutoCompleteQuery-(Optional) [yes/no]. If set to "yes" the adapter automatically concatenates a where statement to the specified query that does as follows:
- Creating where statement that will bring only new values based on the key fields.
- Ordering the results statement based on the key fields.
QueryKeyFields-defines the fields to start the next data retrieving:
- KeyField:
- Name-the name of the field, taken from the fields of the query.
- Sort-(optional) [asc/desc]. Data sorting method (ascending/ descending).
- Function-(optional). Use this attribute if a special function should be operate on this field, to combine the field value in the function use (:fieldname). For example using the Oracle date function with a field name Ts-Function="to_date(':ts','dd/mm/yyy')"
- ValueLeftWrapper-(optional). Use this attribute to concatenate characters before the value of the field. The default for string and date fields is "'" (apostrophe).
- ValueRightWrapper-(optional). Use this attribute to concatenate characters after the value of the field. The default for string and date fields is "'" (apostrophe).
- NameLeftWrapper-(optional). Use this attribute to concatenate characters before the name of the field. The default is null string.
- NameRightWrapper-(optional). Use this attribute to concatenate characters after the name of the field. The default is null string.
- IsPrimaryKey-(optional) [yes/no]. When set to "no", this key is not used by the adapter in the automatic where statement in the query.
- DefaultInitialValue-(optional). If the SelectInitialValues query does not return records, the adapter takes the default from here. If there are some key fields, all key fields must have this attribute.
- SelectInitialValues-a select statement that provides the initial values for the first where statement (When the control file is empty).
Note: The order of the values must be in the same order of the Field elements for this QueryKeyFields and return a result for each Field.